Don't display NULL record in result

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

Don't display NULL record in result

Author

Topic

hspatil31
Posting Yak Master

182 Posts

Posted - 2009-04-23 : 06:26:31

Dear All,

I have used the following query which displays the ouput as shown below the query.

In the result i dont want to display the record which has taxamount as zero value but in my query it displays record with zero value (eg Octroi)

how to do this......

Query :

select DocEntry,AbsId,Name,TaxAmount from
(
select I4.DocEntry,MAX(ot.AbsId) as AbsId, oa.Name, sum(I4.TaxSum) as TaxAmount
from inv4 I4
left join ostt ot on ot.AbsId = I4.StaType
left join osta oa on I4.StaCode = oa.Code
where I4.DocEntry = '92'
group by oa.Name,I4.DocEntry

union all

select i2.DocEntry,i2.ExpnsCode As AbsId,ox.ExpnsName As Name,i2.LineTotal As TaxAmount from inv2 i2
left join oexd ox on ox.ExpnsCode = i2.ExpnsCode
where i2.DocEntry in (select DocEntry from inv4 where DocEntry = '92')
)t order by case when AbsId in (1,7,8,9) then 1 else 2 end,absid

Query Output:

Docentry AbsID Name Taxamount
92 1 Packing Charges 212.000000
92 7 Excise 2908.640000
92 8 ECESS 58.170000
92 9 HCESS 29.080000
92 2 Transportion Charge 2500.000000
92 14 OCTROI 0.000000
92 15 VAT_4 1383.290000

Harish Patil

bklr
Master Smack Fu Yak Hacker

1693 Posts

Posted - 2009-04-23 : 06:29:03

select * from (select DocEntry,AbsId,Name,TaxAmount from
(
select I4.DocEntry,MAX(ot.AbsId) as AbsId, oa.Name, sum(I4.TaxSum) as TaxAmount
from inv4 I4
left join ostt ot on ot.AbsId = I4.StaType
left join osta oa on I4.StaCode = oa.Code
where I4.DocEntry = '92'
group by oa.Name,I4.DocEntry

union all

select i2.DocEntry,i2.ExpnsCode As AbsId,ox.ExpnsName As Name,i2.LineTotal As TaxAmount from inv2 i2
left join oexd ox on ox.ExpnsCode = i2.ExpnsCode
where i2.DocEntry in (select DocEntry from inv4 where DocEntry = '92')
)t order by case when AbsId in (1,7,8,9) then 1 else 2 end,absid
) where taxamount > 0

hspatil31
Posting Yak Master

182 Posts

Posted - 2009-04-23 : 06:52:26

Dear Freind,

I tried ur solution but it gives following error.
Can u plz tell me how to solve this ?

Error:

The ORDER BY clause is invalid in views, inline functions, derived tables, subqueries, and common table expressions, unless TOP or FOR XML is also specified.

bklr
Master Smack Fu Yak Hacker

1693 Posts

Posted - 2009-04-23 : 06:57:13

select * from (select DocEntry,AbsId,Name,TaxAmount from
(
select I4.DocEntry,MAX(ot.AbsId) as AbsId, oa.Name, sum(I4.TaxSum) as TaxAmount
from inv4 I4
left join ostt ot on ot.AbsId = I4.StaType
left join osta oa on I4.StaCode = oa.Code
where I4.DocEntry = '92'
group by oa.Name,I4.DocEntry

union all

select i2.DocEntry,i2.ExpnsCode As AbsId,ox.ExpnsName As Name,i2.LineTotal As TaxAmount from inv2 i2
left join oexd ox on ox.ExpnsCode = i2.ExpnsCode
where i2.DocEntry in (select DocEntry from inv4 where DocEntry = '92')
)t
) where taxamount > 0 order by case when AbsId in (1,7,8,9) then 1 else 2 end,absid

hspatil31
Posting Yak Master

182 Posts

Posted - 2009-04-23 : 07:18:06

Dear Freind,

Again it give error.

Erro :

Incorrect syntax near the keyword 'where'.

Harish Patil

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2009-04-23 : 07:27:19

Just add a WHERE to your existing query:


SELECT   docentry, 
         absid, 
         name, 
         taxamount 
FROM     (SELECT   i4.docentry, 
                   Max(ot.absid)  AS absid, 
                   oa.name, 
                   Sum(i4.taxsum) AS taxamount 
          FROM     inv4 i4 
                   LEFT JOIN ostt ot 
                     ON ot.absid = i4.statype 
                   LEFT JOIN osta oa 
                     ON i4.stacode = oa.code 
          WHERE    i4.docentry = '92' 
          GROUP BY oa.name, 
                   i4.docentry 
          UNION ALL 
          SELECT i2.docentry, 
                 i2.expnscode AS absid, 
                 ox.expnsname AS name, 
                 i2.linetotal AS taxamount 
          FROM   inv2 i2 
                 LEFT JOIN oexd ox 
                   ON ox.expnscode = i2.expnscode 
          WHERE  i2.docentry IN (SELECT docentry 
                                 FROM   inv4 
                                 WHERE  docentry = '92')) t 
where isnull(taxamount,0) > 0
ORDER BY CASE 
           WHEN absid IN (1,7,8,9) THEN 1 
           ELSE 2 
         END, 
         absid

No, you're never too old to Yak'n'Roll if you're too young to die.

bklr
Master Smack Fu Yak Hacker

1693 Posts

Posted - 2009-04-23 : 07:29:56

[code]
select * from (
select I4.DocEntry,MAX(ot.AbsId) as AbsId, oa.Name, sum(I4.TaxSum) as TaxAmount
from inv4 I4
left join ostt ot on ot.AbsId = I4.StaType
left join osta oa on I4.StaCode = oa.Code
where I4.DocEntry = '92'
group by oa.Name,I4.DocEntry

union all

select i2.DocEntry,i2.ExpnsCode As AbsId,ox.ExpnsName As Name,i2.LineTotal As TaxAmount from inv2 i2
left join oexd ox on ox.ExpnsCode = i2.ExpnsCode
where i2.DocEntry in (select DocEntry from inv4 where DocEntry = '92')
) where taxamount > 0 order by case when AbsId in (1,7,8,9) then 1 else 2 end,absid
[/code]

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2009-04-23 : 07:35:09

quote:

Originally posted by bklr


 select * from (
select I4.DocEntry,MAX(ot.AbsId) as AbsId, oa.Name, sum(I4.TaxSum) as TaxAmount
from inv4 I4
left join ostt ot on ot.AbsId = I4.StaType
left join osta oa on I4.StaCode = oa.Code
where I4.DocEntry = '92'
group by oa.Name,I4.DocEntry

union all

select i2.DocEntry,i2.ExpnsCode As AbsId,ox.ExpnsName As Name,i2.LineTotal As TaxAmount from inv2 i2
left join oexd ox on ox.ExpnsCode = i2.ExpnsCode
where i2.DocEntry in (select DocEntry from inv4 where DocEntry = '92')
) where taxamount > 0 order by case when AbsId in (1,7,8,9) then 1 else 2 end,absid

and again there is no alias for derived table...

No, you're never too old to Yak'n'Roll if you're too young to die.

hspatil31
Posting Yak Master

182 Posts

Posted - 2009-04-23 : 07:42:06

Dear Friend,

Thnks for the valueable inputs. My problem is solved.
Also i would like to know is there any way to reward points.

Harish Patil

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources