How would I prevent a null value in Oracle sql statement

thayduck
thayduck used Ask the Experts™
on
In below code, I am accessing Oracle tables and inserting into a sql table.

If there is no match, I am getting a null value for fields  Undisputed, Disputed and  DisputedCount in my sql table.
These 3 fields are numeric 13/2 in my sql table.

Is there way to make sure there is a zero value in my sql table when there is no match or is it acceptable to have a null value in numeric fields in my sql table ?
SET @Sql = 'SELECT  TO_CHAR(SYSDATE,''YYYY/MM/DD'') as Processdate, TO_CHAR(R.INVDATE,''YYYY/MM/DD'') as InvoiceDate, R.CUSTNO as CustomerNo, R.TYPE as CustomerType,
                       R.FLEXFIELD4 as InvoiceNo, R.TRAN_ID as TranID, C.COLLECTOR as CollectorCode, C.RESOLVER01 as PortfolioMgrCode, R.AMOUNT as InvoiceAmt, R.TRANBAL as InvoiceBal,                       
                       
                      (SELECT ROUND(TO_NUMBER(SUM(X.TRANBAL))) FROM GPCOMP1.GPRECL X WHERE X.TRANTYPE = ''I'' AND R.CUSTNO = X.CUSTNO GROUP BY X.CUSTNO) as UnDisputed, 
                       
                      (SELECT ROUND(TO_NUMBER(SUM(X.TRANBAL))) FROM GPCOMP1.GPRECL X WHERE X.TRANTYPE = ''B'' AND R.CUSTNO = X.CUSTNO GROUP BY X.CUSTNO) as Disputed,
                      
                      (SELECT COUNT(G.PROBLEM_ID) 
                       FROM          GPCOMP1.GPRECL X, GPCOMP1.GPPROB_RECL GR, GPCOMP1.GPPROB G
                       WHERE     X.TRAN_ID = GR.TRAN_ID AND 
                       GR.PROBLEM_ID = G.PROBLEM_ID AND X.TRANTYPE = ''B'' AND R.CUSTNO = X.CUSTNO GROUP BY X.CUSTNO) as DisputedCount                       
                        
                       FROM GPCOMP1.GPRECL R 
                       LEFT OUTER JOIN GPCOMP1.GPCUST C ON R.CUSTNO = C.CUSTNO'  
        
      SET @Sql = N'INSERT INTO [NonFreight].[Invoice]
            SELECT  * from openquery
            ([GPNFE],    ''' + Replace(@Sql, '''', '''''') + ''')'

      EXEC (@Sql)

Open in new window

Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
you can have nulls in numeric fiels -no problem.There is a big debate on use of nulls or default values in database programming.
If you prefer you can have default values that replace the null with a value like say zero (0) by creating a default Contraint

Example code:

ALTER TABLE Persons
ALTER COLUMN City DROP DEFAULT

Open in new window

Ignore above example.

You can also set defaults in SQL SERVer SSMS
http://www.ehow.com/how_5997148_add-values-microsoft-sql-tables.html
ALTER TABLE Persons
ALTER COLUMN Number SET DEFAULT '0'

Open in new window

Top Expert 2011
Commented:
use the  coalesce function to assign a value for the column when the expression you want it to be is null

e.g.    coalesce(yourcolumn,0.00) as yourcolumnname


there is nothing "wrong" with allowing a numeric column to have a null value , it can be useful in certain circumstances

as previously noted wether to allow nulls in columns can be a "hot" topic of debate in database design circles...
Ensure you’re charging the right price for your IT

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

Other thing you can do(if you have no permission to change the structure of teh db) in SQL Server is to use the ISNULL function which Replaces NULL with the specified replacement value.

An example would be as below where null values in the database are replaced with 0.00 in the select statement.

The difference here is that the first option ensures instead of storing data as null values,it is stored with a default value.With this option,you can store the null values in the db but when retrieving using a select statement,you replace null values with a default value.
SELECT Name,ISNULL(Qty, 0.00) AS 'Quantity'

Open in new window

thayduckProgrammer Analyst

Author

Commented:
Lowfatspread:

Your solution worked for me. Can use coalesce in my sql procedure accessing Oracle tables and in procedure not accessing Oracle tables. Gave you 100 points.

NormanMaina:

Your solution would not work in my sql procedure accessing Oracle table but would work in my sql procedure not accessing Oracle table.  Gave you 25 points.

Thanks for fast and accurate responses.




COALESCE((SELECT ROUND(TO_NUMBER(SUM(X.TRANBAL))) FROM GPCOMP1.GPRECL X WHERE X.TRANTYPE = ''I'' AND R.CUSTNO = X.CUSTNO GROUP BY X.CUSTNO),0) as UnDisputed,
Top Expert 2011

Commented:
isnull is basically a microsoft implementation of coalesce but is limited to only accepting 2 parameters (with coalesce the parameter list is unlimited and you get the first non null value from it).

there are other differences to do with the datatype that you end up with for the resultant column...

personally i stick with coalesce the Ansi/ISO phrase.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial