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

[Bob Stone <bstone@fastenal.com>]

The 'dynamic' part of it is that 90% of the time I want it to be ' ' (no
where clause)...but the rest of the time it might have that extra where
clause.  Yes, I could set up about 4 different non-dynamic SQLs that only
get run inside certain IF statements, but performance isn't an issue in a
Report that runs in under 2 seconds.

I dont care how fast it runs, there's about 2-3000 rows in that table, 1337
rows in the other table.  everything's fast. I just wanted it to run.  It
didn't run because it wasn't compiling.  SQR was trying to parse out the SQL
at compile time (it must not have noticed the 2 dynamic sections, which
should make it parse at run time) and it got to the [] and was confused
because it thought it was looking for the right paren ).  

Jim Womeldorf had the solution to incude more useless dynamic sections right
after the FROM to make sure it knows that SQL section is dynamic (run time
parsing).  It worked for me.  I was able to compile and the SQR compiled and
ran fine.

let $necessary = ''
BEGIN-SELECT
 ....
FROM [$necessary] (select  ....
      from [$necessary] 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 for everyone's suggestions.

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


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

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