mixing ands and ors in sql statement

[Lhuffst]

I have an sql statement that isn't returning all my information. While most of the info is the same, what I'm trying to do is return records if

wbs_name = award AND task_name = 'Execute Notice %'
or
wbs_name = Advertisment & Bidding AND task_name in ('Solicitation Posted %', 'Bid Opening', 'Obtain MDE%')

 My Sql is: 
select distinct
a.proj_id,
b.proj_catg_name,
b.Proj_catg_short_name,
c.wbs_name as cwbs,
extract(year from d.ACT_END_DATE) as YrEndDate,
To_Char(d.early_end_date, 'DD-MON-YYYY') as EarlyEndDate,
d.Task_name,
d.task_code,
d.Status_code,
to_Char(D.ACT_START_DATE,'DD-MON-YYYY') as TaskActStart,
To_Char(d.act_end_date,'DD-MON-YYYY') as TaskActEnd,
e.Proj_Short_Name as ProjShortName
from admuser.projpcat a, admuser.pcatval b, admuser.projwbs c, admuser.task d, admuser.project e
where 
    C.PROJ_ID = A.PROJ_ID 
AND B.PROJ_CATG_ID = A.PROJ_CATG_ID
AND d.proj_id = a.proj_id
and e.proj_id = a.proj_id
AND E.PROJ_SHORT_NAME LIKE 'A6 IDIQ ESA%'
AND E.PROJ_SHORT_NAME NOT LIKE '%-%'
and b.proj_catg_name = 'IDIQ'
and
(
    (
         d.TASK_NAME  like 'Execute Notice of  Award (NOA)'
          and C.WBS_NAME ='Award'
    )
    OR
    (
      (
          d.TASK_NAME like  'Solicitation Posted on CBR' 
          Or d.tASK_NAME  like 'Award *'
          Or d.TASK_NAME  like 'Bid Opening' 
          Or d.TASK_NAME  like 'Obtain MDE approval Prior to Contract Award'
      )
      AND C.WBS_NAME Like 'Advertisement '||chr(38)|| ' Bidding' --<>'award'
      )
) 
--and e.Proj_id = '9917'
and e.proj_id = '9918'
--and d.task_code Not Like Upper('x%')
and extract(year from d.ACT_END_DATE) >= '2011'
order by task_code

I am missing any of the award information.
Does anyone see what I did wrong?

 

[karluk]

I would start with the fact that you are using the "like" operator on strings that don't contain any wildcard characters. It makes me suspicious that you haven't precisely identified all of the strings that you are trying to match.

         d.TASK_NAME like  'Solicitation Posted on CBR' 
          Or d.tASK_NAME  like 'Award *'
          Or d.TASK_NAME  like 'Bid Opening' 
          Or d.TASK_NAME  like 'Obtain MDE approval Prior to Contract Award'
      )
      AND C.WBS_NAME Like 'Advertisement '||chr(38)|| ' Bidding' --<>'award'

 

[SantaMufasa]

Also, LHuffst, I see the use of the "%" in your description in operations without the LIKE operator (e.g., = 'Execute Notice %'; task_name in ('Solicitation Posted %', 'Bid Opening', 'Obtain MDE%').) When you use the "%" operator without the LIKE operator, the the "%", the symbol represents a literal percent sign in your data...I don't believe that is what you are intending.

I propose that when you are using a complex conditional clause such as yours, that if you are not receiving the rows you hoped for, then you begin with no WHERE clause, then add in WHERE subclauses, confirming with each run that you are receiving the results you intended with the new addition, until you complete the WHERE clause as you want it. Further (as you already know), you can override default AND/OR order of execution with the use of parentheses.

Please let us know if any of this turns out to be helpful.

Mufasa
(aka Dave of Sandy, Utah, USA)
“People may forget what you say, but they will never forget how you made them feel.

 

[Lhuffst]

Hi The reason that I had the % sign is because not all of the information was put in the exact same way. For example 'Obtain MDE%' -- I have Obtain MDE approval... as well as Obtain MDE Approval. because of the extra spaces and differences in caps vs lower case, I figured it would be more efficient to stop where the continutity ended. If there is a better way, I am definitely open to suggestions.

I did start backing out each of the where clauses as suggested and what I ultimately figured out is that my problem was with this line

 and extract(year from d.ACT_END_DATE) >= '2011'

I had a few records that were not closed yet so they were omitted from my query. I changed the line to:
[blue]
and extract(year from d.ACT_END_DATE) >= '2011' or Is Null(d.act_end_date) but I get a syntax error. Do you know the correct way to write that statement? [/blue]

 

[SantaMufasa]

Hi The reason that I had the % sign is because not all of the information was put in the exact same way. For example 'Obtain MDE%' -- I have Obtain MDE approval... as well as Obtain MDE Approval. because of the extra spaces and differences in caps vs lower case, I figured it would be more efficient to stop where the continutity ended. If there is a better way, I am definitely open to suggestions.

That is my precise point...in your description, you were using an "=" operator in combination with the "%". To use "%" as a wild-card mask, you must use the LIKE operator instead of the "=" operator.

If you have upper-/lower-case inconsistencies in your data, you can say something like:

...where UPPER(d.task_name) like 'OBTAIN MDE%'...

You can also use the INSTR() function to your advantage, as well:

...where INSTR(UPPER(D.tast_name),'OBTAIN MDE') > 0...

...and extract(year from d.ACT_END_DATE) >= '2011' or Is Null(d.act_end_date) but I get a syntax error. Do you know the correct way to write that statement?

Try this code:

select last_name,start_date
  from s_emp
 where extract(year from start_date) = 2011 or start_date is null;

LAST_NAME           START_DATE
------------------- ----------
Urguhart            19-DEC-11
Giljum              19-DEC-11
Nguyen              23-DEC-11

3 rows selected.

Let us know if you have additional questions.

Mufasa
(aka Dave of Sandy, Utah, USA)
“People may forget what you say, but they will never forget how you made them feel.