Hello Everyone,
I am looking for some help on an issue I am struggling to find a solution. I have a situation where there is a table with data that has null file dates for a specific organization (‘APPLE’ in this case). File dates are available in the file name which is a column currently has data will no null records. I am trying to do a substr to get the file date from file name but I am not sure how I can update the table where file date is null. Below are the create table script, insert statements and sample expected output.
Create table script:
create table TEST_DATA_FILEDT
(
FILE_NM VARCHAR2(200),
FILE_DT DATE,
ORG_NM VARCHAR2(20)
);
Insert statements:
INSERT INTO TEST_DATA_FILEDT (FILE_NM,FILE_DT,ORG_NM) VALUES ('APPLE_CA_RETURN_20230718.txt','18-JUL-2023','APPLE');
INSERT INTO TEST_DATA_FILEDT (FILE_NM,FILE_DT,ORG_NM) VALUES ('APPLE_CA_RETURN_20230618.txt','18-JUN-2023','APPLE');
INSERT INTO TEST_DATA_FILEDT (FILE_NM,FILE_DT,ORG_NM) VALUES ('APPLE_CA_RETURN_20230518.txt','','APPLE');
INSERT INTO TEST_DATA_FILEDT (FILE_NM,FILE_DT,ORG_NM) VALUES ('APPLE_CA_RETURN_20230418.txt','','APPLE');
INSERT INTO TEST_DATA_FILEDT (FILE_NM,FILE_DT,ORG_NM) VALUES ('APPLE_CA_RETURN_20230318.txt','','APPLE');
INSERT INTO TEST_DATA_FILEDT (FILE_NM,FILE_DT,ORG_NM) VALUES ('APPLE_CA_RETURN_20230218.txt','','APPLE');
INSERT INTO TEST_DATA_FILEDT (FILE_NM,FILE_DT,ORG_NM) VALUES ('APPLE_CA_RETURN_20230118.txt','','APPLE');
INSERT INTO TEST_DATA_FILEDT (FILE_NM,FILE_DT,ORG_NM) VALUES ('APPLE_CA_RETURN_20230818.txt','','APPLE');
INSERT INTO TEST_DATA_FILEDT (FILE_NM,FILE_DT,ORG_NM) VALUES ('MICROSOFT_WA_RETURN_20230818.txt','18-AUG-2023','MICROSOFT');
INSERT INTO TEST_DATA_FILEDT (FILE_NM,FILE_DT,ORG_NM) VALUES ('MICROSOFT_WA_RETURN_20230818.txt','18-AUG-2023','MICROSOFT');
INSERT INTO TEST_DATA_FILEDT (FILE_NM,FILE_DT,ORG_NM) VALUES ('AMAZON_WA_RETURN_20230818.txt','18-AUG-2023','AMAZON');
COMMIT;
Expected output:
<table><tbody><tr><td style="height:14.4pt;width:190pt;">APPLE_CA_RETURN_20230718.txt</td><td style="border-left-style:none;width:65pt;">18-JUL-2023</td><td style="border-left-style:none;width:59pt;">APPLE</td></tr><tr><td style="border-top-style:none;height:14.4pt;">APPLE_CA_RETURN_20230618.txt</td><td style="border-left-style:none;border-top-style:none;">18-JUN-2023</td><td style="border-left-style:none;border-top-style:none;">APPLE</td></tr><tr><td style="border-top-style:none;height:14.4pt;">APPLE_CA_RETURN_20230518.txt</td><td style="border-left-style:none;border-top-style:none;">18-MAY-2023</td><td style="border-left-style:none;border-top-style:none;">APPLE</td></tr><tr><td style="border-top-style:none;height:14.4pt;">APPLE_CA_RETURN_20230418.txt</td><td style="border-left-style:none;border-top-style:none;">18-APR-2023</td><td style="border-left-style:none;border-top-style:none;">APPLE</td></tr><tr><td style="border-top-style:none;height:14.4pt;">APPLE_CA_RETURN_20230318.txt</td><td style="border-left-style:none;border-top-style:none;">18-MAR-2023</td><td style="border-left-style:none;border-top-style:none;">APPLE</td></tr><tr><td style="border-top-style:none;height:14.4pt;">APPLE_CA_RETURN_20230218.txt</td><td style="border-left-style:none;border-top-style:none;">18-FEB-2023</td><td style="border-left-style:none;border-top-style:none;">APPLE</td></tr><tr><td style="border-top-style:none;height:14.4pt;">APPLE_CA_RETURN_20230118.txt</td><td style="border-left-style:none;border-top-style:none;">18-JAN-2023</td><td style="border-left-style:none;border-top-style:none;">APPLE</td></tr><tr><td style="border-top-style:none;height:14.4pt;">APPLE_CA_RETURN_20230818.txt</td><td style="border-left-style:none;border-top-style:none;">18-AUG-2023</td><td style="border-left-style:none;border-top-style:none;">APPLE</td></tr><tr><td style="border-top-style:none;height:14.4pt;">MICROSOFT_WA_RETURN_20230818.txt</td><td style="border-left-style:none;border-top-style:none;">18-AUG-2023</td><td style="border-left-style:none;border-top-style:none;">MICROSOFT</td></tr><tr><td style="border-top-style:none;height:14.4pt;">MICROSOFT_WA_RETURN_20230818.txt</td><td style="border-left-style:none;border-top-style:none;">18-AUG-2023</td><td style="border-left-style:none;border-top-style:none;">MICROSOFT</td></tr><tr><td style="border-top-style:none;height:14.4pt;">AMAZON_WA_RETURN_20230818.txt</td><td style="border-left-style:none;border-top-style:none;">18-AUG-2023</td><td style="border-left-style:none;border-top-style:none;">AMAZON</td></tr></tbody></table>
Below query is giving me the date from the file name. I am not sure how to get the file_dt updated in the null file dates for ORG_NM='APPLE'. I don't have any keys in the table.
SELECT SUBSTR(FILE_NM,17,8) AS FILE_DT FROM TEST_DATA_FILEDT WHERE FILE_DT IS NULL AND ORG_NM='APPLE';
Any suggestions please?