Nick Nick - 6 months ago 9
SQL Question

Incorrect syntax error after converting PLSQL to TSQL

I converted Oracle PLSQL code to TSQL. When I test the query I get Incorrect syntax near keyword 'ORDER' error. I am using multiple unions in the FROM statement. If I remove the ORDER BY statement the check returns Incorrect syntax error near ')'. I have included the entire converted TSQL below in hope a second pair of eyes will see the error. I am sure there are better ways of doing this, but I inherited this midway through a conversion form Oracle 11g to SQL 2012. Any help in getting around the error would be appreciated. Here is the entire statement:

SELECT casenumber,partynumber,ptyname,connection1,connection2,connection3,connection4,connection5,CASE WHEN LTrim(appointtype) IN ('OSA','NOA','PRO') THEN '' ELSE CAST(barnumber AS char) END AS barnumber,
appointtype,attyname,address1,address2,city,state,zipcode,zipfour,phone,emailaddress,oralargument,timely,prisonid,appear, vacstart, vacend, vacstart1, vacend1, vacstart2, vacend2, vacstart3, vacend3,
vacstart4, vacend4, vacstart5, vacend5, vacstart6, vacend6, vacstart7, vacend7, vacstart8, vacend8, vacstart9, vacend9, vacstart10,vacend10,vacstart11,vacend11,vacstart12,vacend12,vacstart13,vacend13,
vacstart14,vacend14,altaddr1,altaddr2,altcity,altstate,altzip,altzipfour, descr,eserv_address1,eserv_address2
from (SELECT p.casenumber, p.partynumber,p.ptyname,connection1,connection2,connection3,connection4,connection5,1 AS Attorney_order,barnumber1 AS barnumber ,appointtype1 AS appointtype,
CASE WHEN LTrim(RTrim(appointtype1))='OSA' THEN osaname ELSE attyname END AS attyname,
CASE WHEN LTrim(Rtrim(appointtype1)) in ('PRO','NOA') THEN d.address1 WHEN appointtype1='OSA' THEN osa.address1 ELSE addr1 END AS Address1,
CASE WHEN LTrim(Rtrim(appointtype1)) in ('PRO','NOA') THEN d.address2 WHEN LTrim(Rtrim(appointtype1))='OSA' THEN osa.address2 ELSE addr2 END AS Address2,
CASE WHEN LTrim(Rtrim(appointtype1)) in ('PRO','NOA') THEN z2.city WHEN appointtype1='OSA' THEN z3.city ELSE z1.city END AS CITY,
CASE WHEN LTrim(Rtrim(appointtype1)) in ('PRO','NOA') THEN z2.state WHEN appointtype1='OSA' THEN z3.state ELSE z1.state END AS STATE,
CASE WHEN LTrim(Rtrim(appointtype1)) in ('PRO','NOA') THEN d.zipcode WHEN appointtype1='OSA' THEN osa.zipcode ELSE a.zipcode END AS ZIPCODE,
CASE WHEN LTrim(Rtrim(appointtype1)) in ('PRO','NOA') THEN d.zipfour WHEN appointtype1='OSA' THEN osa.zipfour ELSE a.zipfour END AS ZIPFOUR,
CASE WHEN LTrim(Rtrim(appointtype1)) in ('PRO','NOA') THEN CONCAT(IIF(d.phonearea is NOT NULL, CONCAT(d.phonearea,'/'),''),'',SUBSTRING(CAST(d.phonenbr as VARCHAR(100)),1,3),'-',RIGHT(CAST(d.phonenbr as VARCHAR(100)),4))
WHEN appointtype1='OSA' THEN CONCAT(IIF(osa.phonearea is NOT NULL, CONCAT(osa.phonearea,'/'),''),'',SUBSTRING(CAST(osa.phonenbr as VARCHAR(100)),1,3),'-',RIGHT(CAST(osa.phonenbr as VARCHAR(100)),4))
--CASE WHEN LTrim(Rtrim(appointtype1)) in ('PRO','NOA') THEN ISNULL(d.phonearea,CAST(d.phonearea AS char)+'/','')+' '+SubString(CAST(d.phonenbr AS char),1,3)+'-'+SubString(d.phonenbr,-4)
--WHEN appointtype1='OSA' THEN ISNULL(osa.phonearea,CAST(osa.phonearea AS char)+'/','')+' '+SubString(CAST(osa.phonenbr AS char),1,3)+'-'+SubString(osa.phonenbr,-4)
--ELSE ISNULL(a.areacode,CAST(a.areacode AS char)+'/','')+' '+SubString(CAST(a.phonenbr AS char),1,3)+'-'+SubString(a.phonenbr,-4) END AS phone,
ELSE CONCAT(IIF(a.phonearea is NOT NULL, CONCAT(a.phonearea,'/'),''),'',SUBSTRING(CAST(a.phonenbr as VARCHAR(100)),1,3),'-',RIGHT(CAST(a.phonenbr as VARCHAR(100)),4)) END AS phone,
CASE WHEN LTrim(Rtrim(appointtype1)) in ('PRO','NOA') THEN d.emailaddress WHEN appointtype1='OSA' THEN osa.emailaddress ELSE a.emailaddress END AS EMAILADDRESS,
CASE WHEN LTrim(Rtrim(appointtype1)) in ('PRO','NOA') THEN d.eserv_address1 WHEN appointtype1='OSA' THEN osa.eserv_address1 ELSE a.eserv_address1 END AS eserv_address1,
CASE WHEN LTrim(Rtrim(appointtype1)) in ('PRO','NOA') THEN d.eserv_address2 WHEN appointtype1='OSA' THEN osa.eserv_address2 ELSE a.eserv_address2 END AS eserv_address2,
oralargument,timely,prisonid,appear1 AS appear, vacstart, vacend, vacstart1, vacend1 ,vacstart2 , vacend2 , vacstart3, vacend3,vacstart4,vacend4 ,vacstart5 ,vacend5,vacstart6 ,vacend6,
vacstart7 ,vacend7, vacstart8 ,vacend8,vacstart9 ,vacend9, vacstart10,vacend10,vacstart11,vacend11,vacstart12,vacend12,vacstart13,vacend13,vacstart14,vacend14,altaddr1,altaddr2,
z5.city AS altcity, z5.state AS altstate,altzip,altzipfour, descr
from parties p left JOIN attorneys a ON p.barnumber1=a.attynbr
left JOIN address d ON p.casenumber=d.casenumber AND p.partynumber=d.partynumber AND p.courtid=d.courtid AND p.caseyear=d.caseyear AND ISNULL(p.barnumber1,0)=0
left join outstateaddr osa ON p.casenumber=osa.casenumber AND p.courtid=osa.courtid AND p.caseyear=osa.caseyear AND p.partynumber=osa.partynumber and p.appointtype1='OSA' AND partyattyseq=1
left JOIN zipcodes z1 ON a.zipcode=z1.zipcode left JOIN zipcodes z2 ON d.zipcode=z2.zipcode left JOIN zipcodes z3 ON osa.zipcode=z3.zipcode left JOIN zipcodes z5 ON a.altzip=z5.zipcode
left join events on a.status = events.tablecode and events.tableid = 0509 WHERE p.casenumber=332755 AND p.courtid=84
UNION
SELECT p.casenumber, p.partynumber,p.ptyname, connection1,connection2,connection3,connection4,connection5,2 AS Attorney_order,barnumber2 AS barnumber ,appointtype2 AS appointtype,
CASE WHEN LTrim(RTrim(appointtype2))='OSA' THEN osaname ELSE attyname END AS attyname,
CASE WHEN LTrim(Rtrim(appointtype2)) in ('PRO','NOA') THEN d.address1 WHEN appointtype2='OSA' THEN osa.address1 ELSE addr1 END AS Address1,
CASE WHEN LTrim(Rtrim(appointtype2)) in ('PRO','NOA') THEN d.address2 WHEN appointtype2='OSA' THEN osa.address2 ELSE addr2 END AS Address2,
CASE WHEN LTrim(Rtrim(appointtype2)) in ('PRO','NOA') THEN z2.city WHEN appointtype2='OSA' THEN z3.city ELSE z1.city END AS CITY,
CASE WHEN LTrim(Rtrim(appointtype2)) in ('PRO','NOA') THEN z2.state WHEN appointtype2='OSA' THEN z3.state ELSE z1.state END AS STATE,
CASE WHEN LTrim(Rtrim(appointtype2)) in ('PRO','NOA') THEN d.zipcode WHEN appointtype2='OSA' THEN osa.zipcode ELSE a.zipcode END AS ZIPCODE,
CASE WHEN LTrim(Rtrim(appointtype2)) in ('PRO','NOA') THEN d.zipfour WHEN appointtype2='OSA' THEN osa.zipfour ELSE a.zipfour END AS ZIPFOUR,
CASE WHEN LTrim(Rtrim(appointtype2)) in ('PRO','NOA') THEN CONCAT(IIF(d.phonearea is NOT NULL, CONCAT(d.phonearea,'/'),''),'',SUBSTRING(CAST(d.phonenbr as VARCHAR(100)),1,3),'-',RIGHT(CAST(d.phonenbr as VARCHAR(100)),4))
WHEN appointtype2='OSA' THEN CONCAT(IIF(osa.phonearea is NOT NULL, CONCAT(osa.phonearea,'/'),''),'',SUBSTRING(CAST(osa.phonenbr as VARCHAR(100)),1,3),'-',RIGHT(CAST(osa.phonenbr as VARCHAR(100)),4))
ELSE CONCAT(IIF(a.phonearea is NOT NULL, CONCAT(a.phonearea,'/'),''),'',SUBSTRING(CAST(a.phonenbr as VARCHAR(100)),1,3),'-',RIGHT(CAST(a.phonenbr as VARCHAR(100)),4)) END AS phone,
--CASE WHEN LTrim(Rtrim(appointtype2)) in ('PRO','NOA') THEN ISNULL(d.phonearea,CAST(d.phonearea AS char)+'/','')+' '+SubString(CAST(d.phonenbr AS char),1,3)+'-'+SubString(d.phonenbr,-4)
-- WHEN appointtype2='OSA' THEN ISNULL(osa.phonearea,CAST(osa.phonearea AS char)+'/','')+' '+SubString(CAST(osa.phonenbr AS char),1,3)+'-'+SubString(osa.phonenbr,-4)
-- ELSE ISNULL(a.areacode,CAST(a.areacode AS char)+'/','')+' '+SubString(CAST(a.phonenbr AS char),1,3)+'-'+SubString(a.phonenbr,-4) END AS phone,
CASE WHEN LTrim(Rtrim(appointtype2)) in ('PRO','NOA') THEN d.emailaddress WHEN appointtype2='OSA' THEN osa.emailaddress ELSE a.emailaddress END AS EMAILADDRESS,
CASE WHEN LTrim(Rtrim(appointtype2)) in ('PRO','NOA') THEN d.eserv_address1 WHEN appointtype2='OSA' THEN osa.eserv_address1 ELSE a.eserv_address1 END AS eserv_address1,
CASE WHEN LTrim(Rtrim(appointtype2)) in ('PRO','NOA') THEN d.eserv_address2 WHEN appointtype2='OSA' THEN osa.eserv_address2 ELSE a.eserv_address2 END AS eserv_address2,
oralargument,timely,prisonid ,appear2 AS appear, vacstart, vacend, vacstart1, vacend1 ,vacstart2 , vacend2 , vacstart3, vacend3,vacstart4,vacend4 ,vacstart5 ,vacend5,vacstart6 ,vacend6,
vacstart7 ,vacend7, vacstart8 ,vacend8,vacstart9 ,vacend9, vacstart10,vacend10, vacstart11,vacend11,vacstart12,vacend12,vacstart13,vacend13,vacstart14,vacend14,altaddr1,altaddr2,
z5.city AS altcity, z5.state AS altstate,altzip,altzipfour, descr
from parties p left JOIN attorneys a ON p.barnumber2=a.attynbr
left JOIN address d ON p.casenumber=d.casenumber AND p.partynumber=d.partynumber AND p.courtid=d.courtid AND p.caseyear=d.caseyear AND ISNULL(p.barnumber2,0)=0
left join outstateaddr osa ON p.casenumber=osa.casenumber AND p.courtid=osa.courtid AND p.caseyear=osa.caseyear AND p.partynumber=osa.partynumber AND p.appointtype2='OSA' AND partyattyseq=2
left JOIN zipcodes z1 ON a.zipcode=z1.zipcode left JOIN zipcodes z2 ON d.zipcode=z2.zipcode left JOIN zipcodes z3 ON osa.zipcode=z3.zipcode
left JOIN zipcodes z5 ON a.altzip=z5.zipcode left join events on a.status = events.tablecode and events.tableid = 0509 WHERE p.casenumber=332755 AND appointtype2 IS NOT null AND p.courtid=84
UNION
SELECT p.casenumber, p.partynumber,p.ptyname, connection1,connection2,connection3,connection4,connection5,3 AS Attorney_order,barnumber3 AS barnumber ,appointtype3 AS appointtype,
CASE WHEN LTrim(RTrim(appointtype3))='OSA' THEN osaname ELSE attyname END AS attyname,
CASE WHEN LTrim(Rtrim(appointtype3)) in ('PRO','NOA') THEN d.address1 WHEN appointtype3='OSA' THEN osa.address1 ELSE addr1 END AS Address1,
CASE WHEN LTrim(Rtrim(appointtype3)) in ('PRO','NOA') THEN d.address2 WHEN appointtype3='OSA' THEN osa.address2 ELSE addr2 END AS Address2,
CASE WHEN LTrim(Rtrim(appointtype3)) in ('PRO','NOA') THEN z2.city WHEN appointtype3='OSA' THEN z3.city ELSE z1.city END AS CITY,
CASE WHEN LTrim(Rtrim(appointtype3)) in ('PRO','NOA') THEN z2.state WHEN appointtype3='OSA' THEN z3.state ELSE z1.state END AS STATE,
CASE WHEN LTrim(Rtrim(appointtype3)) in ('PRO','NOA') THEN d.zipcode WHEN appointtype3='OSA' THEN osa.zipcode ELSE a.zipcode END AS ZIPCODE,
CASE WHEN LTrim(Rtrim(appointtype3)) in ('PRO','NOA') THEN d.zipfour WHEN appointtype3='OSA' THEN osa.zipfour ELSE a.zipfour END AS ZIPFOUR,
CASE WHEN LTrim(Rtrim(appointtype3)) in ('PRO','NOA') THEN CONCAT(IIF(d.phonearea is NOT NULL, CONCAT(d.phonearea,'/'),''),'',SUBSTRING(CAST(d.phonenbr as VARCHAR(100)),1,3),'-',RIGHT(CAST(d.phonenbr as VARCHAR(100)),4))
WHEN appointtype3='OSA' THEN CONCAT(IIF(osa.phonearea is NOT NULL, CONCAT(osa.phonearea,'/'),''),'',SUBSTRING(CAST(osa.phonenbr as VARCHAR(100)),1,3),'-',RIGHT(CAST(osa.phonenbr as VARCHAR(100)),4))
ELSE CONCAT(IIF(a.phonearea is NOT NULL, CONCAT(a.phonearea,'/'),''),'',SUBSTRING(CAST(a.phonenbr as VARCHAR(100)),1,3),'-',RIGHT(CAST(a.phonenbr as VARCHAR(100)),4)) END AS phone,
--CASE WHEN LTrim(Rtrim(appointtype3)) in ('PRO','NOA') THEN ISNULL(d.phonearea,CAST(d.phonearea AS char)+'/','')+' '+SubString(CAST(d.phonenbr AS char),1,3)+'-'+SubString(d.phonenbr,-4)
--WHEN appointtype3='OSA' THEN ISNULL(osa.phonearea,CAST(osa.phonearea AS char)+'/','')+' '+SubString(CAST(osa.phonenbr AS char),1,3)+'-'+SubString(osa.phonenbr,-4)
-- ELSE ISNULL(a.areacode,CAST(a.areacode AS char)+'/','')+' '+SubString(CAST(a.phonenbr AS char),1,3)||'-'||SubString(a.phonenbr,-4) END AS phone,
CASE WHEN LTrim(Rtrim(appointtype3)) in ('PRO','NOA') THEN d.emailaddress WHEN appointtype3='OSA' THEN osa.emailaddress ELSE a.emailaddress END AS EMAILADDRESS,
CASE WHEN LTrim(Rtrim(appointtype3)) in ('PRO','NOA') THEN d.eserv_address1 WHEN appointtype3='OSA' THEN osa.eserv_address1 ELSE a.eserv_address1 END AS eserv_address1,
CASE WHEN LTrim(Rtrim(appointtype3)) in ('PRO','NOA') THEN d.eserv_address2 WHEN appointtype3='OSA' THEN osa.eserv_address2 ELSE a.eserv_address2 END AS eserv_address2,
oralargument,timely,prisonid,appear3 AS appear, vacstart, vacend, vacstart1, vacend1 ,vacstart2 , vacend2 , vacstart3, vacend3,vacstart4,vacend4 ,vacstart5 ,vacend5,vacstart6 ,vacend6,
vacstart7 ,vacend7, vacstart8 ,vacend8,vacstart9 ,vacend9, vacstart10 ,vacend10, vacstart11 ,vacend11, vacstart12 ,vacend12, vacstart13,vacend13, vacstart14, vacend14,altaddr1,altaddr2,
z5.city AS altcity, z5.state AS altstate,altzip,altzipfour, descr
from parties p left JOIN attorneys a ON p.barnumber3=a.attynbr
left JOIN address d ON p.casenumber=d.casenumber AND p.partynumber=d.partynumber AND p.courtid=d.courtid AND p.caseyear=d.caseyear AND ISNULL(p.barnumber3,0)=0
left join outstateaddr osa ON p.casenumber=osa.casenumber AND p.courtid=osa.courtid AND p.caseyear=osa.caseyear AND p.partynumber=osa.partynumber AND p.appointtype3='OSA' AND partyattyseq=3
left JOIN zipcodes z1 ON a.zipcode=z1.zipcode left JOIN zipcodes z2 ON d.zipcode=z2.zipcode left JOIN zipcodes z3 ON osa.zipcode=z3.zipcode
left JOIN zipcodes z5 ON a.altzip=z5.zipcode left join events on a.status = events.tablecode and events.tableid = 0509
WHERE p.casenumber=332755 AND appointtype3 IS NOT null AND p.courtid=84
UNION
SELECT p.casenumber, p.partynumber,p.ptyname, connection1,connection2,connection3,connection4,connection5,4 AS Attorney_order,barnumber2 AS barnumber ,appointtype4 AS appointtype,
CASE WHEN LTrim(RTrim(appointtype4))='OSA' THEN osaname ELSE attyname END AS attyname,
CASE WHEN LTrim(Rtrim(appointtype4)) in ('PRO','NOA') THEN d.address1 WHEN appointtype4='OSA' THEN osa.address1 ELSE addr1 END AS Address1,
CASE WHEN LTrim(Rtrim(appointtype4)) in ('PRO','NOA') THEN d.address2 WHEN appointtype4='OSA' THEN osa.address2 ELSE addr2 END AS Address2,
CASE WHEN LTrim(Rtrim(appointtype4)) in ('PRO','NOA') THEN z2.city WHEN appointtype4='OSA' THEN z3.city ELSE z1.city END AS CITY,
CASE WHEN LTrim(Rtrim(appointtype4)) in ('PRO','NOA') THEN z2.state WHEN appointtype4='OSA' THEN z3.state ELSE z1.state END AS STATE, CASE WHEN LTrim(Rtrim(appointtype4)) in ('PRO','NOA')
THEN d.zipcode WHEN appointtype4='OSA' THEN osa.zipcode ELSE a.zipcode END AS ZIPCODE,
CASE WHEN LTrim(Rtrim(appointtype4)) in ('PRO','NOA') THEN d.zipfour WHEN appointtype4='OSA' THEN osa.zipfour ELSE a.zipfour END AS ZIPFOUR,
CASE WHEN LTrim(Rtrim(appointtype4)) in ('PRO','NOA') THEN CONCAT(IIF(d.phonearea is NOT NULL, CONCAT(d.phonearea,'/'),''),'',SUBSTRING(CAST(d.phonenbr as VARCHAR(100)),1,3),'-',RIGHT(CAST(d.phonenbr as VARCHAR(100)),4))
WHEN appointtype4='OSA' THEN CONCAT(IIF(osa.phonearea is NOT NULL, CONCAT(osa.phonearea,'/'),''),'',SUBSTRING(CAST(osa.phonenbr as VARCHAR(100)),1,3),'-',RIGHT(CAST(osa.phonenbr as VARCHAR(100)),4))
ELSE CONCAT(IIF(a.phonearea is NOT NULL, CONCAT(a.phonearea,'/'),''),'',SUBSTRING(CAST(a.phonenbr as VARCHAR(100)),1,3),'-',RIGHT(CAST(a.phonenbr as VARCHAR(100)),4)) END AS phone,
--CASE WHEN LTrim(Rtrim(appointtype4)) in ('PRO','NOA') THEN ISNULL(d.phonearea,CAST(d.phonearea AS char)+'/','')+' '+SubString(CAST(d.phonenbr AS char),1,3)+'-'+SubString(d.phonenbr,-4)
--WHEN LTrim(Rtrim(appointtype4))='OSA' THEN ISNULL(osa.phonearea,CAST(osa.phonearea AS char)+'/','')+' '+SubString(CAST(osa.phonenbr AS char),1,3)+'-'+SubString(osa.phonenbr,-4)
--ELSE ISNULL(a.areacode,CAST(a.areacode AS char)+'/','')+' '+SubString(CAST(a.phonenbr AS char),1,3)+'-'+SubString(a.phonenbr,-4) END AS phone,
CASE WHEN LTrim(Rtrim(appointtype4)) in ('PRO','NOA') THEN d.emailaddress WHEN appointtype4='OSA' THEN osa.emailaddress ELSE a.emailaddress END AS EMAILADDRESS,
CASE WHEN LTrim(Rtrim(appointtype4)) in ('PRO','NOA') THEN d.eserv_address1 WHEN appointtype4='OSA' THEN osa.eserv_address1 ELSE a.eserv_address1 END AS eserv_address1,
CASE WHEN LTrim(Rtrim(appointtype4)) in ('PRO','NOA') THEN d.eserv_address2 WHEN appointtype4='OSA' THEN osa.eserv_address2 ELSE a.eserv_address2 END AS eserv_address2, oralargument,timely,
prisonid,appear4 AS appear, vacstart, vacend, vacstart1, vacend1 ,vacstart2 , vacend2 , vacstart3, vacend3,vacstart4,vacend4 ,vacstart5 ,vacend5,vacstart6 ,vacend6, vacstart7 ,vacend7, vacstart8 ,
vacend8,vacstart9 ,vacend9, vacstart10,vacend10,vacstart11,vacend11,vacstart12,vacend12,vacstart13,vacend13,vacstart14,vacend14, altaddr1,altaddr2,z5.city AS altcity,
z5.state AS altstate,altzip,altzipfour, descr
from parties p left JOIN attorneys a ON p.barnumber4=a.attynbr
left JOIN address d ON p.casenumber=d.casenumber AND p.partynumber=d.partynumber AND p.courtid=d.courtid AND p.caseyear=d.caseyear AND ISNULL(p.barnumber4,0)=0
left join outstateaddr osa ON p.casenumber=osa.casenumber AND p.courtid=osa.courtid AND p.caseyear=osa.caseyear AND p.partynumber=osa.partynumber AND p.appointtype4='OSA' AND partyattyseq=4
left JOIN zipcodes z1 ON a.zipcode=z1.zipcode left JOIN zipcodes z2 ON d.zipcode=z2.zipcode left JOIN zipcodes z3 ON osa.zipcode=z3.zipcode left JOIN zipcodes z5 ON a.altzip=z5.zipcode
left join events on a.status = events.tablecode and events.tableid = 0509 WHERE p.casenumber=332755 AND p.courtid=84 AND appointtype4 IS NOT NULL)
**ORDER BY casenumber,partynumber,attorney_order**

Answer

When you use SELECT * FROM (SELECT ...) ORDER BY ... you must name your derived table.

Before the ORDER BY you must set a AS tbl after the closing paranthesis.

So the full query is like this

 SELECT col1, col2, ... 
 FROM 
 (SELECT Something) AS tbl --any alias
 ORDER BY col1
Comments