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

[Bob Stone <bstone@fastenal.com>]

Jim (and Jeff),

The $month_where_clause_1 is created to allow a date-range run of off the
run cntl panel.  99% of the time they'll probably just run the default, but
it's something i added and I would like it to work if possible.  

let $month_where_clause_1 = 'and (   (calendar_year = ' || $to_date_yr   ||
' and to_number(monthcd) <= ' || $to_date_mon   || ') ' || '     or
(calendar_year = ' || $from_date_yr || ' and to_number(monthcd) > '  ||
$from_date_mon || ') )'

example:  date range (12-01-2004, 04-01-2005).  I grab out $to_date_yr
(=2005) $to_date_mon (=04), etc.  
$month_where_clause_1 = 
   "and ( (calendar_year = 2005 and to_number(monthcd) <= 4)
        or(calendar_year = 2004 and to_number(monthcd) > 12) )"  

I guess that's not exactly perfect (if run for 24 or 36 months, for
example), but hey, that's a different problem.

It's dying during compilation.  



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


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

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