|
|||||||||
|
|||||||||
| |||||||||
|
|||||||||
|
|||||||||
| |||||||||
|
|
|
| |||||||||
 |
|
|
«
Previous Thread
|
Next Thread
»
|
Thread Tools | Search this Thread | Rate Thread | Display Modes |
Dev Shed Forums Sponsor:
|
|
Stop making mediocre tutorials.The best tutorials are video! Camtasia Studio makes it easy to create engaging, buzz-building screen videos at any size, in any popular format. Download the free trial!
|
|
#1
September 16th, 2004, 09:43 AM
|
|||
|
|||
|
SQL statements not returning same values
I am trying to determin why one SQL statement using the CASE statement returns a set of values and the same statement not using the CASE statement in the select and placing its where condition directly in the WHERE clause returns no records... Any help in understanding why this is occurring would be greatly appreciated.... (SQL below)
This returns nothing: SELECT labels.dt_tm_print,Count(lab_num) "LPN", labels.qty, labels.status, labels.label_type, labels.line_num, labels.order_type, labels.ord_num, labels.itm_num, labels.case_qty, labels.model_lot, labels.hot_flg, labels.cust_num, labels.LOCATION, labels.dt_tm_void, labels.dt_tm_recv, labels.dt_tm_shp, labels.reg_num, labels.scan_type, labels.proc_type, labels.tran_num, labels.pic_zone, labels.pick_wrk_asgn, labels.master_label, labels.dt_tm_divert, labels.dt_tm_stage, labels.damage_flg, labels.vas_flg, labels.audit_flg, labels.asn_num, labels.process_id, labels.sell_zone, labels.carton_vol, labels.hbc_ovr_flg, labels.reprint_date, labels.create_date, labels.act_reg_num, labels.num_upc_prt FROM labels WHERE status<>45 AND lab_num like '98%' AND dt_tm_print between to_date('09/11/04','MM/DD/YY') AND to_date('09/13/04','MM/DD/YY') GROUP BY labels.dt_tm_print, lab_num, labels.qty, labels.status, labels.label_type, labels.line_num, labels.order_type, labels.ord_num, labels.itm_num, labels.case_qty, labels.model_lot, labels.hot_flg, labels.cust_num, labels.LOCATION, labels.dt_tm_void, labels.dt_tm_recv, labels.dt_tm_shp, labels.reg_num, labels.scan_type, labels.proc_type, labels.tran_num, labels.pic_zone, labels.pick_wrk_asgn, labels.master_label, labels.dt_tm_divert, labels.dt_tm_stage, labels.damage_flg, labels.vas_flg, labels.audit_flg, labels.asn_num, labels.process_id, labels.sell_zone, labels.carton_vol, labels.hbc_ovr_flg, labels.reprint_date, labels.create_date, labels.act_reg_num, labels.num_upc_prt ORDER BY labels.dt_tm_print DESC This returns a ton of records but for the lab_num field (which is labeled "LPN" the value is blank: SELECT labels.dt_tm_print,CASE WHEN lab_num like '98%' THEN lab_num END "LPN", labels.qty, labels.status, labels.label_type, labels.line_num, labels.order_type, labels.ord_num, labels.itm_num, labels.case_qty, labels.model_lot, labels.hot_flg, labels.cust_num, labels.LOCATION, labels.dt_tm_void, labels.dt_tm_recv, labels.dt_tm_shp, labels.reg_num, labels.scan_type, labels.proc_type, labels.tran_num, labels.pic_zone, labels.pick_wrk_asgn, labels.master_label, labels.dt_tm_divert, labels.dt_tm_stage, labels.damage_flg, labels.vas_flg, labels.audit_flg, labels.asn_num, labels.process_id, labels.sell_zone, labels.carton_vol, labels.hbc_ovr_flg, labels.reprint_date, labels.create_date, labels.act_reg_num, labels.num_upc_prt FROM labels WHERE status<>45 AND dt_tm_print between to_date('09/11/04','MM/DD/YY') AND to_date('09/13/04','MM/DD/YY') GROUP BY labels.dt_tm_print, CASE WHEN lab_num like '98%' THEN lab_num END, labels.qty, labels.status, labels.label_type, labels.line_num, labels.order_type, labels.ord_num, labels.itm_num, labels.case_qty, labels.model_lot, labels.hot_flg, labels.cust_num, labels.LOCATION, labels.dt_tm_void, labels.dt_tm_recv, labels.dt_tm_shp, labels.reg_num, labels.scan_type, labels.proc_type, labels.tran_num, labels.pic_zone, labels.pick_wrk_asgn, labels.master_label, labels.dt_tm_divert, labels.dt_tm_stage, labels.damage_flg, labels.vas_flg, labels.audit_flg, labels.asn_num, labels.process_id, labels.sell_zone, labels.carton_vol, labels.hbc_ovr_flg, labels.reprint_date, labels.create_date, labels.act_reg_num, labels.num_upc_prt ORDER BY labels.dt_tm_print DESC Do I need to add the where lab_num like '98%' for all the other fileds also? Thanks cavall
|
|
#2
September 16th, 2004, 11:22 AM
|
|||
|
|||
|
As you have accepted that the second query does not show any value in label because it does not comply the searching condition, and the same thing happened in your first query, returns no record, i dont know what is your table structure and what kinda data you have in it. so i just guess that label attribute is defined as character into the table and you have these two digits in your data set (i,e '98'), it might be anywhere whithin string (first, between, last). so your WHERE condition should be as follows:
modification is marked with red color, AND lab_num like '%98%' the other codition in your query is WHERE status<>45 this might also be causing to generate no data, remove this condition and run your first query again without making any furthur modification, if you still found no data then use WHERE condition defined above (without WHERE status <>45). Regards,
|
|
| Viewing: Dev Shed Forums > Databases > Oracle Development > SQL statements not returning same values |
| Thread Tools | Search this Thread |
| Display Modes | Rate This Thread |
|
|
|
 |
|
|
Del.icio.us
Digg
Google
Spurl
Blink
Furl
Simpy
Y! MyWeb
Linear Mode
