Semi Colon issue - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Semi Colon issue

Author

Topic

pvong
Yak Posting Veteran

58 Posts

Posted - 2015-02-20 : 11:38:11

What is wrong with my Semi Colon? I'm getting an error 102 around the ; but if I take it out, I'm getting the error that I need a colon. The colon is right before the WITH statement.

Select * INTO TradesMoxyImport
From

  ;WITH ClientsTable AS (SELECT        ClientID, Autex
                                                  FROM            Clients
                                                  WHERE        (ActiveAcct = 'Active') AND (Directed = 'no') AND (Taxable LIKE '%' + '%')), AxysPal AS
    (SELECT        Autex, Quantity, PctAssets
      FROM            AxysPals
      WHERE        (SecType = 'csus') AND (Ticker = 'ace'))
    SELECT        ClientsTable_1.Autex, ClientsOnePercent.SortOrder, CASE WHEN ROUND(ClientsOnePercent.PctEqty * AxysAUM.Total * CONVERT(DECIMAL(3, 2), 5) / 100 / 113.52, 
                              0) > ISNULL(AxysPal_1.Quantity, 0) THEN ROUND(ClientsOnePercent.PctEqty * ROUND(AxysAUM.Total, 0) * CONVERT(DECIMAL(3, 2), 5) 
                              / 100 / 113.52 - ISNULL(AxysPal_1.Quantity, 0), 0) END AS BuyQty, CASE WHEN ROUND(ClientsOnePercent.PctEqty * AxysAUM.Total * CONVERT(DECIMAL(3, 2), 5) 
                              / 100 / 113.52, 0) < ISNULL(AxysPal_1.Quantity, 0) THEN ROUND(ISNULL(AxysPal_1.Quantity, 0) 
                              - ClientsOnePercent.PctEqty * AxysAUM.Total * CONVERT(DECIMAL(3, 2), 5) / 100 / 113.52, 0) END AS SellQty
     FROM            ClientsOnePercent INNER JOIN
                              ClientsTable AS ClientsTable_1 ON ClientsOnePercent.ClientID = ClientsTable_1.ClientID INNER JOIN
                              AxysAUM ON ClientsTable_1.Autex = AxysAUM.Autex LEFT OUTER JOIN
                              AxysPal AS AxysPal_1 ON ClientsOnePercent.Autex = AxysPal_1.Autex
     WHERE        (ClientsOnePercent.OnePctGroupID = 1) AND (ClientsOnePercent.BuyRule IS NULL) AND (ClientsOnePercent.SellRule IS NULL)
     ORDER BY ClientsOnePercent.SortOrder

------------------------------
Using VS2010 / Learning in VB.Net / Win2008 R2 / SQL 2008 R2
Be kind to the newbies because you were once there.

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2015-02-20 : 12:21:18

You didn't finish the Select statement.

pvong
Yak Posting Veteran

58 Posts

Posted - 2015-02-20 : 13:10:42

quote:
Originally posted by gbritton

You didn't finish the Select statement.

I'm sorry but I'm a newbie at this. What am I missing? Basically, I just want to create a new table from the Select statement starting with the WITH.

------------------------------
Using VS2010 / Learning in VB.Net / Win2008 R2 / SQL 2008 R2
Be kind to the newbies because you were once there.

pvong
Yak Posting Veteran

58 Posts

Posted - 2015-02-20 : 14:40:16

Someone else answered it for me. I need to have the INTO New Table before the FROM. My mistake.


;WITH ClientsTable
     AS (SELECT ClientID,
                Autex
         FROM   Clients
         WHERE  ( ActiveAcct = 'Active' )
                AND ( Directed = 'no' )
                AND ( Taxable LIKE '%' + '%' )),
     AxysPal
     AS (SELECT Autex,
                Quantity,
                PctAssets
         FROM   AxysPals
         WHERE  ( SecType = 'csus' )
                AND ( Ticker = 'ace' )) 

SELECT ClientsTable_1.Autex,
       ClientsOnePercent.SortOrder,
       CASE
         WHEN Round(ClientsOnePercent.PctEqty * AxysAUM.Total * CONVERT(DECIMAL(3, 2), 5) / 100 / 113.52, 0) > Isnull(AxysPal_1.Quantity, 0) THEN Round(ClientsOnePercent.PctEqty * Round(AxysAUM.Total, 0) * CONVERT(DECIMAL(3, 2), 5) / 100 / 113.52 - Isnull(AxysPal_1.Quantity, 0), 0)
       END AS BuyQty,
       CASE
         WHEN Round(ClientsOnePercent.PctEqty * AxysAUM.Total * CONVERT(DECIMAL(3, 2), 5) / 100 / 113.52, 0) < Isnull(AxysPal_1.Quantity, 0) THEN Round(Isnull(AxysPal_1.Quantity, 0) - ClientsOnePercent.PctEqty * AxysAUM.Total * CONVERT(DECIMAL(3, 2), 5) / 100 / 113.52, 0)
       END AS SellQty




INTO   TradesMoxyImport



FROM   ClientsOnePercent
       INNER JOIN ClientsTable AS ClientsTable_1
               ON ClientsOnePercent.ClientID = ClientsTable_1.ClientID
       INNER JOIN AxysAUM
               ON ClientsTable_1.Autex = AxysAUM.Autex
       LEFT OUTER JOIN AxysPal AS AxysPal_1
                    ON ClientsOnePercent.Autex = AxysPal_1.Autex
WHERE  ( ClientsOnePercent.OnePctGroupID = 1 )
       AND ( ClientsOnePercent.BuyRule IS NULL )
       AND ( ClientsOnePercent.SellRule IS NULL )
 ORDER  BY ClientsOnePercent.SortOrder

------------------------------
Using VS2010 / Learning in VB.Net / Win2008 R2 / SQL 2008 R2
Be kind to the newbies because you were once there.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources