HTML Code:
INSERT INTO duplicate_table
(
duplicate_status,
number_of_duplicates,
tracking_number,
invoice_number,
account_number,
net_amount,
billed_weight,
invoice_date,
transaction_date,
pickup_record_number,
receiver_name,
receiver_company_name,
receiver_address_line_1,
receiver_address_line_2,
receiver_city,
receiver_state,
receiver_postal,
receiver_country_territory
)
VALUES
(
SELECT 'duplicate in history' AS duplicate_status,
Count(*) AS number_of_duplicates,
sd.tracking_number AS tracking_number,
sd.invoice_number AS invoice_number,
sd.account_number AS account_number,
sd.net_amount AS net_amount,
sd.billed_weight AS billed_weight,
sd.invoice_date AS invoice_date,
sd.transaction_date AS transaction_date,
sd.pickup_record_number,
sd.receiver_name AS receiver_name,
sd.receiver_company_name AS receiver_company_name,
sd.receiver_address_line_1 AS receiver_address_line_1,
sd.receiver_address_line_2 AS receiver_address_line_2,
sd.receiver_city AS receiver_city,
sd.receiver_state AS receiver_state,
sd.receiver_postal AS receiver_postal,
sd.receiver_country_territory AS receiver_country_territory
FROM workspace_1157176226 AS sd
INNER JOIN duplicate_verification AS dv
ON sd.tracking_number = dv.tracking_number
AND sd.account_number = dv.account_number
WHERE sd.charge_classification_code = 'FRT'
AND sd.bill_option_code NOT IN ( 'DTP',
'DFC' )
AND (
sd.charge_category_code = 'SHP'
OR sd.charge_category_detail_code = 'CLB' )
AND sd.tracking_number <> ''
AND net_amount > 0
GROUP BY sd.tracking_number,
sd.invoice_number,
sd.account_number,
sd.net_amount,
sd.billed_weight,
sd.invoice_date,
sd.transaction_date,
sd.pickup_record_number,
sd.receiver_name,
sd.receiver_company_name,
sd.receiver_address_line_1,
sd.receiver_address_line_2,
sd.receiver_city,
sd.receiver_state,
sd.receiver_postal,
sd.receiver_country_territory
UNION ALL
SELECT 'original' AS duplicate_status,
NULL AS number_of_duplicates,
dv.tracking_number AS tracking_number,
dv.invoice_number AS invoice_number,
dv.account_number AS account_number,
dv.freight_charges AS net_amount,
dv.billed_weight AS billed_weight,
dv.invoice_date AS invoice_date,
dv.transaction_date AS transaction_date,
dv.pickup_record_number,
dv.receiver_name AS receiver_name,
dv.receiver_company_name AS company_name,
dv.receiver_address_line_1 AS receiver_address_line_1,
dv.receiver_address_line_2 AS receiver_address_line_2,
dv.receiver_city AS receiver_city,
dv.receiver_state AS receiver_state,
dv.receiver_postal AS receiver_postal,
dv.receiver_country_territory AS receiver_country_territory
FROM workspace_1157176226 AS sd
INNER JOIN duplicate_verification AS dv
ON sd.tracking_number = dv.tracking_number
AND sd.account_number = dv.account_number
WHERE sd.charge_classification_code = 'FRT'
AND sd.bill_option_code NOT IN ( 'DTP',
'DFC' )
AND (
sd.charge_category_code = 'SHP'
OR sd.charge_category_detail_code = 'CLB' )
AND sd.tracking_number <> ''
AND net_amount > 0
GROUP BY dv.tracking_number,
dv.invoice_number,
dv.account_number,
dv.freight_charges,
dv.billed_weight,
dv.invoice_date,
dv.transaction_date,
dv.pickup_record_number,
dv.receiver_name,
dv.receiver_company_name,
dv.receiver_address_line_1,
dv.receiver_address_line_2,
dv.receiver_city,
dv.receiver_state,
dv.receiver_postal,
dv.receiver_country_territory
)
union
(
SELECT duplicate_status,
number_of_duplicatesx - 1 AS number_of_duplicates,
tracking_number,
invoice_number,
account_number,
net_amount,
billed_weight,
invoice_date,
transaction_date,
pickup_record_number,
receiver_name,
company_name,
receiver_address_line_1,
receiver_address_line_2,
receiver_city,
receiver_state,
receiver_postal,
receiver_country_territory
FROM (
SELECT 'duplicate in batch loaded' AS duplicate_status,
count(*) AS number_of_duplicatesx,
sd.tracking_number AS tracking_number,
sd.invoice_number AS invoice_number,
sd.account_number AS account_number,
sd.net_amount AS net_amount,
sd.billed_weight AS billed_weight,
sd.invoice_date AS invoice_date,
sd.transaction_date AS transaction_date,
sd.pickup_record_number,
sd.receiver_name AS receiver_name,
sd.receiver_company_name AS company_name,
sd.receiver_address_line_1 AS receiver_address_line_1,
sd.receiver_address_line_2 AS receiver_address_line_2,
sd.receiver_city AS receiver_city,
sd.receiver_state AS receiver_state,
sd.receiver_postal AS receiver_postal,
sd.receiver_country_territory AS receiver_country_territory
FROM workspace_1157176226 AS sd
INNER JOIN
(
SELECT tracking_number,
account_number
FROM workspace_1157176226 AS xxx
WHERE xxx.charge_classification_code = 'FRT'
AND xxx.bill_option_code NOT IN ( 'DTP',
'DFC' )
AND (
xxx.charge_category_code = 'SHP'
OR xxx.charge_category_detail_code = 'CLB' )
AND xxx.tracking_number <> ''
AND xxx.net_amount > 0) AS dv
ON sd.tracking_number = dv.tracking_number
AND sd.account_number = dv.account_number
WHERE sd.charge_classification_code = 'FRT'
AND sd.bill_option_code NOT IN ( 'DTP',
'DFC' )
AND (
sd.charge_category_code = 'SHP'
OR sd.charge_category_detail_code = 'CLB' )
AND sd.tracking_number <> ''
AND sd.net_amount > 0
GROUP BY sd.tracking_number,
sd.invoice_number,
sd.account_number,
sd.net_amount,
sd.billed_weight,
sd.invoice_date,
sd.transaction_date,
sd.pickup_record_number,
sd.receiver_name,
sd.receiver_company_name,
sd.receiver_address_line_1,
sd.receiver_address_line_2,
sd.receiver_city,
sd.receiver_state,
sd.receiver_postal,
sd.receiver_country_territory )
WHERE number_of_duplicatesx > 1)
The error is: