Bob,
Just to make sure that you don't have a bug there, I suggest you add ON-ERROR
clause to your BEGIN-SELECT statement, and add show_sql procedure where you
display the oracle statement being executed:
BEGIN-SELECT ON-ERROR=show_sql
.
.
END-SELECT
END-PROCEDURE
BEGIN-PROCEDURE show_sql
SHOW $sql-error
SHOW 'Error in SQL statement: ' $sql-text
END-PROCEDURE show_sql
Bob Stone <bstone@fastenal.com>
Sent by: sqr-users-bounces+olga.gal=bbh.com@sqrug.org
To
sqr-users@sqrug.org
04/21/2005 12:22 PM
cc
Subject
Please respond to
[sqr-users] dynamic where clause in subselect
"This list is for discussion about the SQR database reporting
language from Hyperion
Solutions." <sqr-users@sqrug.org>
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