Portal | Manuals | References | Downloads | Info | Programs | JCLs | Master the Mainframes
IBM Mainframe Computers Forums Index
 
Register
 
IBM Mainframe Computers Forums Index Mainframe: Search IBM Mainframe Forum: FAQ Memberlist Usergroups Profile Log in to check your private messages Log in
 

 

Error while using 'Where' Clause in DB2 Table

 
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2
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    Post subject: Error while using 'Where' Clause in DB2 Table
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

Site Director


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

PostPosted: Tue May 13, 2008 1:19 pm    Post subject:
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    Post subject:
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: 6968
Location: porcelain throne

PostPosted: Tue May 13, 2008 4:51 pm    Post subject:
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    Post subject:
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    Post subject:
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    Post subject:
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: 26
Location: Chennai

PostPosted: Wed May 14, 2008 3:47 pm    Post subject:
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    Post subject:
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: 26
Location: Chennai

PostPosted: Thu May 15, 2008 1:46 pm    Post subject:
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

Global Moderator


Joined: 14 Mar 2007
Posts: 10274
Location: italy

PostPosted: Thu May 15, 2008 1:58 pm    Post subject: Reply to: Error while using 'Where' Clause in DB2 Table
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    IBMMAINFRAMES.com Support Forums -> DB2 All times are GMT + 6 Hours
Page 1 of 1

 

Search our Forum:

Similar Topics
Topic Author Forum Replies Posted
No new posts Loading data to table gives wrong for... Raghu navaikulam DB2 16 Thu Jul 13, 2017 2:11 pm
No new posts DB2 Program abending without giving a... rahulgarg14 DB2 7 Mon May 29, 2017 8:10 pm
No new posts Getting error while opening a variabl... apandey1 COBOL Programming 5 Fri May 05, 2017 12:22 pm
No new posts unload data from table with lob columns farhad_evan DB2 1 Sat Apr 22, 2017 1:32 pm
No new posts IDC2902I Error tzeche ABENDS & Debugging 2 Tue Mar 28, 2017 11:39 am


Facebook
Back to Top
 
Mainframe Wiki | Forum Rules | Bookmarks | Subscriptions | FAQ | Tutorials | Contact Us