I have a SQL statement for a table insert that will put incrementally the next number available into an ID field see below
(SELECT MAX(IDSTANDARD)+1 FROM STANDARD)
Works great if there is at least 1 item in the STANDARD table. If there are no items i would like to default it to 1. Any ideas?This message has been edited. Last edited by: Kerry,
WebFOCUS 7.6.8 Unix ALL
September 08, 2010, 10:50 AM
Tony A
Try the COALESCE function with SQL.
T
In FOCUS since 1986
WebFOCUS Server 8.2.01M, thru 8.2.07 on Windows Svr 2008 R2
WebFOCUS App Studio 8.2.06 standalone on Windows 10
September 08, 2010, 02:15 PM
T.Peters
CASE should work as well.
CASE WHEN (SELECT MAX(IDSTANDARD)+1 FROM STANDARD) > 1 THEN (SELECT MAX(IDSTANDARD)+1 FROM STANDARD) ELSE 1 END
WebFOCUS: 7702 O/S : Windows Data Migrator: 7702
September 09, 2010, 11:20 AM
njsden
Which database are you working against? If it's Oracle, the NVL function may help.
..., NVL((SELECT MAX(IDSTANDARD) + 1 FROM STANDARD), 1) ,...This message has been edited. Last edited by: njsden,