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

["Alexander, Steve" <Steven.Alexander@sanjoseca.gov>]

A $month_where_clause_1 will probably not make your query run much faster.
The to_number function eliminates the possibility of using indexes, so
you're examining every row anyway.  The "or" in this example will make it
harder for your SQL optimizer.    You might be no worse off leaving out the
dynamic clause.

-----Original Message-----
From: Bob Stone [mailto:bstone@fastenal.com]
Sent: Thursday, April 21, 2005 9:45 AM
To: 'This list is for discussion about the SQR database reporting
languagefrom Hyperion Solutions.'
Subject: RE: [sqr-users] dynamic where clause in subselect


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

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