if NULL ? ??

  • not sure if Im answering the ? right,

    and I stink at this for a lack of better words..

    but when I run it with data CUST_RID = ^ and wondeing if thats why it bypasses the second select statement?

  • It can be difficult to ask questions in a way that other people can understand. At this point I think the only way to get you an answer is if you post the proc you are running. Would be exceptionally helpful if you can post ddl and some sample data (insert statements). This will make it easy for myself (or somebody else) to get you tested, functional and fast code. Take a look at the article referenced in my signature for the best practices about how to post data and such.

    _______________________________________________________________

    Need help? Help us help you.

    Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

    Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

    Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
    Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
    Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
    Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

  • Sean Lange (9/2/2011)


    ... but I still suggest you look into why you are using the nolock hint. It is ok and useful in some places but not always.

    I would have phrased that differently. More like: "It is almost always wrong to use the with (NOLOCK) hint. Only in very rare cases it can be useful to read while the changes have not been committed yet.".



    Posting Data Etiquette - Jeff Moden[/url]
    Posting Performance Based Questions - Gail Shaw[/url]
    Hidden RBAR - Jeff Moden[/url]
    Cross Tabs and Pivots - Jeff Moden[/url]
    Catch-all queries - Gail Shaw[/url]


    If you don't have time to do it right, when will you have time to do it over?

  • R.P.Rozema (9/4/2011)


    Sean Lange (9/2/2011)


    ... but I still suggest you look into why you are using the nolock hint. It is ok and useful in some places but not always.

    I would have phrased that differently. More like: "It is almost always wrong to use the with (NOLOCK) hint. Only in very rare cases it can be useful to read while the changes have not been committed yet.".

    Very true!!! I figured by the third or fourth time mentioning getting rid of that hint it was kind of redundant. My rule of thumb is to have the person explain why they are using nolock. Most of the time they can't explain (or even really understand what it does) in which case it can almost always be removed. Seems that most of the time it is used to "make it faster" or "because I was getting deadlocks" before. It is one of those things that many people use to fix the effect and ignore the cause.

    _______________________________________________________________

    Need help? Help us help you.

    Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

    Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

    Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
    Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
    Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
    Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

  • jbalbo (9/2/2011)


    not sure if Im answering the ? right,

    and I stink at this for a lack of better words..

    but when I run it with data CUST_RID = ^ and wondeing if thats why it bypasses the second select statement?

    It's not running the second statement because the first ALWAYS inserts a row. The flaw is - some of the time, the row being inserted has a NULL in the CustRID

    Skip the inserts for a minute so you understand why it's failing, and run these 2 statements:

    --Your original SELECT statement

    SELECT TAG_NAME = 'CUST_RID',

    TAG_DATA =

    ( SELECT TOP 1 CLIENT_IDENTIFIER

    FROM CLIENT_TO_CLIENT_IDENTIFIER_COLLECTION (NOLOCK)

    INNER JOIN dbo.CLIENT_IDENTIFIER (NOLOCK) on CLIENT_IDENTIFIER.OID =CLIENT_TO_CLIENT_IDENTIFIER_COLLECTION.OID_LINK

    INNER JOIN CLIENT_IDENTIFIER_TYPE (NOLOCK) on CLIENT_IDENTIFIER.CLIENT_IDENTIFIER_TYPE_MONIKER=CLIENT_IDENTIFIER_TYPE.OID

    where AbbrName = 'MMIS'

    AND CLIENT_TO_CLIENT_IDENTIFIER_COLLECTION.OID = @CLIENT_OID

    AND CLIENT_IDENTIFIER.Expdate IS NULL

    ORDER By CLIENT_IDENTIFIER.effdate DESC)

    select @@rowcount

    --One that I think meets what you ACTUALLY want

    SELECT TOP 1

    TAG_NAME = 'CUST_RID',

    TAG_DATA = CLIENT_IDENTIFIER

    FROM CLIENT_TO_CLIENT_IDENTIFIER_COLLECTION (NOLOCK)

    INNER JOIN dbo.CLIENT_IDENTIFIER (NOLOCK) on CLIENT_IDENTIFIER.OID =CLIENT_TO_CLIENT_IDENTIFIER_COLLECTION.OID_LINK

    INNER JOIN CLIENT_IDENTIFIER_TYPE (NOLOCK) on CLIENT_IDENTIFIER.CLIENT_IDENTIFIER_TYPE_MONIKER=CLIENT_IDENTIFIER_TYPE.OID

    where AbbrName = 'MMIS'

    AND CLIENT_TO_CLIENT_IDENTIFIER_COLLECTION.OID = @CLIENT_OID

    AND CLIENT_IDENTIFIER.Expdate IS NULL

    ORDER By CLIENT_IDENTIFIER.effdate DESC

    select @@rowcount

    The first one always returns a row, even when the subquery fails.

    ----------------------------------------------------------------------------------
    Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?

  • So being new at SQL, I got it to work using an if null..

    SOrry Im still confused w/ @@ROWCOUNT.... more classes coming up....

    But thansk for all the help ...

    here is the new code:

    INSERT INTO @TEMPTABLE(TAG_NAME,TAG_DATA)

    SELECT TAG_NAME = 'CUST_RID',

    TAG_DATA =isnull(

    ( SELECT TOP 1 CLIENT_IDENTIFIER

    FROM CLIENT_TO_CLIENT_IDENTIFIER_COLLECTION (NOLOCK)

    INNER JOIN dbo.CLIENT_IDENTIFIER (NOLOCK) on CLIENT_IDENTIFIER.OID =CLIENT_TO_CLIENT_IDENTIFIER_COLLECTION.OID_LINK

    INNER JOIN CLIENT_IDENTIFIER_TYPE (NOLOCK) on CLIENT_IDENTIFIER.CLIENT_IDENTIFIER_TYPE_MONIKER=CLIENT_IDENTIFIER_TYPE.OID

    where AbbrName = 'MMIS'

    AND CLIENT_TO_CLIENT_IDENTIFIER_COLLECTION.OID = @CLIENT_OID

    AND CLIENT_IDENTIFIER.Expdate IS NULL

    ORDER By CLIENT_IDENTIFIER.effdate DESC),

    -- otherwise it does this

    (SELECT TOP 1 ISNULL(payor_code,'')+' '+

    ISNULL( PAYOR.PAYOR_NAME_LINE_1,'')

    FROM EPISODE INNER JOIN Episode_Helper ON Episode_Helper.Episode_OID = Episode.OID

    INNER JOIN EPISODE_TO_OTHERPAYORLINK_COLLECTION WITH (nolock) ON Episode.OID = EPISODE_TO_OTHERPAYORLINK_COLLECTION.OID

    INNER JOIN OtherPayorLink WITH (nolock) ON EPISODE_TO_OTHERPAYORLINK_COLLECTION.OID_LINK = OtherPayorLink.OID

    INNER JOIN Other WITH (nolock) ON OtherPayorLink.OtherPayorLink = Other.OID

    LEFT OUTER JOIN PAYOR WITH (nolock) ON Other.Payer = PAYOR.OID

    WHERE Client_OID =@CLIENT_OID

    AND (OTHER.expDate IS NULL or OTHER.ExpDate > =OTHER.effdate)

    AND OTHER.RANK =1 and payor.PAYOR_CODE=4000

    ORDER BY OTHER.RANK,OTHER.effdate))

    -- othewise it put nothing and leaves the field blank

  • @@ROWCOUNT just says how many rows were affected by the last sql statement. So when you try to insert...if @@ROWCOUNT = 0 you need to insert from your second statement. Something like this.

    INSERT INTO @TEMPTABLE(TAG_NAME,TAG_DATA)

    SELECT TAG_NAME = 'CUST_RID',

    TAG_DATA =isnull(

    ( SELECT TOP 1 CLIENT_IDENTIFIER

    FROM CLIENT_TO_CLIENT_IDENTIFIER_COLLECTION (NOLOCK)

    INNER JOIN dbo.CLIENT_IDENTIFIER (NOLOCK) on CLIENT_IDENTIFIER.OID =CLIENT_TO_CLIENT_IDENTIFIER_COLLECTION.OID_LINK

    INNER JOIN CLIENT_IDENTIFIER_TYPE (NOLOCK) on CLIENT_IDENTIFIER.CLIENT_IDENTIFIER_TYPE_MONIKER=CLIENT_IDENTIFIER_TYPE.OID

    where AbbrName = 'MMIS'

    AND CLIENT_TO_CLIENT_IDENTIFIER_COLLECTION.OID = @CLIENT_OID

    AND CLIENT_IDENTIFIER.Expdate IS NULL

    ORDER By CLIENT_IDENTIFIER.effdate DESC),

    -- otherwise it does this

    if @@ROWCOUNT = 0

    INSERT INTO @TEMPTABLE(TAG_NAME,TAG_DATA) --need to insert

    (SELECT TOP 1 ISNULL(payor_code,'')+' '+

    ISNULL( PAYOR.PAYOR_NAME_LINE_1,'')

    FROM EPISODE INNER JOIN Episode_Helper ON Episode_Helper.Episode_OID = Episode.OID

    INNER JOIN EPISODE_TO_OTHERPAYORLINK_COLLECTION WITH (nolock) ON Episode.OID = EPISODE_TO_OTHERPAYORLINK_COLLECTION.OID

    INNER JOIN OtherPayorLink WITH (nolock) ON EPISODE_TO_OTHERPAYORLINK_COLLECTION.OID_LINK = OtherPayorLink.OID

    INNER JOIN Other WITH (nolock) ON OtherPayorLink.OtherPayorLink = Other.OID

    LEFT OUTER JOIN PAYOR WITH (nolock) ON Other.Payer = PAYOR.OID

    WHERE Client_OID =@CLIENT_OID

    AND (OTHER.expDate IS NULL or OTHER.ExpDate > =OTHER.effdate)

    AND OTHER.RANK =1 and payor.PAYOR_CODE=4000

    ORDER BY OTHER.RANK,OTHER.effdate))

    And still...why the nolock hint? You are going to get dirty reads. Like mentioned previously, unless you can explain what that hint does and why it is important to the query you should NOT be using it.

    _______________________________________________________________

    Need help? Help us help you.

    Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

    Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

    Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
    Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
    Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
    Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

Viewing 7 posts - 16 through 21 (of 21 total)

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