View previous topic :: View next topic
|
Author |
Message |
mushreyas
New User
Joined: 18 Jul 2008 Posts: 59 Location: Bangalore
|
|
|
|
Hi,
Am getting a SQLCODE +354 while doing a Multifetch from the ROWSET. However if i run the same SQL in SPUFI its going fine. I think the problem is with the OUTER JOIN which am using due to which some of the column values may not have data. To find this out i need to know how use GET diagnostics information. Can you please help me with this ? |
|
Back to top |
|
|
Pandora-Box
Global Moderator
Joined: 07 Sep 2006 Posts: 1592 Location: Andromeda Galaxy
|
|
|
|
Did you use null indicator?? |
|
Back to top |
|
|
dbzTHEdinosauer
Global Moderator
Joined: 20 Oct 2006 Posts: 6966 Location: porcelain throne
|
|
Back to top |
|
|
dbzTHEdinosauer
Global Moderator
Joined: 20 Oct 2006 Posts: 6966 Location: porcelain throne
|
|
Back to top |
|
|
mushreyas
New User
Joined: 18 Jul 2008 Posts: 59 Location: Bangalore
|
|
|
|
My another problem is that am preparing this SQL cursor dynamically. SO in this case can we use GET DIAGNOSTICS |
|
Back to top |
|
|
mushreyas
New User
Joined: 18 Jul 2008 Posts: 59 Location: Bangalore
|
|
|
|
premkrishnan wrote: |
Did you use null indicator?? |
Null indicator can be used only if the columns being retrieved can contain NULL. If they are defined as NOT NULL WITH DEFAULT then can we use Null indicator. |
|
Back to top |
|
|
dbzTHEdinosauer
Global Moderator
Joined: 20 Oct 2006 Posts: 6966 Location: porcelain throne
|
|
|
|
mushreyas wrote: |
My another problem is that am preparing this SQL cursor dynamically. SO in this case can we use GET DIAGNOSTICS
and
premkrishnan wrote:
Did you use null indicator??
Null indicator can be used only if the columns being retrieved can contain NULL. If they are defined as NOT NULL WITH DEFAULT then can we use Null indicator.
|
mushrevas,
yes, the fact that the cursor is dynamic,
does not mean that you can not use imbedded get diagnostics.
null inidcators?
you can always code null indicators....always, always, always.
column defintion has nothing to do with it. |
|
Back to top |
|
|
mushreyas
New User
Joined: 18 Jul 2008 Posts: 59 Location: Bangalore
|
|
|
|
Hi Dick,
All i know is that GET diagnostics is to be used to identify which row and what column has caused the error. However in my case i know the cause of error. Let me simplify this with the help of a simple SQL
Code: |
SELECT A.EMP_NAM, B.EMP_ADDR1
FROM EMPLOYEE A
LEFT OUTER JOIN EMPLOYEE_ADDRESS B
ON A.EMP_ID=B.EMP_ID
|
The above query may or may not retrieve the 2nd column value and if i run the above query in SPUFI wherein no entry exists in table EMPLOYEE_ADDRESS for a given EMP_ID the 2nd column value will be shown as "------". But when i run the same query with a multifetch in embedded appln i get the SQLCODE +354. In case when entry is present in both the tables the SQLCODE will be ZERO.
How can i resolve this problem? Can using the null indicator provides the solution even though the above columns are declared as NOT NULL WITH DEFAULT. |
|
Back to top |
|
|
Pandora-Box
Global Moderator
Joined: 07 Sep 2006 Posts: 1592 Location: Andromeda Galaxy
|
|
|
|
I googled this link might help for basic concepts with simpler examples
Good read |
|
Back to top |
|
|
Akatsukami
Global Moderator
Joined: 03 Oct 2009 Posts: 1788 Location: Bloomington, IL
|
|
|
|
mushreyas wrote: |
Can using the null indicator provides the solution even though the above columns are declared as NOT NULL WITH DEFAULT. |
It is not a null indicator; it is an indicator which shows among other things that the value is null. |
|
Back to top |
|
|
saiprasadh
Active User
Joined: 20 Sep 2006 Posts: 154 Location: US
|
|
|
|
Hi Shre,
When you have Left outer join you have to declare null indicator for those column.
Sai |
|
Back to top |
|
|
ChowHan
New User
Joined: 16 Oct 2009 Posts: 15 Location: India
|
|
|
|
As far as get diagnostics is concerned - I have heard (on some forums - so can not verify --- highly suspect/potentially misleading misinformation - mods please delete/edit this if not correct) - that it is pretty expensive and need not be used for fetch - but rather for inserts/updates where you can do something about the data you are trying to insert.
I got the same SQLCODE earlier this week- and it turned out there was a mismatch in what I had selected and what I had fetched into- it could probably be an issue with the query you are preparing and whether or not you are handling nulls properly. (Would COALESCE/IFNULL work in your case? |
|
Back to top |
|
|
dbzTHEdinosauer
Global Moderator
Joined: 20 Oct 2006 Posts: 6966 Location: porcelain throne
|
|
|
|
as ChowHan said, you may get away with using IFNULL/COALESE/VALUE
otherwise:
if you do not have an array of null indicators (to go along with each array for each column)
that may very well be the problem.
if you have null indicators, then you will receive an sql code of 0,
and
you will have to deal with the null indicators
to determine if the data is
complete,
truncated
or in fact a null value was return,
for each column,
of each row,
just means you need to know how to deal with parallel cobol internal tables.
you need to put in your get diagnostics,
so that you can determine the error
row by row.
also, anyone who suggests not to have the logic available
so that it can be executed when it is required (SQLCODE other than 0 or +100)
is a complete idiot.
how else are you going to determine what the error is?
it is worse than not having a CALL to DNSTIAR for singleton sql
if you don't have an sqlerror,
do not invoke the get diagnostics sql.
after thorough testing
the only time you are going to invoke the get diagnostics
is when you have sql code not equal to 0 and +100. |
|
Back to top |
|
|
ChowHan
New User
Joined: 16 Oct 2009 Posts: 15 Location: India
|
|
|
|
MY bad... As I said it was most probably wrong ... wonder if I can edit it now - Just that i came across an old post someone saying that on idug (and didn't bother to follow up ) ...
Now that i think of it... it sounds silly - you would waste more cpu if you don't know where the issue is... I need to watch what I say when I am sleepy...
|
|
Back to top |
|
|
dbzTHEdinosauer
Global Moderator
Joined: 20 Oct 2006 Posts: 6966 Location: porcelain throne
|
|
|
|
ChowHan,
i did not mean you,
you were only repeating something that someone else said.
yes, get diagnostics eats up a lot of resources,
but as i said,
you should only invoke it when you are in an error situation,
not after a normal (SQLCODE = 0 or +100) return.
the goal is always to have your code tested and perfected
so that you only are an victim of 927's and other system related sqlcodes. |
|
Back to top |
|
|
|