correct the code

Question

correct the code

riya_dave

Hall of Fame

Points: 3106
More actions
May 31, 2012 at 9:25 am

#256803

hi,
i am trying to get 0 as a value if no of rows is 0
still not getting ,my result set is blank.
please see whats wrong in this code
declare @rowcount int
select @rowcount = COUNT(*) from @temp1
select @rowcount
if(@rowcount = 0)
SELECT
'12/31/2011' as CloseDate,
'0' as CostBasis,
'09/30/2010' as OpenDate,
'1' as PortfolioBaseIDOrder,
'0' as Quantity,
'0' as RealizedGainLoss
from dbo.fRealizedGainLoss(@ReportData) r

Viewing 10 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply

John Mitchell-245523 SSC Guru Points: 148786 More actions · Answer 1

Please post the whole of your code, including the parts where @temp1 and @ReportData are declared. Please also post the definition of dbo.fRealizedGainLoss and any table definitions and sample data we may need to help you.

John

Lynn Pettis SSC Guru Points: 442447 More actions · Answer 2

if(@rowcount = 0)

SELECT

'12/31/2011' as CloseDate,

'0' as CostBasis,

'09/30/2010' as OpenDate,

'1' as PortfolioBaseIDOrder,

'0' as Quantity,

'0' as RealizedGainLoss

from

dbo.fRealizedGainLoss(@ReportData) r

If all you want from the above if @rowcount = 0 are the hardcoded values in the select then all you need is this:

if(@rowcount = 0)

SELECT

'12/31/2011' as CloseDate,

'0' as CostBasis,

'09/30/2010' as OpenDate,

'1' as PortfolioBaseIDOrder,

'0' as Quantity,

'0' as RealizedGainLoss

There is no need for the from clause.

Lynn Pettis SSC Guru Points: 442447 More actions · Answer 3

John Mitchell-245523 (5/31/2012)
Please post the whole of your code, including the parts where @temp1 and @ReportData are declared. Please also post the definition of dbo.fRealizedGainLoss and any table definitions and sample data we may need to help you.
John

Good luck on this one, have tried in vain in most of the OPs post to get that information.

John Mitchell-245523 SSC Guru Points: 148786 More actions · Answer 4

Thanks Lynn. I saw those other threads... just thought I'd try this once. If I don't get the required information, I shan't be asking again.

John

John Mitchell-245523 SSC Guru Points: 148786 More actions · Answer 5

Lynn Pettis (5/31/2012)
There is no need for the from clause.

Unless, for some bizarre reason, he wants those figures repeated as many times as the function returns rows.

John

Eugene Elutin SSC Guru Points: 59322 More actions · Answer 6

You do something very stupid here:

SELECT

'12/31/2011' as CloseDate,

'0' as CostBasis,

'09/30/2010' as OpenDate,

'1' as PortfolioBaseIDOrder,

'0' as Quantity,

'0' as RealizedGainLoss

from dbo.fRealizedGainLoss(@ReportData) r

If the result of dbo.fRealizedGainLoss(@ReportData) is 5 rows, you will get five rows of constants you specified in SELECT, as you do not select anything from the function result.

I can only guess (as you never provide enough details) that you want something like:

SELECT

r.CloseDate,

r.CostBasis,

r.OpenDate,

r.PortfolioBaseIDOrder,

r.Quantity,

r.RealizedGainLoss

from dbo.fRealizedGainLoss(@ReportData) r

UNION

SELECT

'12/31/2011' as CloseDate,

'0' as CostBasis,

'09/30/2010' as OpenDate,

'1' as PortfolioBaseIDOrder,

'0' as Quantity,

'0' as RealizedGainLoss

WHERE NOT EXISTS (SELECT 1 FROM dbo.fRealizedGainLoss(@ReportData) r1)

If the table returned by fRealizedGainLoss is not empty, you will get get records from it, otherwise you will get one record with "default" values.

_____________________________________________
"The only true wisdom is in knowing you know nothing"
"O skol'ko nam otkrytiy chudnyh prevnosit microsofta duh!":-D
(So many miracle inventions provided by MS to us...)

How to post your question to get the best and quick help[/url]

Lynn Pettis SSC Guru Points: 442447 More actions · Answer 7

Eugene Elutin (5/31/2012)
You do something very stupid here:
SELECT
'12/31/2011' as CloseDate,
'0' as CostBasis,
'09/30/2010' as OpenDate,
'1' as PortfolioBaseIDOrder,
'0' as Quantity,
'0' as RealizedGainLoss
from dbo.fRealizedGainLoss(@ReportData) r
If the result of dbo.fRealizedGainLoss(@ReportData) is 5 rows, you will get five rows of constants you specified in SELECT, as you do not select anything from the function result.
I can only guess (as you never provide enough details) that you want something like:
SELECT
r.CloseDate,
r.CostBasis,
r.OpenDate,
r.PortfolioBaseIDOrder,
r.Quantity,
r.RealizedGainLoss
from dbo.fRealizedGainLoss(@ReportData) r
UNION
SELECT
'12/31/2011' as CloseDate,
'0' as CostBasis,
'09/30/2010' as OpenDate,
'1' as PortfolioBaseIDOrder,
'0' as Quantity,
'0' as RealizedGainLoss
WHERE NOT EXISTS (SELECT 1 FROM dbo.fRealizedGainLoss(@ReportData) r1)
If the table returned by fRealizedGainLoss is not empty, you will get get records from it, otherwise you will get one record with "default" values.

Testing out your new crystal ball I see. Hope it works! 😀

riya_dave Hall of Fame Points: 3106 More actions · Answer 8

riya_dave

Hall of Fame

Points: 3106

May 31, 2012 at 11:42 am

#1495413

thanks lynn,

it works now

Dwain Camps SSC Guru Points: 86913 More actions · Answer 9

Lynn Pettis (5/31/2012)
Testing out your new crystal ball I see. Hope it works! 😀

DBCC TIMEWARP (SELECT Timestamp FROM riya_dave WHERE DDL IS NOT NULL)

DBCC TIMEWARP GETDATE()

My mantra: No loops! No CURSORs! No RBAR! Hoo-uh![/I]
My thought question: Have you ever been told that your query runs too fast?

My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.

Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St