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','name4')
   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
LVL 3
Dan_WongAsked:
Who is Participating?
 
shanesuebsahakarnConnect With a Mentor Commented:
The UNION SELECT blah blah is basically adding a single record to the subquery with two fields, both of which contain the word "MISSING NUMBER". It's a common technique that is often used in Access to add something like "(all records)" as an option in a combo box, for example.
0
 
shanesuebsahakarnCommented:
"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.
0
 
peter57rConnect With a Mentor Commented:
Hello Dan_Wong,

I think to make this work you just need to set up a table called Dual which contains 1 record of 1 field (any datatype but a number field defaulting to 1 would protect against duplicates).
I don't think there is any change needed to the SQL.

Pete
0
Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

 
shanesuebsahakarnCommented:
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.
0
 
Dan_WongAuthor Commented:
So what does this subquery do ?

i.e. what 's the purpose of -

union select 'MISSING NUMBER', 'MISSING NUMBER' from dual

?

d.
0
 
Dan_WongAuthor Commented:
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.
0
 
shanesuebsahakarnCommented:
Yes, that's right.
0
 
Dan_WongAuthor Commented:
o.k.

Thanks All.



0
All Courses

From novice to tech pro — start learning today.