Results 1 to 5 of 5
  1. #1
    Join Date
    Sep 2011
    Posts
    220

    Unanswered: Select different table

    Hi;

    Based on the 'SRVE' value the Joining Table(REF_TABLE / LINK_TABLE) needs to be replaced
    I tried the below query,but it is not working
    Code:
    SELECT       
           TAB1.PLACE       
          ,TAB1.LOCAT    
          ,TAB1.ID_NUM         
          ,TAB2.ID_DES 
          
     FROM   
        BASE_TABLE TAB1                    
       INNER JOIN  
         (SELECT TAB2.PLACE,TAB2.LOCAT,TAB2.ID_NUM,TAB2.ID_DES 
    FROM                    
       (SELECT CASE WHEN (SRVE ='D')
                         
                           THEN 'REF_TABLE'
                     WHEN (SRVE ='E')
                          THEN 'LINK_TABLE'  
                      
            END )As TAB2      
             
    ) AS B
        
       ON                                                
         TAB1.PLACE           = TAB2.PLACE          
       AND  TAB1.LOCAT      = TAB2.LOCAT   
       AND  TAB1.ID_NUM     = TAB2.ID_NUM
    Please help

  2. #2
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    You cannot do that. you will need to LEFT OUTER join to each table like this:

    Code:
    left outer join REF_TABLE as r on (tab1.place = r.place and tab1.locat = r.locat and tab1.id_num = r.idnum and srve = 'D')
    left outer join LINK_TABLE as l on (tab1.place = l.place and tab1.locat = l.locat and tab1.id_num = l.idnum and srve = 'E')
    Andy

  3. #3
    Join Date
    Nov 2011
    Posts
    334
    Maybe you can use union all
    Code:
    SELECT       
           TAB1.PLACE       
          ,TAB1.LOCAT    
          ,TAB1.ID_NUM         
          ,TAB2.ID_DES 
     FROM   
        BASE_TABLE TAB1                    
       INNER JOIN  
         (SELECT PLACE,LOCAT,ID_NUM,ID_DES 
    FROM   REF_TABLE ) TAB2
        ON   TAB1.PLACE           = TAB2.PLACE          
       AND  TAB1.LOCAT      = TAB2.LOCAT   
       AND  TAB1.ID_NUM     = TAB2.ID_NUM
        AND  SRVE ='D'
    UNION ALL
       SELECT       
           TAB1.PLACE       
          ,TAB1.LOCAT    
          ,TAB1.ID_NUM         
          ,TAB3.ID_DES 
     FROM   
        BASE_TABLE TAB1                    
       INNER JOIN  
         (SELECT PLACE,LOCAT,ID_NUM,ID_DES 
    FROM   REF_TABLE ) TAB3
        ON   TAB1.PLACE           = TAB3.PLACE          
       AND  TAB1.LOCAT      = TAB3.LOCAT   
       AND  TAB1.ID_NUM     = TAB3.ID_NUM
        AND  SRVE ='E'

  4. #4
    Join Date
    Sep 2011
    Posts
    220
    Thanks for all, In the real query has multiple Joins and length is more,So that i want to use the way of 'Using Case' statement ,If i use the 'Union' , again the query size will be larger.

    Please suggest any approach..

    DB2 9.1 Z/os

  5. #5
    Join Date
    Oct 2009
    Location
    221B Baker St.
    Posts
    486
    If you intend to do many things in a query, be prepared to implement more code. . .

    Your first concern should be that the query runs correctly Every time. Then, if the length of the code really is an issue, consider alternatives.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •