I am extremely new to dotConnect, so please bear with me.
I am trying to add the following statement through the VB.NET TableAdapter Configuration Wizard, connecting to the database using dotConnect for PostgreSQL:
SELECT TSPName, TypeDescpt,
CASE WHEN MarketNumber = 0 THEN 'Domestic' WHEN MarketNumber = 1 THEN 'International' WHEN MarketNumber = 2 THEN 'Inter-Theater'
END AS MarketName, ReportParms_Market.MarketName AS Code, DirectionName, ScopeName,
OriginGBOC.GBOCCode | | ' - ' | | OriginGBOC.GBOCDescpt AS OriginName, DestGBOC.GBOCCode | | ' - ' | | DestGBOC.GBOCDescpt AS DestName,
CASE WHEN Quest1 0 THEN 'Y' ELSE 'N' END AS SitApplies,
RangeStart | | ' - ' | | RangeEnd AS DateRange, tmpReportCompositeStates.CompQuest1, tmpReportCompositeStates.CompQuest2,
tmpReportCompositeStates.CompQuest3, tmpReportCompositeStates.CompQuest4, tmpReportCompositeStates.CompQuest5,
tmpReportCompositeStates.CompQuest6, tmpReportCompositeStates.CompTotals, tmpReportCompositeStates.CompWeight,
tmpReportCompositeStates.CompShipments, tmpReportCompositeGBLOCs.compquest1 AS GBLOCQuest1,
tmpReportCompositeGBLOCs.compquest2 AS GBLOCQuest2, tmpReportCompositeGBLOCs.compquest3 AS GBLOCQuest3,
tmpReportCompositeGBLOCs.compquest4 AS GBLOCQuest4, tmpReportCompositeGBLOCs.compquest5 AS GBLOCQuest5,
tmpReportCompositeGBLOCs.compquest6 AS GBLOCQuest6, tmpReportCompositeGBLOCs.compTotals AS GBLOCTotals,
tmpReportCompositeGBLOCs.compWeight AS GBLOCWeight, tmpReportCompositeGBLOCs.compShipments AS GBLOCShipments,
OriginAgent.AgentName AS OriginAgentName, DestAgent.AgentName AS DestAgentName, ScopeDetail, LevelDetail, StateDescpt
FROM tmpReportData JOIN
SCACS ON tmpReportData.ScacId = SCACs.SCACId JOIN
TSPs ON tmpReportData.TSPId = TSPs.TSPId JOIN
ReportParms_ShipmentType ON tmpReportData.ShipmentTypeId = ReportParms_ShipmentType.TypeId JOIN
ReportParms_Market ON tmpReportData.MarketCodeId = ReportParms_Market.MarketId JOIN
ReportParms_Direction ON tmpReportData.DirectionId = ReportParms_Direction.DirectionId JOIN
ReportParms_Scope ON tmpReportData.ScopeId = ReportParms_Scope.ScopeId JOIN
GBOCs OriginGBOC ON tmpReportData.OriginGBOCId = OriginGBOC.GBOCId JOIN
States ON OriginGBOC.StateId = States.StateId JOIN
GBOCs DestGBOC ON tmpReportData.DestGBOCId = DestGBOC.GBOCId JOIN
tmpReportCompositeStates ON tmpReportData.UserId = tmpReportCompositeStates.UserId AND
States.StateId = tmpReportCompositeStates.StateId JOIN
tmpReportCompositeGBLOCs ON tmpReportData.UserId = tmpReportCompositeGBLOCs.UserId AND
OriginGBOC.GBOCId = tmpReportCompositeGBLOCS.GBLOCId JOIN
Agents OriginAgent ON tmpReportData.OriginAgentId = OriginAgent.AgentId JOIN
Agents DestAgent ON tmpReportData.DestAgentId = DestAgent.AgentId
WHERE tmpReportData.userid = :userid
ORDER BY StateDescpt, OriginGBOC.GBOCCode, OriginAgentName, PickupDate, RequiredDeliveryDate, OriginName, DestName, ShipperName
I am receiving the following error:
Error in SELECT clause: expression near 'MarketNumber'.
Missing FROM clause.
Error in SELECT clause: expression near ','.
Error in SELECT clause: expression near '|'.
Unable to parse query text.
The first error, that references 'MarketNumber', looks like it's coming from the case statement. The second error that is referencing '|' appears to be coming from the lines where I'm trying to append two fields together into one string.
This statement runs just fine when I run it through pgAdmin. Is there a specific change I need to run case statements through dotConnect?
Any help would be greatly appreciated.