• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1958
  • Last Modified:

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
0
Dan_Wong
Asked:
Dan_Wong
3 Solutions
 
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
 
peter57rCommented:
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
 
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
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
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
 
shanesuebsahakarnCommented:
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
 
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now