I created this Oracle Function:
CREATE or replace FUNCTION f_notify_clause
(letter_of_credit_nbr IN number
)
RETURN varchar2
IS
lv_description varchar2(500);
BEGIN
SELECT applicant_name || ', ' || SUBSTR(applicant_address, 1, 294) || ', ' || SUBSTR(COALESCE(bol_notify, '1'), '')
INTO lv_description
FROM s1_letter_of_credit loc
WHERE letter_of_credit_nbr = loc.letter_of_credit_nbr;
RETURN(lv_description);
END;
Table Definition:
CREATE TABLE s1_letter_of_credit
(
letter_of_credit_nbr number not null,
applicant_no varchar(10) null,
letter_of_credit_status char(1) DEFAULT 'N' not null
CONSTRAINT ckc_loc_letterofcredit_status CHECK (letter_of_credit_status IN ('N','O','I','P','C','X')),
letter_of_credit_no varchar2(50) not null,
issue_date date not null,
expiry_date date null,
applicant_id varchar2(10) null,
applicant_name varchar2(100) not null,
applicant_address varchar2(1000) not null,
issuing_bank_id varchar2(10) null,
issuing_bank_name varchar2(100) not null,
add_by varchar2(40) DEFAULT user not null,
add_date date DEFAULT sysdate not null,
change_by varchar2(40) not null,
change_date date not null,
beneficiary_id varchar2(10) null,
beneficiary_name varchar2(100) null,
beneficiary_certificate varchar2(1000) null,
advising_bank_id varchar2(10) null,
advising_bank_name varchar2(100) null,
advising_bank_reference_id varchar2(30) null,
amount number(20,2) DEFAULT 0 not null,
currency_code varchar2(10) DEFAULT '01' not null,
amount_free_form_note varchar2(100) null,
draft_required_flag char(1) DEFAULT 'N' not null
CONSTRAINT ckc_loc_draft_required_flag CHECK (draft_required_flag IN ('Y','N')),
tenor varchar2(100) null,
drawee varchar2(1000) null,
special_draft_clause varchar2(1000) null,
delivery_basis varchar2(100) null,
port_of_discharge varchar2(1000) null,
latest_date_of_shipment date null,
description varchar2(3000) null,
bol_consignee varchar2(1000) null,
bol_notify varchar2(1000) null,
bol_freight_clause varchar2(1000) null,
contract_customer_id varchar2(10) null,
proof_amount number(20,2) DEFAULT 0 not null,
CONSTRAINT pk_letter_of_credit PRIMARY KEY (letter_of_credit_nbr)
using index
tablespace smartsoft_index
)
tablespace smartsoft_data
/
Sample data:
insert into s1_letter_of_credit (letter_of_credit_nbr,
applicant_no,
letter_of_credit_status,
letter_of_credit_no,
issue_date,
applicant_name,
applicant_address,
issuing_bank_name,
add_by,
add_date,
change_by,
change_date,
bol_notify,
bol_consignee,
bol_freight_clause
)
values (1,
null,
'N',
'1',
TRUNC(SYSDATE),
'Test 300 & Test Name1',
'Address Line 1 N2B 3W4 Kitchener, ONT-Ontario-Test Canada',
'15',
'ANY USER',
TRUNC(SYSDATE),
'ANY USER',
TRUNC(SYSDATE),
null,
'CONSIGNEE 1' ,
'FREIGHT 1'
);
I tested the Function using this SQL:
set serveroutput on
exec dbms_output.put_line(f_notify_clause(1));
and get the desired results:
Test 300 & Test Name1, Address Line 1 N2B 3W4 Kitchener, ONT-Ontario-Test Canada,
I want to use this Function in a Merge statement; here is the SQL:
MERGE INTO bv_order_draft_bl base
USING (SELECT letter_of_credit_nbr,
bol_consignee,
bol_freight_clause
FROM s1_letter_of_credit
) new
ON (new.letter_of_credit_nbr = base.draft_bl_nbr)
WHEN MATCHED
THEN UPDATE SET base.cn_clause = new.bol_consignee,
base.cn_clause_cust_flag = 'N',
base.cn_clause_nbr = NULL,
base.fr_clause = new.bol_freight_clause,
base.fr_clause_cust_flag = 'N',
base.fr_clause_nbr = NULL,
base.nt_clause = f_notify_clause(letter_of_credit_nbr)
/
Table definition:
CREATE TABLE bv_order_draft_bl
(
draft_bl_nbr number not null,
bv_order_nbr number not null,
vendor_customer_id varchar2(10) not null,
term_code varchar2(10) not null,
currency_code varchar2(10) DEFAULT '01' not null,
letter_of_credit_nbr number null,
template varchar2(40) null,
last_create_date date null,
last_sent_date date null,
file_path varchar2(255) null,
add_by varchar2(40) DEFAULT user not null,
add_date date DEFAULT sysdate not null,
change_by varchar2(40) not null,
change_date date not null,
ready_recd char(1) DEFAULT 'N' not null
CONSTRAINT ckc_bvodbl_ready_recd CHECK (ready_recd IN ('Y','N')),
loading_port number(12,0) null,
export_num varchar2(50) null,
cp_clause varchar2(500) null,
cp_clause_nbr number null,
cp_clause_cust_flag char(1) DEFAULT 'N' not null
CONSTRAINT ckc_bvodbl_cp_clause_cust_flag CHECK (cp_clause_cust_flag IN ('Y','N')),
cn_clause varchar2(500) null,
cn_clause_nbr number null,
cn_clause_cust_flag char(1) DEFAULT 'N' not null
CONSTRAINT ckc_bvodbl_cn_clause_cust_flag CHECK (cn_clause_cust_flag IN ('Y','N')),
fr_clause varchar2(500) null,
fr_clause_nbr number null,
fr_clause_cust_flag char(1) DEFAULT 'N' not null
CONSTRAINT ckc_bvodbl_fr_clause_cust_flag CHECK (fr_clause_cust_flag IN ('Y','N')),
nt_clause varchar2(500) null,
lc_change_date date null,
CONSTRAINT pk_bv_order_draft_bl PRIMARY KEY (draft_bl_nbr)
using index
tablespace smartsoft_index
)
tablespace smartsoft_data
/
Sample data:
insert into bv_order_draft_bl (draft_bl_nbr,
bv_order_nbr,
vendor_customer_id,
term_code,
add_by,
add_date,
change_by,
change_date
)
values (1,
1,
'100',
'DEMAND',
'ANY USER',
TRUNC(SYSDATE),
'ANY USER',
TRUNC(SYSDATE)
);
When I run the Merge SQL I get this result:
Column headers:DRAFT_BL_NBR, BV_ORDER_NBR, VENDOR_CUSTOMER_ID, TERM_CODE, CURRENCY_CODE, LETTER_OF_CREDIT_NBR, TEMPLATE, LAST_CREATE_DATE, LAST_SENT_DATE, FILE_PATH, ADD_BY, ADD_DATE, CHANGE_BY, CHANGE_DATE, READY_RECD, LOADING_PORT, EXPORT_NUM, CP_CLAUSE, CP_CLAUSE_NBR, CP_CLAUSE_CUST_FLAG, CN_CLAUSE, CN_CLAUSE_NBR, CN_CLAUSE_CUST_FLAG, FR_CLAUSE, FR_CLAUSE_NBR, FR_CLAUSE_CUST_FLAG, NT_CLAUSE, LC_CHANGE_DATE
Data: 1 1 100 DEMAND 01 ANY USER 11-JUN-2019 12:00:00 ANY USER 11-JUN-2019 12:00:00 N N CONSIGNEE 1 N FREIGHT 1 N
The column: nt_clause did not get update; all of the other columns are updated correctly.
Am I using the correct syntax to call the function within the Merge SQL? ( I suspect not )
Can anyone assist with the correct syntax?
Thanks
Murray