[sqr-users] RE: Dynamic table names

["White, Denise" <DEWhite@vicr.com>]

Ivan,

I would try it like this:

move ', PS_JRNL_HEADER_FS Z1' to $from_tables

FROM PS_CUSTOMER m,PS_ENTRY_TYPE_TBL B,PS_ITEM_ACTIVITY
A,ps_cust_prd_info_l cp,ps_cust_type_bu_l ct [$from_tables]

The only thing that appears to be variable is that one table.  I've never tried 
it, but I doubt SQR would take well to having some tables listed twice, with 
the same alias.

HTH,

Denise M. White
Sr. Software Engineer
HR/Payroll Systems
Vicor Corporation
dewhite@vicr.com
------------------------------

Message: 8
Date: Mon, 26 Apr 2004 11:10:07 -0500
From: "Turner, Ivan" <Ivan.Turner@qwest.com>
Subject: [sqr-users] Dynamic table names


Logic dictates using different tables based on user choices.  I hate
program maintenance, but it goes with the territory!


an assignment takes place such as this in the program based on a user
choice.
move 'PS_ITEM_ACTIVITY A,PS_CUSTOMER m,PS_ENTRY_TYPE_TBL B,
PS_JRNL_HEADER_FS Z1,ps_cust_prd_info_l cp,ps_cust_type_bu_l ct ' to
$from_tables

But the driving query doesn't include table ps_jrnl_header_fs in the
statement below.  Is variable $from_tables cumulative?  Is there no need
to incluse it in the driving query's select?

SELECT ...

FROM [PS_CUSTOMER m,PS_ENTRY_TYPE_TBL B,PS_ITEM_ACTIVITY
A,ps_cust_prd_info_l cp,ps_cust_type_bu_l ct : $from_tables]

WHERE  [$where]
AND A.ENTRY_TYPE = B.ENTRY_TYPE
AND B.CLASS_L IN ($Adj,$Occ,$Inv,$Pay)
AND A.CUST_ID = m.CUST_ID
and m.setid = b.setid
and a.cust_id = cp.cust_id(+)
[$Where_IR_State] !Outer join to get the SAM IR account's state value
(arl50000.sqc).
[$where_rj]   !Logic to retrieve RJ exception/non-exception entries
(arl50000.sqc).
[$where_HIST]   !Use historic cust_type where available, else use
pc_customer cust_type (cust_type.sqc)
[$exclude_am]   !Exclude entry_type 195 from output.
AND m.SETID = 'LCI'
[$order_by]   !Order of report breaks



_______________________________________________
sqr-users mailing list
sqr-users@sqrug.org
http://www.sqrug.org/mailman/listinfo/sqr-users