Description:
Using the 'Reformat SQL' feature ( accessible by right clicking the editor) ,beautifies the code but deletes some insert statements.
How to repeat:
Have the following query and right click - reformat sql. It will delete lines 65 to 80.
BEGIN READ WRITE;
--CREATE labtranstoappend table
CREATE TABLE labtranstoappend
(
workgroup VARCHAR(50),
crewid VARCHAR(50),
wonum VARCHAR(50),
description VARCHAR(200),
reportdate TIMESTAMP,
month VARCHAR(50),
classn VARCHAR(200),
fieldtech VARCHAR(50),
workteam VARCHAR(50),
transtype VARCHAR(50),
startdate DATE,
startyear INT,
startmonth INT,
starttime TIMESTAMP,
finishtime TIMESTAMP,
regularhrs REAL,
failurecode VARCHAR(50),
PRIMARY KEY (wonum,fieldtech,transtype,startdate,regularhrs)
);
-- Create a temp table for storing counts for logging
CREATE TEMP TABLE tmp_logging
(
total BIGINT,
eventtime TIMESTAMP,
value VARCHAR(50)
);
-- Copy connection string
--Deriving userid from fieldtech
ALTER TABLE labtranstoappend ADD COLUMN userid VARCHAR(50);
UPDATE labtranstoappend
SET userid = SUBSTRING(fieldtech,1,6);
-- -- Log Insert start time
INSERT INTO tmp_logging
(
eventtime,
value
)
VALUES
(
GETDATE(),
'InsertStartTime'
);
INSERT INTO labourtransactions
(SELECT labtranstoappend.workgroup,
allwater.labtranstoappend.crewid,
allwater.labtranstoappend.wonum,
allwater.labtranstoappend.classn,
allwater.labtranstoappend.fieldtech,
allwater.labtranstoappend.userid,
allwater.labtranstoappend.workteam,
allwater.labtranstoappend.transtype,
allwater.labtranstoappend.startdate,
allwater.labtranstoappend.regularhrs,
allwater.labtranstoappend.failurecode,
allwater.employees.employeenumber,
allwater.employees.supervisorname
FROM allwater.labtranstoappend LEFT OUTER JOIN allwater.employees ON
allwater.labtranstoappend.userid = allwater.employees.maximouserid);
-- Log Insert finish time
INSERT INTO tmp_logging
(
eventtime,
value
)
VALUES
(
GETDATE(),
'InsertEndTime'
);
-- Update the logging tables
-- Get the count for newly inserted records
INSERT INTO tmp_logging
(
total,
value
)
VALUES
(
(WITH inserted_result AS
(
SELECT query,
MAX(si.starttime) OVER () AS last_q_stime,
si.starttime AS stime
FROM stl_insert si,
SVV_TABLE_INFO sti
WHERE sti.table_id = si.tbl
AND sti."table" = 'costrecord'
)
SELECT SUM(ROWS)
FROM stl_insert si,
inserted_result ir
WHERE si.query = ir.query
AND ir.last_q_stime = stime),
('recordsadded')
);
BEGIN;
-- Update logging table
INSERT INTO logging
(
transactiondate,
sourcefilename,
destinationtable,
sourcerecordcount,
recordsadded,
recordsappended
)
VALUES
(
GETDATE(),
(SELECT TOP 1 TRIM(filename)
FROM stl_load_commits
WHERE query = pg_last_copy_id()
ORDER BY CURTIME DESC),
--inserts the actual file name that was loaded from S3
'timesheetrecords',
(SELECT (pg_last_copy_count ()) +(SELECT COUNT(*)
FROM STL_LOAD_ERRORS
WHERE QUERY = pg_last_copy_id())),
NVL((SELECT total FROM tmp_logging WHERE value = 'recordsadded'),0),
NVL((SELECT total FROM tmp_logging WHERE value = 'recordsdeleted'),0)
);
END;
BEGIN;
-- Cleanup
DROP TABLE labtranstoappend;
DROP TABLE tmp_logging;
END;
END TRANSACTION;
Suggested fix:
Reformat should beautify the code and not delete stuff.
Description: Using the 'Reformat SQL' feature ( accessible by right clicking the editor) ,beautifies the code but deletes some insert statements. How to repeat: Have the following query and right click - reformat sql. It will delete lines 65 to 80. BEGIN READ WRITE; --CREATE labtranstoappend table CREATE TABLE labtranstoappend ( workgroup VARCHAR(50), crewid VARCHAR(50), wonum VARCHAR(50), description VARCHAR(200), reportdate TIMESTAMP, month VARCHAR(50), classn VARCHAR(200), fieldtech VARCHAR(50), workteam VARCHAR(50), transtype VARCHAR(50), startdate DATE, startyear INT, startmonth INT, starttime TIMESTAMP, finishtime TIMESTAMP, regularhrs REAL, failurecode VARCHAR(50), PRIMARY KEY (wonum,fieldtech,transtype,startdate,regularhrs) ); -- Create a temp table for storing counts for logging CREATE TEMP TABLE tmp_logging ( total BIGINT, eventtime TIMESTAMP, value VARCHAR(50) ); -- Copy connection string --Deriving userid from fieldtech ALTER TABLE labtranstoappend ADD COLUMN userid VARCHAR(50); UPDATE labtranstoappend SET userid = SUBSTRING(fieldtech,1,6); -- -- Log Insert start time INSERT INTO tmp_logging ( eventtime, value ) VALUES ( GETDATE(), 'InsertStartTime' ); INSERT INTO labourtransactions (SELECT labtranstoappend.workgroup, allwater.labtranstoappend.crewid, allwater.labtranstoappend.wonum, allwater.labtranstoappend.classn, allwater.labtranstoappend.fieldtech, allwater.labtranstoappend.userid, allwater.labtranstoappend.workteam, allwater.labtranstoappend.transtype, allwater.labtranstoappend.startdate, allwater.labtranstoappend.regularhrs, allwater.labtranstoappend.failurecode, allwater.employees.employeenumber, allwater.employees.supervisorname FROM allwater.labtranstoappend LEFT OUTER JOIN allwater.employees ON allwater.labtranstoappend.userid = allwater.employees.maximouserid); -- Log Insert finish time INSERT INTO tmp_logging ( eventtime, value ) VALUES ( GETDATE(), 'InsertEndTime' ); -- Update the logging tables -- Get the count for newly inserted records INSERT INTO tmp_logging ( total, value ) VALUES ( (WITH inserted_result AS ( SELECT query, MAX(si.starttime) OVER () AS last_q_stime, si.starttime AS stime FROM stl_insert si, SVV_TABLE_INFO sti WHERE sti.table_id = si.tbl AND sti."table" = 'costrecord' ) SELECT SUM(ROWS) FROM stl_insert si, inserted_result ir WHERE si.query = ir.query AND ir.last_q_stime = stime), ('recordsadded') ); BEGIN; -- Update logging table INSERT INTO logging ( transactiondate, sourcefilename, destinationtable, sourcerecordcount, recordsadded, recordsappended ) VALUES ( GETDATE(), (SELECT TOP 1 TRIM(filename) FROM stl_load_commits WHERE query = pg_last_copy_id() ORDER BY CURTIME DESC), --inserts the actual file name that was loaded from S3 'timesheetrecords', (SELECT (pg_last_copy_count ()) +(SELECT COUNT(*) FROM STL_LOAD_ERRORS WHERE QUERY = pg_last_copy_id())), NVL((SELECT total FROM tmp_logging WHERE value = 'recordsadded'),0), NVL((SELECT total FROM tmp_logging WHERE value = 'recordsdeleted'),0) ); END; BEGIN; -- Cleanup DROP TABLE labtranstoappend; DROP TABLE tmp_logging; END; END TRANSACTION; Suggested fix: Reformat should beautify the code and not delete stuff.