Re: [sqr-users] dynamic SQL problem

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

Can you email the values for $WC_Where_1 value, $fromdate and
$thrudate?

On a bird's eye-view, I see no alias name as 'field' in the sub-query,
x. Out of curiosity, why are you grouping by  b.field1? Are there other
columns in the inline view, that you need the grouping?

Thanks, Jessie

>>> fbarchie@yahoo.com 03/09/05 4:47 AM >>>
Hi Sqr-Users

I have a problem with SQL containing dynamic where
clauses in a begin-select statement.  When SQR
compiles, it generates an error saying that it is
missing a right parenthesis, however if I remove the
dynamic SQL string it compiles with no errors:

Sample:

begin-select 
x.field
    do something
FROM (SELECT
b.field1
FROM table A, table B
WHERE B.field1 = $variable1
  AND A.field1 = B.field2
  AND A.field3 = B.field3
  AND A.field4 = $variable2
  [$WC_Where_1] 
  AND B.field5 BETWEEN $fromdate AND $thrudate 
  AND A.field6 = (SELECT MAX(A1.field6) FROM table A1
                  WHERE A1.field1 = A.field1
                    AND A1.field2 = A.field2
                    AND A1.field6 <= $thrudate)
GROUP BY B.field1
UNION
SELECT
d.field1
FROM table C, table D
WHERE D.field1 = $variable1
  AND C.field1 = D.field2
  AND C.field3 = D.field3
  AND C.field4 = $variable2
  [$WC_Where_1] 
  AND D.field5 BETWEEN $fromdate AND $thrudate 
  AND C.field6 = (SELECT MAX(C1.field6) FROM table C1
                  WHERE C1.field1 = C.field1
                    AND C1.field2 = C.field2
                    AND C1.field6 <= $thrudate)
GROUP BY B.field1) x
GROUP BY x.field
ORDER BY x.field
end-select

When it compiles the SQL error shows the following:

ORA-00907: missing right parenthesis
SQL:  SELECT X.FIELD  FROM (SELECT B.FIELD1 FROM TABLE
A,
      TABLE B WHERE A.FIELD1 = B.FIELD1 AND A.FIELD2 =
      B.FIELD2 AND A.FIELD3 = 'VALUE'  GROUP BY
B.FIELD1 

It appears the dynamic variable $WC_Where_1 is causing
the SQL to skip lines from the SQL and finish at the
group by, it doesn't go to the UNION or finish to SQL
to see there is a right parenthesis at the end.

If I remove the dynamic variable $WC_Where_1 the SQR
compiles the SQL successfully and generates a report. 


I want to introduce dynamic where clauses to filter
the data returned.

Any suggestions on what is going wrong would be
greatly appreciated.

The SQL above is an abridged version, there are 8
unions in the actual SQL.

Regards,
Daniel



        
                
__________________________________ 
Celebrate Yahoo!'s 10th Birthday! 
Yahoo! Netrospective: 100 Moments of the Web 
http://birthday.yahoo.com/netrospective/ 

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


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