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

[Bob Stone <bstone@fastenal.com>]

for the specific run i was doing the dynamic where clause was ' '  ...with
no parens.  

It's not seeing the 2nd paren (which is there), that's the problem.  And
since it finds that 2nd paren when I comment out the
![$month_where_clause_1], that leads me to believe that the []'s of that
dyamic where clause are what's causing it not to read the order by clause.

-----Original Message-----
From: sqr-users-bounces+bstone=fastenal.com@sqrug.org
[mailto:sqr-users-bounces+bstone=fastenal.com@sqrug.org]On Behalf Of
Knapp, Richard
Sent: Thursday, April 21, 2005 11:31 AM
To: This list is for discussion about the SQR database reportinglanguage
from Hyperion Solutions.
Subject: RE: [sqr-users] dynamic where clause in subselect



Sometimes the error message is correct.  Does your dynamic where string
have a missing right paren?

Richard Knapp
Database Programmer/Analyst
Institutional Research and Planning
University of Missouri System
573-882-8856
knappr@umsystem.edu

-----Original Message-----
From: sqr-users-bounces+knappr=umsystem.edu@sqrug.org
[mailto:sqr-users-bounces+knappr=umsystem.edu@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

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