Labeling Data?

[rfigueroa1976]

Still getting a syntax error.


SELECT dbo_orderheader.ord_number AS [Order Number], dbo_orderheader.ord_refnum AS [Reference Number], dbo_NCR_Occurrence.BookingTerminal AS [Booking Terminal], dbo_orderheader.ord_origin_latestdate AS [Scheduled Loading Date], dbo_orderheader.ord_startdate AS [Actual Loading Date Date], dbo_orderheader.ord_dest_latestdate AS [Scheduled Delivery Date], dbo_orderheader.ord_completiondate AS [Actual Delivery Date], dbo_company_1.cmp_name AS [Shipper Name], dbo_city.cty_name AS [Shipper City], dbo_city.cty_state AS [Shipper State], dbo_company.cmp_name AS Consignee, dbo_city_1.cty_name AS [Consignee City], dbo_city_1.cty_state AS [Consignee State], dbo_NCR_Occurrence.EnteredBy AS [NCR Created By], dbo_NCR_Occurrence.NCR_ID AS [NCR ID], dbo_NCR_Occurrence.CustomerComplaintNumber AS [Customer Complaint Number], dbo_NCR_Occurrence.OccurrenceDate AS [Occurrence Date], dbo_NCR_IncidentTypes.Description AS [Incident Type], dbo_NCR_RootCauses.Description AS [Root Cause], dbo_NCR_PreventiveActions.Description AS [Preventive Action], Switch (dbo_NCR_RootCauses.Description LIKE "Carrier*", "1-Carrier", dbo_NCR_RootCauses.Description LIKE "Shipper*", "2-Shipper", dbo_NCR_RootCauses.Description LIKE "Consignee*", "3-Consignee", dbo_NCR_RootCauses.Description LIKE "Unforeseen*", "4-Unforeseen",TRUE, "5-Other")" AS [Fault Hierarchy]
FROM (((((((dbo_NCR_Occurrence INNER JOIN dbo_NCR_RootCauses ON dbo_NCR_Occurrence.RootCause_ID = dbo_NCR_RootCauses.RootCause_ID) INNER JOIN dbo_orderheader ON dbo_NCR_Occurrence.OrderNumber = dbo_orderheader.ord_number) INNER JOIN dbo_NCR_IncidentTypes ON dbo_NCR_Occurrence.IncidentType_ID = dbo_NCR_IncidentTypes.IncidentType_ID) INNER JOIN dbo_company AS dbo_company_1 ON dbo_orderheader.ord_shipper = dbo_company_1.cmp_id) INNER JOIN dbo_city ON dbo_company_1.cmp_city = dbo_city.cty_code) INNER JOIN dbo_company ON dbo_orderheader.ord_consignee = dbo_company.cmp_id) INNER JOIN dbo_NCR_PreventiveActions ON dbo_NCR_Occurrence.PreventiveAct_ID = dbo_NCR_PreventiveActions.PreventiveAct_ID) INNER JOIN dbo_city AS dbo_city_1 ON dbo_company.cmp_city = dbo_city_1.cty_code
WHERE (((dbo_orderheader.ord_origin_latestdate) Between [start] And [end]) AND ((dbo_NCR_RootCauses.Description) Not Like "*internal*") AND ((dbo_NCR_Occurrence.CustomerName) Like "*arclin*") AND ((dbo_NCR_Occurrence.BilltoName) Like "*arclin*") AND ((dbo_NCR_Occurrence.Status_ID) Not Like "26"))


ORDER BY dbo_city.cty_name, dbo_NCR_RootCauses.Description;

[June7]

Why bother with alias field names?

Are you using query builder to construct the SQL?

LIKE without wildcard might as well use = or in this case <>:

AND ((dbo_NCR_Occurrence.Status_ID) <> "26"))

Status_ID is a text field? If it is number type, remove the quote marks.

[rfigueroa1976]

The wildcard ( "*" ) covers the multiple results that could come. Carrier - Dispatch error, Carrier - Capacity, Carrier - Driver attitude, etc. Each of them could be one of hundreds. This is why i'm trying to bring it down to 5 faults.

[June7]

Was referring to just the Status_ID using LIKE without wildcard.

[rfigueroa1976]

I'm lost June. What do you mean?

What i need to happen is have a formula/command/whatever look at the result of the root cause description field which could be one of hundreds. For the most part, the root cause will start with any of the four previously mentioned (Carrier, shipper, consignee, unforeseen). If the root cause description result starts with Carrier, i want the field/cell next to it to show as "1-Carrier", If starts with Consignee, show at "2-Consignee", etc.

Each of the root causes could have several that start with the same word but end differently; meaning, there could be over 100 root causes that start with the word Carrier. I want the fields/cells next to those labeled as "1-Carrier".

[June7]

I don't see anything wrong with the root causes calculation. My comment specifically addressed the criteria: AND ((dbo_NCR_Occurrence.Status_ID) Not Like "26")) which uses LIKE without a wildcard.

[ssanfu]

As mentioned before, shouldn't use reserved words as object names.
"Description", "Start" and "End" are reserved words.

WHERE (((dbo_orderheader.ord_origin_latestdate) Between [start] And [end]) AND
 ((dbo_NCR_RootCauses.Description) Not Like "*internal*") AND

(plus other tables that have "Description" as a field name)

It looks like you have an extra double quote in the Switch function:

<snip>
Switch (
        dbo_NCR_RootCauses.Description LIKE "Carrier*"    , "1-Carrier", 
        dbo_NCR_RootCauses.Description LIKE "Shipper*"    , "2-Shipper", 
        dbo_NCR_RootCauses.Description LIKE "Consignee*"  , "3-Consignee", 
        dbo_NCR_RootCauses.Description LIKE "Unforeseen*" , "4-Unforeseen",
        TRUE                                              , "5-Other")"  <<-- last double quote should be deleted 

AS [Fault Hierarchy]<snip>

Still an error?
Try creating another query, but without the WHERE clause. Execute it. Does it error?
If yes, start deleting fields in the SELECT clause until the error is gone. Fix the error.
If No, add one condition/criteria to the WHERE clause. Execute it. Error?
Add one condition until you get an error.
Fix the error.

[rfigueroa1976]

IT WAS THE DOUBLE QUOTE! Thank god this finally worked! Thank you all SOOOOOOOO MUCH for your help and patience with this. I truly appreciate you all!

[ssanfu]

Happy you got it working....


Ready to mark this solved?