RE: [sqr-users] dynamic where clause in subselect

[James Womeldorf <jwomeldo@fastenal.com>]

Hi Bob,
What are the values of the two dynamic where variables?
Is it dying during compilation or during execution?
Jim

-----Original Message-----
From: sqr-users-bounces+jwomeldo=fastenal.com@sqrug.org
[mailto:sqr-users-bounces+jwomeldo=fastenal.com@sqrug.org]On Behalf Of
Bob Stone
Sent: Thursday, April 21, 2005 11:22 AM
To: sqr-users@sqrug.org
Subject: [sqr-users] dynamic where clause in subselect


Hi there...i'm having a few problems with using a dynamic where clause
within a subselect in the from clause (i forget the technical term for
that).  If I comment out the [$month_where_clause_1] the SQL runs in the
SQR.  When I have it in there it says 'ORA-00907: missing right parenthesis'
meaning that it doesn't get as far as the ' monthcd desc )' of the order
clause.  The [] stop it.  

Has anybody seen this before?  Any work arounds?

I would really like to do this date logic (it's confusing because the table
was made ~not by me, mind you~ with the 'date' being kept in a 'month'
column and a 'year' column.  since we only care what month it is..), and I
would really like to not rewrite this sql (it was confusing enough the first
time).   Let me know if you see anything.

-------------------------------------------------------------
BEGIN-SELECT
calendar_year     &year
monthcd_num       &monthcd
full_date         &full_date

  let $full_date = &full_date
  put $full_date into MONTHS_RUN_FOR(#monthcnt) month  !YYYY-MM-DD

  let #monthcnt = #monthcnt + 1
FROM (select distinct calendar_year, monthcd, to_number(monthcd) as
monthcd_num, calendar_year || '-' || monthcd || '-01' as full_date
      from sysadm.ps_fas_scan_usage 
      where 1=1 
[$month_where_clause_1]
         order by calendar_year desc, monthcd desc )
WHERE 1 = 1 
[$month_where_clause_2]
END-SELECT
---------------------------------------------------


Thanks

Bob Stone  
Fastenal PS AR/Billing Developer
(507) 453-8514


_______________________________________________
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