Need help on query

Satheeshkumar · New User Joined: 20 Feb 2009 Posts: 51 Location: Chennai

Can any one help me in providing a query.

I have written query to select the maxmimum value of one column in a table with certain condition..

(SELECT MAX(COMP_STAT_SEQ_NBR)+1
FROM VTCOMP
WHERE COMP_STATUS_MNE = 'HISTOR' AND CON_NBR = '056044572 0'),

It is running fine if it found the row which satisfy the criteria.

If the criteria is not satisfied(No valid row in a table) . The query errored with THE OBJECT COLUMN CANNOT CONTAIN NULL VALUES.

Can any one help me out

dbzTHEdinosauer · Posted: Tue Jun 09, 2009 3:57 pm

maybe using VALUE, and the substitute for NULL would be 1

Satheeshkumar · New User Joined: 20 Feb 2009 Posts: 51 Location: Chennai

Could you please give some more information

Raghu navaikulam · Posted: Tue Jun 09, 2009 4:30 pm

Hi Satheeshkumar

You can use VALUE function along with MAX function to get rid of this issue.
Eg. SELECT MAX(VALUE(Column_name, xxxx)) FROM TABLE_NAME ...

Here xxxx is the value you have to provide if the column is NULL.
And the value you provided should match the datatype of the column.

Hope this will help.

Regards
Raghu

dbzTHEdinosauer · Posted: Tue Jun 09, 2009 4:40 pm

IFNULL
COALESCE or VALUE

Satheeshkumar · New User Joined: 20 Feb 2009 Posts: 51 Location: Chennai

Still i am getting Null values. Please see the screen shot below

---------+---------+---------+---------+---------+---------+----
SELECT MAX(VALUE(COMP_STAT_SEQ_NBR,0))
FROM E0384DBA.CYVTCOMP
WHERE COMP_STATUS_MNE = 'HISTOR' AND CON_NBR = '03Y044631 0';

DSNE610I NUMBER OF ROWS DISPLAYED IS 1
DSNE616I STATEMENT EXECUTION WAS SUCCESSFUL, SQLCODE IS 100
---------+---------+---------+---------+---------+---------+----
--

dbzTHEdinosauer · Posted: Tue Jun 09, 2009 5:40 pm

SELECT VALUE(MAX(COMP_STAT_SEQ_NBR),0)

maggiebms · New User Joined: 23 May 2005 Posts: 16

Hi,
You can use COALESCE(MAX(COMP_STAT_SEQ_NBR),0) also.

Regards,
Maggie J