|
View previous topic :: View next topic
|
| Author |
Message |
kedianirmal
New User
.jpg)
Joined: 08 Feb 2008
Posts: 82
Location: Bangalore, India
|
|
|
|
Hi All,
Please check the below spufis:
1
| Code: |
SELECT * FROM TB019
WHERE JSPN = '6775607' AND START_DATE >= '03.04.2008'
AND END_DATE <= '03.04.2008';
---------+---------+---------+---------+---------+---------+---------+-
JS_PROD_CODE BR_CODE EFDTE ADJ_ON_SALE_DATE ORIG_ON_SALE_DATE
---------+---------+---------+---------+---------+---------+---------+-
DSNE610I NUMBER OF ROWS DISPLAYED IS 0
DSNE616I STATEMENT EXECUTION WAS SUCCESSFUL, SQLCODE IS 100
---------+---------+---------+---------+---------+---------+---------+- |
2
| Code: |
SELECT ADJ_ON_SALE_DATE FROM TB019
WHERE JSPN = '6775607' AND START_DATE >= '03.04.2008'
AND END_DATE <= '03.04.2008';
---------+---------+---------+---------+---------+---------+---------+-
ADJ_ON_SALE_DATE
---------+---------+---------+---------+---------+---------+---------+-
DSNE610I NUMBER OF ROWS DISPLAYED IS 0
DSNE616I STATEMENT EXECUTION WAS SUCCESSFUL, SQLCODE IS 100 |
3
| Code: |
SELECT MIN(ADJ_ON_SALE_DATE) AS IDATE FROM TB019
WHERE JSPN = '6775607' AND START_DATE >= '03.04.2008'
AND END_DATE <= '03.04.2008';
---------+---------+---------+---------+---------+---------+---------+
IDATE
---------+---------+---------+---------+---------+---------+---------+
----------
DSNE610I NUMBER OF ROWS DISPLAYED IS 1
DSNE616I STATEMENT EXECUTION WAS SUCCESSFUL, SQLCODE IS 100 |
4
| Code: |
SELECT MIN(ADJ_ON_SALE_DATE) FROM TB019
WHERE JSPN = '6775607' AND START_DATE >= '03.04.2008'
AND END_DATE <= '03.04.2008';
---------+---------+---------+---------+---------+---------+---------+
---------+---------+---------+---------+---------+---------+---------+
----------
DSNE610I NUMBER OF ROWS DISPLAYED IS 1
DSNE616I STATEMENT EXECUTION WAS SUCCESSFUL, SQLCODE IS 100 |
All the Spufis are same just having difference in the select, but the output is different.
The third and fourth spufi is giving some garbage value as hyphen, which causing some problem for a job.
Can anybody help why its giving different result, is it some error which can be corrected or a DB2 bug.
Thanks,
Nirmal |
|
| Back to top |
|
 |
Craq Giegerich
Senior Member
Joined: 19 May 2007
Posts: 1512
Location: Virginia, USA
|
|
|
|
| Quote: |
| DB2 error or what???? |
No just a user error!
The --------- is how SPUFI displays a null column.
In the first 2 you are getting the headings for the columns but since no rows satisfied your where there is not data under the headings.
In number 3 you got the column heading and the ----------- under that is a null value that was returned since no rows satisfied your where.
In number 4 you didn't get the column heading because you didn't specify what name to use for MIN(ADJ_ON_SALE_DATE) and the ----------- under that is a null value that was returned since no rows satisfied your where. |
|
| Back to top |
|
|
kedianirmal
New User
Joined: 08 Feb 2008
Posts: 82
Location: Bangalore, India
|
|
|
|
Hi Craq,
Do you mean whenever i will do a select on a single column and there will be no data to fetch it will give some null value as the output?
Thanks,
Nirmal |
|
| Back to top |
|
|
Craq Giegerich
Senior Member
Joined: 19 May 2007
Posts: 1512
Location: Virginia, USA
|
|
|
|
When you use a column function that can return null as in number 3 & 4, yes. In number 1 & 2 you didn't get any data, look closely at the output from the samples you posted.
----+----+----+ separator before the headings
column headings
----+----+----+ separator before the data |
|
| Back to top |
|
|
the_gautam
Active User
Joined: 05 Jun 2005
Posts: 165
Location: Bangalore
|
|
|
|
when we are selecting a row from the table and no row matches the WHERE clause then we get 0 rows selected.
and in case of column function, if the WHERE clause is not satisfied with any rows then we get a NULL as the output. |
|
| Back to top |
|
|
stodolas
Active Member
Joined: 13 Jun 2007
Posts: 632
Location: Wisconsin
|
|
|
|
| So basically you need to add a null indicator variable to your program and on specify what it is on your select. Check that variable before further processing the record. |
|
| Back to top |
|
|
|
|
