[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

Subject: RE: [sqr-users] Union issue - any ideas
From: "Knapp, Richard" <KnappR@umsystem.edu>
Date: Mon, 18 Aug 2003 15:19:31 -0500
List-help: <mailto:sqr-users-request@sqrug.org?subject=help>
List-id: This list is for discussion about the SQR database reporting language from Brio Software. <sqr-users.sqrug.org>
List-post: <mailto:sqr-users@sqrug.org>
List-subscribe: <http://www.sqrug.org/mailman/listinfo/sqr-users>,<mailto:sqr-users-request@sqrug.org?subject=subscribe>
List-unsubscribe: <http://www.sqrug.org/mailman/listinfo/sqr-users>,<mailto:sqr-users-request@sqrug.org?subject=unsubscribe>
Thread-index: AcNlpTIhtTxtD4ogQUK8HBwgpwsMlgAIJ8Fg
Thread-topic: [sqr-users] Union issue - any ideas


Anything after the UNION has to be straight SQL - no SQR variables of any kind 
- no square brackets no dollar signs.

Richard Knapp
Database Programmer/Analyst
Institutional Research and Planning
University of Missouri System
573-882-8856
knappr@umsystem.edu


-----Original Message-----
From: Alexander, Steve [mailto:Steve.Alexander@ci.sj.ca.us]
Sent: Monday, August 18, 2003 11:27 AM
To: 'sqr-users@sqrug.org'
Subject: 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

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

Follow-Ups:
- RE: [sqr-users] Union issue - any ideas
  - From: Steve Cavill <steve.cavill@infoclarity.com.au>

Prev by Date: Re: [sqr-users] How to get column headers to print
Next by Date: RE: [sqr-users] Union issue - any ideas
Previous by thread: RE: [sqr-users] Union issue - any ideas
Next by thread: RE: [sqr-users] Union issue - any ideas
Indexes: [ Thread] [ Author] [ Date]
SQR-USERS List and Archive Info
SQRUG Home Page