#1
  1. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Apr 2009
    Posts
    7
    Rep Power
    0

    complex view query with multiple lookups


    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.
  2. #2
  3. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,734
    Rep Power
    4288
    Code:
    CREATE VIEW natrajdreams
    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
           )

    Comments on this post

    • Will-O-The-Wisp agrees
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

IMN logo majestic logo threadwatch logo seochat tools logo