[sqr-users] Dynamic SQL - Select Variables in Sub-Select

["Jessie Dickenson" <JMDICKENSON@rainbow-media.com>]

Hi,

I have a problem when I use variables in the SELECT statement in a
Sub-Select. I am attaching the code here below. 

Purpose of the query:
Get a single row for each product, with order values from the 2 weeks,
dates for which have to be passed as variables of some sort. When I
execute the sub-select, this would result in a cartesian set & that's
why I am using the aggregate function, SUM(SUBQRY.METHOD_1_WEEK_1), ...

Query Results:
When I use method_3 (i.e. use the absolute values) this query works
fine. It fails for method_1 & method_2. 

I know that I am doing something incorrectly here. I would greatly
appreciate your help. Thanks in advance.

- Jessie

BEGIN-PROCEDURE REPORT_DETAILS
        
        Let $week_1_order_str = 'DECODE(TRUNC(TABLE_1.START_DATE,
''DD''), TO_DATE(''' ||
                                                                                
        '03/03/2003'
|| ''', ''MM/DD/YYYY''), SUM(TABLE_2.VALUE)/ 30 ,0) '
        
        Let $week_2_order_str = 'DECODE(TRUNC(TABLE_1.START_DATE,
''DD''), TO_DATE(''' ||
                                                                                
        '03/10/2003'
|| ''', ''MM/DD/YYYY''), SUM(TABLE_2.VALUE)/ 30 ,0) '
                                                                                
        
        Let $tmp_date = 'TO_DATE(''' || '03/03/2003' || ''',
''MM/DD/YYYY'')'  
        
BEGIN-SELECT ON-ERROR=SQR_ERROR(1,'REPORT_DETAILS') 
SUBQRY.PRODUCT                                  &product ()  
                                                                on-break
                                                                level=1
                                                                
save=$curr_product
                                                                
after=Aft_Product_Change
                                                                print=never

SUM(SUBQRY.METHOD_1_WEEK_1)                     &method_1_week_1
SUM(SUBQRY.METHOD_2_WEEK_1)                     &method_2_week_1
SUM(SUBQRY.METHOD_3_WEEK_1)         &method_3_week_1
SUM(SUBQRY.METHOD_1_WEEK_2)                     &method_1_week_2

        Move &method_1_week_1 to #method_1_week_1
        Move &method_2_week_1 to #method_2_week_1
        Move &method_3_week_1 to #method_3_week_1
        Move &method_1_week_2 to #method_1_week_2
        
FROM
        (SELECT     
                TABLE_1.PRODUCT                                                 
        AS
product,
                
                $week_1_order_str                                               
        AS
method_1_week_1,
                
                DECODE(TRUNC(TABLE_1.START_DATE, 'DD'), 
                                $tmp_date, SUM(TABLE_2.VALUE)/ 30 
                                ,0)
                                                                AS
method_2_week_1,
                                
                DECODE(TRUNC(TABLE_1.START_DATE, 'DD'),
                                TO_DATE('03/03/2003',
'MM/DD/YYYY'),SUM(TABLE_2.VALUE)/ 30 
                                ,0)                             
                                        AS method_3_week_1,
                
                $week_2_order_str
                                                        AS
method_1_week_2
        
        FROM
                SCHEMA.TABLE_1          TABLE_1, 
                SCHEMA.TABLE_2          TABLE_2
                
        WHERE 
                TABLE_1.VALUE_CODE      = TABLE_2.VALUE_CODE
        GROUP BY  
                TABLE_1.PRODUCT,
                TABLE_1.START_DATE
        ) SUBQRY
GROUP BY
        SUBQRY.PRODUCT
ORDER BY
        1
END-SELECT
END-PROCEDURE REPORT_DETAILS

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