Dear All,

I got stuck with the following update statement. Can someone take a look and
help me out. I am learning the update with case statement.

Thank you for your help!

Susan Yang

SQL> update zz_supplier_documentation A
2 set forma_dcmtn_rcpt_stus_cd =
3 (case
4 when loc.busns_type_cd ='1'
5 and doc.CASH_FLOW_RCPT_IND='Y'
6 and doc.BAL_SHEET_RCPT_IND='Y'
7 and doc.INCM_STMT_RCPT_IND='Y'
8 and doc.SCHDL_L_RCPT_IND='Y'
9 and doc.CRED_RPT_RCPT_IND='Y'
10 then '02' ---for complete
11 else '01' ---for incomplete
12 end
13 )
14 where A.suplr_id in
15 (select doc.suplr_id
16 from cbss.zz_supplier_location loc,
17 cbss.zz_supplier_documentation doc
18 where loc.suplr_id = doc.suplr_id);

and doc.CRED_RPT_RCPT_IND='Y'
*
ERROR at line 9:
ORA-00904: "DOC"."CRED_RPT_RCPT_IND": invalid identifier

SQL> desc zz_supplier_documentation
Name Null? Type
----------------------------------------- --------
----------------------------
SUPLR_ID NOT NULL NUMBER(38)
BIDDER_NUM CHAR(7)
NSC_NUM CHAR(10)
CASH_FLOW_RCPT_IND CHAR(1)
BAL_SHEET_RCPT_IND CHAR(1)
INCM_STMT_RCPT_IND CHAR(1)
SCHDL_L_RCPT_IND CHAR(1)
SCHDL_C_RCPT_IND CHAR(1)
CRED_RPT_RCPT_IND CHAR(1)
FORM_10K_RCPT_IND CHAR(1)
EXPNSN_LTR_RCPT_IND CHAR(1)
LGL_NTWRK_MBR_CNTRCT_RCPT_IND CHAR(1)
CRTFCTN_DCMTN_RCPT_IND CHAR(1)
FORMA_DCMTN_RCPT_STUS_CD CHAR(2)
LAST_CHG_USER_ID VARCHAR2(80)
LAST_CHG_DT DATE
FORMB_DCMTN_RCPT_STUS_CD CHAR(2)


Oracle LazyDBA home page