Complex Query
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 4 of 4

Thread: Complex Query

  1. #1
    Join Date
    Mar 2001
    Posts
    6

    Unhappy

    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

  2. #2
    Join Date
    Nov 2000
    Location
    Baltimore, MD USA
    Posts
    1,339
    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

  3. #3
    Join Date
    Mar 2001
    Posts
    6
    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

  4. #4
    Join Date
    Nov 2000
    Location
    Baltimore, MD USA
    Posts
    1,339
    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
  •  



Click Here to Expand Forum to Full Width