CASE Statement and Append Help

Discussion of open issues, suggestions and bugs regarding ADO.NET provider for PostgreSQL
Post Reply
Posts: 1
Joined: Thu 11 Jun 2009 13:07

CASE Statement and Append Help

Post by kipesmith » Thu 11 Jun 2009 13:37

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

Site Admin
Posts: 8241
Joined: Thu 14 Aug 2008 12:44

Post by Shalex » Fri 12 Jun 2009 15:58

Please send us (support*devart*com, mention this thread in your letter) the script that creates database objects you use in your query. We will try to reproduce the errors.

Post Reply