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

    Unanswered: select qurery with diff tables

    Hi;

    please find the scenario

    if the current region is DER means,we have to get the rows from

    MAIN_TABLE_DER

    if the current region is RET means,we have to get the rows from

    MAIN_TABLE_RET

    current region is not the column name( we are passing thru host variables) like :ws-cur-reg


    select
    inv_no,
    inv_nme,
    inv_loc

    from tablename

    Please help me

  2. #2
    Join Date
    Apr 2006
    Location
    Belgium
    Posts
    2,514
    Provided Answers: 11
    is it not possible to create a dynamic statement
    select * from $tablename --> where you pass the tname
    prep and exec..
    Best Regards, Guy Przytula
    Database Software Consultant
    Good DBAs are not formed in a week or a month. They are created little by little, day by day. Protracted and patient effort is needed to develop good DBAs.
    Spoon feeding : To treat (another) in a way that discourages independent thought or action, as by overindulgence.
    DB2 UDB LUW Certified V7-V8-V9-V9.7-V10.1-V10.5 DB Admin - Advanced DBA -Dprop..
    Information Server Datastage Certified
    http://www.infocura.be

  3. #3
    Join Date
    Sep 2011
    Posts
    220
    we are not using dynamic sql...

    DB2 9.1 z/os ,the region name will be getting by host variables from COBOL

    Any possibility to use the case statement to get table name after FROM based on the Host variable value

  4. #4
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    Some ideas.
    (Not tesed)
    Please publish DB2 version/release/fixpack and platform OS.

    Example 1:
    Code:
    SELECT inv_no
         , inv_nme
         , inv_loc
     FROM  main_table_der
     WHERE :ws-cur-reg = 'DER'
    UNION ALL
    SELECT inv_no
         , inv_nme
         , inv_loc
     FROM  main_table_ret
     WHERE :ws-cur-reg = 'RET'

    Example 2-1: May not work on z/OS.
    Code:
    SELECT COALESCE(der.inv_no  , ret.inv_no ) AS inv_no
         , COALESCE(der.inv_nme , ret.inv_nme) AS inv_nme
         , COALESCE(der.inv_loc , ret.inv_nme) AS inv_nme
     FROM  (SELECT t.*
             FROM  main_table_der t
             WHERE :ws-cur-reg = 'DER'
           ) der
     FULL  OUTER JOIN
           (SELECT t.*
             FROM  main_table_ret t
             WHERE :ws-cur-reg = 'RET'
           ) ret
      ON   0=1

    Example 2-2: If Example 2-1 got error on z/OS, try this.
    Code:
    SELECT COALESCE(der.inv_no  , ret.inv_no ) AS inv_no
         , COALESCE(der.inv_nme , ret.inv_nme) AS inv_nme
         , COALESCE(der.inv_loc , ret.inv_nme) AS inv_nme
     FROM  (SELECT t.*
                 , 'DER' AS _id
             FROM  main_table_der t
             WHERE :ws-cur-reg = 'DER'
           ) der
     FULL  OUTER JOIN
           (SELECT t.*
                 , 'RET' AS _id
             FROM  main_table_ret t
             WHERE :ws-cur-reg = 'RET'
           ) ret
      ON   ret.t_id = der.t_id

    Example 3:
    Code:
    SELECT COALESCE(der.inv_no  , ret.inv_no ) AS inv_no
         , COALESCE(der.inv_nme , ret.inv_nme) AS inv_nme
         , COALESCE(der.inv_loc , ret.inv_nme) AS inv_nme
     FROM  sysibm.sysdummy1
     LEFT  OUTER JOIN
           main_table_der der
      ON   :ws-cur-reg = 'DER'
     LEFT  OUTER JOIN
           main_table_der der
      ON   :ws-cur-reg = 'RET'

Posting Permissions

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