RE: [sqr-users] In DB2 $sql-error not displaying the complete errormessage

["Manuel Basil Arakkal" <manuelbasil@hotmail.com>]

I had basically added the code with an error in it in my email to display 
the fact that $sql-error does not give the full description when an error is 
encountered.

You have said that the error message gives enough information. However the 
error message with the full information was obtained without using the 
$sql-error. If we use the $sql-error then the error message is not displayed 
fully and that is my problem. We would like to have $sql-error display the 
full error message so that we could take action based on the error message.

We could use on-error to get information on which row is being processed by 
the SQR. However since $sql-error does not display the full error in DB2 
this is not possible.

There should be no problem with using &variables within the SQL statement. 
These variables are used to pick up values from other SQL statements in the 
same SQL without assigning them to $variables. There should also be no 
problem in indenting FROM and WHERE clauses.

Manuel



RE: [sqr-users] In DB2 $sql-error not displaying the complete error message

--------------------------------------------------------------------------------

Subject: RE: [sqr-users] In DB2 $sql-error not displaying the complete error 
message
From: Bob Stone <bstone@fastenal.com>
Date: Thu, 2 Dec 2004 08:39:49 -0600
Delivery-date: Thu, 02 Dec 2004 09:40:58 -0500
List-id: "This list is for discussion about the SQR database 
reportinglanguage from Hyperion Solutions." <sqr-users.sqrug.org>

--------------------------------------------------------------------------------

Well, I think there are a few problems in your code that should help you fix
it.  The error you get gives you enough information, you just have to read
it.  It says it found a '[' and the SQR doesn't know what the heck that is.
So, you cant have the bracket in there...and, like the SQR, i'm not sure
what that is either.  It looks like you're trying to check a value, but dont
know if it will exists.  And if it does not exist, you want to stick the
variable in there.  Since I dont know DB2, i dont know what the fix is on
that.  In Oracle you can say

select
nvl(col1, $var1)                ! <-- nvl - if no value (null value) of col1
then use $var1
from table where [$where_clause]

