August 10, 2009 at 6:41 am
This is the code that ran in access, I replaced the Trim with ltrim and I've tried to replace =1 with ='1', = ['1'], and even = [1] and I've had no luck.
code
INSERT INTO tblShipments ( [AP SIC], [Load ID], [Shipment Id], [LMS Carrier Id], [Conf Mode], [SF Name], [SF Address], [SF City Name], [SF State/Province], [SF Postal/Zip Code], [SF Country], [ST Name], [ST Address], [ST City Name], [ST State/Province], [ST Postal/Zip Code], [ST Country], [Wgt Gross Planned], [Qty Planned], [Pickup Stop Seq], [Delivery Stop Seq], [Start Date], [Start Time], [Start TS], [End Date], [End Time], [End TS], [Pln Loading Date], [Pln Loading time], [Pln Unloading Date], [Pln Unloading Time], [SH Arv PU Loc TS], [SH Arv D Loc Date], [SH Arv D Loc TS], [Transport Means Grp], [Load Distance], [Out of Route Distance], [Total Miles], [SH Arv PU Loc Date], [Pln Unloading TS], [Depart PU EDI], [Arv D EDI], [LoadType], [Actual Ship Date], [SH Est AP LH Amt], [SH Est AR LH Amt], [SH Approved AP Amt], [SH Approved AR Amt], [FrctStndRte 1/0], [TCM Route Id], [SH Est Acs Amt], [SH Aprv Acs Amt] )
SELECT [T Shipments].[AP SIC], [T Shipments].[Load Id], [T Shipments].[Shipment Id], ltrim([T Shipments].[LMS Carrier Id]) AS Expr1, ltrim([T Shipments].[Conf Mode]) AS [Conf Mode], ltrim([T Shipments].[SF Name]) AS [SF Name], ltrim([T Shipments].[SF Address]) AS [SF Address], ltrim([T Shipments].[SF City]) AS [SF City], ltrim([T Shipments].[SF State]) AS [SF State], ltrim([T Shipments].[SF Zip]) AS [SF Zip], ltrim([T Shipments].[SF Country]) AS [SF Country], ltrim([T Shipments].[ST Name]) AS [ST Name], ltrim([T Shipments].[ST Address]) AS [ST Address], ltrim([T Shipments].[ST City]) AS [ST City], ltrim([T Shipments].[ST State]) AS [ST State], ltrim([T Shipments].[ST Zip]) AS [ST Zip], ltrim([T Shipments].[ST Country]) AS [ST Country], [T Shipments].[Wgt Gross Planned], [T Shipments].[Qty Planned], ltrim([T Shipments].[Pickup Stop Seq]) AS [Pickup Stop Seq], ltrim([T Shipments].[Delivery Stop Seq]) AS [Delivery Stop Seq], [T Shipments].[Start Date Ld], [T Shipments].[Start Time Ld], [T Shipments].[Start TS Ld], [T Shipments].[End Date Ld], [T Shipments].[End Time Ld], [T Shipments].[End TS Ld], [T Shipments].[Pln Loading Date], [T Shipments].[Pln Loading Time], [T Shipments].[Pln Unloading Date], [T Shipments].[Pln Unloading Time], [T Shipments].[SH Arv PU Loc TS], [T Shipments].[SH Arv D Loc Date], [T Shipments].[SH Arv D Loc TS], [T Loads].[TMG], [T Loads].[Load Distance], [T Loads].[Out of Route Distance], [T Loads].[Load Distance]+[T Loads].[Out of Route Distance] AS [Total Miles], [T Shipments].[SH Arv PU Loc Date], [T Shipments].[Pln Unloading TS], IIf([T Shipments].[SH PU Rcvd EDI 0/1]=1,[T Shipments].[SH PU Rcvd EDI 0/1],IIf([T Shipments].[SH PU Rcvd Web 0/1]=1,[T Shipments].[SH PU Rcvd Web 0/1],0)) AS [Depart PU EDI], IIf([T Shipments].[SH Arv D Loc EDI 0/1]=1,[T Shipments].[SH Arv D Loc EDI 0/1],IIf([T Shipments].[SH Arv D Loc Web 0/1]=1,[T Shipments].[SH Arv D Loc Web 0/1],0)) AS [Arv D EDI], ltrim([T Loads].[Load Type (VOR)]) AS [Load Type (VOR)], [T Loads].[Actual Ship Date], [T Shipments].[SH Est AP LH Amt], [T Shipments].[SH Est AR LH Amt], [T Shipments].[SH Approved AP Amt], [T Shipments].[SH Billed AR Amt], [T Loads].[FrctStndRte], ltrim([T Loads].[TCM Route Id]) AS [TCM Route Id], [T Shipments].[SH Est Acs Amt], [T Shipments].[SH Aprv Acs Amt]
FROM [T Shipments] INNER JOIN [T Loads] ON [T Shipments].[Load Id] = [T Loads].[Load Id]
GROUP BY [T Shipments].[AP SIC], [T Shipments].[Load Id], [T Shipments].[Shipment Id], ltrim([T Shipments].[LMS Carrier Id]), ltrim([T Shipments].[Conf Mode]), ltrim([T Shipments].[SF Name]), ltrim([T Shipments].[SF Address]), ltrim([T Shipments].[SF City]), ltrim([T Shipments].[SF State]), ltrim([T Shipments].[SF Zip]), ltrim([T Shipments].[SF Country]), ltrim([T Shipments].[ST Name]), ltrim([T Shipments].[ST Address]), ltrim([T Shipments].[ST City]), ltrim([T Shipments].[ST State]), ltrim([T Shipments].[ST Zip]), ltrim([T Shipments].[ST Country]), [T Shipments].[Wgt Gross Planned], [T Shipments].[Qty Planned], ltrim([T Shipments].[Pickup Stop Seq]), ltrim([T Shipments].[Delivery Stop Seq]), [T Shipments].[Start Date Ld], [T Shipments].[Start Time Ld], [T Shipments].[Start TS Ld], [T Shipments].[End Date Ld], [T Shipments].[End Time Ld], [T Shipments].[End TS Ld], [T Shipments].[Pln Loading Date], [T Shipments].[Pln Loading Time], [T Shipments].[Pln Unloading Date], [T Shipments].[Pln Unloading Time], [T Shipments].[SH Arv PU Loc TS], [T Shipments].[SH Arv D Loc Date], [T Shipments].[SH Arv D Loc TS], [T Loads].[TMG], [T Loads].[Load Distance], [T Loads].[Out of Route Distance], [T Loads].[Load Distance]+[T Loads].[Out of Route Distance], [T Shipments].[SH Arv PU Loc Date], [T Shipments].[Pln Unloading TS], IIf([T Shipments].[SH PU Rcvd EDI 0/1]=1,[T Shipments].[SH PU Rcvd EDI 0/1],IIf([T Shipments].[SH PU Rcvd Web 0/1]=1,[T Shipments].[SH PU Rcvd Web 0/1],0)), IIf([T Shipments].[SH Arv D Loc EDI 0/1]=1,[T Shipments].[SH Arv D Loc EDI 0/1],IIf([T Shipments].[SH Arv D Loc Web 0/1]=1,[T Shipments].[SH Arv D Loc Web 0/1],0)), ltrim([T Loads].[Load Type (VOR)]), [T Loads].[Actual Ship Date], [T Shipments].[SH Est AP LH Amt], [T Shipments].[SH Est AR LH Amt], [T Shipments].[SH Approved AP Amt], [T Shipments].[SH Billed AR Amt], [T Loads].[FrctStndRte], ltrim([T Loads].[TCM Route Id]), [T Shipments].[SH Est Acs Amt], [T Shipments].[SH Aprv Acs Amt];
error message
Msg 102, Level 15, State 1, Line 2
Incorrect syntax near '='.
August 11, 2009 at 3:51 am
SQL does not have "iif ". Even if there was you cannot use it in a select statement.
"Keep Trying"
August 11, 2009 at 4:23 am
....IIf([T Shipments].[SH PU Rcvd Web 0/1]=1,[T Shipments].[SH PU Rcvd Web 0/1],0)) AS [Depart PU EDI], IIf([T Shipments].[SH Arv D Loc EDI 0/1]=1....
Look up CASE statement IN BOL
untested code:
CASE [T Shipments].[SH Arv D Loc EDI 0/1] WHEN 1 THEN [T Shipments].[SH Arv D Loc EDI 0/1]
ELSE
CASE [T Shipments].[SH Arv D Loc Web 0/1] WHEN 1 THEN [T Shipments].[SH Arv D Loc Web 0/1]
ELSE 0
END
________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day
August 11, 2009 at 6:31 am
I replaced all of the IIf with CASE WHEN, but I'm still getting the error message : Msg 102, Level 15, State 1, Line 95
Incorrect syntax near '='.
August 11, 2009 at 6:51 am
can we see the new code ?
________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day
August 11, 2009 at 8:13 am
It was the same sample earlier that I replaced the IIf with CASE WHEN, I worked through the = problem and now I'm just trying to correct this error message Msg 102, Level 15, State 1, Line 96
Incorrect syntax near ','.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply