June 14, 2016 at 1:30 pm
My question is listed at the very end.
Help me write neat code and lets see whether someone can come up with some code where we don't have to hard code values ( using CASE statements )
Question: Each member whose PLAN CODE is not in the language_source table should get the value "'Health Plan Direct" as the ncqa_code when running the
script ( it does not happen now as it is .. Can you help )
If object_id('tempdb..#language_source') IS NOT NULL DROP TABLE #language_source
If object_id('tempdb..#memeber') IS NOT NULL DROP TABLE #member
CREATE TABLE #language_source( member_plan varchar(5), ncqa_code varchar(100));
CREATE TABLE #member( member_plan varchar(5),member_name varchar(100));
INSERT INTO #language_source( member_plan, ncqa_code )
Select 'MCR','CMS/State Database'
UNION
Select 'MMP','CMS/State Database'
UNION
Select NULL,'Health Plan Direct'
INSERT INTO #member( member_plan, member_name )
Select 'MCR', 'Julia Roberts'
UNION
Select 'ABC', 'John Trump'
UNION
Select 'XYZ', 'Valdimir Putin'
Select M.member_name, M.member_plan, LS.ncqa_code FROM #member M
Left join #language_source LS on ( LS.member_plan = M.member_plan )
-- I want everyone whose memeber_plan is not in the #language_source table to list 'Health Plan Direct' for ncqa_code field
June 14, 2016 at 1:35 pm
mw112009 (6/14/2016)
My question is listed at the very end.Help me write neat code and lets see whether someone can come up with some code where we don't have to hard code values ( using CASE statements )
Question: Each member whose PLAN CODE is not in the language_source table should get the value "'Health Plan Direct" as the ncqa_code when running the
script ( it does not happen now as it is .. Can you help )
If object_id('tempdb..#language_source') IS NOT NULL DROP TABLE #language_source
If object_id('tempdb..#memeber') IS NOT NULL DROP TABLE #member
CREATE TABLE #language_source( member_plan varchar(5), ncqa_code varchar(100));
CREATE TABLE #member( member_plan varchar(5),member_name varchar(100));
INSERT INTO #language_source( member_plan, ncqa_code )
Select 'MCR','CMS/State Database'
UNION
Select 'MMP','CMS/State Database'
UNION
Select NULL,'Health Plan Direct'
INSERT INTO #member( member_plan, member_name )
Select 'MCR', 'Julia Roberts'
UNION
Select 'ABC', 'John Trump'
UNION
Select 'XYZ', 'Valdimir Putin'
Select M.member_name, M.member_plan, LS.ncqa_code FROM #member M
Left join #language_source LS on ( LS.member_plan = M.member_plan )
-- I want everyone whose memeber_plan is not in the #language_source table to list 'Health Plan Direct' for ncqa_code field
Help me write neat code and lets see whether someone can come up with some code where we don't have to hard code values ( using CASE statements )
what is your adversion to CASE statements...and please explain why they cannot be used?
________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day
June 14, 2016 at 1:36 pm
great job on providing sample data.
it looks like a simple isnull is needed, am i right?
SELECT M.member_name,
M.member_plan,
ISNULL(LS.ncqa_code, 'Health Plan Direct')
FROM #member M
LEFT JOIN #language_source LS
ON ( LS.member_plan = M.member_plan )
Lowell
June 14, 2016 at 1:46 pm
Not quite...
What I was looking was a way to get that value but without having to hard code the value inside the code.
I was thinking whether there was a way to somehow get it from the table.
Of course one way would be to replace the HARD CODED value ( in your code )
with ( Select top 1 ncqa_code from #language_source where plan_code is null )
You see, I want to keep hard coded values in a table and not have them mixed in the code. This way, if someone asked me to change the value "Health Plan Direct" to some other value then I would only do one change ( that is in the table ) and not have to worry about searching for code and changing values.
Anyway, if there is no other way, I will have to hard code
June 14, 2016 at 1:47 pm
I came up with the same query and then I re-read the OP and it seemed he did not want anything 'hard coded'. So I came up with this.
Select M.member_name, M.member_plan,
isnull(LS.ncqa_code, (SELECT ncqa_code from #language_source where member_plan is NULL))
FROM #member M
Left join #language_source LS on ( LS.member_plan = M.member_plan )
Though, I would not recommend using NULL as a valid "value" for member_plan.
__________________________________________________________________________________________________________
How to Post to get the most: http://www.sqlservercentral.com/articles/Best+Practices/61537/
June 14, 2016 at 1:54 pm
ok, in that case, i would cross join the default value, and select it in the isnull:
SELECT M.member_name,
M.member_plan,
isnull(LS.ncqa_code, DEF.ncqa_code)
FROM #member M
LEFT JOIN #language_source LS
ON ( LS.member_plan = M.member_plan )
CROSS JOIN (SELECT TOP 1 ncqa_code
FROM #language_source
WHERE member_plan IS NULL) DEF
Lowell
June 14, 2016 at 2:04 pm
Good, I think I mentioned the same when replying..
The reason NULL was used is because every person in the table who had any other plan other than the 2 that was listed would get this common value ( 'Health Plan Direct' )
If I use anything other than NULL then I have to worry about that value being a value that will never ever be used as a plan_code.
Anyway, your solution works.. I guess that is the only solution.
June 16, 2016 at 12:14 am
You can use a CROSS APPLY as well
SELECT M.member_name,
M.member_plan,
LS.ncqa_code
FROM #member M
CROSS APPLY (
SELECT TOP 1 L.ncqa_code
FROM #language_source AS L
WHERE L.member_plan = M.member_plan OR L.member_plan IS NULL
ORDER BY L.member_plan DESC
) AS LS
How to post data/code on a forum to get the best help - Jeff Moden
http://www.sqlservercentral.com/articles/Best+Practices/61537/
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply