September 16, 2011 at 12:08 pm
Hi,
I have this statement:
insert into @TEMPTABLE (TAG_NAME, TAG_DATA)
select TAG_NAME = 'CUST_ALLERGIES',
TAG_DATA =CRT.FullName + '~' + M.GENERIC_NAME
FROM MEDICATION M WITH (NOLOCK)
INNER JOIN CLIENT_ALLERGY CA ON M.OID = CA.MEDICATION_MONIKER
Inner join CHEMICAL_REFERENCE_TYPE CRT (nolock) on crt.oid = m.chemical_reference_type
INNER JOIN @Top5Allergies T ON CA.OID = T.CACA_OID_LINK
INNER JOIN CLIENTAGENCY_TO_CLIENT_ALLERGY_COLLECTION CACA (NOLOCK) ON CACA.OID_LINK = T.CACA_OID_LINK
INNER JOIN CLIENT_TO_CLIENTAGENCY_COLLECTION CAC (NOLOCK) ON CAC.OID_LINK = CACA.OID
WHERE CAC.OID = @CLIENT_OID
I want to add an isnull to it so if there is no data TAG_DATA = "No Allergies"
Thx in Advance
Joe
September 16, 2011 at 12:17 pm
TAG_DATA = isnull(CRT.FullName + '~' + M.GENERIC_NAME, 'No Allergies')
--EDIT--
You might also need to change your join to CLIENT_ALLERGY from an inner join to a left join?
_______________________________________________________________
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 16, 2011 at 12:36 pm
Hi Sean,
Thanks for the advice..
SO I set it like:
TAG_DATA = isnull(CRT.FullName + '~' + M.GENERIC_NAME)
FROM MEDICATION M WITH (NOLOCK)
INNER JOIN CLIENT_ALLERGY CA ON M.OID = CA.MEDICATION_MONIKER
Inner join CHEMICAL_REFERENCE_TYPE CRT (nolock) on crt.oid = m.chemical_reference_type
INNER JOIN @Top5Allergies T ON CA.OID = T.CACA_OID_LINK
INNER JOIN CLIENTAGENCY_TO_CLIENT_ALLERGY_COLLECTION CACA (NOLOCK) ON CACA.OID_LINK = T.CACA_OID_LINK
INNER JOIN CLIENT_TO_CLIENTAGENCY_COLLECTION CAC (NOLOCK) ON CAC.OID_LINK = CACA.OID
WHERE CAC.OID = @CLIENT_OID,'No Allergies')
but getting this error:
Msg 156, Level 15, State 1, Procedure usi_sp_MA_AT_ACA_UDD_TrtPln, Line 834
Incorrect syntax near the keyword 'FROM'.
but it runs fine with out the isnull..
Thx
September 16, 2011 at 12:46 pm
Well you didn't specify what value to use when it is null.
TAG_DATA = isnull(CRT.FullName + '~' + M.GENERIC_NAME)
Isnull requires two arguments and you only gave it one. Here is the BOL article.
_______________________________________________________________
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 16, 2011 at 1:08 pm
jbalbo (9/16/2011)
Hi Sean,Thanks for the advice..
SO I set it like:
TAG_DATA = isnull(CRT.FullName + '~' + M.GENERIC_NAME)
FROM MEDICATION M WITH (NOLOCK)
INNER JOIN CLIENT_ALLERGY CA ON M.OID = CA.MEDICATION_MONIKER
Inner join CHEMICAL_REFERENCE_TYPE CRT (nolock) on crt.oid = m.chemical_reference_type
INNER JOIN @Top5Allergies T ON CA.OID = T.CACA_OID_LINK
INNER JOIN CLIENTAGENCY_TO_CLIENT_ALLERGY_COLLECTION CACA (NOLOCK) ON CACA.OID_LINK = T.CACA_OID_LINK
INNER JOIN CLIENT_TO_CLIENTAGENCY_COLLECTION CAC (NOLOCK) ON CAC.OID_LINK = CACA.OID
WHERE CAC.OID = @CLIENT_OID,'No Allergies')
but getting this error:
Msg 156, Level 15, State 1, Procedure usi_sp_MA_AT_ACA_UDD_TrtPln, Line 834
Incorrect syntax near the keyword 'FROM'.
but it runs fine with out the isnull..
Thx
WOW I just realized what you did. A query does not return a null it returns rows. These rows contain columns. The crossroads of a row and column is often referred to as a field (or cell). The IsNull says if this cell is null to replace the null with something.
You need to understand what you are trying to do. I am not 100% sure which field you want to check for null. You might to check for null on the client_allergy table. Something like:
select
...
case CA.MEDICATION_MONIKER
when null then 'No Allergies'
else CRT.FullName + '~' + M.GENERIC_NAME
end as TAG_DATA
...
It is hard for me to speculate what your requirements are but I would think one of those two paths should get you what you need. Make sure YOU understand the code before deploying this live. It is your phone that will ring at 3am when the system crashes and you have to fix 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/
September 16, 2011 at 1:10 pm
One other point. Why are you using the nolock hint on every table? http://sqlblog.com/blogs/andrew_kelly/archive/2009/04/10/how-dirty-are-your-reads.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 16, 2011 at 1:13 pm
Also, it's still using Inner Join for all the joins so if there is no allergy the query will presumably return no row rather than a row with a NULL in it, as it is not likely that the MEDICATION table contains rows with NULL in the generic name column or the CHEMICAL_REFERENCE_TABLE contains row with NULL in the FullName column; it seems likely that at least one (and probably more) of these joins needs to be a left join rather than an inner join.
Tom
September 16, 2011 at 1:17 pm
Hi Sean,
Im not sure on the NOLOCKS, its something I inherited and as you can tell Im new at this.. 🙂
But your not the first person to ask...
I think you right on the CASE statement ..
what I want is if there is no data found for allergeies I want 'No Alleregies' in TAG_DATA
September 16, 2011 at 1:22 pm
As Tom and I have pointed out you will have to change your join to a left from an inner. You should read that article I linked about dirty reads. My guess is they should be removed. All too often they are used because "the queries are faster". This may provide a very slight improvement on speed but is generally a sign of a larger underlying issue. The risk of dirty reads is rarely worth it. The only time I am usually ok with (nolock) is for reports but even though there needs to be a justifiable reason (and performance does not count). 🙂
Give it a shot with the left join and case statement and let us know if you get if figured out.
_______________________________________________________________
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 16, 2011 at 1:44 pm
Hi..
So I got the Nolocks and inner joins cleaned up
insert into @TEMPTABLE (TAG_NAME, TAG_DATA)
select TAG_NAME = 'CUST_ALLERGIES',
TAG_DATA =CRT.FullName + '~' + M.GENERIC_NAME
FROM MEDICATION M
left outer JOIN CLIENT_ALLERGY CA ON M.OID = CA.MEDICATION_MONIKER
left outer join CHEMICAL_REFERENCE_TYPE CRT on crt.oid = m.chemical_reference_type
left outer JOIN @Top5Allergies T ON CA.OID = T.CACA_OID_LINK
left outer JOIN CLIENTAGENCY_TO_CLIENT_ALLERGY_COLLECTION CACA ON CACA.OID_LINK = T.CACA_OID_LINK
left outer JOIN CLIENT_TO_CLIENTAGENCY_COLLECTION CAC ON CAC.OID_LINK = CACA.OID
WHERE CAC.OID = @CLIENT_OID
Im just trying to figure out the CASE statement..
Joe
September 16, 2011 at 2:02 pm
jbalbo (9/16/2011)
Hi..So I got the Nolocks and inner joins cleaned up
insert into @TEMPTABLE (TAG_NAME, TAG_DATA)
select TAG_NAME = 'CUST_ALLERGIES',
TAG_DATA =CRT.FullName + '~' + M.GENERIC_NAME
FROM MEDICATION M
left outer JOIN CLIENT_ALLERGY CA ON M.OID = CA.MEDICATION_MONIKER
left outer join CHEMICAL_REFERENCE_TYPE CRT on crt.oid = m.chemical_reference_type
left outer JOIN @Top5Allergies T ON CA.OID = T.CACA_OID_LINK
left outer JOIN CLIENTAGENCY_TO_CLIENT_ALLERGY_COLLECTION CACA ON CACA.OID_LINK = T.CACA_OID_LINK
left outer JOIN CLIENT_TO_CLIENTAGENCY_COLLECTION CAC ON CAC.OID_LINK = CACA.OID
WHERE CAC.OID = @CLIENT_OID
Im just trying to figure out the CASE statement..
Joe
Definitely looking better but you might be a bit careful about just changing all your joins from inner to left. That may not get the same results. The type of join depends on what data you want to return. You might want to read on join types so you understand what they mean.
_______________________________________________________________
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 16, 2011 at 2:17 pm
Hi Sean,
Im more of a crystal reports person, and the person who orginally wrote this ALWAYS used inner joins
I tested the joins and kno via Crystal they r Left outer on those tables.
Is there any place to ssuggest how to setup the case, just confused on where rto put the case logic
also can I put some kind of null statement following the select statement instead of case
where if the Medication_moniker was null it would move "No Allergies" to Tag-Data?
September 16, 2011 at 2:20 pm
Look at my example of the case statement from several posts ago. A case statement is just a way to conditionally get a value in your query.
select
[other columns],
case [look at my example from before],
[any other columns]
from [your list of tables and joins]
where [your where clause here]
_______________________________________________________________
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 16, 2011 at 2:35 pm
tried this.. getting a systax error??
select
TAG_NAME = 'CUST_ALLERGIES',
case CA.MEDICATION_MONIKER
when null then 'No Allergies'
else
CRT.FullName + '~' + M.GENERIC_NAME
FROM MEDICATION M
left outer JOIN CLIENT_ALLERGY CA ON M.OID = CA.MEDICATION_MONIKER
left outer join CHEMICAL_REFERENCE_TYPE CRT on crt.oid = m.chemical_reference_type
left outer JOIN @Top5Allergies T ON CA.OID = T.CACA_OID_LINK
left outer JOIN CLIENTAGENCY_TO_CLIENT_ALLERGY_COLLECTION CACA ON CACA.OID_LINK = T.CACA_OID_LINK
left outer JOIN CLIENT_TO_CLIENTAGENCY_COLLECTION CAC ON CAC.OID_LINK = CACA.OID
WHERE CAC.OID = @CLIENT_OID
end as TAG_DATA
September 16, 2011 at 2:42 pm
select
TAG_NAME = 'CUST_ALLERGIES',
case CA.MEDICATION_MONIKER
when null then 'No Allergies'
else
CRT.FullName + '~' + M.GENERIC_NAME
end --you have to end your case statement
You really need to use BOL. Here is the article for the case statement. I know this stuff is new to you and possibly a bit over your head but it is the basic syntax you missed. 🙂
Just an fyi, with the way this is written if either CRT.FullName or M.GENERIC_NAME is null this will also return null because when you add anything to null it is ALWAYS null. If this is ok, then you should be good to go. If this is not ok then you may need to wrap each of those columns in an Isnull. Like Isnull(CRT.FullName, '') + '~' + Isnull(M.GENERIC_NAME, ''). That way if one of those columns is null you will get an empty string for that portion of the concatenated columns. Make sense?
_______________________________________________________________
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 22 total)
You must be logged in to reply to this topic. Login to reply