IBM Mainframe Forum Index
 
Log In
 
IBM Mainframe Forum Index Mainframe: Search IBM Mainframe Forum: FAQ Register
 

Error while using 'Where' Clause in DB2 Table


IBM Mainframe Forums -> DB2
Post new topic   Reply to topic
View previous topic :: View next topic  
Author Message
isys2006

New User


Joined: 11 Mar 2006
Posts: 13

PostPosted: Tue May 13, 2008 1:03 pm
Reply with quote

Hi All
I would be grateful if people here could help me in resolving this issue.

* * * * *
Query Inside a program:

When I am hard coding a value in the query I am getting the sqlcode as 0.
But when I am using the variable “Ws-base-id” in the query I am getting SQLCODE as 100.

EXEC SQL
SELECT X
INTO :WS-TD-X
FROM Tbname
WHERE ACCT_NBR = :WS-BASE-ID
AND PRODUCT_CODE = 95
GROUP BY ACCT_NBR WITH UR
END-EXEC
* * * * *

This variable is not part of the linkage section. Since I am getting this error only for this table, I am wondering if there is something else that I have not taken care of.

Any help would be greatly appreciated.

Thanks
ram
Back to top
View user's profile Send private message
dick scherrer

Moderator Emeritus


Joined: 23 Nov 2006
Posts: 19244
Location: Inside the Matrix

PostPosted: Tue May 13, 2008 1:19 pm
Reply with quote

Hello,

Please post the definition of acct-nbr and :ws-base-id.

How is a value put into :ws-base-id? Are you sure it is being initialized before the query is executed?
Back to top
View user's profile Send private message
isys2006

New User


Joined: 11 Mar 2006
Posts: 13

PostPosted: Tue May 13, 2008 3:29 pm
Reply with quote

Here's a small description that might help.

WORKING-STORAGE SECTION.

01 WS-MISC.
05 WS-ACCT-ID.
10 WS-FIRM-ID PIC X(03) VALUE SPACES.
10 WS-BRNCH-ID PIC X(05) VALUE SPACES.
10 WS-BASE-ID PIC X(08) VALUE SPACES.

Populating WS-ACCT-ID:
----------------------------
<snip>
READ ABCD-FILE
AT END
SET WS-EOF-Y TO TRUE
NOT AT END
ADD +1 TO WS-READ-COUNT
MOVE C2DUMP-ACCT-ID TO WS-ACCT-ID
END-READ
.
<snip>

* * * * *
Before executing the query, a display statement is used to display the
Bse Id.

Thanks
ram
Back to top
View user's profile Send private message
dbzTHEdinosauer

Global Moderator


Joined: 20 Oct 2006
Posts: 6966
Location: porcelain throne

PostPosted: Tue May 13, 2008 4:51 pm
Reply with quote

what is the dcl gen definition of the column ACCT_NBR?
Back to top
View user's profile Send private message
isys2006

New User


Joined: 11 Mar 2006
Posts: 13

PostPosted: Wed May 14, 2008 3:20 pm
Reply with quote

The definition is Pic X(08).

Thanks
Ram
Back to top
View user's profile Send private message
Bharath Bhat

Active User


Joined: 20 Mar 2008
Posts: 283
Location: chennai

PostPosted: Wed May 14, 2008 3:42 pm
Reply with quote

Hi,

Please try to display the value in WS-BASE-ID just before executing the query. I suspect it does not contain the value you need in the where clause.

Hope this helps.
Back to top
View user's profile Send private message
Bharath Bhat

Active User


Joined: 20 Mar 2008
Posts: 283
Location: chennai

PostPosted: Wed May 14, 2008 3:45 pm
Reply with quote

Sorry Ram, I didn't see your second post icon_redface.gif
Back to top
View user's profile Send private message
satheeshkamal

New User


Joined: 09 Jan 2007
Posts: 28
Location: Chennai

PostPosted: Wed May 14, 2008 3:47 pm
Reply with quote

What is the strucutre for C2DUMP-ACCT-ID ?
Back to top
View user's profile Send private message
isys2006

New User


Joined: 11 Mar 2006
Posts: 13

PostPosted: Thu May 15, 2008 1:27 pm
Reply with quote

C2DUMP-ACCT-ID: pic x(16)

I am not sure of this: the only reasonable answer could be that there is some problem with the table definition. I could be wrong though.

Please respond if you have any other ideas.

Thanks
Ram
Back to top
View user's profile Send private message
satheeshkamal

New User


Joined: 09 Jan 2007
Posts: 28
Location: Chennai

PostPosted: Thu May 15, 2008 1:46 pm
Reply with quote

I am not sure the below will help. A similar question was discussed in the forum

Check : http://ibmmainframes.com/viewtopic.php?t=30613
Back to top
View user's profile Send private message
enrico-sorichetti

Superior Member


Joined: 14 Mar 2007
Posts: 10872
Location: italy

PostPosted: Thu May 15, 2008 1:58 pm
Reply with quote

after creating a table,
even if the generated copybook is not going to be used,
it is a good habit to run a DCLGEN to check if the hand written definitions
of the columns variables agree with those that will be enforced by DB2

this simple action will avoid lots of errors due to definition misunderstandings!
Back to top
View user's profile Send private message
View previous topic :: :: View next topic  
Post new topic   Reply to topic View Bookmarks
All times are GMT + 6 Hours
Forum Index -> DB2

 


Similar Topics
Topic Forum Replies
No new posts Error to read log with rexx CLIST & REXX 11
No new posts Load new table with Old unload - DB2 DB2 6
No new posts Error when install DB2 DB2 2
No new posts Pulling a fixed number of records fro... DB2 2
No new posts CLIST - Virtual storage allocation error CLIST & REXX 5
Search our Forums:

Back to Top