-------------the problem of your SQL
#ifdef MVS
,\$Incr_Effdt\
,\#Job_Insert_Effseq\
#else
,[$Incr_Effdt]
,[#Job_Insert_Effseq]
#end-if
--------------

Another problem in your SQR is at the bottom.  Recently someone reread the
rules on indentation, and I've already forgotten them, BUT, i'm gonna go
ahead and say that you shouldn't have indented the 'FROM' and 'WHERE'.  I
know its okay to indent the 'AND's (possibly the where too).  It maybe be
personal preference, but I like to keep my entire SQL statement flush to the
left margin...it makes for more easily readable SQR (in my opinion).

But the serious problem at the bottom are the &variables you're trying to
match against...You cant match against &variables in a SQL clause...you need
to have set them up as $variables. (let $var1 = &var1, etc).  If you're
trying to match them up as a 2 table where clause thing, then include both
tables in the FROM, and match them up in the WHERE.

Once you figure out that # \\ [] problem up top, unindent your FROM/WHEREs,
and set up the variables as $variables, your SQR should work a lot better.

You dont necesarily need to get a better error message, you just need to
understand the one you got a bit better.  When it says 'An unexpected token
"[" was found following "" ' and you've got a [ character sitting in your
SQL, that should be a clue that it doesn't like that.  Also when it says
'SQL: ...WHERE EMPLID = ? AND EMPL_RCD = ? AND EFFDT = ? AND EFFSEQ = ?' you
might realize that it couldn't figure out what those &variable were supposed
to be.


-----Original Message-----
From: sqr-users-bounces+bstone=fastenal.com@sqrug.org
[mailto:sqr-users-bounces+bstone=fastenal.com@sqrug.org]On Behalf Of
Manuel Basil Arakkal
Sent: Wednesday, December 01, 2004 5:35 PM
To: sqr-users@sqrug.org
Subject: [sqr-users] In DB2 $sql-error not displaying the complete error
message


We have this code in our program:

Begin-Sql ON-ERROR=KP-SQL-ERROR('PS_JOB_EARNS_DIST - INSERT ',&JOB.EMPLID,
#sql-status, $sql-error)
INSERT INTO PS_JOB_EARNS_DIST
SELECT
EMPLID
,EMPL_RCD
#ifdef MVS
,\$Incr_Effdt\
,\#Job_Insert_Effseq\
#else
,[$Incr_Effdt]
,[#Job_Insert_Effseq]
#end-if
,DEPTID
,JOBCODE
,POSITION_NBR
,GL_PAY_TYPE
,ACCT_CD
,SHIFT
,LOCATION
,ERNCD
,COMPRATE
,DIST_PCT
,STD_HOURS
,BUSINESS_UNIT

        FROM PS_JOB_EARNS_DIST
        WHERE EMPLID = &JOBI.EMPLID
        AND EMPL_RCD = &JOBI.EMPL_RCD
        AND EFFDT = &JOBI.EFFDT
        AND EFFSEQ = &JOBI.EFFSEQ
End-Sql

Begin-Procedure KP-SQL-ERROR($Table_Name, $Emplid, #sql-status, $sql-error)

    Let $sql-status = #sql-status
    Let $error_message = 'FATAL SQL ERROR. Table_Name:' || $Table_Name ||
'Emplid: '||$Emplid||'Error: '||$sql-error||' - '||$sql-status

    Show $error_message
    Stop

End-PROCEDURE

which produces the following message in the log file:

FATAL SQL ERROR. Table_Name:PS_JOB_EARNS_DIST - INSERT Emplid: KC0007
Error: SQL0518N  The statement named in the EXECUTE statement is not in a
prepared
state or is a SELECT or VALUES statemen

- -518.0000000000000000000000000000000000000000000000000

Error on line 3259:
   (SQR 3301) Program stopped by user request.

SQR for PeopleSoft: Program Aborting.

The $sql-error varaible does not give the complete error message.

When the ON-ERROR code is removed then we get the complete error message
from DB2:

(SQR 5528) DB2 SQL PREPARE/DECLARE error -104 in cursor 37:
   SQL0104N  An unexpected token "[" was found following "".  Expected
tokens may
include:  "( + - ? : CASE CAST USER <FLOAT> CURRENT <DECIMAL> <INTEGER> ".
SQLSTATE=42601

SQL: INSERT INTO PS_JOB_EARNS_DIST SELECT EMPLID ,EMPL_RCD ,'2000-12-16' ,[?
     ,DEPTID ,JOBCODE ,POSITION_NBR ,GL_PAY_TYPE ,ACCT_CD ,SHIFT ,LOCATION
     ,ERNCD ,COMPRATE ,DIST_PCT ,STD_HOURS ,BUSINESS_UNIT FROM
     PS_JOB_EARNS_DIST WHERE EMPLID = ? AND EMPL_RCD = ? AND EFFDT = ? AND
     EFFSEQ = ?
(SQR 5528) DB2 SQL OPEN/EXECUTE error -518 in cursor 37:
   SQL0518N  The statement named in the EXECUTE statement is not in a
prepared
state or is a SELECT or VALUES statement.  SQLSTATE=07003

Error on line 2536:
   (SQR 3735) Could not execute SQL.

SQR for PeopleSoft: Program Aborting.

Is there some way I could get the complete error message from DB2 using
ON-ERROR?

Thanks,
Manuel



_______________________________________________
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



--------------------------------------------------------------------------------

Prev by Date: [sqr-users] Printing data in next page using on-break
Next by Date: RE: [sqr-users] MAX() Query
Previous by thread: [sqr-users] Printing data in next page using on-break
Next by thread: [sqr-users] How to extract data in an SQR
Indexes: [ Thread] [ Author] [ Date]
SQR-USERS List and Archive Info
SQRUG Home Page



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