error in Pivot (re-submitted with Code Formatted)

[Former Member]

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

[Breck_Carter]

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_nbr

I suspect the result of the PIVOT doesn't have any of the checkoff.columns named in the ON clause... a PIVOT changes everything.

Could not execute statement.
Syntax error near 'ON' on line 91
SQLCODE=-131, ODBC 3 State="42000"

[Breck_Carter]

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:

[VolkerBarth]

Break the giant SELECT into two or more separate statements

...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.