Dan_Wong
asked on
CONVERT ORACLE SQL TO EQUIVALENT ACCESS SQL ( using Oracle "dual" table )
Hi All - wonder if anyone can help me with this ?
I am trying to convert an Oracle query in to an Access 2000 query.
I'm struggling with the following.
Within the FROM statement (of the oracle query) there is a subquery which looks like this ;
(select table1.field1, table1.field2 from table1_long_name table1, table2_long_name table2
where table1.cor_id = table2.id and table2.reference in ('name1','name2','name3','
union select 'MISSING NUMBER', 'MISSING NUMBER' from dual)
what would the equivalent Acess SQL be ?
Don't really understand this whole 'dual' caper at all ?
thank you,
dan
I am trying to convert an Oracle query in to an Access 2000 query.
I'm struggling with the following.
Within the FROM statement (of the oracle query) there is a subquery which looks like this ;
(select table1.field1, table1.field2 from table1_long_name table1, table2_long_name table2
where table1.cor_id = table2.id and table2.reference in ('name1','name2','name3','
union select 'MISSING NUMBER', 'MISSING NUMBER' from dual)
what would the equivalent Acess SQL be ?
Don't really understand this whole 'dual' caper at all ?
thank you,
dan
shanesuebsahakarn
"dual" looks to be a table name. The equvalent Access/Jet subquery would be identical to the SQL that you have posted above, if that is the case. It could be re-written using INNER JOINs but that would be generally unecessary.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
It actually wouldn't matter how many records the "Dual" table contains (although only one record would be faster) - since the UNION clause includes an implied DISTINCT statement.
ASKER
So what does this subquery do ?
i.e. what 's the purpose of -
union select 'MISSING NUMBER', 'MISSING NUMBER' from dual
?
d.
i.e. what 's the purpose of -
union select 'MISSING NUMBER', 'MISSING NUMBER' from dual
?
d.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Hmm o.k. i think that I understand that vaguely...
In which case if I create a table in Access called 'Dual' with a single record.
This should have an identical effect as whatever the original statement intended ?
d.
In which case if I create a table in Access called 'Dual' with a single record.
This should have an identical effect as whatever the original statement intended ?
d.
Yes, that's right.
ASKER
o.k.
Thanks All.
Thanks All.