My Oracle version is 11g and I have two tables , their structures are like
CREATE TABLE "WP_CLI_CARD_JF" (
"COM_MD_CODE" VARCHAR2(6 BYTE) NOT NULL,
"COM_DB_ID" VARCHAR2(5 BYTE) NOT NULL,
"CLI_CARD_JF_LIST_NO" VARCHAR2(20 BYTE) NOT NULL,
"CLI_CARD" VARCHAR2(10 BYTE) DEFAULT ' ' NOT NULL,
"CLI_CARD_JF_TYPE" VARCHAR2(2 BYTE) NOT NULL,
"CLI_CARD_JF_NUM" NUMBER(12,3) DEFAULT NULL,
"CLI_CARD_JF_DATE" DATE DEFAULT NULL,
"CLI_CARD_JF_MEMO" VARCHAR2(50 BYTE) DEFAULT NULL,
"CUR_NO" VARCHAR2(2 BYTE) DEFAULT NULL,
"CLI_CARD_JF_ZT" VARCHAR2(2 BYTE) DEFAULT '1' NOT NULL,
"PROD_NO" VARCHAR2(8 BYTE) NOT NULL,
"PROD_ADD" VARCHAR2(50 BYTE) NOT NULL,
"BATCH_NO" VARCHAR2(12 BYTE) NOT NULL,
"PROD_NUM" NUMBER(12,3) DEFAULT NULL,
"SELL_PRICE" NUMBER(10,2) DEFAULT NULL,
"STD_PRICE" NUMBER(10,2) DEFAULT NULL,
"CLI_CARD_JF_DATE_UP" DATE DEFAULT sysdate,
"UPLOADEDBYWEBAPI" CHAR(1 BYTE) DEFAULT NULL
)
CREATE TABLE "YW_JFXFJLB" (
"ID" NUMBER(9,0) NOT NULL,
"HYKH" VARCHAR2(255 BYTE),
"HYSJH" VARCHAR2(255 BYTE),
"HYMC" VARCHAR2(255 BYTE),
"RETURNCODE" NUMBER(9,0),
"CURRINDEX" NUMBER(9,0),
"PAGESCOUNT" NUMBER(9,0),
"TOTALRECORDSCOUNT" NUMBER(9,0),
"TOTALRECORDS" NUMBER(9,0),
"COMDBID" VARCHAR2(255 BYTE),
"CLICARDJFLISTNO" VARCHAR2(255 BYTE),
"CLICARD" VARCHAR2(255 BYTE),
"CLICARDJFTYPE" VARCHAR2(255 BYTE),
"CLICARDJFNUM" NUMBER(20,8),
"CLICARDJFDATE" VARCHAR2(255 BYTE),
"CLICARDJFMEMO" VARCHAR2(255 BYTE),
"CURNO" VARCHAR2(255 BYTE),
"PRODNO" VARCHAR2(255 BYTE),
"BATCHNO" VARCHAR2(255 BYTE),
"PRODADD" VARCHAR2(255 BYTE),
"PRODNAME" VARCHAR2(255 BYTE),
"MONAD" VARCHAR2(255 BYTE),
"PRODPZWH" VARCHAR2(255 BYTE),
"PRODMEMO" VARCHAR2(255 BYTE),
"PRODSIZE" VARCHAR2(255 BYTE),
"PRODNUM" NUMBER(20,8),
"SELLPRICE" NUMBER(20,8),
"STDPRICE" NUMBER(20,8),
"CLICARDJFDATEUP" VARCHAR2(255 BYTE),
"COMMDCODE" VARCHAR2(255 BYTE),
"COMJCNAME" VARCHAR2(255 BYTE),
"COMZIPNAME" VARCHAR2(255 BYTE),
"BEIZHU" VARCHAR2(255 BYTE),
"NAME" VARCHAR2(255 BYTE),
"CREATEBY" NUMBER(9,0),
"CREATED" DATE DEFAULT SYSDATE,
"UPDATED" DATE DEFAULT SYSDATE,
"UPDATEBY" NUMBER(9,0),
"ORGID" NUMBER(9,0),
"NOTE" VARCHAR2(255 BYTE)
)
I tried to insert a record to each of them
INSERT INTO "YW_JFXFJLB" VALUES ('3110632', NULL, NULL, NULL, '0', '16', '25', '484', '0', '01', '2016120501005', '9000000003', '1', '70', '20161205150855', NULL, '01', 'H0110050', '3344', 'loreal', NULL, NULL, NULL, NULL, NULL, '1', '70', '70', '20161205150855', '800000', 'hq', 'for test', NULL, NULL, '0', TO_DATE('2024-07-05 09:20:06', 'SYYYY-MM-DD HH24:MI:SS'), TO_DATE('2024-07-05 09:20:06', 'SYYYY-MM-DD HH24:MI:SS'), '0', '0', NULL);
INSERT INTO wp_cli_card_jf (com_md_code, com_db_id, cli_card_jf_list_no, cli_card, cli_card_jf_type, cli_card_jf_num, cli_card_jf_date, cli_card_jf_memo, cur_no, cli_card_jf_zt, prod_no, prod_add, batch_no, prod_num, sell_price, std_price, cli_card_jf_date_up, uploadedbywebapi) VALUES ('800000', '01', '2016120501005', '9000000003', '1', 70.000, TO_DATE('2016-12-05 15:03:40', 'YYYY-MM-DD HH24:MI:SS'), '', '01', '1', 'H0110050', 'loreal', '3344', 1.000, 70.00, 70.00, TO_DATE('2016-12-05 15:08:55', 'YYYY-MM-DD HH24:MI:SS'), NULL);
and I tried to find the differences between two tables based on some key fields like com_md_code, prod_no,batch_no,prod_add and cli_card_jf_memo as well, I used this SQL statement:
SELECT b.*
FROM Yw_jfxfjlb b
LEFT JOIN WP_CLI_CARD_JF a
ON a.com_md_code = b.commdcode
AND NVL(trim(a.cli_card_jf_list_no),'') = NVL(trim(b.clicardjflistno),'')
AND NVL(trim(a.prod_no),'') = NVL(trim(b.prodno),'')
AND NVL(trim(a.batch_no), '') = NVL(trim(b.batchno), '')
AND NVL(trim(a.prod_add), '') = NVL(trim(b.prodadd), '')
AND NVL(trim(a.CLI_CARD_JF_MEMO), '') = NVL(trim(b.CLICARDJFMEMO), '')
WHERE a.com_md_code IS NULL and b.CLICARD='9000000003';
I expected if it showcases the records which values in fields com_md_code, prod_no,batch_no,prod_add and cli_card_jf_memo not exactly the same between those two tables , but it turned out being the opposite, I have no idea why my SQL statement doesn't work , could someone point out the mistake I made ? I appreciated it
In Oracle, '' is identical to NULL so NVL(something, '') is the same as NVL(something, NULL) which can be simplified to just something.
Therefore:
SELECT b.*
FROM Yw_jfxfjlb b
LEFT JOIN WP_CLI_CARD_JF a
ON a.com_md_code = b.commdcode
AND NVL(trim(a.cli_card_jf_list_no),'') = NVL(trim(b.clicardjflistno),'')
AND NVL(trim(a.prod_no),'') = NVL(trim(b.prodno),'')
AND NVL(trim(a.batch_no), '') = NVL(trim(b.batchno), '')
AND NVL(trim(a.prod_add), '') = NVL(trim(b.prodadd), '')
AND NVL(trim(a.CLI_CARD_JF_MEMO), '') = NVL(trim(b.CLICARDJFMEMO), '')
WHERE a.com_md_code IS NULL
AND b.CLICARD='9000000003';
is the equivalent of:
SELECT b.*
FROM Yw_jfxfjlb b
LEFT JOIN WP_CLI_CARD_JF a
ON a.com_md_code = b.commdcode
AND trim(a.cli_card_jf_list_no) = trim(b.clicardjflistno)
AND trim(a.prod_no) = trim(b.prodno)
AND trim(a.batch_no) = trim(b.batchno)
AND trim(a.prod_add) = trim(b.prodadd)
AND trim(a.CLI_CARD_JF_MEMO) = trim(b.CLICARDJFMEMO)
WHERE a.com_md_code IS NULL
AND b.CLICARD='9000000003';
If either of the untrimmed values is just whitespace or is NULL then it will be evaluated as NULL and the comparison NULL = anything is never true so your JOIN will not match the rows.
What you probably want is to use NVL (or the ANSI standard COALESCE) with a non-empty string, instead of '', and ensure that the non-empty string will never appear in your data. Since you are trimming the leading and trailing whitespace then using a single space character would work:
SELECT b.*
FROM Yw_jfxfjlb b
LEFT JOIN WP_CLI_CARD_JF a
ON a.com_md_code = b.commdcode
AND COALESCE(trim(a.cli_card_jf_list_no),' ') = COALESCE(trim(b.clicardjflistno),' ')
AND COALESCE(trim(a.prod_no),' ') = COALESCE(trim(b.prodno),' ')
AND COALESCE(trim(a.batch_no), ' ') = COALESCE(trim(b.batchno), ' ')
AND COALESCE(trim(a.prod_add), ' ') = COALESCE(trim(b.prodadd), ' ')
AND COALESCE(trim(a.CLI_CARD_JF_MEMO), ' ') = COALESCE(trim(b.CLICARDJFMEMO), ' ')
WHERE a.com_md_code IS NULL
AND b.CLICARD='9000000003';
Which for your sample data, generates no rows of output because the rows do match.