locked
Show blank record if no record is returned RRS feed

  • Question

  • User-797751191 posted

    Hi

      I have below query & i want if select statement does not return any record it should display blank line.

    If first select statement does not have any record then it should display

       Exempt   -      -

    If second statement does not have any record then it should display

       Non GST Supply    -        -

    select "Description" as "Nature of supplies",
    		   InterState as "Inter-State supplies",
    		   IntraState as "Intra-state supplies"
    	from 
    	(select 'Nil rated supply' as "Description",
    		    convert(numeric(19, 2), isnull((case when IsIntraState <> 'Y' then BaseSum else 0 end), 0)) as "InterState",
    		    convert(numeric(19, 2), isnull((case when IsIntraState = 'Y' then BaseSum else 0 end), 0)) as "IntraState",
    		    1 as TabOrder
    	from #Temp
    	where ItemTaxType <> 'NN'
    	union
    	select 'Non GST supply' as "Description",
    		    convert(numeric(19, 2), isnull((case when IsIntraState <> 'Y' then BaseSum else 0 end), 0)) as "InterState",
    		    convert(numeric(19, 2), isnull((case when IsIntraState = 'Y' then BaseSum else 0 end), 0)) as "IntraState",
    		    1 as TabOrder
    	from #Temp
    	where ItemTaxType = 'NN') t0
    

    Thanks

    Sunday, January 19, 2020 3:37 PM

Answers

  • User452040443 posted

    Hi,

    Try:

    with
    	CTE1 as
    	(
    		select 'Nil rated supply' as "Description",
    		    convert(numeric(19, 2), isnull((case when IsIntraState <> 'Y' then BaseSum else 0 end), 0)) as "InterState",
    		    convert(numeric(19, 2), isnull((case when IsIntraState = 'Y' then BaseSum else 0 end), 0)) as "IntraState",
    		    1 as TabOrder
    		from #Temp
    		where ItemTaxType <> 'NN'
    
                    union all
    
    		select 'Exempt', null, null, null
                    where not exists
    			(select 1 from #temp where ItemTaxType <> 'NN')
    	),
    
    	CTE2 as
    	(
    		select 'Non GST supply' as "Description",
    		    convert(numeric(19, 2), isnull((case when IsIntraState <> 'Y' then BaseSum else 0 end), 0)) as "InterState",
    		    convert(numeric(19, 2), isnull((case when IsIntraState = 'Y' then BaseSum else 0 end), 0)) as "IntraState",
    		    1 as TabOrder
    		from #Temp
    		where ItemTaxType = 'NN'
    
                    union all
    
    		select 'Non GST supply', null, null, null
                    where not exists
    			(select 1 from #temp where ItemTaxType = 'NN')
    	),
    
    	CTEU as
    	(
    		select * from CTE1
    
    		union
    
    		select * from CTE2
    	)
    
    select "Description" as "Nature of supplies",
    		   InterState as "Inter-State supplies",
    		   IntraState as "Intra-state supplies"
    from CTEU
    
    

    Hope this help

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Monday, January 20, 2020 12:04 AM