Running SQL Anywhere 17.0.4.2053 on Windows 10 64-bit.
I have SQL using the PIVOT directive:
Code Sample
SELECT s.settlement_nbr,
s1_settlement_ngc_split.payee_id,
s.uom_buy_sell,
s.uom_buy_sell_description,
s.uom_price,
s.uom_price_description
FROM s1_settlement_ngc_split,
(SELECT s.settlement_nbr,
'S' AS sv_type,
s.settlement_type,
'Contract No. ' + CAST(c.contract_id AS varchar(10))
AS group_description,
s.uom_code AS uom_buy_sell,
uom_s.description AS uom_buy_sell_description,
c.alt_price_uom AS uom_price,
uom_p.description AS uom_price_description
FROM s1_settlement s,
s1_settlement_ticket_detail td
LEFT OUTER JOIN
(SELECT vehicle_disposition_nbr,
SUM(advance_amount) AS advance_amount
FROM s1_vp_advance_amount
GROUP BY vehicle_disposition_nbr
) vp_a
ON vp_a.vehicle_disposition_nbr = td.vehicle_disposition_nbr
LEFT OUTER JOIN
(SELECT settlement_nbr,
contract_nbr,
pricing_order,
vehicle_disposition_nbr,
amount
FROM s1_settlement_ticket_discount
WHERE deduction_addition_code = 'F'
) fr
ON fr.settlement_nbr = td.settlement_nbr
AND fr.contract_nbr = td.contract_nbr
AND fr.pricing_order = td.pricing_order
AND fr.vehicle_disposition_nbr = td.vehicle_disposition_nbr
LEFT OUTER JOIN s1_settlement_ticket_ntp_view ntp
ON ntp.settlement_nbr = td.settlement_nbr
AND ntp.contract_nbr = td.contract_nbr
AND ntp.pricing_order = td.pricing_order
AND ntp.vehicle_disposition_nbr = td.vehicle_disposition_nbr
LEFT OUTER JOIN
(SELECT settlement_nbr,
contract_nbr,
pricing_order,
vehicle_disposition_nbr,
1 AS c1,
2 AS c2,
3 AS c3,
4 AS c4,
5 AS c5,
6 AS c6
FROM (SELECT settlement_nbr,
contract_nbr,
pricing_order,
vehicle_disposition_nbr,
RANK() OVER (PARTITION BY CAST(settlement_nbr AS varchar(10)) + ' ' + CAST(contract_nbr AS varchar(10)) + ' ' + CAST(pricing_order AS varchar(10)) + ' ' + CAST(vehicle_disposition_nbr AS varchar(10))
ORDER BY checkoff_code
)
AS row_count,
amount
FROM (SELECT DISTINCT sc.settlement_nbr,
sc.contract_nbr,
sc.pricing_order,
sc.vehicle_disposition_nbr,
(-1) * sc.amount
AS amount,
sc.checkoff_code
FROM s1_settlement_checkoff sc
) a
) b
PIVOT (MIN(amount) FOR row_count IN ('1', '2', '3', '4', '5', '6'))
) checkoff
ON checkoff.settlement_nbr = td.settlement_nbr
AND checkoff.contract_nbr = td.contract_nbr
AND checkoff.pricing_order = td.pricing_order
AND checkoff.vehicle_disposition_nbr = td.vehicle_disposition_nbr
LEFT OUTER JOIN
(SELECT settlement_nbr,
contract_nbr,
pricing_order,
vehicle_disposition_nbr,
SUM(tax1_amount) AS tax1,
SUM(tax2_amount) AS tax2,
SUM(tax3_amount) AS tax3
FROM s1_settlement_ticket_epr_payee
GROUP BY settlement_nbr,
contract_nbr,
pricing_order,
vehicle_disposition_nbr
) epr
ON epr.settlement_nbr = td.settlement_nbr
AND epr.contract_nbr = td.contract_nbr
AND epr.pricing_order = td.pricing_order
AND epr.vehicle_disposition_nbr = td.vehicle_disposition_nbr,
s1_settlement_vehicle sv,
s1_contract c,
s1_contract_pricing cp,
s1_uom uom_s,
s1_uom uom_p,
s1_uom_conv_10000 uom,
s1_grainsmart_option g_o
WHERE s.settlement_nbr = td.settlement_nbr
AND td.contract_nbr = c.contract_nbr
AND td.contract_nbr = cp.contract_nbr
AND td.pricing_order = cp.pricing_order
AND td.settlement_nbr = sv.settlement_nbr
AND td.quantity_entry_nbr = sv.quantity_entry_nbr
AND uom_s.uom_code = s.uom_code
AND uom_p.uom_code = c.alt_price_uom
AND uom.from_uom = 'LB'
AND uom.to_uom = s.uom_code
AND (uom.commodity_id IS NULL OR
uom.commodity_id = s.commodity_id
)
AND c.contract_id > 0
GROUP BY s.settlement_nbr,
s.settlement_type,
c.contract_id,
td.contract_nbr,
s.uom_code,
uom_s.description,
c.alt_price_uom,
uom_p.description
) s
WHERE s1_settlement_ngc_split.settlement_nbr = s.settlement_nbr
;
However It gives this error: Could not execute statement. Syntax error near ')' on line 75 SQLCODE=-131 ODBC 3 State = "42000"
Of course, line 75 points to the PIVOT.
I thought I was following the syntax in the above mentioned article. Any suggestions would be appreciated. Murray
asked
31 Mar '20, 13:43
murraysobol
(suspended)
accept rate:
16%
Getting rid of the extra ) just moves the syntax error down one line
/* PIVOT (MIN(amount) FOR row_count IN ('1', '2', '3', '4', '5', '6')) ) checkoff */ PIVOT (MIN(amount) FOR row_count IN ('1', '2', '3', '4', '5', '6') ) checkoff ON checkoff.settlement_nbr = td.settlement_nbr AND checkoff.contract_nbr = td.contract_nbr AND checkoff.pricing_order = td.pricing_order AND checkoff.vehicle_disposition_nbr = td.vehicle_disposition_nbrI suspect the result of the PIVOT doesn't have any of the checkoff.columns named in the ON clause... a PIVOT changes everything.
Suggestion: Break the giant SELECT into two or more separate statements, with intermediate temporary tables. One of the outer SELECT statements should contain JUST the PIVOT. Then you can test step-by-step.
If the result performs poorly because of the extra intermediate tables, then you can try combining the code... but at least you have a working version to build on.
...and you can avoid this:
...or use common table tables (aka WITH clauses) to refactor the statement, say, by turning the derived tables in the giant FROM clause into local views, so only their name has to be used within the FROM clause...
As to the PIVOT clause, I'm certainly neither able to use nor to understand that within a really complex FROM clause, so I'm out of my wits here.