| View previous topic :: View next topic |
| Author |
Message |
ranga_subham
Intermediate
Joined: 31 Jan 2006
Posts: 255
Topics: 72
|
 Posted: Wed Jan 13, 2010 6:38 am Post subject: checking for a value in table. |
 |
|
Hi,
I have a field in a table in our DB2 database against which I need to check whether date field is having value or not. And, depending on successful return code want to process some other para. I have written the code like below:
| Code: |
EXEC SQL
SELECT MATCH_DT
INTO :W-MATCH-DT
FROM DB1.MATCH_TABLE
WHERE MATCH_ONE = 'ABC123'
AND MATCH_TWO = 'OLD'
AND MATCH_CD = '1000'
END-EXEC
EVALUATE SQLCODE
WHEN W-SQL-RC-00
PERFORM 1000-MOVE-PARA
WHEN W-SQL-RC-100
PERFORM 2000-ABEND
END-EVALUATE.
|
How else we can modify the SELECT to not put the values into the host-variable ":W-MATCH-DT"?
Thanks.
_________________
Ranga
*****
None of us is as smart as all of us - Ken Blanchard |
|
| Back to top |
|
 |
papadi
Supermod
Joined: 20 Oct 2009
Posts: 594
Topics: 1
|
| Posted: Wed Jan 13, 2010 5:11 pm Post subject: |
|
|
| Quote: | | How else we can modify the SELECT to not put the values into the host-variable ":W-MATCH-DT"? |
What are you asking?
Showing some kind of example may help clarify.
Explaining what you want to happen would be more informative than posting what you don't waht. . .
_________________
All the best,
di |
|
| Back to top |
|
|
dbzTHEdinosauer
Supermod
Joined: 20 Oct 2006
Posts: 1411
Topics: 26
Location: germany
|
| Posted: Thu Jan 14, 2010 1:50 am Post subject: |
|
|
I am going out on a limb here.
If the date COLUMN is nullable:
if | Code: |
WHERE MATCH_ONE = 'ABC123'
AND MATCH_TWO = 'OLD'
AND MATCH_CD = '1000' |
are not the primary (unique) keys for the table
you run the risk of a -811 (look it up if you don't know) using a singleton select..
you should use a cursor.
and select the primary keys
using a WHERE clause composed of
| Code: |
WHERE MATCH_ONE = 'ABC123'
AND MATCH_TWO = 'OLD'
AND MATCH_CD = '1000'
AND MATCH_DT IS NULL
|
other wise if MATCH_ONE, MATCH_TWO and MATCH_CD constitute a unique key, use a singleton select as follows:
| Code: |
SELECT 1
INTO :numeric-host-variable
FROM DB1.MATCH_TABLE
WHERE MATCH_ONE = 'ABC123'
AND MATCH_TWO = 'OLD'
AND MATCH_CD = '1000'
AND MATCH_DT IS NULL
|
If the MATCH_DT column is not nullable, what do you mean by
| Quote: | | whether date field is having value or not. |
_________________
Dick Brenholtz
American living in Varel, Germany |
|
| Back to top |
|
|
Terry_Heinze
Supermod
Joined: 31 May 2004
Posts: 391
Topics: 4
Location: Richfield, MN, USA
|
| Posted: Thu Jan 14, 2010 7:17 pm Post subject: |
|
|
Please post in the appropriate forum (Database) next time.
_________________
....Terry |
|
| Back to top |
|
|
ranga_subham
Intermediate
Joined: 31 Jan 2006
Posts: 255
Topics: 72
|
| Posted: Mon Jan 18, 2010 7:37 am Post subject: thx |
|
|
Hi,
Thanks. I wrote a program to check if a SELECT works without a host-variable. It failed during compilation itself.
_________________
Ranga
*****
None of us is as smart as all of us - Ken Blanchard |
|
| Back to top |
|
|
|
|
|
FAQ
Search
Quick Manuals
Register
Profile
Log in to check your private messages
Log in
