invalid use of null - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

invalid use of null

Author

Topic

asm
Posting Yak Master

140 Posts

Posted - 2005-10-15 : 02:12:16

Hi

Please help how to solve the invalid use of Null value in numeric field

1. Query name : Genled
----------------------
SELECT PARTY.PARTYNAME AS PARTYNAME, GEN.GLNAME AS GLNAME,
GEN.AMOUNT AS OAMOUNT, CASH.CASHNAME AS CASHNAME, TXN.CBCODE AS CBCODE,
GEN.CB AS CB, TXN.GLCODE AS GLCODE, TXN.VOUCHER AS VOUCHER,
TXN.DOCDATE AS DOCDATE, TXN.AMOUNT AS TAMOUNT, TXN.VTYPE AS VTYPE,
TXN.NARR AS NARR, TXN.CHEQUE AS CHEQUE, TXN.CHEQDATE AS CHEQDATE,
TXN.BANK AS BANK, TXN.BILLNO AS BILLNO, TXN.BILLDT AS BILLDT
FROM PARTY RIGHT JOIN (GEN LEFT JOIN (TXN LEFT JOIN CASH ON TXN.CBCODE
= CASH.CBCODE) ON GEN.GLCODE = TXN.GLCODE) ON PARTY.PARTYCODE =
TXN.PARTYCODE WHERE (((GEN.GLCODE) Not In (SELECT CASHCODE FROM CASH)));

2. From query 1 i have to create temporary table ogenled & generalled

3. From table ogenled i have pass this query -
select glname, oamount, sum(tamount) as tamt,
(oamount+sum(tamount))as opbal from ogenled group by glname,
oamount order by glname

4. from query 3 i have update generalled table for opbal
but the problem is this if only oamount is <>0 and tamt is empty
then error message show "INVALID USE OF NULL"

5. gen table contain the glname, oamount , txn table contains the detail
transaction. It is quite possible that only opening amt is exit.
by query it nothing transaction is done than tamount field show " "

6. Please help how to show the default value 0 insted of " "

Thanks

ASM

chiragkhabaria
Master Smack Fu Yak Hacker

1907 Posts

Posted - 2005-10-15 : 02:21:09

You can use Case for that matter..

Select Case When oamount = ' ' Then 0 Else OAmount End As OAmount From TableName...

Hope this helps.

Complicated things can be done by simple thinking

sachinsamuel
Constraint Violating Yak Guru

383 Posts

Posted - 2005-10-15 : 03:41:54

You can also use isnull function. Check BOL for more details.

Regards
Sachin

chiragkhabaria
Master Smack Fu Yak Hacker

1907 Posts

Posted - 2005-10-15 : 04:40:49

aha I guess he wants to change blank value with 0 and blank and null are different..
well if he wants to change blank value as well as null to 0 then .. try out the following query ..

Select Case When oamount = ' ' or oAmount Is Null Then 0 Else OAmount End As OAmount From
TableName...

Complicated things can be done by simple thinking

asm
Posting Yak Master

140 Posts

Posted - 2005-10-17 : 04:55:53

I have two table
1. gen - contains the all general ledger name with opening amount
2. txn - contains the details transaction during the year.

I have to generate report by below query
-----------------------------------------------
SELECT GEN.GLNAME, GEN.AMOUNT, Sum(TXN.AMOUNT) AS TxnAmount,
(gen.amount+TxnAmount) AS Balance
FROM GEN LEFT JOIN TXN ON GEN.GLCODE=TXN.GLCODE
GROUP BY GEN.GLNAME, GEN.AMOUNT ORDER BY GEN.GLNAME;

result are
----------
glname oamount txnamount balance

c1 50.00 25 75
c2 0 50 50
c3 100
c4 0 25 25

I want c3 balance should be 100.

Please suggest how to write the query.

Thanks

asM

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-10-17 : 04:58:20

Try this

SELECT GEN.GLNAME, GEN.AMOUNT, Sum(TXN.AMOUNT) AS TxnAmount,
(IsNull(gen.amount,0)+IsNull(TxnAmount,0)) AS Balance
FROM GEN LEFT JOIN TXN ON GEN.GLCODE=TXN.GLCODE
GROUP BY GEN.GLNAME, GEN.AMOUNT ORDER BY GEN.GLNAME;

Madhivanan

Failing to plan is Planning to fail

asm
Posting Yak Master

140 Posts

Posted - 2005-10-17 : 06:42:53

Not Working... argument error

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-10-17 : 06:53:57

Try this

SELECT GEN.GLNAME, GEN.AMOUNT, Sum(TXN.AMOUNT) AS TxnAmount,
(IsNull(gen.amount,0)+IsNull(Txn.Amount,0)) AS Balance
FROM GEN LEFT JOIN TXN ON GEN.GLCODE=TXN.GLCODE
GROUP BY GEN.GLNAME, GEN.AMOUNT ORDER BY GEN.GLNAME;

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources