> Gentlemen and Ladies
>
>         Below is the complete query that I have convert over from HQL to SQL
> and I am getting one last error which is
>
>         Server: Msg 170, Level 15, State 1 Line116
>
>         Line 52: Incorrect Syntax Near ')'
>
>         I am running lines 65 thru 115 when I get this error -- HELP -- I am
> going blind looking for the error
>
> Mark
>
> {========================================================}
>
> Drop View T0;
>
> Create View T0 as
>
> Select StoreID,
>        VendorNumber,
>        ItemNumber,
>        PLUNumber
>
> From   Item
>
> Where  StoreID = 151995
>   and  DepartmentCode = (1)
>
> /* -------------------------------------------------------------------------
> -- */
>
> Drop View T1A;
>
> Create View T1A as
>
> Select T1A.StoreID,
>        T1A.VendorNumber,
>        T1A.ItemNumber,
>        T1A.PLUNumber,
>        Min(LandedCost)                             AS  MinLandedCost,
>        Min(FeeOperating + FeeService + FeeFreight) AS  LowFees,
>        Max(LandedCost)                             AS  MaxLandedCost,
>        Max(FeeOperating + FeeService + FeeFreight) AS  HighFees,
>        Max(IDate)                                  AS  LastInvoiceDate,
>        Sum(PurchaseQty)                            AS  TotalPurchaseQty,
>        Sum(PurchaseQty * CasePack * SRP)           AS  ExtendedRetail,
>        Count(*)                                    AS  NumOfInv
>
> From Invoice as T1A, T0
>
> Where    T1A.StoreID      = T0.StoreID
>   and    T1A.VendorNumber = T0.VendorNumber
>   and    T1A.ItemNumber   = T0.ItemNumber
>   and    T1A.PLUNumber    = T0.PLUNumber
>
> Group By T1A.StoreID,
>          T1A.VendorNumber,
>          T1A.ItemNumber,
>          T1A.PLUNumber
>
> /* -------------------------------------------------------------------------
> -- */
>
> Drop View T1;
>
> Create View T1 as
>
> Select *,
>        ( MaxLandedCost - MinLandedCost)                        AS
> DiffCentsCost,
>        ( HighFees      - LowFees)                              AS
> DiffCentsFees,
>        ((MaxLandedCost - MinLandedCost) / ISNULL(NULLIF(MinLandedCost,
> 0.00), 1.00 )) * 100 AS DiffPercentCost,
>        ((HighFees      - LowFees)       / ISNULL(NULLIF(LowFees,
> 0.00), 1.00 )) * 100 AS DiffPercentFees
>
> From T1A
>
> /* -------------------------------------------------------------------------
> -- */
>
> Drop View T2;
>
> Create View T2 as
>
> Select   StoreID,
>          VendorNumber,
>          ItemNumber,
>          PLUNumber,
>          Brand,
>          [Description],
>          BaseCasePack,
>          SizeQuantity,
>          AvgWeeklyMovement,
>          InventoryTurns,
>          ( ( CurrentMargin / ( CASE ( CurrentMargin ) WHEN 100 THEN 1 ELSE
> ( 100 - CurrentMargin ) END ) ) * InventoryTurns ) AS ROII
>
> From (
>         Select  T2.StoreID,
>           T2.VendorNumber,
>           T2.ItemNumber,
>           T2.PLUNumber,
>           I3.Brand,
>           I3.[Description],
>           I3.BaseCasePack,
>           I3.SizeQuantity,
>           T2.AvgWeeklyMovement,
>           ( ( T2.AvgWeeklyMovement * 52 ) / ( CASE ( I3.BaseCasePack ) WHEN
> 0 THEN 1 ELSE I3.BaseCasePack END ) ) AS InventoryTurns,
>          I3.CurrentMargin,
>           I3.CurrentPrice
>
>         from    Item AS I3, (
>     Select  StoreID,
>              VendorNumber,
>              ItemNumber,
>              PLUNumber,
>              ( sum( POSMovement ) / ( ( DATEDIFF( day, min(TDate),
> max(TDate) ) + 1 ) / 7 ) ) AS AvgWeeklyMovement
>
>     From    Movement
>
>     Where   StoreID  = 151995
>       and   TDate   >= DATEADD( day, - ( 3 * 365 ), CurDate )  --  '- ( 7 *
> 26 )'
>       and   TDate   <= CurDate
>
>      Group By StoreID,
>             VendorNumber,
>             ItemNumber,
>             PLUNumber
>                             ) AS
>    --------  ERROR IS ON THIS LINE ----------
>
> Where    I3.StoreID      = T2.StoreID
>   and    I3.VendorNumber = T2.VendorNumber
>   and    I3.ItemNum      = T2.ItemNumber
>   and    I3.PLUNum       = T2.PLUNumber
>      )
>
> /* -------------------------------------------------------------------------
> -- */
>
> Select   T1.StoreID,
>          T1.VendorNumber,
>          T1.ItemNumber,
>          T1.PLUNumber,
>          T2.Brand,
>          T2.[Description],
>          T2.BaseCasePack,
>          T2.SizeQuantity,
>          T2.AvgWeeklyMovement,
>          T2.InventoryTurns,
>          T2.ROII,
>          T1.MinLandedCost,
>          T1.LowFees,
>          T1.MaxLandedCost,
>          T1.HighFees,
>          T1.LastInvoiceDate,
>          T1.TotalPurchaseQty,
>          T1.ExtendedRetail,
>          T1.NumOfInv,
>          T1.DiffCentsCost,
>          T1.DiffCentsFees,
>          T1.DiffPercentCost,
>          T1.DiffPercentFees
>
> From T1 T1, T2 T2
>
> Where    T2.StoreID      = T1.StoreID
>   and    T2.VendorNumber = T1.VendorNumber
>   and    T2.ItemNumber   = T1.ItemNumber
>   and    T2.PLUNumber    = T1.PLUNumber
>
> Order By T1.DiffPercentCost;
>
>

"Jeff Williams" <jeff.williams@nospam.com> wrote in message
news:%23ihZ7loqFHA.4012@TK2MSFTNGP12.phx.gbl...
>        Group By StoreID,
>               VendorNumber,
>               ItemNumber,
>               PLUNumber
>                               ) AS
>
> ???? What is the table alias name ????
>
>   Where    I3.StoreID      = T2.StoreID
>     and    I3.VendorNumber = T2.VendorNumber
>     and    I3.ItemNum      = T2.ItemNumber
>     and    I3.PLUNum       = T2.PLUNumber
>
>
> Mark Moss wrote:
> > Gentlemen and Ladies
> >
> >         Below is the complete query that I have convert over from HQL to
SQL
> > and I am getting one last error which is
> >
> >         Server: Msg 170, Level 15, State 1 Line116
> >
> >         Line 52: Incorrect Syntax Near ')'
> >
> >         I am running lines 65 thru 115 when I get this error -- HELP --
I am
> > going blind looking for the error
> >
> > Mark
> >
> > {========================================================}
> >
> > Drop View T0;
> >
> > Create View T0 as
> >
> > Select StoreID,
> >        VendorNumber,
> >        ItemNumber,
> >        PLUNumber
> >
> > From   Item
> >
> > Where  StoreID = 151995
> >   and  DepartmentCode = (1)
> >
> >
/* -------------------------------------------------------------------------
> > -- */
> >
> > Drop View T1A;
> >
> > Create View T1A as
> >
> > Select T1A.StoreID,
> >        T1A.VendorNumber,
> >        T1A.ItemNumber,
> >        T1A.PLUNumber,
> >        Min(LandedCost)                             AS  MinLandedCost,
> >        Min(FeeOperating + FeeService + FeeFreight) AS  LowFees,
> >        Max(LandedCost)                             AS  MaxLandedCost,
> >        Max(FeeOperating + FeeService + FeeFreight) AS  HighFees,
> >        Max(IDate)                                  AS  LastInvoiceDate,
> >        Sum(PurchaseQty)                            AS  TotalPurchaseQty,
> >        Sum(PurchaseQty * CasePack * SRP)           AS  ExtendedRetail,
> >        Count(*)                                    AS  NumOfInv
> >
> > From Invoice as T1A, T0
> >
> > Where    T1A.StoreID      = T0.StoreID
> >   and    T1A.VendorNumber = T0.VendorNumber
> >   and    T1A.ItemNumber   = T0.ItemNumber
> >   and    T1A.PLUNumber    = T0.PLUNumber
> >
> > Group By T1A.StoreID,
> >          T1A.VendorNumber,
> >          T1A.ItemNumber,
> >          T1A.PLUNumber
> >
> >
/* -------------------------------------------------------------------------
> > -- */
> >
> > Drop View T1;
> >
> > Create View T1 as
> >
> > Select *,
> >        ( MaxLandedCost - MinLandedCost)                        AS
> > DiffCentsCost,
> >        ( HighFees      - LowFees)                              AS
> > DiffCentsFees,
> >        ((MaxLandedCost - MinLandedCost) / ISNULL(NULLIF(MinLandedCost,
> > 0.00), 1.00 )) * 100 AS DiffPercentCost,
> >        ((HighFees      - LowFees)       / ISNULL(NULLIF(LowFees,
> > 0.00), 1.00 )) * 100 AS DiffPercentFees
> >
> > From T1A
> >
> >
/* -------------------------------------------------------------------------
> > -- */
> >
> > Drop View T2;
> >
> > Create View T2 as
> >
> > Select   StoreID,
> >          VendorNumber,
> >          ItemNumber,
> >          PLUNumber,
> >          Brand,
> >          [Description],
> >          BaseCasePack,
> >          SizeQuantity,
> >          AvgWeeklyMovement,
> >          InventoryTurns,
> >          ( ( CurrentMargin / ( CASE ( CurrentMargin ) WHEN 100 THEN 1
ELSE
> > ( 100 - CurrentMargin ) END ) ) * InventoryTurns ) AS ROII
> >
> > From (
> >         Select  T2.StoreID,
> >           T2.VendorNumber,
> >           T2.ItemNumber,
> >           T2.PLUNumber,
> >           I3.Brand,
> >           I3.[Description],
> >           I3.BaseCasePack,
> >           I3.SizeQuantity,
> >           T2.AvgWeeklyMovement,
> >           ( ( T2.AvgWeeklyMovement * 52 ) / ( CASE ( I3.BaseCasePack )
WHEN
> > 0 THEN 1 ELSE I3.BaseCasePack END ) ) AS InventoryTurns,
> >          I3.CurrentMargin,
> >           I3.CurrentPrice
> >
> >         from    Item AS I3, (
> >     Select  StoreID,
> >              VendorNumber,
> >              ItemNumber,
> >              PLUNumber,
> >              ( sum( POSMovement ) / ( ( DATEDIFF( day, min(TDate),
> > max(TDate) ) + 1 ) / 7 ) ) AS AvgWeeklyMovement
> >
> >     From    Movement
> >
> >     Where   StoreID  = 151995
> >       and   TDate   >= DATEADD( day, - ( 3 * 365 ), CurDate )  --  '-
( 7 *
> > 26 )'
> >       and   TDate   <= CurDate
> >
> >      Group By StoreID,
> >             VendorNumber,
> >             ItemNumber,
> >             PLUNumber
> >                             ) AS
> >    --------  ERROR IS ON THIS LINE ----------
> >
> > Where    I3.StoreID      = T2.StoreID
> >   and    I3.VendorNumber = T2.VendorNumber
> >   and    I3.ItemNum      = T2.ItemNumber
> >   and    I3.PLUNum       = T2.PLUNumber
> >      )
> >
> >
/* -------------------------------------------------------------------------
> > -- */
> >
> > Select   T1.StoreID,
> >          T1.VendorNumber,
> >          T1.ItemNumber,
> >          T1.PLUNumber,
> >          T2.Brand,
> >          T2.[Description],
> >          T2.BaseCasePack,
> >          T2.SizeQuantity,
> >          T2.AvgWeeklyMovement,
> >          T2.InventoryTurns,
> >          T2.ROII,
> >          T1.MinLandedCost,
> >          T1.LowFees,
> >          T1.MaxLandedCost,
> >          T1.HighFees,
> >          T1.LastInvoiceDate,
> >          T1.TotalPurchaseQty,
> >          T1.ExtendedRetail,
> >          T1.NumOfInv,
> >          T1.DiffCentsCost,
> >          T1.DiffCentsFees,
> >          T1.DiffPercentCost,
> >          T1.DiffPercentFees
> >
> > From T1 T1, T2 T2
> >
> > Where    T2.StoreID      = T1.StoreID
> >   and    T2.VendorNumber = T1.VendorNumber
> >   and    T2.ItemNumber   = T1.ItemNumber
> >   and    T2.PLUNumber    = T1.PLUNumber
> >
> > Order By T1.DiffPercentCost;
> >
> >

>Jeff
>
>        Sorry about that, I must have deleted it when  I added the
>'--------  ERROR IS ON THIS LINE ----------'
>        It should and does in my code read as below
>
>        Group By StoreID,
>               VendorNumber,
>               ItemNumber,
>               PLUNumber
>                               ) AS T2
>
>
>       Where    I3.StoreID      = T2.StoreID
>           and    I3.VendorNumber = T2.VendorNumber
>           and    I3.ItemNum      = T2.ItemNumber
>           and    I3.PLUNum       = T2.PLUNumber
>
>        Order By T1.DiffPercentCost;
>
>Thank you in advance for any help that you can give me.