-
problem converting this query to oracle
Hi friends,
could anybody pls help me in converting below access query into oracle query .
i am not able to understand from clause here .
SELECT DISTINCT item.SCALE_ID, item.ITEM_ID, item.COMPLETE_DT, long_txt_1.LONG_TXT AS Stemtxt, long_txt.LONG_TXT, scale.SCALE_NM
FROM (((version_item INNER JOIN (version_stem_txt INNER JOIN long_txt AS long_txt_1 ON version_stem_txt.STEM_TXT_ID = long_txt_1.LONG_TXT_ID) ON version_item.QUEST_ID = version_stem_txt.QUEST_ID) INNER JOIN long_txt ON version_item.RESP_ITEM_TXT_ID = long_txt.LONG_TXT_ID) INNER JOIN item ON version_item.ITEM_ID = item.ITEM_ID) INNER JOIN scale ON item.SCALE_ID = scale.SCALE_ID
WHERE (((item.SCALE_ID)=[Forms]![FrmFindMatchItems]![subfrmItem].[Form]![VerScale]) AND ((item.ITEM_ID)<>[Forms]![FrmFindMatchItems]![subfrmItem].[Form]![item_id]) AND ((item.COMPLETE_DT)<>#1/1/1950#) AND ((long_txt_1.LONG_TXT) Like "*" & [Forms]![FrmFindMatchItems]![SearchTxt] & "*"));
CAN anybody let me know if i am missing anything in below mentioned query ...
i cannot execute access query as i don't have access to cross check number of records
SELECT
DISTINCT item.SCALE_ID,
item.ITEM_ID,
item.COMPLETE_DT,
long_txt_1.LONG_TXT AS Stemtxt,
long_txt.LONG_TXT,
scale.SCALE_NM
From
long_txt long_txt_1 , long_txt , item , scale , version_item , version_stem_txt
where
version_stem_txt.STEM_TXT_ID = long_txt_1.LONG_TXT_ID
and version_item.QUEST_ID = version_stem_txt.QUEST_ID
and version_item.RESP_ITEM_TXT_ID = long_txt.LONG_TXT_ID
and version_item.ITEM_ID = item.ITEM_ID
and item.SCALE_ID = scale.SCALE_ID
-- and item.SCALE_ID = &scale_id
-- and item.ITEM_ID)<> &item_id
and item.COMPLETE_DT != to_date( '1/1/1950' , 'mm/dd/yyyy')
-- and long_txt_1.LONG_TXT Like '*%*'
Thanks for your help .
-
Re: problem converting this query to oracle
format your code using the [ code ] and [ / code ] tags, it's really hard to read otherwise
-
Hi dave,
sorry for not formatting code in the first place ..
pls find formatted code ...
if i am missing anything while converting to oracle sql
MS Access generated Code :
--------------------------
Code:
SELECT
DISTINCT item.SCALE_ID,
item.ITEM_ID,
item.COMPLETE_DT,
long_txt.LONG_TXT,
scale.SCALE_NM,
long_txt_1.LONG_TXT
FROM
scale INNER JOIN (((version_item INNER JOIN long_txt
ON version_item.RESP_ITEM_TXT_ID = long_txt.LONG_TXT_ID)
INNER JOIN item
ON version_item.ITEM_ID = item.ITEM_ID)
LEFT JOIN (long_txt AS long_txt_1
RIGHT JOIN version_stem_txt
ON long_txt_1.LONG_TXT_ID=version_stem_txt.STEM_TXT_ID)
ON version_item.QUEST_ID = version_stem_txt.QUEST_ID)
ON scale.SCALE_ID = item.SCALE_ID
WHERE
(((item.SCALE_ID)=[Forms]![FrmFindMatchItems]!
[subfrmItem].[Form]![VerScale])
AND ((item.COMPLETE_DT)>#1/1/1950#)
AND ((long_txt.LONG_TXT) Like "*" &
[Forms]![FrmFindMatchItems]![SearchTxt] & "*"));
oracle code :
-------------
Code:
CREATE OR REPLACE VIEW QRYMATCHITEMS_VW
( SCALE_ID, ITEM_ID, COMPLETE_DT, LONG_TXT, SCALE_NM,
STEM_TXT )
AS SELECT
DISTINCT item.SCALE_ID,
item.ITEM_ID,
item.COMPLETE_DT,
long_txt.LONG_TXT,
scale.SCALE_NM,
long_txt_1.LONG_TXT as stem_txt
FROM
scale , version_item , long_txt , item , long_txt long_txt_1 ,
version_stem_txt
where
version_item.RESP_ITEM_TXT_ID = long_txt.LONG_TXT_ID
and version_item.ITEM_ID = item.ITEM_ID
and long_txt_1.LONG_TXT_ID = version_stem_txt.STEM_TXT_ID
and version_item.QUEST_ID = version_stem_txt.QUEST_ID
and scale.SCALE_ID = item.SCALE_ID
AND item.COMPLETE_DT > to_date( '1/1/1950' , 'mm/dd/yyyy')
please advise if i am missing outer joins etc ...
-
oracle supports writing joins in that way, so you can write the joins in the same way if you prefer
-
Where are LEFT JOIN AND RIGHT JOIN in the Oracle code?
You have to use either (+) or LEFT OUTER JOIN.
Tamil
-
Code:
CREATE OR REPLACE VIEW QRYMATCHITEMS_VW
( SCALE_ID, ITEM_ID, COMPLETE_DT, LONG_TXT, SCALE_NM,
STEM_TXT )
AS SELECT
DISTINCT item.SCALE_ID,
item.ITEM_ID,
item.COMPLETE_DT,
long_txt.LONG_TXT,
scale.SCALE_NM,
long_txt_1.LONG_TXT as stem_txt
FROM
scale , version_item , long_txt , item , long_txt long_txt_1 ,
version_stem_txt
where
version_item.RESP_ITEM_TXT_ID = long_txt.LONG_TXT_ID
and version_item.ITEM_ID = item.ITEM_ID
and long_txt_1.LONG_TXT_ID = version_stem_txt.STEM_TXT_ID (+)
and version_item.QUEST_ID = version_stem_txt.QUEST_ID
and scale.SCALE_ID = item.SCALE_ID
AND item.COMPLETE_DT > to_date( '1/1/1950' , 'mm/dd/yyyy')
should this statement be interpreted like this
Code:
LEFT JOIN (long_txt AS long_txt_1
RIGHT JOIN version_stem_txt
ON long_txt_1.LONG_TXT_ID=version_stem_txt.STEM_TXT_ID)
Code:
long_txt_1.LONG_TXT_ID = version_stem_txt.STEM_TXT_ID (+)
-
no need to convert it, the syntax is fine and is perfectly valid (the joins)
-
Hi dave ,
do i need to use (+) operator ...
for left join and right join
hi tamilselvam ,
where should i use + operator ...
I don't understand this bit ( as i don't see outer join )
-
just keep the join syntax from your old version
Last edited by davey23uk; 01-10-2005 at 04:38 PM.
-
Originally posted by prakashs43
I don't understand this bit ( as i don't see outer join )
The docs explain it quite well...maybe you should try them first next time???
Jeff Hunter
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|