September 5, 2002 at 7:38 am
I do understand what OR does :]
I also tried to use IN but it did not work either(its an or written a different way). I need to see if the last field(QUALIFIED_EQUIP) is one value or the other value but still keep all the other where checks. I was wondering if there was a way to have it check for the 2 values w/o having to write all the where checks again for the OR part. This is written in Unidata SQL but just take the sql statement as if it were written in sql server sql. I'll figure out the equilivent if there is on. You'll see why I dont want to write all my checks again unless I really have to...
But take the following sql statement(brace yourself now):
SELECT DISTINCT CM.ID,NAME1,PHONE,CITY,STATE,QUALIFIED_EQUIP,INSURANCE_EXP,INSURANCE_EXP2,INSURANCE_AMT FROM CONTRACTOR_MASTER CM INNER JOIN CONTRACTOR_DRIVER_PROFILE CDP ON CM.ID = CDP.CONTRACTOR_ID UNNEST QUALIFIED_EQUIP WHERE CDP.CONT_TYPE = 2 AND CDP.BROKER < 21185 AND CDP.CANCEL_DATE < CDP.CONTRACT_DATE AND INSURANCE_AMT >= 100000 AND INSURANCE_EXP_NC > TODAY_NC AND INSURANCE_EXP2 > TODAY_NC AND QUALIFIED_EQUIP IN('" & type1 & "','ALL') ORDER BY NAME1
Matt
September 5, 2002 at 7:47 am
Not quite sure what you're asking. Maybe the CASE statement will do what you want. From Books On Line:
USE pubs
GO
SELECT 'Price Category' =
CASE
WHEN price IS NULL THEN 'Not yet priced'
WHEN price < 10 THEN 'Very Reasonable Title'
WHEN price >= 10 and price < 20 THEN 'Coffee Table Title'
ELSE 'Expensive book!'
END,
CAST(title AS varchar(20)) AS 'Shortened Title'
FROM titles
ORDER BY price
GO
Oracle has something similar in their version, but I'm not pulling it out of my head right now.
There is also a subselect. ...AND QUALIFIED_EQUIP IN (SELECT ...)
Steve
Steve Miller
September 5, 2002 at 7:51 am
hmm I don't think that is what I am looking for. Let me try to explain myself better. All that stuff that has AND in between it has to take effect when I want qualified_equip to be lets say for example Container or all. I dont want to have write all those ands again after the Or to get what I need and its probably bad for performance. I'll take a closer look at what you wrote but I dont think it will help.
Matt
September 5, 2002 at 8:00 am
Hmm I am so screwed once again. No support for CASE in unisql. if there is possibly another way I am all ears.
Matt
September 5, 2002 at 10:04 am
I think part of the problem is that I'm not seeing any OR in the whole select statement. (It would help if it was not all uppercase.) Sorry, but I'm not seeing what the problem is.
Steve
Steve Miller
September 5, 2002 at 11:12 am
I'm gonna give it a stab...try this:
SELECT DISTINCT CM.ID,NAME1,PHONE,CITY,STATE,QUALIFIED_EQUIP,INSURANCE_EXP,INSURANCE_EXP2,INSURANCE_AMT
FROM CONTRACTOR_MASTER CM INNER JOIN CONTRACTOR_DRIVER_PROFILE CDP
ON CM.ID = CDP.CONTRACTOR_ID UNNEST QUALIFIED_EQUIP
WHERE (CDP.CONT_TYPE = 2 AND CDP.BROKER < 21185 AND CDP.CANCEL_DATE < CDP.CONTRACT_DATE AND INSURANCE_AMT >= 100000 AND INSURANCE_EXP_NC > TODAY_NC AND INSURANCE_EXP2 > TODAY_NC) AND (QUALIFIED_EQUIP IN('" & type1 & "','ALL'))ORDER BY NAME1
Note the parenthesis that I added to the where clause. Ignore the line breaks, I did that to improve 'readability'. I hope it works for you.
-Bill Fleming
September 5, 2002 at 11:48 am
That did the trick bill. I wish I thought of that. Thats gonna come in handy.
Steve: IN is like an OR except less code for multiple or's.
September 5, 2002 at 1:05 pm
>> Steve: IN is like an OR except less code for multiple or's.
Well, sure. But if ask my wife, she'd tell you about this. If says that the tea is in the red box, and it's really in a green box, I won't see it, even if it's 6 inches from my nose. I'm still looking for the <euphemism> red box.
By the way, I thought of Bill's solution about 1/2 hour after I posted my last message. Course, no one will believe me at this point [:-)] So here's to Bill.
Steve
Steve Miller
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply