CASE Statement and Append Help

CASE Statement and Append Help

Postby 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 NULL ELSE Quest1 END AS Quest1, CASE WHEN Quest2 < 0 THEN NULL ELSE Quest2 END AS Quest2,
CASE WHEN Quest3 < 0 THEN NULL ELSE Quest3 END AS Quest3, CASE WHEN Quest4 < 0 THEN NULL ELSE Quest4 END AS Quest4,
CASE WHEN Quest5 < 0 THEN NULL ELSE Quest5 END AS Quest5, CASE WHEN Quest6 < 0 THEN NULL ELSE Quest6 END AS Quest6,
CASE WHEN Quest1 < 0 THEN NULL ELSE Quest1 + Quest2 + Quest3 + Quest4 + Quest5 + Quest6 END AS QuestTotal, ShipperName, GBL, PickupDate,
RequiredDeliveryDate, ActualDeliveryDate, ShipWeight, CASE WHEN SitApplies <> 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.
Posts: 1
Joined: Thu 11 Jun 2009 13:07

Postby 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.
Devart Team
Posts: 7659
Joined: Thu 14 Aug 2008 12:44

Return to dotConnect for PostgreSQL