Query Help

  • 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..

  • 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]

  • 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

  • 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.....

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

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