Query Help

Question

Query Help

rocky_498

SSCertifiable

Points: 6532
More actions
October 22, 2012 at 8:00 pm

#283359

Hi guys,
Need help in sql query, Let me let you what i need, during the S.P i want to like
to use this LOGIC, if EID = 0 and PID = 0 then don't run UDF.
Here is the syntax
LEFT OUTER JOIN Information_Sales A ON Case IF EID = 0 or PID = 0 then SID = 148 ELSE UDF.NEW (EID,PID) = A.SID
Please let me know if my question is not clear.
Thanks in advance..

Viewing 4 posts - 1 through 3 (of 3 total)

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

CapnHector SSCoach Points: 16143 More actions · Answer 1

can you post DDL and Sample data? While things may seem simple the udf and the the query you are trying and/or the outcome you are trying to get will help us immensely

For faster help in answering any problems Please read How to post data/code on a forum to get the best help - Jeff Moden[/url] for the best way to ask your question.

For performance Issues see how we like them posted here: How to Post Performance Problems - Gail Shaw[/url]

Need to Split some strings? Jeff Moden's DelimitedSplit8K[/url]
Jeff Moden's Cross tab and Pivots Part 1[/url]
Jeff Moden's Cross tab and Pivots Part 2[/url]

The Dixie Flatline SSC Guru Points: 53354 More actions · Answer 2

LEFT OUTER JOIN Information_Sales A ON Case IF EID = 0 or PID = 0 then SID = 148 ELSE UDF.NEW (EID,PID) = A.SID

Its important to remember that a CASE expression is an expression, which produces a single scalar value. It is not an IF clause.

Assuming that the SID = 148 and A.Sid are the same column. You want something more like this

LEFT OUTER JOIN Information_Sales A on A.SID = Case When EID = 0 then 148 when PID = 0 then 148 else UDF.NEW (EID,PID) end

---------

Note: NO guarantees on performance of such a join. You may well be better off doing separate queries

LEFT OUTER JOIN Information_Sales A on SID = 148

WHERE EID = 0 or PID = 0

LEFT OUTER JOIN Information_Sales A on UDF.NEW (EID,PID)

WHERE EID <> 0 and PID <> 0

As has already been pointed out. A lot more information is needed before anyone can give you solid, specific advice.

__________________________________________________

Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills

rocky_498 SSCertifiable Points: 6532 More actions · Answer 3

Thanks for help guys,

Its a 18 Pages S.P, its hard for me to through all logic here, that's what i want

LEFT OUTER JOIN Information_Sales A on A.SID = Case When EID = 0 then 148 when PID = 0 then 148 else UDF.NEW (EID,PID) end

I checked Performance wise its ok not bad.

Thanks all.....