Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1915
  • 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
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
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

Featured Post

Microsoft Certification Exam 74-409

VeeamĀ® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

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