1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

Complex view query with multiple lookups

Discussion in 'SQL PL/SQL' started by RonuRaj, Apr 22, 2015.

  1. RonuRaj

    RonuRaj Active Member

    Messages:
    2
    Likes Received:
    0
    Trophy Points:
    55
    I have four tables named HOLDER,ADDRESS,CLNT,DETAIL

    ADDRESS table having columns ST_ADDR1,CITY,STATE,ZIPCODE.

    we need to write a view on ADDRESS table for the above columns according the logic given by below for each column.

    ST_ADDR1
    ------------------------------------
    Join HOLDER.NUM = ADDRESS.KEY_ID
    AND ADDRESS.ADDR_TYPE = 'LA' then
    take HOLDER.L_ID
    IF HOLDER.L_ID > 0 then look up on DETAIL
    CLNT.L_ID = DETAIL.L_CD
    AND CLNT.I_CD = DETAIL.I_CD
    AND DETAIL.DT <= SYSDATE
    AND (DETAIL.EXP_DT is null or DETAIL.EXP_DT >= SYSDATE) then
    take DETAIL.AD_DIS

    CITY
    ---------------------------------------
    Join HOLDER.NUM = ADDRESS.KEY_ID
    AND ADDRESS.ADDR_TYPE = 'LA' then
    take HOLDER.L_ID
    IF HOLDER.L_ID > 0 then look up on DETAIL
    CLNT.L_ID = DETAIL.L_CD
    AND CLNT.I_CD = DETAIL.I_CD
    AND DETAIL.DT <= SYSDATE
    AND (DETAIL.EXP_DT is null or DETAIL.EXP_DT >= SYSDATE) then
    take DETAIL.CITY_D

    STATE
    ---------------------------------
    Join HOLDER.NUM = ADDRESS.KEY_ID
    AND ADDRESS.ADDR_TYPE = 'LA' then
    take HOLDER.L_ID
    IF HOLDER.L_ID > 0 then look up on DETAIL
    CLNT.L_ID = DETAIL.L_CD
    AND CLNT.I_CD = DETAIL.I_CD
    AND DETAIL.DT <= SYSDATE
    AND (DETAIL.EXP_DT is null or DETAIL.EXP_DT >= SYSDATE) then
    take DETAIL.STATE_CD

    ZIPCODE
    ----------------------------------
    Join HOLDER.NUM = ADDRESS.KEY_ID
    AND ADDRESS.ADDR_TYPE = 'LA' then
    take HOLDER.L_ID
    IF HOLDER.L_ID > 0 then look up on DETAIL
    CLNT.L_ID = DETAIL.L_CD
    AND CLNT.I_CD = DETAIL.I_CD
    AND DETAIL.EFF_DT <= SYSDATE
    AND (DETAIL.EXP_DT is null or DETAIL.EXP_DT >= SYSDATE) then
    take DETAIL.ZIP_CD


    Kindly help me to write a view by using the above logic mentioned.

    Please help me
     
  2. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,345
    Likes Received:
    347
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    This looks like classwork to me. What have you done to try to solve this? Post your work and we'll be happy to help you fix it.
     
  3. RonuRaj

    RonuRaj Active Member

    Messages:
    2
    Likes Received:
    0
    Trophy Points:
    55
    Hi David thanks for ur reply.

    I have tried the below query. Please correct me.

    CREATE VIEW P_VIEW
    AS
    SELECT CASE WHEN holder.l_id > 0
    THEN detail.ad_dis
    ELSE address.st_addr1 END AS addr1
    , CASE WHEN holder.l_id > 0
    THEN detail.city_d
    ELSE address.city END AS city
    , CASE WHEN holder.l_id > 0
    THEN detail.state_cd
    ELSE address.state END AS state
    , CASE WHEN holder.l_id > 0
    THEN detail.zip_cd
    ELSE address.zipcode END AS zipcode
    FROM holder
    LEFT OUTER
    JOIN address
    ON address.key_id = holder.num
    AND address.addr_type='LA'
    LEFT OUTER
    JOIN clnt
    ON clnt.l_id = holder.l_id
    LEFT OUTER
    JOIN detail
    ON detail.l_cd = clnt.l_cd
    AND detail.i_cd = clnt.i_cd
    AND DETAIL.DT < sysdate
    AND (
    detail.exp_dt IS NULL
    OR detail.exp_dt >= SYSDATE
    )
     
  4. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,345
    Likes Received:
    347
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    What is the problem with the query you posted? You can't give us code without saying why it isn't working. You also need to post any error messages you may see. If the query takes a long time to run you also need to post the query execution plan so we can see what the optimizer is doing.


    The more you tell us the better we can assist.