where 后面的条件可能为空,这样整个查询就没有结果.怎么办?

我写一个查询的语句,如

SELECT c.*
FROM a, b,c
WHERE a .col1=b.col2
AND c,col1=a.col1

但是如果a.col1 是空的就没有结果,我想要的结果是如果a.col1为空,那么就忽略这个a.col1=b.col2 条件,以 c,col1=a.col1为条件进行查询[@more@]

where 后面的条件可能为空,这样整个查询就没有结果.怎么办?

我写一个查询的语句,如

SELECT c.*
FROM a, b,c
WHERE a .col1=b.col2
AND c,col1=a.col1

但是如果a.col1 是空的就没有结果,我想要的结果是如果a.col1为空,那么就忽略这个a.col1=b.col2 条件,以 c,col1=a.col1为条件进行查询

我试着写成了
SELECT c.*
FROM a, b,c
WHERE NVL(a .col1=b.col2, 1=1)
AND c,col1=a.col1

但是编译出错误:ORA-00909: invalid number of arguments

写成:
SELECT c.*
FROM a, b,c
WHERE DECODE(a .col1,
NULL, 1=1,
a .col1=b.col2)
AND c,col1=a.col1

编译出错:ORA-00907: missing right parenthesis

未修改的sql 语句:

/* Formatted on 2006/03/30 16:43 (Formatter Plus v4.8.6) */
SELECT ph.segment1 pono, pv.vendor_name vendor, ph.fob_lookup_code fob,
ph.ship_via_lookup_code via, ap.NAME term_description,
NVL (pll.promised_date, pll.need_by_date) pr_date,
pl.line_num po_lineno, pll.shipment_num po_shipment,
msi.segment1 item_no,
NVL (msi.must_use_approved_vendor_flag, 'N') avl_flag,
pl.item_description descrip,
pll.quantity - NVL (pll.quantity_cancelled, 0) qty,
NVL (pl.attribute1, pl.unit_meas_lookup_code) i_uom,
ph.currency_code currency, pl.unit_price u_price,
DECODE (ph.vendor_contact_id,
NULL, '',
pvc.last_name || pvc.first_name
) contact_name,
ROUND (( (pll.quantity - NVL (pll.quantity_cancelled, 0))
* pl.unit_price
),
4
) line_total,

--((pll.quantity- NVL (pll.quantity_cancelled, 0)) * pl.unit_price) line_total_old,
ph.comments v_desc, pll.line_location_id location_id,
pv.vendor_id v_vendor_id, ph.vendor_site_id v_site_id,
pl.item_id item_id, pl.line_type_id line_type,
DECODE (pod.req_distribution_id,
NULL, pod.req_header_reference_num,
prh.segment1
) requisition_num
FROM po_vendors pv,
po_vendor_sites_all pvs,
po_vendor_contacts pvc,
po_headers_all ph,
po_lines_all pl,
ap_terms ap,
po_line_locations_all pll,
mtl_system_items msi,
po_requisition_lines_all prl, --請購單行
po_requisition_headers_all prh,
po_distributions_all pod, --採購單的分佈屬性
po_req_distributions_all prod
WHERE pv.vendor_id = ph.vendor_id
AND pvs.vendor_site_id = ph.vendor_site_id
AND DECODE(ph.vendor_contact_id,
NULL, A=A,
pvc.vendor_contact_id = ph.vendor_contact_id)
--就是这里, 可能为空的是ph.vendor_contact_id
AND ph.terms_id = ap.term_id
AND ph.org_id = NVL (:p_org_id, 141)
AND ph.po_header_id = pl.po_header_id
AND pl.org_id = ph.org_id
AND pl.po_line_id = pll.po_line_id
AND pl.po_header_id = pll.po_header_id
AND pll.org_id = pl.org_id
AND pl.item_id = msi.inventory_item_id
AND pl.org_id = msi.organization_id
AND pll.quantity - NVL (pll.quantity_cancelled, 0) > 0
AND ph.segment1 IN
(NVL (:p_segment1, ph.segment1),
NVL (:p_segment2, ''),
NVL (:p_segment3, ''),
NVL (:p_segment4, ''),
NVL (:p_segment5, ''),
NVL (:p_segment6, ''),
NVL (:p_segment7, ''),
NVL (:p_segment8, ''),
NVL (:p_segment9, ''),
NVL (:p_segment10, '')
)
AND prh.requisition_header_id = prl.requisition_header_id
AND prod.requisition_line_id = prl.requisition_line_id
AND pod.req_distribution_id = prod.distribution_id
AND pll.line_location_id = pod.line_location_id
AND pll.po_header_id = ph.po_header_id
ORDER BY ph.segment1;

best an:

SELECT c.*
FROM a, b,c
WHERE (a .col1=b.col2 or a.col1 is null)
AND c,col1=a.col1

other ans:

NVL(COL1,COL2) = COL2
COL1空的时候取COL2的直.

a.col1 = decode(a.col1,null,a.col1,b.col2)

references:

http://www.itpub.net/showthread.php?s=&threadid=514601

http://218.16.124.196/oradev/bbs_content.jsp?bbs_sn=1488575&bbs_page_no=1&bbs_id=0010

最后修改后的sql 语句:

/* Formatted on 2006/03/30 16:43 (Formatter Plus v4.8.6) */
SELECT ph.segment1 pono, pv.vendor_name vendor, ph.fob_lookup_code fob,
ph.ship_via_lookup_code via, ap.NAME term_description,
NVL (pll.promised_date, pll.need_by_date) pr_date,
pl.line_num po_lineno, pll.shipment_num po_shipment,
msi.segment1 item_no,
NVL (msi.must_use_approved_vendor_flag, 'N') avl_flag,
pl.item_description descrip,
pll.quantity - NVL (pll.quantity_cancelled, 0) qty,
NVL (pl.attribute1, pl.unit_meas_lookup_code) i_uom,
ph.currency_code currency, pl.unit_price u_price,
DECODE (ph.vendor_contact_id,
NULL, '',
pvc.last_name || pvc.first_name
) contact_name,
ROUND (( (pll.quantity - NVL (pll.quantity_cancelled, 0))
* pl.unit_price
),
4
) line_total,

--((pll.quantity- NVL (pll.quantity_cancelled, 0)) * pl.unit_price) line_total_old,
ph.comments v_desc, pll.line_location_id location_id,
pv.vendor_id v_vendor_id, ph.vendor_site_id v_site_id,
pl.item_id item_id, pl.line_type_id line_type,
DECODE (pod.req_distribution_id,
NULL, pod.req_header_reference_num,
prh.segment1
) requisition_num
FROM po_vendors pv,
po_vendor_sites_all pvs,
po_vendor_contacts pvc,
po_headers_all ph,
po_lines_all pl,
ap_terms ap,
po_line_locations_all pll,
mtl_system_items msi,
po_requisition_lines_all prl, --½ÐÁʳæ¦æ
po_requisition_headers_all prh,
po_distributions_all pod, --±ÄÁʳ檺¤À§GÄÝ©Ê
po_req_distributions_all prod
WHERE pv.vendor_id = ph.vendor_id
AND pvs.vendor_site_id = ph.vendor_site_id
-- AND NVL(ph.vendor_contact_id, pvc.vendor_contact_id)=pvc.vendor_contact_id--notice null value
--if use this condition, the head of ph.segment1 must be written as : distinct(ph.segment1 )
and (ph.vendor_contact_id=pvc.vendor_contact_id or ph.vendor_contact_id is null) --best
AND ph.terms_id = ap.term_id
AND ph.org_id = NVL (:p_org_id, 141)
AND ph.po_header_id = pl.po_header_id
AND pl.org_id = ph.org_id
AND pl.po_line_id = pll.po_line_id
AND pl.po_header_id = pll.po_header_id
AND pll.org_id = pl.org_id
AND pl.item_id = msi.inventory_item_id
AND pl.org_id = msi.organization_id
AND pll.quantity - NVL (pll.quantity_cancelled, 0) > 0
AND ph.segment1 IN
(NVL (:p_segment1, ph.segment1),
NVL (:p_segment2, ''),
NVL (:p_segment3, ''),
NVL (:p_segment4, ''),
NVL (:p_segment5, ''),
NVL (:p_segment6, ''),
NVL (:p_segment7, ''),
NVL (:p_segment8, ''),
NVL (:p_segment9, ''),
NVL (:p_segment10, '')
)
AND prh.requisition_header_id = prl.requisition_header_id
AND prod.requisition_line_id = prl.requisition_line_id
AND pod.req_distribution_id = prod.distribution_id
AND pll.line_location_id = pod.line_location_id
AND pll.po_header_id = ph.po_header_id
ORDER BY ph.segment1;

注意这些,值得好好想一想结合数据库原理:

-- AND NVL(ph.vendor_contact_id, pvc.vendor_contact_id)=pvc.vendor_contact_id--notice null value
--if use this condition, the head of ph.segment1 must be written as : distinct(ph.segment1 )
and (ph.vendor_contact_id=pvc.vendor_contact_id or ph.vendor_contact_id is null) --best

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/226700/viewspace-833952/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/226700/viewspace-833952/