chalie001
asked on
query returning everything
hi i have the following query
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
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));
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
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
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)
;
ASKER
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))
ASKER
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))
ASKER
if i pass eturning when you are passing these values null, null, 15588890 ? the query return everything
>>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.
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.
ASKER
So I must remove the last part from my query
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.
I cannot tell you what you need change because I don't know your requirements or your data.
ASKER
I what to return value for the supplie passes even if two date are null
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
(:hire_date_from IS null
and :hire_date_to IS null
and i.unt = :supplier
)
and :hire_date_to IS null
and i.unt = :supplier
)
hire_date_to null ; supplier 15588890 ;
what is the count(*) returning when you are passing these values null, null, 15588890 ?