September 2, 2011 at 1:01 pm
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?
September 2, 2011 at 1:06 pm
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/
September 4, 2011 at 8:04 am
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.".
September 6, 2011 at 9:23 am
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/
September 6, 2011 at 10:40 am
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?
September 6, 2011 at 10:50 am
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
September 6, 2011 at 11:58 am
@@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