Solved

CONVERT ORACLE SQL TO EQUIVALENT ACCESS SQL ( using Oracle "dual" table )

Posted on 2004-09-09
9
1,859 Views
Last Modified: 2008-01-16
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
Comment
Question by:Dan_Wong
9 Comments
 
LVL 41

Expert Comment

by:shanesuebsahakarn
ID: 12017609
"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
 
LVL 77

Assisted Solution

by:peter57r
peter57r earned 50 total points
ID: 12017631
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
 
LVL 41

Expert Comment

by:shanesuebsahakarn
ID: 12017678
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
Backup Your Microsoft Windows Server®

Backup all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

 
LVL 3

Author Comment

by:Dan_Wong
ID: 12017808
So what does this subquery do ?

i.e. what 's the purpose of -

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

?

d.
0
 
LVL 44

Assisted Solution

by:GRayL
GRayL earned 50 total points
ID: 12017939
0
 
LVL 41

Accepted Solution

by:
shanesuebsahakarn earned 400 total points
ID: 12017986
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
 
LVL 3

Author Comment

by:Dan_Wong
ID: 12018083
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
 
LVL 41

Expert Comment

by:shanesuebsahakarn
ID: 12018143
Yes, that's right.
0
 
LVL 3

Author Comment

by:Dan_Wong
ID: 12018164
o.k.

Thanks All.



0

Featured Post

Simplifying Server Workload Migrations

This use case outlines the migration challenges that organizations face and how the Acronis AnyData Engine supports physical-to-physical (P2P), physical-to-virtual (P2V), virtual to physical (V2P), and cross-virtual (V2V) migration scenarios to address these challenges.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article is a continuation or rather an extension from Cascading Combos (http://www.experts-exchange.com/A_5949.html) and builds on examples developed in detail there. It should be understandable alone, but I recommend reading the previous artic…
I see at least one EE question a week that pertains to using temporary tables in MS Access.  But surprisingly, I was unable to find a single article devoted solely to this topic. I don’t intend to describe all of the uses of temporary tables in t…
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.

776 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question