BEGIN WHILE EOF

24 Aug 2006 12:39 PM

Mike C

Hi. I'm trying to learn the syntax for doing the loops that I used so often
in Access. I have a table with products in it (dbo.ProductL3ID). I have a
view with shipments of product (dbo.vwShipments ). The view tells me how many
days elapsed between each shipment for each customer. I'm trying to loop
through the products table, run the view, and append the results to a table
(dbo.ATestTable). I'm getting an error that says "Incorrect syntax near the
keyword 'BEGIN'." Any suggestions? THANKS!!

DELETE FROM dbo.ATestTable

SELECT ProductL3ID FROM dbo.ProductL3ID WHERE dbo.ProductL3ID.ProductL3ID <
3 ORDER BY dbo.ProductL3ID.ProductL3ID

WHILE Not dbo.ProductL3ID.EOF
BEGIN

INSERT INTO dbo.ATestTable (ProductL3ID, AccountID, INVDATE,
DaysBetweenShipments, ShipDollars, ShipUnits)
SELECT   ProductL3ID, AccountID, INVDATE, DATEDIFF(dd,
                          (SELECT     MAX(X.InvDate)
                            FROM          dbo.vwShipments AS X
                            WHERE      X.ProductL3ID =
dbo.ProductL3ID.ProductL3ID AND X.InvDate < dbo.vwShipments.InvDate AND
X.[ProductL3ID] = dbo.vwShipments.ProductL3ID AND
                                                   X.[AccountID] =
dbo.vwShipments.AccountID), INVDATE) AS DaysBetweenShipments,
SUM(ShipDollars) AS ShipDollars, SUM(ShipUnits)
                      AS ShipUnits
FROM         dbo.vwShipments
WHERE ProductL3ID = dbo.ProductL3ID.ProductL3ID
GROUP BY ProductL3ID, AccountID, INVDATE
ORDER BY ProductL3ID, AccountID, INVDATE; SELECT * FROM dbo.ATestTable

END

24 Aug 2006 1:18 PM

ML

Loop? Why would you need to loop. Based on what I can understand from your
post a set-based solution is what you need.

Please post proper DDL, sample data and expected results, so that we can
give you an accurate solution.
Read this for details:
http://www.aspfaq.com/etiquette.asp?id=5006

ML

---
http://milambda.blogspot.com/

24 Aug 2006 1:18 PM

Roy Harvey

Transact-SQL, the dialect of SQL used by Microsoft SQL Server, does
not work that way. There is no .EOF syntax such as you postulate. You
can not deal with a result set one row at a time without using a
cursor. More important, I can not see anything in what you showed
that would require dealing with one row at a time in the first place.

Consider this variation:

INSERT INTO dbo.ATestTable
       (ProductL3ID, AccountID, INVDATE, DaysBetweenShipments,
        ShipDollars, ShipUnits)
SELECT ProductL3ID, AccountID, INVDATE,
       DATEDIFF(dd,
                (SELECT MAX(X.InvDate)
                   FROM dbo.vwShipments AS X
                  WHERE X.ProductL3ID = dbo.ProductL3ID.ProductL3ID
                    AND X.InvDate < dbo.vwShipments.InvDate
                    AND X.[ProductL3ID] = dbo.vwShipments.ProductL3ID
                    AND X.[AccountID] = dbo.vwShipments.AccountID),
                 INVDATE) AS DaysBetweenShipments,
       SUM(ShipDollars) AS ShipDollars,
       SUM(ShipUnits) AS ShipUnits
FROM dbo.vwShipments
WHERE ProductL3ID IN
       (SELECT ProductL3ID FROM dbo.ProductL3ID
         WHERE dbo.ProductL3ID.ProductL3ID < 3)
GROUP BY ProductL3ID, AccountID, INVDATE
ORDER BY ProductL3ID, AccountID, INVDATE;

Note the change to using IN with a subquery in the WHERE clause. No
loops, just INSERT all the rows in one command.

Hope that helps.

Roy Harvey
Beacon Falls, CT

On Thu, 24 Aug 2006 05:39:02 -0700, Mike C
<Mi***@discussions.microsoft.com> wrote:

Show quote

>Hi. I'm trying to learn the syntax for doing the loops that I used so often
>in Access. I have a table with products in it (dbo.ProductL3ID). I have a
>view with shipments of product (dbo.vwShipments ). The view tells me how many
>days elapsed between each shipment for each customer. I'm trying to loop
>through the products table, run the view, and append the results to a table
>(dbo.ATestTable). I'm getting an error that says "Incorrect syntax near the
>keyword 'BEGIN'." Any suggestions? THANKS!!
>
>DELETE FROM dbo.ATestTable
>
>SELECT ProductL3ID FROM dbo.ProductL3ID WHERE dbo.ProductL3ID.ProductL3ID <
>3 ORDER BY dbo.ProductL3ID.ProductL3ID
>
>WHILE Not dbo.ProductL3ID.EOF
>BEGIN
>
>INSERT INTO dbo.ATestTable (ProductL3ID, AccountID, INVDATE,
>DaysBetweenShipments, ShipDollars, ShipUnits)
>SELECT   ProductL3ID, AccountID, INVDATE, DATEDIFF(dd,
>                          (SELECT     MAX(X.InvDate)
>                            FROM          dbo.vwShipments AS X
>                            WHERE      X.ProductL3ID =
>dbo.ProductL3ID.ProductL3ID AND X.InvDate < dbo.vwShipments.InvDate AND
>X.[ProductL3ID] = dbo.vwShipments.ProductL3ID AND
>                                                   X.[AccountID] =
>dbo.vwShipments.AccountID), INVDATE) AS DaysBetweenShipments,
>SUM(ShipDollars) AS ShipDollars, SUM(ShipUnits)
>                      AS ShipUnits
>FROM         dbo.vwShipments
>WHERE ProductL3ID = dbo.ProductL3ID.ProductL3ID
>GROUP BY ProductL3ID, AccountID, INVDATE
>ORDER BY ProductL3ID, AccountID, INVDATE; SELECT * FROM dbo.ATestTable
>
>END

24 Aug 2006 1:20 PM

Tracy McKibben

Mike C wrote:
Show quote

> Hi. I'm trying to learn the syntax for doing the loops that I used so often
> in Access. I have a table with products in it (dbo.ProductL3ID). I have a
> view with shipments of product (dbo.vwShipments ). The view tells me how many
> days elapsed between each shipment for each customer. I'm trying to loop
> through the products table, run the view, and append the results to a table
> (dbo.ATestTable). I'm getting an error that says "Incorrect syntax near the
> keyword 'BEGIN'." Any suggestions? THANKS!!
>
> DELETE FROM dbo.ATestTable
>
> SELECT ProductL3ID FROM dbo.ProductL3ID WHERE dbo.ProductL3ID.ProductL3ID <
> 3 ORDER BY dbo.ProductL3ID.ProductL3ID
>
> WHILE Not dbo.ProductL3ID.EOF
> BEGIN
>
> INSERT INTO dbo.ATestTable (ProductL3ID, AccountID, INVDATE,
> DaysBetweenShipments, ShipDollars, ShipUnits)
> SELECT   ProductL3ID, AccountID, INVDATE, DATEDIFF(dd,
>                           (SELECT     MAX(X.InvDate)
>                             FROM          dbo.vwShipments AS X
>                             WHERE      X.ProductL3ID =
> dbo.ProductL3ID.ProductL3ID AND X.InvDate < dbo.vwShipments.InvDate AND
> X.[ProductL3ID] = dbo.vwShipments.ProductL3ID AND
>                                                    X.[AccountID] =
> dbo.vwShipments.AccountID), INVDATE) AS DaysBetweenShipments,
> SUM(ShipDollars) AS ShipDollars, SUM(ShipUnits)
>                       AS ShipUnits
> FROM         dbo.vwShipments
> WHERE ProductL3ID = dbo.ProductL3ID.ProductL3ID
> GROUP BY ProductL3ID, AccountID, INVDATE
> ORDER BY ProductL3ID, AccountID, INVDATE; SELECT * FROM dbo.ATestTable
>
> END

Uggghhh...   First, lose the notion of "looping", that is the absolute
worst method of processing a data set.

That said, all you are attempting to do is insert records from your view
into the table ATestTable, where ProductL3ID < 3. Knowing that, just
tell SQL what you want it to do:

INSERT INTO dbo.ATestTable
    (
       ProductL3ID,
       AccountID,
       INVDATE,
       DaysBetweenShipments,
       ShipDollars,
       ShipUnits
    )
SELECT
    ProductL3ID,
    AccountID,
    INVDATE,
    DATEDIFF(dd,
       (
          SELECT MAX(X.InvDate)
          FROM dbo.vwShipments AS X
          WHERE X.ProductL3ID = dbo.ProductL3ID.ProductL3ID
             AND X.InvDate < dbo.vwShipments.InvDate
             AND X.[ProductL3ID] = dbo.vwShipments.ProductL3ID
             AND X.[AccountID] = dbo.vwShipments.AccountID), INVDATE
       ) AS DaysBetweenShipments,
    SUM(ShipDollars) AS ShipDollars,
    SUM(ShipUnits) AS ShipUnits
FROM         dbo.vwShipments
WHERE ProductL3ID < 3
GROUP BY ProductL3ID, AccountID, INVDATE
ORDER BY ProductL3ID, AccountID, INVDATE

--
Tracy McKibben
MCDBA
http://www.realsqlguy.com

24 Aug 2006 2:03 PM

Mike C

"Thank you sir, may I have another."

