September 29, 2017 at 11:00 am
I hope you can help me. I have been informed that the code I wrote does not have mutually exclusive case statements for the WH_STATE_CD. I am clueless as of how to fix it (it may be so simple that it is flying over my head). The parameters are:
1) Records prior to 2017-01-01 will have a state code and labor location code that will correspond when it is a valid domestic location and will have "NA" in the state code when it is a valid foreign locality;
2) Records after 2017-01-01 will have the STATE_CD IS NULL and only the locality will provide the state code (SUBSTRING(LAB_LOC_CD,3,2) or (SUBSTRING(UDF12_ID,3,2) depending upon whether the date is coming from APP1 or APP2). If it is a valid 6-character, domestic location beginning with 'COUNTRY_CD'
Any thoughts on what I may be missing?
WHEN LEN(B.LAB_LOC_CD) > 2 AND LEFT(B.LAB_LOC_CD,2) = 'COUNTRY_CD' THEN SUBSTRING(B.LAB_LOC_CD,3,2)
WHEN LEN(B.LAB_LOC_CD) = 2 AND (B.WH_STATE_CD = 'NA' OR B.WH_STATE_CD = 'NA') THEN ' '
WHEN (B.WH_STATE_CD = 'NA' OR B.WH_STATE_CD = 'NA') THEN ' '
WHEN B. WH_STATE_CD IS NULL TTHEN ' ' ELSE B.WH_STATE_CD END AS WH_STATE_CD,
September 29, 2017 at 11:07 am
How about you post the whole query instead of just a snippet along with the full error message.
September 29, 2017 at 11:08 am
patti44556 - Friday, September 29, 2017 11:00 AMI hope you can help me. I have been informed that the code I wrote does not have mutually exclusive case statements for the WH_STATE_CD. I am clueless as of how to fix it (it may be so simple that it is flying over my head). The parameters are:
1) Records prior to 2017-01-01 will have a state code and labor location code that will correspond when it is a valid domestic location and will have "NA" in the state code when it is a valid foreign locality;
2) Records after 2017-01-01 will have the STATE_CD IS NULL and only the locality will provide the state code (SUBSTRING(LAB_LOC_CD,3,2) or (SUBSTRING(UDF12_ID,3,2) depending upon whether the date is coming from APP1 or APP2). If it is a valid 6-character, domestic location beginning with 'COUNTRY_CD'
Any thoughts on what I may be missing?WHEN LEN(B.LAB_LOC_CD) > 2 AND LEFT(B.LAB_LOC_CD,2) = 'COUNTRY_CD' THEN SUBSTRING(B.LAB_LOC_CD,3,2)
WHEN LEN(B.LAB_LOC_CD) = 2 AND (B.WH_STATE_CD = 'NA' OR B.WH_STATE_CD = 'NA') THEN ' '
WHEN (B.WH_STATE_CD = 'NA' OR B.WH_STATE_CD = 'NA') THEN ' '
WHEN B. WH_STATE_CD IS NULL TTHEN ' ' ELSE B.WH_STATE_CD END AS WH_STATE_CD,
Let's start with the comparison of exactly 2 characters from LAB_LOC_CD to the string 'COUNTRY_CD'. That's never going to be equal. If you were comparing to an actual field named COUNTRY_CD, you wouldn't need the quotes there, so maybe that's the problem?
Steve?(aka sgmunson)?:) 🙂 :)?
Health & Nutrition
Rent Servers for Income (picks and shovels strategy)
September 29, 2017 at 11:12 am
The more I think about this, the more it seems likely that COUNTRY_CD was not meant to be a literal string. I suspect that the query was created without that understanding, and that the comparison would have to be against a list of valid country codes, so you might need to be re-thinking this and using an IN (SELECT COUNTRY_CD FROM SOME_TABLE) where you have to figure out where that data is located and make that subquery a part of your CASE statement.
Steve?(aka sgmunson)?:) 🙂 :)?
Health & Nutrition
Rent Servers for Income (picks and shovels strategy)
September 29, 2017 at 11:18 am
So if I replace the COUNTRY_CD on the script to be permanently US, aren't they continue to be mutually exclusive? That is what I was told. The logic is not correct as the state and location don't have mutually exclusive case statements.
WHEN LEN(B.LAB_LOC_CD) > 2 AND LEFT(B.LAB_LOC_CD,2) = 'US' THEN SUBSTRING(B.LAB_LOC_CD,3,2)
WHEN LEN(B.LAB_LOC_CD) = 2 AND (B.WH_STATE_CD = 'NA' OR B.WH_STATE_CD = 'NA') THEN ' '
WHEN (B.WH_STATE_CD = 'NA' OR B.WH_STATE_CD = 'NA') THEN ' '
WHEN B. WH_STATE_CD IS NULL THEN ' ' ELSE B.WH_STATE_CD END AS WH_STATE_CD,
September 29, 2017 at 11:20 am
Lynn Pettis - Friday, September 29, 2017 11:07 AMHow about you post the whole query instead of just a snippet along with the full error message.
Hi Lynn, that is the section of code that is erroing out. I was informed that the logic for the state and location is not quite right because it doesn't have mutually exclusive case statements for the WH_STATE_CD.
September 29, 2017 at 11:24 am
patti44556 - Friday, September 29, 2017 11:20 AMLynn Pettis - Friday, September 29, 2017 11:07 AMHow about you post the whole query instead of just a snippet along with the full error message.Hi Lynn, that is the section of code that is erroing out. I was informed that the logic for the state and location is not quite right because it doesn't have mutually exclusive case statements for the WH_STATE_CD.
What ever that means. It would help to see all the code and the actual error message.
September 29, 2017 at 11:32 am
patti44556 - Friday, September 29, 2017 11:18 AMSo if I replace the COUNTRY_CD on the script to be permanently US, aren't they continue to be mutually exclusive? That is what I was told. The logic is not correct as the state and location don't have mutually exclusive case statements.
WHEN LEN(B.LAB_LOC_CD) > 2 AND LEFT(B.LAB_LOC_CD,2) = 'US' THEN SUBSTRING(B.LAB_LOC_CD,3,2)
WHEN LEN(B.LAB_LOC_CD) = 2 AND (B.WH_STATE_CD = 'NA' OR B.WH_STATE_CD = 'NA') THEN ' '
WHEN (B.WH_STATE_CD = 'NA' OR B.WH_STATE_CD = 'NA') THEN ' '
WHEN B. WH_STATE_CD IS NULL THEN ' ' ELSE B.WH_STATE_CD END AS WH_STATE_CD,
You also have an OR condition in the second line that isn't doing anything useful, as it compares B.WH_STATE_CD to 'NA' on both pieces of the OR, so the parentheses associated with the grouping can go away and you can remove either of the two copies of B.WH_STATE_CD = 'NA'. You then do the exact same OR as your next condition to be tested, and then follow that up with a test for a NULL value. I'm guessing this is not the correct order to this in, as a NULL value could potentially mess up the other conditions, but without knowing your data, it's difficult to know the end result. Finally, when you examine the whole thing, you test for a length > 2 AND the leftmost 2 = 'US', and then you test for a length of 2 AND it's equal to 'NA', which is kind of redundant. I'm not sure why the length check is necessary if you're going to check for equality to a specific value. By the third line, you know that either the length is > 2 AND the first two letters are NOT 'US', or the length is NOT = 2 AND that the value is NOT = 'NA'. I'd suggest strongly that you come to an agreed upon list of tests and the order in which they should be performed, because the length checking may be complicating the conditions and might not be relevant to what you want to accomplish.
Steve?(aka sgmunson)?:) 🙂 :)?
Health & Nutrition
Rent Servers for Income (picks and shovels strategy)
September 29, 2017 at 11:32 am
Hi Lynn, script I wrote is below. I don't have access to the store procedure to see the whole thing but that script below was based on the parameters given.
,CASE
WHEN LEN(B.LAB_LOC_CD) > 2 AND LEFT(B.LAB_LOC_CD,2) = 'US' THEN SUBSTRING(B.LAB_LOC_CD,3,2)
WHEN LEN(B.LAB_LOC_CD) = 2 AND (B.WH_STATE_CD = 'NA' OR B.WH_STATE_CD = 'NA') THEN ' '
WHEN (B.WH_STATE_CD = 'NA' OR B.WH_STATE_CD = 'NA') THEN ' '
WHEN B. WH_STATE_CD IS NULL THEN ' ' ELSE B.WH_STATE_CD END AS WH_STATE_CD
,CASE
WHEN LEN(B.LAB_LOC_CD) > 2 AND LEFT(B.LAB_LOC_CD,2) = 'US' THEN SUBSTRING(B.LAB_LOC_CD,5,2) ELSE LAB B.LAB_LOC_CD END AS WH_LOC_CD.
September 29, 2017 at 11:47 am
Hi Steve, the query was based on the parameters given by the business. It looks like they need validation due to the fact that locations must be two characters long and if no location, then ' '.
The whole script I wrote is below based on their parameters. I don't have access to the stored procedures to see the whole thing but that script below was based just on the parameters given. I am new to SQL so I am trying to figure this out the best way I can. Can you highlight what I need to exclude from the statement?
,CASE
WHEN LEN(B.LAB_LOC_CD) > 2 AND LEFT(B.LAB_LOC_CD,2) = 'US' THEN SUBSTRING(B.LAB_LOC_CD,3,2)
WHEN LEN(B.LAB_LOC_CD) = 2 AND (B.WH_STATE_CD = 'NA' OR B.WH_STATE_CD = 'NA') THEN ' '
WHEN (B.WH_STATE_CD = 'NA' OR B.WH_STATE_CD = 'NA') THEN ' '
WHEN B. WH_STATE_CD IS NULL THEN ' ' ELSE B.WH_STATE_CD END AS WH_STATE_CD
,CASE
WHEN LEN(B.LAB_LOC_CD) > 2 AND LEFT(B.LAB_LOC_CD,2) = 'US' THEN SUBSTRING(B.LAB_LOC_CD,5,2) ELSE LAB B.LAB_LOC_CD END AS WH_LOC_CD
September 29, 2017 at 12:28 pm
CASE
WHEN LEN(B.LAB_LOC_CD) > 2 AND LEFT(B.LAB_LOC_CD,2) = 'US' THEN SUBSTRING(B.LAB_LOC_CD,3,2)
WHEN LEN(B.LAB_LOC_CD) = 2 AND B.WH_STATE_CD = 'NA' THEN ' '
WHEN B.WH_STATE_CD = 'NA' THEN ' '
WHEN B.WH_STATE_CD IS NULL THEN ' '
ELSE B.WH_STATE_CD
END AS WH_STATE_CD
Steve?(aka sgmunson)?:) 🙂 :)?
Health & Nutrition
Rent Servers for Income (picks and shovels strategy)
October 3, 2017 at 3:04 am
patti44556 - Friday, September 29, 2017 11:47 AMHi Steve, the query was based on the parameters given by the business. It looks like they need validation due to the fact that locations must be two characters long and if no location, then ' '.
The whole script I wrote is below based on their parameters. I don't have access to the stored procedures to see the whole thing but that script below was based just on the parameters given. I am new to SQL so I am trying to figure this out the best way I can. Can you highlight what I need to exclude from the statement?,CASE
WHEN LEN(B.LAB_LOC_CD) > 2 AND LEFT(B.LAB_LOC_CD,2) = 'US' THEN SUBSTRING(B.LAB_LOC_CD,3,2)
WHEN LEN(B.LAB_LOC_CD) = 2 AND (B.WH_STATE_CD = 'NA' OR B.WH_STATE_CD = 'NA') THEN ' '
WHEN (B.WH_STATE_CD = 'NA' OR B.WH_STATE_CD = 'NA') THEN ' '
WHEN B. WH_STATE_CD IS NULL THEN ' ' ELSE B.WH_STATE_CD END AS WH_STATE_CD
,CASE
WHEN LEN(B.LAB_LOC_CD) > 2 AND LEFT(B.LAB_LOC_CD,2) = 'US' THEN SUBSTRING(B.LAB_LOC_CD,5,2) ELSE LAB B.LAB_LOC_CD END AS WH_LOC_CD
The syntax looks fine.
First solve the problem then write the code !
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply