November 20, 2007 at 9:30 am
Hello all
Scenario: I am looking to only use this case statement if the field P21PLAY.dbo.p21_view_oe_pick_ticket.carrier_id is equal to (105188, 105191,
105194, 105197, 105200, 105203, 105206, 105209, 105212 )
- If the carrier_id is not equal to these numbers than I need to return NULL
Right now it is doing to opposite, Im guess im just looking for the opposite of the IN trigger??
CASE WHEN P21PLAY.dbo.p21_view_oe_pick_ticket.carrier_id
IN (105188, 105191, 105194, 105197, 105200
, 105203, 105206, 105209, 105212 )
THEN NULL
WHEN SUBSTRING(P21PLAY.dbo.p21_view_oe_hdr.delivery_instructions
, PATINDEX('%[^a-z ]%'
, P21PLAY.dbo.p21_view_oe_hdr.delivery_instructions) + 1, 6)
IS NULL
THEN dbo.Address_Table.ups_code
ELSE SUBSTRING(P21PLAY.dbo.p21_view_oe_hdr.delivery_instructions
, PATINDEX('%[^a-z ]%'
, P21PLAY.dbo.p21_view_oe_hdr.delivery_instructions) + 1, 6)
END AS UPS_FINAL
November 20, 2007 at 9:37 am
How about just using the keyword Not?
As in,
WHEN NOT P21PLAY.dbo.p21_view_oe_pick_ticket.carrier_id IN
November 20, 2007 at 9:39 am
Bah im an idiot.. I tried like 5 diff switches before that haha
November 20, 2007 at 9:55 am
OK could someone help me re-evaluate my statement here. I am looking to only use this CASE when these values (105188, 105191, 105194, 105197, 105200, 105203, 105206, 105209, 105212) are in p21_view_oe_pick_ticket.carrier_id field. Here is my statement:
CASE WHEN P21PLAY.dbo.p21_view_oe_pick_ticket.carrier_id
NOT IN (105188, 105191, 105194, 105197, 105200
, 105203, 105206, 105209, 105212 )
THEN NULL
WHEN SUBSTRING(P21PLAY.dbo.p21_view_oe_hdr.delivery_instructions
, PATINDEX('%[^a-z ]%'
, P21PLAY.dbo.p21_view_oe_hdr.delivery_instructions) + 1, 6)
IS NULL
THEN dbo.Address_Table.ups_code
ELSE SUBSTRING(P21PLAY.dbo.p21_view_oe_hdr.delivery_instructions
, PATINDEX('%[^a-z ]%'
, P21PLAY.dbo.p21_view_oe_hdr.delivery_instructions) + 1, 6)
END AS UPS_FINAL
When I run this it returns the values that are in my CASE if P21PLAY.dbo.p21_view_oe_pick_ticket.carrier_id is on of my values or not. any help appreciate ???
November 20, 2007 at 10:49 am
I'm not sure what you mean by 'not using the CASE'.
In your code the Case is run on every record. One of three things can happen:
If your carrier_id does not equal one of the numbers in your in statement, UPS_Final is set to null
else if your substring conditions are true, something else happens
else you return substring
Is your problem that UPS_Final is never being set to Null?
November 20, 2007 at 10:55 am
Yup exactly thats my issue is that my values in UPS_Final are never set to null. When I execute with a matching number I get a value in UPS_Final and when I dont have a match in Carrier_id I still get a value in UPS_Final when I want a null
November 20, 2007 at 11:32 am
Hmm, then that is strange, I can't see anything wrong with your Case statement, and in my own tests I can't reproduce the problem. Is it possible that the carrier_id is not an integer? If it is a varchar or something, that could explain why the IN is not working.
November 20, 2007 at 11:36 am
Wierd to me to.. the other way works perfectly for me?? I checked the field and it is
decimal(19,0) null
Is there another way I can write this same case statement possibly ??
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply