[Date Prev][Date Next][Thread Prev][Thread Next]
[Author Index]
[Date Index]
[Thread Index]
[SQR-USERS Info]
[SQRUG Home Page]
RE: [sqr-users] Union issue - any ideas
I think the second selection block in a union shouldn't have its own list of
fields.
-----Original Message-----
From: Steve Cavill [mailto:steve.cavill@infoclarity.com.au]
Sent: Friday, August 15, 2003 11:47 PM
To: sqr-users@sqrug.org
Subject: RE: [sqr-users] Union issue - any ideas
There don't appear to be many bind variables to cause the error.
$TempPeriod looks like a possible candidate for the error - is it the right
datatype?
Try replacing it with a hardcoded value and see what happens.
Steve.
-----Original Message-----
From: sqr-users-admin@sqrug.org [mailto:sqr-users-admin@sqrug.org]On
Behalf Of Lynds,Rick
Sent: Saturday, 16 August 2003 8:34 AM
To: 'sqr-users@sqrug.org'
Subject: RE: [sqr-users] Union issue - any ideas
Clark,
In the union - I see where the returned value "posted_total_amt" is not
aliased. Could that be conflicting with the "&posted_total_amt=number" in
the first part of the statement?
Rick
-----Original Message-----
From: the dragon [mailto:ceprn@hotmail.com]
Sent: Friday, August 15, 2003 3:22 PM
To: sqr-users@sqrug.org
Subject: [sqr-users] Union issue - any ideas
Hi all,
It's late and my brain has flat-lined. Running
Platform: WINDOWS-NT
Database: ODBC to DB2/390
Locale: system
SQR Version: SQR for PeopleSoft/8.40/PC/Windows NT 4.0/DB2 6.1.0/Feb 21
2002
an sqr and having a problem. If I run this select (code below) without the
union, it works great, but I add a union and the sqr flips out with this
error:
(SQR 5528) DB2 SQL OPEN/EXECUTE error -301 in cursor 25:
SQL0301N The value of a host variable in the EXECUTE or OPEN statement
cannot
be used because of its data type. SQLSTATE=42895
Error on line 359:
(SQR 3723) Problem executing cursor.
BUT, if I copy the sql into db2 command center and replace all the
variables, it runs fine.
Any ideas??
begin-SELECT
lgr.business_unit &business_unit
lgr.ledger &ledger
lgr.account &account
lgr.deptid &deptid
lgr.operating_unit &oper_unit
lgr.product &product
lgr.affiliate &affiliate
lgr.project_id &project
lgr.fiscal_year &fiscal_year=number
[$lgrpostedamt] &posted_total_amt=number
glac.account_type &account_type
let #Total_Amt = &Posted_Total_Amt
Do Print-File ! proc 9
add 1 to #Tot_Recs
from ps_ledger lgr,
ps_gl_account_tbl GLAC,
ps_set_cntrl_rec CNTRL
where lgr.fiscal_year = #Select_Fiscal_Year
[$BUClause]
and lgr.ledger in ('ACTUALS', 'BUDGET')
and lgr.account = glac.account
and lgr.currency_cd = 'USD'
and glac.effdt = (select max(effdt)
from ps_gl_account_tbl
where setid = glac.setid
and account = glac.account
and effdt <= current date)
and cntrl.setcntrlvalue = lgr.business_unit
and cntrl.recname = 'GL_ACCOUNT_TBL'
and cntrl.setid = glac.setid
[$WherePeriod]
[$WhereAccount]
[$GroupBy]
union
select
lgr.business_unit,
lgr.ledger,
lgr.account,
lgr.deptid,
lgr.operating_unit,
lgr.product,
lgr.affiliate,
lgr.project_id,
lgr.fiscal_year,
posted_total_amt,
glac.account_type
from ps_ledger_adb_mtd lgr,
ps_gl_account_tbl glac,
ps_set_cntrl_rec cntrl
where lgr.fiscal_year = #Select_Fiscal_Year
[$BUClause]
and lgr.ledger = 'AVGACTMTD'
and lgr.account = glac.account
and lgr.currency_cd = 'USD'
and glac.effdt = (select max(effdt)
from ps_gl_account_tbl
where setid = glac.setid
and account = glac.account
and effdt <= current date)
and cntrl.setcntrlvalue = lgr.business_unit
and cntrl.recname = 'GL_ACCOUNT_TBL'
and cntrl.setid = glac.setid
and lgr.accounting_period = $TempPeriod
and ( glac.account_type in ('E','R')
or ( glac.statistics_account = 'Y'
and glac.balance_fwd_sw = 'N'))
cursor 25 :
Cursor #25:
SQL = SELECT lgr.business_unit, lgr.ledger, lgr.account, lgr.deptid,
lgr.operating_unit, lgr.product, lgr.affiliate, lgr.project_id,
lgr.fiscal_year, [$lgrpostedamt], glac.account_type from ps_ledger
lgr, ps_gl_account_tbl GLAC, ps_set_cntrl_rec CNTRL where
lgr.fiscal_year = ? [$BUClause] and lgr.ledger in
('ACTUALS', 'BUDGET') and lgr.account = glac.account and
lgr.currency_cd = 'USD' and glac.effdt = (select max(effdt) from
ps_gl_account_tbl where setid = glac.setid and account =
glac.account and effdt <= current date) and cntrl.setcntrlvalue
= lgr.business_unit and cntrl.recname = 'GL_ACCOUNT_TBL'
and cntrl.setid = glac.setid [$WherePeriod]
[$WhereAccount] [$GroupBy] union select lgr.business_unit,
lgr.ledger, lgr.account, lgr.deptid, lgr.operating_unit,
lgr.product, lgr.affiliate, lgr.project_id, lgr.fiscal_year,
posted_total_amt, glac.account_type from ps_ledger_adb_mtd lgr,
ps_gl_account_tbl glac, ps_set_cntrl_rec cntrl where
lgr.fiscal_year = ? [$BUClause] and lgr.ledger =
'AVGACTMTD' and lgr.account = glac.account and
lgr.currency_cd = 'USD' and glac.effdt = (select max(effdt)
from ps_gl_account_tbl where setid = glac.setid and account =
glac.account and effdt <= current date) and cntrl.setcntrlvalue
= lgr.business_unit and cntrl.recname =
'GL_ACCOUNT_TBL' and cntrl.setid = glac.setid and
lgr.accounting_period = ? and ( glac.account_type in ('E','R')
or ( glac.statistics_account = 'Y' and glac.balance_fwd_sw =
'N'))
Compiles = 1
Executes = 0
Rows = 0
thanks in advance and have a great weekend.
clark
PSA: Salary <> Slavery. If you earn a salary, your employer is renting your
services for 40 hours a week, not purchasing your soul. Your time is the
only real finite asset that you have, and once used it can never be
recovered, so don't waste it by giving it away.
"Time is the coin of your life. It is the only coin you have, and only you
can determine how it will be spent. Be careful lest you let other people
spend it for you."
Carl Sandburg
(1878 - 1967)
_________________________________________________________________
STOP MORE SPAM with the new MSN 8 and get 2 months FREE*
http://join.msn.com/?page=features/junkmail
_______________________________________________
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