September 17, 2013 at 3:36 pm
I'm just learning SQL and not very good at it yet. Trying to self teach so it's been slow. So I tried to duplicate this sql from our old database to our new one and I get this error. It works fine in the old but not the new. Any help would be appreciated. Thanks!
Msg 156, Level 15, State 1, Line 22
Incorrect syntax near the keyword 'FROM'.
Msg 156, Level 15, State 1, Line 36
Incorrect syntax near the keyword 'AND'.
SELECT
ID,ShipTo,ShipToID,ShipToCompany,BillTo,BillToCompany,PaymentType,DateCreated,EmployeeID,Type,Frequency,CCPartial
FROM
Aptify.dbo.vwStandingOrders
WHERE --- we'll only study active future fulfillments
(Status LIKE 'Active' AND
(DateExpires > GETDATE() OR DateExpires < '1/2/1901')
)
AND
ID IN -- find only stabding orders for dues products
(SELECT StandingOrderID FROM vwStandingOrProd sop
inner join vwProducts prod on sop.productID=prod.id
AND prod.categoryID IN(1,8,9,10,11)
)
AND PaymentTypeID IN (11,12,18,19,20,21,22,26,27,28,29)
and ( (isNull( PONumber, '' ) = '' ) OR (isNull( Terms , '' ) = '' ) )
ORDER BY Aptify.dbo.vwStandingOrders.[DateCreated] DESC
FROM
Aptify.dbo.vwStandingOrders
WHERE --- we'll only study active future fulfillments
(Status LIKE 'Active' AND
(DateExpires > GETDATE() OR DateExpires < '1/2/1901')
)
AND
ID IN -- find only stabding orders for dues products
(SELECT StandingOrderID FROM vwStandingOrProd sop
inner join vwProducts prod on sop.productID=prod.id
AND prod.categoryID IN(1,8,9,10,11)
)
AND PaymentTypeID IN (11,12,18,19,20,21,22,26,27,28,29)
and ( (isNull( PONumber, '' ) = '' ) OR (isNull( Terms , '' ) = '' ) )
ORDER BY Aptify.dbo.vwStandingOrders.[DateCreated] DESC
September 18, 2013 at 9:14 am
It looks like you have pasted a portion of your query a second time. Look where the first ORDER BY is, you then have FROM ... the same sql as before it.
Total shot in the dark but maybe this is what you are looking for. BTW, I ran this through a formatter so it is legible.
SELECT ID
,ShipTo
,ShipToID
,ShipToCompany
,BillTo
,BillToCompany
,PaymentType
,DateCreated
,EmployeeID
,Type
,Frequency
,CCPartial
FROM Aptify.dbo.vwStandingOrders
WHERE --- we'll only study active future fulfillments
(
STATUS LIKE 'Active'
AND (
DateExpires > GETDATE()
OR DateExpires < '1/2/1901'
)
)
AND ID IN -- find only stabding orders for dues products
(
SELECT StandingOrderID
FROM vwStandingOrProd sop
INNER JOIN vwProducts prod ON sop.productID = prod.id
AND prod.categoryID IN (
1
,8
,9
,10
,11
)
)
AND PaymentTypeID IN (
11
,12
,18
,19
,20
,21
,22
,26
,27
,28
,29
)
AND (
(isNull(PONumber, '') = '')
OR (isNull(Terms, '') = '')
)
ORDER BY Aptify.dbo.vwStandingOrders.[DateCreated] DESC
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply