Insert and Select using where clause

snufse · March 18th, 2009, 12:26 PM

I have following code that I need to expand. I need to select only records from t2 where field t2.jde_equipment_hours > 0. I tried to do this but does not seem to work:

Code:

 
insert into #Combined_LaborHrsDetailTable (jobdate, costcode, employeename, equipmentid, equipmentname, jdeequipmenthours) 
select t2.jde_job_date, t2.jde_cost_code, t2.jde_employee_name, t2.jde_equipment_id, t2.jde_equipment_name, 
t2.jde_equipment_hours 
from #JDE_LaborHrsDetailTable AS t2 
where not exists (select * from #Combined_LaborHrsDetailTable AS t1 where t1.jobdate = t2.jde_job_date and
t1.equipmentid = t2.jde_equipment_id and 
t1.employeename = t2.jde_employee_name and
t1.eqequipmenthours = t2.jde_equipment_hours and
t2.jde_equipment_hours > 0)

urtrivedi · March 19th, 2009, 12:49 AM

Please post structures of your table and define your requirement properly

rstelma · March 19th, 2009, 12:01 PM

Your logic in your query may be off. You're saying WHERE NOT EXISTS and then asking the query to select t2.jde_equipment_ > 0. Maybe if you said WHERE t2.jde_equipment_hours = 0 ... so that everything you get from the outer query will be > 0.

Just a shot in the dark.

Old Pedant · March 22nd, 2009, 04:50 PM

I answered this but, once again, this forum is throwing away my posts!

Grumble!

I'm not positive, but I *THINK* you just have a misplaced right paren.

Try this:

Code:

insert into #Combined_LaborHrsDetailTable 
    (jobdate, costcode, employeename, equipmentid, equipmentname, jdeequipmenthours) 
select t2.jde_job_date, t2.jde_cost_code, t2.jde_employee_name, 
       t2.jde_equipment_id, t2.jde_equipment_name, t2.jde_equipment_hours 
from #JDE_LaborHrsDetailTable AS t2 
where not exists (
      select * from #Combined_LaborHrsDetailTable AS t1 
      where t1.jobdate = t2.jde_job_date 
      and t1.equipmentid = t2.jde_equipment_id 
      and t1.employeename = t2.jde_employee_name 
      and t1.eqequipmenthours = t2.jde_equipment_hours 
      )
and t2.jde_equipment_hours > 0

.