Thanks everyone for the suggestions. I'll give them a try.

Show quote

"Tracy McKibben" wrote:

> Mike C wrote:
> > Hi. I'm trying to learn the syntax for doing the loops that I used so often
> > in Access. I have a table with products in it (dbo.ProductL3ID). I have a
> > view with shipments of product (dbo.vwShipments ). The view tells me how many
> > days elapsed between each shipment for each customer. I'm trying to loop
> > through the products table, run the view, and append the results to a table
> > (dbo.ATestTable). I'm getting an error that says "Incorrect syntax near the
> > keyword 'BEGIN'." Any suggestions? THANKS!!
> >
> > DELETE FROM dbo.ATestTable
> >
> > SELECT ProductL3ID FROM dbo.ProductL3ID WHERE dbo.ProductL3ID.ProductL3ID <
> > 3 ORDER BY dbo.ProductL3ID.ProductL3ID
> >
> > WHILE Not dbo.ProductL3ID.EOF
> > BEGIN
> >
> > INSERT INTO dbo.ATestTable (ProductL3ID, AccountID, INVDATE,
> > DaysBetweenShipments, ShipDollars, ShipUnits)
> > SELECT   ProductL3ID, AccountID, INVDATE, DATEDIFF(dd,
> >                           (SELECT     MAX(X.InvDate)
> >                             FROM          dbo.vwShipments AS X
> >                             WHERE      X.ProductL3ID =
> > dbo.ProductL3ID.ProductL3ID AND X.InvDate < dbo.vwShipments.InvDate AND
> > X.[ProductL3ID] = dbo.vwShipments.ProductL3ID AND
> >                                                    X.[AccountID] =
> > dbo.vwShipments.AccountID), INVDATE) AS DaysBetweenShipments,
> > SUM(ShipDollars) AS ShipDollars, SUM(ShipUnits)
> >                       AS ShipUnits
> > FROM         dbo.vwShipments
> > WHERE ProductL3ID = dbo.ProductL3ID.ProductL3ID
> > GROUP BY ProductL3ID, AccountID, INVDATE
> > ORDER BY ProductL3ID, AccountID, INVDATE; SELECT * FROM dbo.ATestTable
> >
> > END
>
>
> Uggghhh...   First, lose the notion of "looping", that is the absolute
> worst method of processing a data set.
>
> That said, all you are attempting to do is insert records from your view
> into the table ATestTable, where ProductL3ID < 3. Knowing that, just
> tell SQL what you want it to do:
>
> INSERT INTO dbo.ATestTable
>     (
>        ProductL3ID,
>        AccountID,
>        INVDATE,
>        DaysBetweenShipments,
>        ShipDollars,
>        ShipUnits
>     )
> SELECT
>     ProductL3ID,
>     AccountID,
>     INVDATE,
>     DATEDIFF(dd,
>        (
>           SELECT MAX(X.InvDate)
>           FROM dbo.vwShipments AS X
>           WHERE X.ProductL3ID = dbo.ProductL3ID.ProductL3ID
>              AND X.InvDate < dbo.vwShipments.InvDate
>              AND X.[ProductL3ID] = dbo.vwShipments.ProductL3ID
>              AND X.[AccountID] = dbo.vwShipments.AccountID), INVDATE
>        ) AS DaysBetweenShipments,
>     SUM(ShipDollars) AS ShipDollars,
>     SUM(ShipUnits) AS ShipUnits
> FROM         dbo.vwShipments
> WHERE ProductL3ID < 3
> GROUP BY ProductL3ID, AccountID, INVDATE
> ORDER BY ProductL3ID, AccountID, INVDATE
>
>
> --
> Tracy McKibben
> MCDBA
> http://www.realsqlguy.com
>

24 Aug 2006 2:09 PM

Tracy McKibben

Mike C wrote:

> "Thank you sir, may I have another."
>
> Thanks everyone for the suggestions. I'll give them a try.
>

Sorry, didn't mean to sound hostile... Your "mistake" is a common one.
Learning to think about problems in terms of "sets" instead of
"procedures" is probably the hardest part about learning to write good
SQL code...

--
Tracy McKibben
MCDBA
http://www.realsqlguy.com

24 Aug 2006 3:28 PM

SQL Menace

Yes,

now we just have to wait for the inevitable Celko-attack that will come
later......

Denis the SQL Menace
http://sqlservercode.blogspot.com/

Tracy McKibben wrote:
Show quote

> Mike C wrote:
> > "Thank you sir, may I have another."
> >
> > Thanks everyone for the suggestions. I'll give them a try.
> >
>
> Sorry, didn't mean to sound hostile... Your "mistake" is a common one.
> Learning to think about problems in terms of "sets" instead of
> "procedures" is probably the hardest part about learning to write good
> SQL code...
>
>
> --
> Tracy McKibben
> MCDBA
> http://www.realsqlguy.com