Help with sql

[Sandy]

Hi
I have been given a rather hairy piece of sql to put into a module but I
have not been able to properly convert it to SQL that access can process.

Can anyone help with this?

The sql is:

update cdb_data a from cdb_description b set item_date =
date(varchar(b.start_year)+'.1.1') + date(varchar(b.start_period-1)+'
days') - date(varchar((locate('Mon Tue Wed Thu Fri
',dow(date(varchar(b.start_year)+'.1.1')+ date(varchar(b.start_period-1)+'
days'))))/4)+' days') + date(varchar(((a.period-1+(locate('Mon Tue Wed Thu
Fri ',dow(date(varchar(b.start_year)+'.1.1')+
date(varchar(b.start_period-1)+' days')))/4))/5)*7) + 'days') +
date(varchar(mod((a.period-1+(locate('Mon Tue Wed Thu Fri ',
dow(date(varchar(b.start_year)+'.1.1')+date(varchar(b.start_period-1)+'
days')))/4)),5)) + ' days') where a.identifier = b.identifier And
b.periodicity = 366 and b.data_type = 1 and a.item_date = '';

I think that 'locate' needs to change to 'instr' and 'date' needs to change
to 'day' but try as I might, I am not having much success with converting
the sql.

Thanks in advance
Sandy

 

[Bob Hairgrove]

Hi
I have been given a rather hairy piece of sql to put into a module but I
have not been able to properly convert it to SQL that access can process.

Can anyone help with this?

The sql is:

update cdb_data a from cdb_description b set item_date =
date(varchar(b.start_year)+'.1.1') + date(varchar(b.start_period-1)+'
days') - date(varchar((locate('Mon Tue Wed Thu Fri
',dow(date(varchar(b.start_year)+'.1.1')+ date(varchar(b.start_period-1)+'
days'))))/4)+' days') + date(varchar(((a.period-1+(locate('Mon Tue Wed Thu
Fri ',dow(date(varchar(b.start_year)+'.1.1')+
date(varchar(b.start_period-1)+' days')))/4))/5)*7) + 'days') +
date(varchar(mod((a.period-1+(locate('Mon Tue Wed Thu Fri ',
dow(date(varchar(b.start_year)+'.1.1')+date(varchar(b.start_period-1)+'
days')))/4)),5)) + ' days') where a.identifier = b.identifier And
b.periodicity = 366 and b.data_type = 1 and a.item_date = '';

I think that 'locate' needs to change to 'instr' and 'date' needs to change
to 'day' but try as I might, I am not having much success with converting
the sql.

Thanks in advance
Sandy

Where are the tables stored -- locally in Access, or on a database on theserver
running some other kind of DBMS software?

If they are local Access tables, you need to tell us what the data types of the
fields are (in particular, "item_date") and whether or not these are nullable.
Then you will have to replace the function calls "date()" and "dow()" with the
Access functions DateSerial() and DatePart() -- you can look up the syntax in
the Access help file.

However, if these tables are stored in a different kind of database running on
the server, you can create a passthrough query in Access preserving the original
SQL syntax which the server can understand. As the name implies, Access doesn't
try to parse the SQL text but passes it on to the database server runningon the
client. This will also perform much better than doing it with Access syntax, but
having the tables stored remotely.

 

[Sandy]

The original tables were stored on a server running an Ingres??? database
however, we have imported the data into Access and need to perform the same
queries on this table. start_period and start_years are integers (not
required) I have worked out most of the equivalent function calls but for
the life of me, can't get it running. I keep getting told there are too
many brackets or simply that I have an invalid syntax. It's very
frustrating.

I sincerely appreciate any help with this. I have 6 more to convert after
this one and figure that once I get one, I will be able to get the others
converted.

Sandy

Hi
I have been given a rather hairy piece of sql to put into a module but I
have not been able to properly convert it to SQL that access can process.

Can anyone help with this?

The sql is:

update cdb_data a from cdb_description b set item_date =
date(varchar(b.start_year)+'.1.1') + date(varchar(b.start_period-1)+'
days') - date(varchar((locate('Mon Tue Wed Thu Fri
',dow(date(varchar(b.start_year)+'.1.1')+ date(varchar(b.start_period-1)+'
days'))))/4)+' days') + date(varchar(((a.period-1+(locate('Mon Tue Wed Thu
Fri ',dow(date(varchar(b.start_year)+'.1.1')+
date(varchar(b.start_period-1)+' days')))/4))/5)*7) + 'days') +
date(varchar(mod((a.period-1+(locate('Mon Tue Wed Thu Fri ',
dow(date(varchar(b.start_year)+'.1.1')+date(varchar(b.start_period-1)+'
days')))/4)),5)) + ' days') where a.identifier = b.identifier And
b.periodicity = 366 and b.data_type = 1 and a.item_date = '';

I think that 'locate' needs to change to 'instr' and 'date' needs to change
to 'day' but try as I might, I am not having much success with converting
the sql.

Thanks in advance
Sandy

Where are the tables stored -- locally in Access, or on a database on the
server
running some other kind of DBMS software?

If they are local Access tables, you need to tell us what the data types of
the
fields are (in particular, "item_date") and whether or not these are
nullable.
Then you will have to replace the function calls "date()" and "dow()" with
the
Access functions DateSerial() and DatePart() -- you can look up the syntax
in
the Access help file.

However, if these tables are stored in a different kind of database running
on
the server, you can create a passthrough query in Access preserving the
original
SQL syntax which the server can understand. As the name implies, Access
doesn't
try to parse the SQL text but passes it on to the database server running on
the
client. This will also perform much better than doing it with Access syntax,
but
having the tables stored remotely.

 

[John W. Vinson]

update cdb_data a from cdb_description b

This may be part of the problem. This is peculiar (INGRES??) syntax. If you
are trying to update fields in cdb_data based on values in cdb_description,
you need to use an INNER JOIN instead of the "from".

I'm totally buffaloed by all the VarChar and Date() and other INGRES-specific
functions, though. Could you explain in words what this query is intended to
accomplish, and what values it's working with?

John W. Vinson [MVP]

 

[Sandy]

Hi John
The table I am working with stores a date in a very peculiar way. There is a
start period and a start year. The start period is a number value from 1 to
365 (indicating the day of the year) and the start year (2000,2001 etc). The
sql I have works out the actual date of the data and inserts the date into
another table. Apparantly there is something funny with the dates where
extra calculations need to be done to account for some difference in the
start period (perhaps relating to the day of week). Anyway, I have several
of these functions to convert and this is the one that deals with data that
is daily (the others are monthly, annually etc.). I have tried to set up a
join and change the functions to access ones but keeps getting syntax
errors. Any further assistance would be greatly appreciated.

Sandy