I am generating an error in visual studio 2008's query pane that I can't seem to figure out.
Error in list of function arguments: '=' not recognized. Unable to parse query text.
After some cleaning up on the syntax and adding some proper quotations the issue still persisted. I attempted to execute the query anyway and it ends up waiting endlessly in visual studio 2008.
This is the query.
SELECT
leg.tsrido,
leg.tsrnum,
shipment.dosvlg,
condition(leg.tsakti = 1, load.land, unload.land),
condition(leg.tsakti = 1, load.postun, unload.postun),
condition(leg.tsakti = 1, load.tsadr1, unload.tsadr1)
FROM
tsroma leg
LEFT OUTER JOIN (SELECT * FROM tsdsmd WHERE (srtdos = 'd')) shipment
ON leg.dosvlg = shipment.dosvlg
LEFT OUTER JOIN (SELECT * FROM tsdnaw WHERE (tsroln = 1)) load
ON leg.dosvlg = load.dosvlg
LEFT OUTER JOIN (SELECT * FROM tsdnaw WHERE (tsroln = 3)) unload
ON leg.dosvlg = unload.dosvlg
WHERE (leg.tsrido = 79106279)
ORDER BY 2
I am using ODBC and the connection string includes Driver={UNIMS}. It is a very old database that I have to support in my application.
There's probably a fault in the syntax somewhere, but I can't see it.
Edit1: Here is an example condition code that "WORKS" on the UNIMS database. (Taken out of an AnsiString in the old application.)
condition(leg.tstohn = '" + pAuthorization[7] + "', shipment.pllaad, shipment.pllos)
condition(leg.tstohn = '" + pAuthorization[7] + "', shipment.tslzcd, shipment.tsuzcd)
condition(leg.tstohn = '" + pAuthorization[7] + "', shipment.lalaad, shipment.lalos)
condition(leg.tstohn = '" + pAuthorization[7] + "', shipment.nmlaad, shipment.nmlos) zkafz
condition(leg.tstohn = '" + pAuthorization[7] + "', shipment.adrlaa, shipment.adrlos)
Edit2:
Also unable to find much documentation on UNIMS SQL UNIMS SQL. Here's more information that might help.
Edit3: Here is the entire C# Code
public string qShipments(string a)
{
string q = "";
#region Oracle
if (env == "Oracle")
{
/* string Oracle_Shipment
* Alias issue: http://stackoverflow.com/questions/14218695/ora-00972-identifier-is-too-long
SELECT DISTINCT
csl."tripNumber",
csl."shipmentNumber",
csl."legType",
csl."planSequence",
csl."toHubSearchname",
CASE WHEN csl."legType" = 1 THEN Address1."countryCode" ELSE Address3."countryCode" END countryCode,
CASE WHEN csl."legType" = 1 THEN Address1."postcode" ELSE Address3."postcode" END postcode,
CASE WHEN csl."legType" = 1 THEN Address1."streetLine1" ELSE Address3."streetLine1" END streetLine
FROM
"cef_v_cw_trip" ct
LEFT OUTER JOIN "cef_v_cw_shipmentLegs" csl
ON ct."tripNumber" = csl."tripNumber"
LEFT OUTER JOIN "cef_v_cw_shipment" cs
ON csl."shipmentNumber" = cs."shipmentNumber"
LEFT OUTER JOIN (SELECT * FROM "cef_v_cw_fileAddress" WHERE "addressRole" = 1) Address1
ON cs."shipmentNumber" = Address1."fileNumber"
LEFT OUTER JOIN (SELECT * FROM "cef_v_cw_fileAddress" WHERE "addressRole" = 3) Address3
ON cs."shipmentNumber" = Address3."fileNumber"
WHERE ct."tripNumber" = '1045013'
AND csl."toHubSearchname" <> ' '
ORDER BY 4
*/
q = string.Format("SELECT DISTINCT " +
"csl.\"tripNumber\", " +
"csl.\"shipmentNumber\", " +
"csl.\"legType\", " +
"csl.\"planSequence\", " +
"csl.\"toHubSearchname\", " +
"CASE WHEN csl.\"legType\" = 1 THEN Address1.\"countryCode\" ELSE Address3.\"countryCode\" END countryCode, " +
"CASE WHEN csl.\"legType\" = 1 THEN Address1.\"postcode\" ELSE Address3.\"postcode\" END postcode, " +
"CASE WHEN csl.\"legType\" = 1 THEN Address1.\"streetLine1\" ELSE Address3.\"streetLine1\" END streetLine " +
"FROM " +
"\"{0}v_cw_trip\" ct " +
"LEFT OUTER JOIN \"{0}v_cw_shipmentLegs\" csl " +
"ON ct.\"tripNumber\" = csl.\"tripNumber\" " +
"LEFT OUTER JOIN \"{0}v_cw_shipment\" cs " +
"ON csl.\"shipmentNumber\" = cs.\"shipmentNumber\" " +
"LEFT OUTER JOIN (SELECT * FROM \"{0}v_cw_fileAddress\" WHERE \"addressRole\" = 1) Address1 " +
"ON cs.\"shipmentNumber\" = Address1.\"fileNumber\" " +
"LEFT OUTER JOIN (SELECT * FROM \"{0}v_cw_fileAddress\" WHERE \"addressRole\" = 3) Address3 " +
"ON cs.\"shipmentNumber\" = Address3.\"fileNumber\" " +
"WHERE ct.\"tripNumber\" = {1} " +
"AND csl.\"toHubSearchname\" <> ' ' " +
"ORDER BY 4", prefix, a.ToString());
}
#endregion
#region UNIMS
if (env == "UNIMS")
{
/* string Unims shipment
* http://stackoverflow.com/questions/14379573/error-in-list-of-function-arguments-not-recognized-unable-to-parse-query-t
SELECT
leg.tsrido,
leg.tsrnum,
shipment.dosvlg,
condition(leg.tsakti = 1, load.land, unload.land),
condition(leg.tsakti = 1, load.postun, unload.postun),
condition(leg.tsakti = 1, load.tsadr1, unload.tsadr1)
FROM
tsroma leg
LEFT OUTER JOIN (SELECT * FROM tsdsmd WHERE (srtdos = 'd')) shipment
ON leg.dosvlg = shipment.dosvlg
LEFT OUTER JOIN (SELECT * FROM tsdnaw WHERE (tsroln = 1)) load
ON leg.dosvlg = load.dosvlg
LEFT OUTER JOIN (SELECT * FROM tsdnaw WHERE (tsroln = 3)) unload
ON leg.dosvlg = unload.dosvlg
WHERE (leg.tsrido = 79106279)
ORDER BY 2
*/
q = string.Format("SELECT " +
"leg.tsrido, " +
"leg.tsrnum, " +
"shipment.dosvlg, " +
"condition(leg.tsakti = 1, load.land, unload.land), " +
"condition(leg.tsakti = 1, load.postun, unload.postun), " +
"condition(leg.tsakti = 1, load.tsadr1, unload.tsadr1) " +
"FROM " +
"tsroma leg " +
"LEFT OUTER JOIN (SELECT * FROM tsdsmd WHERE srtdos = 'd') shipment " +
"ON leg.dosvlg = shipment.dosvlg " +
"LEFT OUTER JOIN (SELECT * FROM tsdnaw where tsroln = 1) load " +
"ON leg.dosvlg = load.dosvlg " +
"LEFT OUTER JOIN (SELECT * FROM tsdnaw where tsroln = 3) unload " +
"ON leg.dosvlg = unload.dosvlg " +
"WHERE leg.tsrido = {1} " +
"ORDER BY 2", prefix, a.ToString());
}
#endregion
return q;
}
Edit4: I noticed that the query is endless when I remove the cases below. I think I am going to reconsider my ODBC connection first.
Edit5: Does not seem to work in PuTTY or directly to the machine either in most cases. It just ends up in a resource hogging query for some reason. New approach: problem resolved with the right query in PuTTY but this is not yet accepted in ODBC Unims
select
leg.tsrido,
leg.tsrnum,
dosier.dosvlg,
condition(leg.tsakti = 1, dosier.lalaad, dosier.lalos),
condition(leg.tsakti = 1, dosier.pklaad, dosier.pklos),
condition(leg.tsakti = 1, dosier.tsla1, dosier.tsua1)
from
tsroma leg
left outer join dosier
on leg.dosvlg = dosier.dosvlg
where
leg.tsrido = 79106279
order by 2;
Final Edit:
Above code works properly despite that visual studio does not recognize the unims syntax for the conditions. Old queries were stuck and over 100 MB in size according to the server, killed those processes. 10 year old databases are a strange thing indeed.
I am not familiar with UniMS, but perhaps one of the following would work:
(case when leg.tsakti = 1 then load.land else unload.land end),
(case when condition(leg.tsakti = 1 then load.postun else unload.postun end),
(case when condition(leg.tsakti = 1 then load.tsadr1 else unload.tsadr1 end)
or:
iif(leg.tsakti = 1, load.land, unload.land),
iif(leg.tsakti = 1, load.postun, unload.postun),
iif(leg.tsakti = 1, load.tsadr1, unload.tsadr1)
or:
if(leg.tsakti = 1, load.land, unload.land),
if(leg.tsakti = 1, load.postun, unload.postun),
if(leg.tsakti = 1, load.tsadr1, unload.tsadr1)
These work in some other databases.