September 2, 2011 at 9:28 am
Hi,
Sorry, fairly new at SQL...
here is what I want to do
do a select, if its null do a different select and put that value in the same tag_name
if this statement isnull.........
INSERT INTO @TEMPTABLE(TAG_NAME,TAG_DATA)
SELECT TAG_NAME = 'CUST_MMIS',
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);
Then do this statement........
INSERT INTO @TEMPTABLE(TAG_NAME,TAG_DATA)
SELECT TAG_NAME = 'CUST_INSURANCECODE', TAG_DATA = (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
ORDER BY OTHER.RANK,OTHER.effdate);
But I Want the tag_name to be the same
Thanks In advance
Joe
September 2, 2011 at 9:33 am
if (select myval from Mytable) is null
insert into ...
else
insert into
...
will work, but you have two tag_names in the different code. What do you mean by having them the same?
September 2, 2011 at 9:34 am
You can just check for @@rowcount
That is after the first select (it is actually an insert into the temptable) do a
If @@rowcount = 0
do the next select
I think this what you were looking for.
-Roy
September 2, 2011 at 9:40 am
thanks for getting back so quick!!
ok so I do want to use the same name I just cut the code that I needed to do the comparisionso I need to change the names
so the @@rowcount=0 can you give me an example
Thaanks in Advance
September 2, 2011 at 9:45 am
insert into @temptab
Select 'Test1', col2, col3 from tableA a
where col7 = Some value
if (@@rowcount = 0)
insert into @temptab
Select 'Test1', tabcol2, Tabcol3 from tableB a
where tabcol7 = Some value
I am not sure what your exact requirement is, but I think you can work with your query from this example.
-Roy
September 2, 2011 at 9:58 am
Sorry being a pain..
would the statement look like this where the tag_name="CUST_RID
INSERT INTO @TEMPTABLE(TAG_NAME,TAG_DATA)
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)
if (@@rowcount=0)
SELECT TAG_NAME = 'CUST_RID', TAG_DATA = (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
ORDER BY OTHER.RANK,OTHER.effdate);
September 2, 2011 at 10:20 am
Your code would be something like:
insert into
if @@rowcount = 0
insert into
Why do you have the nolock hint on all your tables? This looks like a financial application and you are getting dirty reads. The nolock hint should be used sparingly and only in situations where there is a valid reason.
_______________________________________________________________
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 2, 2011 at 10:32 am
??
If there are many Insert into's in the SP
does the @@rowcount only evaluate on the the just prior Insert statement?
Thx
Joe
September 2, 2011 at 10:37 am
jbalbo (9/2/2011)
??If there are many Insert into's in the SP
does the @@rowcount only evaluate on the the just prior Insert statement?
Thx
Joe
Take a look at your code...i added some comments to help clarify
INSERT INTO @TEMPTABLE(TAG_NAME,TAG_DATA)
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)
if (@@rowcount=0) --at this point, @@rowcount will tell you how may rows were inserted from the above statement
--you need to add your insert here
INSERT INTO @TEMPTABLE(TAG_NAME,TAG_DATA)
SELECT TAG_NAME = 'CUST_RID', TAG_DATA = (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
ORDER BY OTHER.RANK,OTHER.effdate);
This should help you with the insert, 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.
_______________________________________________________________
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 2, 2011 at 11:01 am
Thanks so much..
Im going to try an dlook into the nolock
September 2, 2011 at 12:13 pm
Hi,
So the logic definitly works, but my problem is the SP has many SELECTS so @@ROWCOUNT is always >0
is there a way to evaluate just the upper select like a COUNT ?
Thanks
Joe
September 2, 2011 at 12:17 pm
The value of rowcount will hold the count of rows from the last statement, whatever that may be. Read about it in BOL http://msdn.microsoft.com/en-us/library/ms187316.aspx.
_______________________________________________________________
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 2, 2011 at 12:21 pm
See if this quick little example will help.
select top 5 * from sysobjects
if @@ROWCOUNT > 0
select 'found some'
select top 5 * from sysobjects where 1 = 2
if @@ROWCOUNT = 0
select 'Of course there are none'
select COUNT(*) from sysobjects where 1 = 2
if @@ROWCOUNT = 0
select 'You will never see this'
else
select 'but you will see this one'
select @@ROWCOUNT as [RowCount], 'Yeap this is 1'
_______________________________________________________________
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 2, 2011 at 12:42 pm
OK so the SP has this code at the botton
--DO NOT CHANGE BELOW THIS LINE--
--Replaces special characters in data to prevent error when reading into HTML Forms
UPDATE @TEMPTABLE SET TAG_DATA = REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(TAG_DATA,'"','\quote\'),'@','\at\'),'^','\carat\'),'|','\pipe\'),'=','\equal\') + '^'
--Returns data
SELECT TAG_NAME, TAG_DATA FROM @TEMPTABLE ORDER BY [ID]
I assume its Inserting a ^ therefore @@rowcount is not 0?
Anyway to check if it ='^'
Thx once again...
September 2, 2011 at 12:49 pm
jbalbo (9/2/2011)
OK so the SP has this code at the botton--DO NOT CHANGE BELOW THIS LINE--
--Replaces special characters in data to prevent error when reading into HTML Forms
UPDATE @TEMPTABLE SET TAG_DATA = REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(TAG_DATA,'"','\quote\'),'@','\at\'),'^','\carat\'),'|','\pipe\'),'=','\equal\') + '^'
--Returns data
SELECT TAG_NAME, TAG_DATA FROM @TEMPTABLE ORDER BY [ID]
I assume its Inserting a ^ therefore @@rowcount is not 0?
Anyway to check if it ='^'
Thx once again...
Do you mean you don't want to select rows from @TEMPTABLE where TAG_DATA <>< '^'?
_______________________________________________________________
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 15 posts - 1 through 15 (of 21 total)
You must be logged in to reply to this topic. Login to reply