How would I prevent a null value in Oracle sql statement

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

thayduckProgrammer AnalystAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Norman MainaCommented:
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

0
Norman MainaCommented:
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

0
LowfatspreadCommented:
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...
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

Norman MainaCommented:
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

0
thayduckProgrammer AnalystAuthor 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,
0
LowfatspreadCommented:
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.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.