-
Pls Help me to convert this DB2 query to Oracle
select * from hc0mstr a inner join (select distinct(rl0to#),rl0fr#
from rl0reln where rl0rltyp in('BILLNG','SERVCE'')
and rl0totyp in('PHY','MBX')) as b on a.hc0hc#=b.rl0fr# left outer join lc0mstr c on
c.lc0lc#=b.rl0to#
I am using inner join first then a left outerjoin
Thanx
arshad
-
First, let me say 'what an absolutely horrid statement'. :)
The table names are terrible
The column names are terrible
Hard-coded values are used
A Select * is used.
There are *many* things that need to be done to this database, not to mention the statement ;)
However, back to the problem at hand. I would gladly convert this for you except that I have no idea what the following part does:
select distinct(rl0to#),rl0fr#
How can you select distinct on only one column from a column list?
If you could please explain, with a simple example, what this syntax would return, I can convert the rest of the statement for you.
- Chris
-
Hi Chris,
it's my mistake I haven't checked the query. I was thinking of the joins. distinct to a specific coluln doesn't have any meaning.
if the query is like this
select * from hc0mstr a inner join (select rl0to#,rl0fr#
from rl0reln where rl0rltyp in('BILLNG','SERVCE',)
and rl0totyp in('PHY','MBX')) as b on a.hc0hc#=b.rl0fr# left outer join lc0mstr c on c.lc0lc#=b.rl0to#
Pls. help me to convert to Oracle
Regards
Arshad
-
Okay, the literal translation of the query would be:
SELECT
---*
FROM
---HC0MSTR---A,
---(
---SELECT
------RL0TO#,
------RL0FR#
---FROM
------RL0RELN
---WHERE
------RL0RLTYP IN---('BILLNG','SERVCE')---AND
------RL0TOTYP IN---('PHY','MBX')
---) ---------B,
---LC0MSTR---C
WHERE
---B.RL0FR#------=---A.HC0HC#---AND
---C.LC0LC#---(+)=---B.RL0TO#
However, I don't see the resoning behind making the query on B a sub-query. Therefore, the following query would be cleaner and give you the same result:
SELECT
---*
FROM
---HC0MSTR---A,
---RL0RELN---B,
---LC0MSTR---C
WHERE
---B.RL0RLTYP---IN---('BILLNG','SERVCE')---AND
---B.RL0TOTYP---IN---('PHY','MBX')---------AND
---A.HC0HC#------=---B.RL0FR#---------------AND
---C.LC0LC#---(+)=---B.RL0TO#
Of course, I'm assuming that you would replace the * with an actual list of columns, because the two statements *do* return different columns.
Hope this helps,
- Chris
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
|