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, 13:43

murraysobol's gravatar image

murraysobol
793611
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_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"
(31 Mar, 15:17) 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:

(31 Mar, 15:21) Breck Carter
Replies hidden
Comment Text Removed

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.

(01 Apr, 04:06) Volker Barth
Be the first one to answer this question!
toggle preview

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text](http://url.com/ "title")
  • image?![alt text](/path/img.jpg "title")
  • numbered list: 1. Foo 2. Bar
  • to add a line break simply add two spaces to where you would like the new line to be.
  • basic HTML tags are also supported

Question tags:

×40

question asked: 31 Mar, 13:43

question was seen: 81 times

last updated: 01 Apr, 04:07