Focal Point Banner
Community Center Education Summit Technical Support User Groups
Let's Get Social!

Facebook Twitter LinkedIn YouTube
Focal Point    Focal Point Forums  Hop To Forum Categories  WebFOCUS/FOCUS Forum on Focal Point     [CLOSED] How to Return a 1 if nothing to SELECT
Go
New
Search
Notify
Tools
Reply
  
[CLOSED] How to Return a 1 if nothing to SELECT
 Login/Join
 
Member
posted
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
 
Posts: 15 | Registered: March 31, 2009Reply With QuoteReport This Post
Expert
posted Hide Post
Try the COALESCE function with SQL.

T



In FOCUS
since 1986
WebFOCUS Server 8.2.01M, thru 8.2.06 on Windows Svr 2008 R2  
WebFOCUS App Studio 8.2.06 standalone on Windows 10 
 
Posts: 5601 | Location: United Kingdom | Registered: April 08, 2004Reply With QuoteReport This Post
Platinum Member
posted Hide Post
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
 
Posts: 127 | Location: San Antonio | Registered: May 29, 2009Reply With QuoteReport This Post
Virtuoso
posted Hide Post
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,



Prod/Dev: WF Server 8008/Win 2008 - WF Client 8008/Win 2008 - Dev. Studio: 8008/Windows 7 - DBMS: Oracle 11g Rel 2
Test: Dev. Studio 8008 /Windows 7 (Local) Output:HTML, EXL2K.
 
Posts: 1533 | Registered: August 12, 2005Reply With QuoteReport This Post
Platinum Member
posted Hide Post
Good idea, njsden. In DB2, the NULLIF function would work the same way.


WebFOCUS: 7702
O/S : Windows
Data Migrator: 7702
 
Posts: 127 | Location: San Antonio | Registered: May 29, 2009Reply With QuoteReport This Post
  Powered by Social Strata  
 

Focal Point    Focal Point Forums  Hop To Forum Categories  WebFOCUS/FOCUS Forum on Focal Point     [CLOSED] How to Return a 1 if nothing to SELECT

Copyright © 1996-2018 Information Builders, leaders in enterprise business intelligence.