• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 161
  • Last Modified:

query returning everything

hi i have the following query
 
select count(empid) from employees emp,dept i
    where    ((:hire_date_from IS NOT null 
   and      :hire_date_to   IS NOT null
   and      :supplier  IS NOT null
   and      emp.trans_dte      between (:hire_date_from - 1) and (:hire_date_to + 1) 
   and      i.unt                = :supplier)
   or      (:hire_date_from IS NOT null 
   and      :hire_date_to   IS NOT null 
   and      :supplier      IS null
   and      emp.trans_dte      between (hire_date_from - 1) and (:hire_date_to + 1))
   or      (:hire_date_from     IS null
   and      :hire_date_to       IS null
   and      :supplier      IS null));
   

Open in new window

 
 
   the problem is if i pass
   
   hire_date_from    nulll
   hire_date_to      null
   supplier          15588890
   
   
   the query is retuning everything i only what to return 15588890 details
   
   
   but i still what to return everything if
   hire_date_from    null
   hire_date_to      null
   supplier          null
0
chalie001
Asked:
chalie001
1 Solution
 
Naveen KumarProduction Manager / Application Support ManagerCommented:
where is the condition in your query for this type of parameters -->  hire_date_from null ;
   hire_date_to      null ;    supplier          15588890 ;  

what is the count(*) returning when you are passing these values null, null, 15588890 ?
1
 
PortletPaulCommented:
I think you need a join predicate (or more than one) between those 2 tables (e.g. see bold below):

SELECT
      COUNT(empid)
FROM employees emp
inner join dept i on emp.dept_id = i.id

for the where clause I think it's this
WHERE  (  :hire_date_from IS NOT NULL
      AND :hire_date_to   IS NOT NULL
      AND i.unt = :supplier
      AND emp.trans_dte BETWEEN (:hire_date_from - 1) AND (:hire_date_to + 1)
       )
    OR (
          :hire_date_from IS NOT NULL
      AND :hire_date_to   IS NOT NULL
      AND :supplier       IS NULL
      AND emp.trans_dte BETWEEN (hire_date_from - 1) AND (:hire_date_to + 1)
       )
    OR (:supplier IS NULL)
;

Open in new window

0
 
chalie001Author Commented:
hi sorry the original query was suppose to be
select count(empid) from employees emp,dept i
   and    ((:hire_date_from IS NOT null 
   and      :hire_date_from_to   IS NOT null
   and      :supplier IS NOT null
   and      emp.trans_dte      between (:hire_date_from - 1) and (:hire_date_from_to + 1) 
   and      i.iss_unt                = :keys.scr_supplier)
   or      (:hire_date_from IS NOT null 
   and      :hire_date_from_to   IS NOT null 
   and      :supplier     IS null
   and      emp.trans_dte      between (:hire_date_from - 1) and (:hire_date_from_to + 1))
   or      (:hire_date_from     IS null
   and      :hire_date_from_to       IS null
   and      :supplier     IS null)
   or      (:hire_date_from     IS null
   and      :hire_date_from_to       IS null  
   and      :supplier IS NOT null))

Open in new window

0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
chalie001Author Commented:
this is the query
select count(empid) from employees emp,dept i  
   where idept.id = emp.deptid
   and   ((:hire_date_from IS NOT null 
   and      :hire_date_from_to   IS NOT null
   and      :supplier IS NOT null
   and      emp.trans_dte      between (:hire_date_from - 1) and (:hire_date_from_to + 1) 
   and      i.iss_unt                = :keys.scr_supplier)
   or      (:hire_date_from IS NOT null 
   and      :hire_date_from_to   IS NOT null 
   and      :supplier     IS null
   and      emp.trans_dte      between (:hire_date_from - 1) and (:hire_date_from_to + 1))
   or      (:hire_date_from     IS null
   and      :hire_date_from_to       IS null
   and      :supplier     IS null)
   or      (:hire_date_from     IS null
   and      :hire_date_from_to       IS null  
   and      :supplier IS NOT null))

Open in new window

0
 
chalie001Author Commented:
if  i pass eturning when you are passing these values null, null, 15588890 ? the query return everything
0
 
slightwv (䄆 Netminder) Commented:
>>if  i pass eturning when you are passing these values null, null, 15588890 ? the query return everything

That is because the last two OR'ed statements do not alter the where clause.

If you pass null, null, 15588890  your select will be:
select count(empid) from employees emp,dept i      where idept.id = emp.deptid


You hit the last part of the where:
(:hire_date_from     IS null
   and      :hire_date_from_to       IS null  
   and      :supplier IS NOT null)

This does nothing to change the rows returned.
0
 
chalie001Author Commented:
So I must remove the last part from my query
0
 
slightwv (䄆 Netminder) Commented:
I can tell you why that query is returning all the rows.

I cannot tell you what you need change because I don't know your requirements or your data.
0
 
chalie001Author Commented:
I what to return value for the supplie passes even if two date are null
0
 
slightwv (䄆 Netminder) Commented:
I cannot say for sure but is this what you want, using the SQL you posted:

Change:
(:hire_date_from     IS null
   and      :hire_date_to       IS null
   and      :supplier      IS null
)


to:
(:hire_date_from     IS null
   and      :hire_date_to       IS null
   and      i.unt                = :supplier
)


That said, I think this will replace the entire SQL you posted:
select count(empid) from employees emp,dept i  
   where idept.id = emp.deptid
   and 
   		((:hire_date_from  is null and :hire_date_from_to is null) or 
   				emp.trans_dte      between (:hire_date_from - 1) and (:hire_date_from_to + 1))
   and    (:supplier IS null or i.iss_unt = :keys.scr_supplier)

Open in new window

0
 
chalie001Author Commented:
(:hire_date_from     IS null
   and      :hire_date_to       IS null
   and      i.unt                = :supplier
)
0

Featured Post

Prepare for your VMware VCP6-DCV exam.

Josh Coen and Jason Langer have prepared the latest edition of VCP study guide. Both authors have been working in the IT field for more than a decade, and both hold VMware certifications. This 163-page guide covers all 10 of the exam blueprint sections.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now