Complex Query

[jarshad]

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

[chrisrlong]

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

[jarshad]

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

[chrisrlong]

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