• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 515
  • Last Modified:

Incorrect syntax near 't1'.

selecT * FROM ( select o.dateordered,o.company,o.orderid,RTRIM ( LTRIM( o.billfirstname + ' ' + o.billlastname ) ) billfullname,o.email,o.itemid as oitemid ,p.cost1,p.cost2,p.cost3,p.productid,p.title as ptitle ,e.title as etitle ,s.status ,i.sku,i.quantity,i.itemid as iitemid ,CASE sku WHEN p.internalsku1 THEN p.cost1*i.quantity WHEN p.internalsku2 THEN p.cost2*i.quantity WHEN p.internalsku3 THEN p.cost3*i.quantity ELSE 0 END as cost ,(select sum(subtotal) from orderitems where orderid=o.orderid)as subtotal ,(select SUM(debit) from payments where orderid=o.orderid and ( (type='Paypal' and confirmation!='' and resultcode!='Denied' and resultcode!='unconfirmed') or (type!='Paypal') ) )as debit ,(select count(*) orderid from payments where orderid=o.orderid) as paymentcount ,(select SUM(case when isnumeric(paymentfee)=1 then cast(cast(paymentfee as float) as decimal(18,2)) else 0 end) from payments where orderid=o.orderid) as paymentfee , (select top 1(type) from payments where orderid=o.orderid) as realtype ,(select case when min(type) is null then 'none' when min(type) = max(type) then min(type) else 'multiple' end from payments where orderid=o.orderid) as type ,(select top 1(email) from payments where orderid=o.orderid) as paymentemail ,(SELECT ISNULL((SELECT Min(dateentered) FROM payments WHERE orderid = o.orderid),(SELECT TOP 1 dateordered FROM orders WHERE orderid = o.orderid))) AS paymentdate ,(select min(itemid) from orderitems where orderid=o.orderid)as minitemid from orders o left JOIN orderitems i on i.orderid = o.orderid left JOIN products p on i.productid = p.productid left join ebaydata e on o.orderid = e.orderid left join sstatuses s on o.statusid=s.statusid where  o.statusid in (0,0,0,0,0,0,0) and ( 1=0 or 1=0 or 1=0 ) ) t1 where (paymentdate between '04/26/2011' and '05/04/2011')  order by orderid




when I add  ' ('  after status id  and ) or o.orderid like '%5678%' before order by orderid   i get error
Msg 102, Level 15, State 1, Line 1
Incorrect syntax near 't1'.

 selecT * FROM ( select o.dateordered,o.company,o.orderid,RTRIM ( LTRIM( o.billfirstname + ' ' + o.billlastname ) ) billfullname,o.email,o.itemid as oitemid ,p.cost1,p.cost2,p.cost3,p.productid,p.title as ptitle ,e.title as etitle ,s.status ,i.sku,i.quantity,i.itemid as iitemid ,CASE sku WHEN p.internalsku1 THEN p.cost1*i.quantity WHEN p.internalsku2 THEN p.cost2*i.quantity WHEN p.internalsku3 THEN p.cost3*i.quantity ELSE 0 END as cost ,(select sum(subtotal) from orderitems where orderid=o.orderid)as subtotal ,(select SUM(debit) from payments where orderid=o.orderid and ( (type='Paypal' and confirmation!='' and resultcode!='Denied' and resultcode!='unconfirmed') or (type!='Paypal') ) )as debit ,(select count(*) orderid from payments where orderid=o.orderid) as paymentcount ,(select SUM(case when isnumeric(paymentfee)=1 then cast(cast(paymentfee as float) as decimal(18,2)) else 0 end) from payments where orderid=o.orderid) as paymentfee , (select top 1(type) from payments where orderid=o.orderid) as realtype ,(select case when min(type) is null then 'none' when min(type) = max(type) then min(type) else 'multiple' end from payments where orderid=o.orderid) as type ,(select top 1(email) from payments where orderid=o.orderid) as paymentemail ,(SELECT ISNULL((SELECT Min(dateentered) FROM payments WHERE orderid = o.orderid),(SELECT TOP 1 dateordered FROM orders WHERE orderid = o.orderid))) AS paymentdate ,(select min(itemid) from orderitems where orderid=o.orderid)as minitemid from orders o left JOIN orderitems i on i.orderid = o.orderid left JOIN products p on i.productid = p.productid left join ebaydata e on o.orderid = e.orderid left join sstatuses s on o.statusid=s.statusid where ( o.statusid in (0,0,0,0,0,0,0) and ( 1=0 or 1=0 or 1=0 ) ) t1 where (paymentdate between '04/26/2011' and '05/04/2011') ) or o.orderid like '%5678%' order by orderid
0
rgb192
Asked:
rgb192
  • 7
  • 3
  • 3
  • +1
2 Solutions
 
cfEngineersCommented:
( 1=0 or 1=0 or 1=0 ) ) t1 where (paymentdate between

what is this t1
0
 
rgb192Author Commented:
t1 is the entire query
so I can put paymentdate (derived) as part of the where statement
0
 
SharathData EngineerCommented:
try this.
SELECT * 
  FROM (SELECT o.dateordered, 
               o.company, 
               o.orderid, 
               RTRIM (LTRIM(o.billfirstname + ' ' + o.billlastname)) 
                      billfullname, 
               o.email, 
               o.itemid 
                      AS oitemid, 
               p.cost1, 
               p.cost2, 
               p.cost3, 
               p.productid, 
               p.title 
                      AS ptitle, 
               e.title 
                      AS etitle, 
               s.status, 
               i.sku, 
               i.quantity, 
               i.itemid 
                      AS iitemid, 
               CASE sku 
                 WHEN p.internalsku1 THEN p.cost1 * i.quantity 
                 WHEN p.internalsku2 THEN p.cost2 * i.quantity 
                 WHEN p.internalsku3 THEN p.cost3 * i.quantity 
                 ELSE 0 
               END 
                      AS COST, 
               (SELECT SUM(subtotal) 
                  FROM orderitems 
                 WHERE orderid = o.orderid) 
                      AS subtotal, 
               (SELECT SUM(debit) 
                  FROM payments 
                 WHERE orderid = o.orderid 
                       AND ( ( TYPE = 'Paypal' 
                               AND confirmation != '' 
                               AND resultcode != 'Denied' 
                               AND resultcode != 'unconfirmed' ) 
                              OR ( TYPE != 'Paypal' ) )) 
                      AS debit, 
               (SELECT COUNT(*) orderid 
                  FROM payments 
                 WHERE orderid = o.orderid) 
                      AS paymentcount, 
               (SELECT SUM(CASE 
                             WHEN ISNUMERIC(paymentfee) = 1 THEN CAST( 
                             CAST(paymentfee AS FLOAT) AS DECIMAL(18, 2)) 
                             ELSE 0 
                           END) 
                  FROM payments 
                 WHERE orderid = o.orderid) 
                      AS paymentfee, 
               (SELECT TOP 1( TYPE ) 
                  FROM payments 
                 WHERE orderid = o.orderid) 
                      AS realtype, 
               (SELECT CASE 
                         WHEN MIN(TYPE) IS NULL THEN 'none' 
                         WHEN MIN(TYPE) = MAX(TYPE) THEN MIN(TYPE) 
                         ELSE 'multiple' 
                       END 
                  FROM payments 
                 WHERE orderid = o.orderid) 
                      AS TYPE, 
               (SELECT TOP 1( email ) 
                  FROM payments 
                 WHERE orderid = o.orderid) 
                      AS paymentemail, 
               (SELECT ISNULL((SELECT MIN(dateentered) 
                                 FROM payments 
                                WHERE orderid = o.orderid), 
                       (SELECT TOP 1 dateordered 
                          FROM orders 
                         WHERE orderid = o.orderid))) AS 
               paymentdate, 
               (SELECT MIN(itemid) 
                  FROM orderitems 
                 WHERE orderid = o.orderid) 
                      AS minitemid 
          FROM orders o 
               LEFT JOIN orderitems i 
                 ON i.orderid = o.orderid 
               LEFT JOIN products p 
                 ON i.productid = p.productid 
               LEFT JOIN ebaydata e 
                 ON o.orderid = e.orderid 
               LEFT JOIN sstatuses s 
                 ON o.statusid = s.statusid 
         WHERE o.statusid IN ( 0, 0, 0, 0, 
                               0, 0, 0 ) 
               AND ( 1 = 0 
                      OR 1 = 0 
                      OR 1 = 0 )) t1 
 WHERE ( paymentdate BETWEEN '04/26/2011' AND '05/04/2011' ) 
        OR orderid LIKE '%5678%' 
 ORDER BY orderid  

Open in new window

0
 [eBook] Windows Nano Server

Download this FREE eBook and learn all you need to get started with Windows Nano Server, including deployment options, remote management
and troubleshooting tips and tricks

 
HainKurtSr. System AnalystCommented:
try this (but query looks so ugly :)
selecT * FROM ( 

select o.dateordered,o.company,o.orderid,RTRIM ( LTRIM( o.billfirstname + ' ' + o.billlastname ) ) billfullname,o.email,o.itemid as oitemid ,p.cost1,p.cost2,p.cost3,p.productid,p.title as ptitle ,e.title as etitle ,s.status ,i.sku,i.quantity,i.itemid as iitemid ,CASE sku WHEN p.internalsku1 THEN p.cost1*i.quantity WHEN p.internalsku2 THEN p.cost2*i.quantity WHEN p.internalsku3 THEN p.cost3*i.quantity ELSE 0 END as cost,
(select sum(subtotal) from orderitems where orderid=o.orderid) as subtotal,
(select SUM(debit) from payments where orderid=o.orderid and ((type='Paypal' and confirmation!='' and resultcode!='Denied' and resultcode!='unconfirmed') or (type!='Paypal') ) ) as debit,
(select count(*) orderid from payments where orderid=o.orderid) as paymentcount,
(select SUM(case when isnumeric(paymentfee)=1 then cast(cast(paymentfee as float) as decimal(18,2)) else 0 end) from payments where orderid=o.orderid) as paymentfee, 
(select top 1(type) from payments where orderid=o.orderid) as realtype,
(select case when min(type) is null then 'none' when min(type) = max(type) then min(type) else 'multiple' end from payments where orderid=o.orderid) as type,
(select top 1(email) from payments where orderid=o.orderid) as paymentemail,
(SELECT ISNULL((SELECT Min(dateentered) FROM payments WHERE orderid = o.orderid),(SELECT TOP 1 dateordered FROM orders WHERE orderid = o.orderid))) AS paymentdate,
(select min(itemid) from orderitems where orderid=o.orderid) as minitemid 

from orders o 
left JOIN orderitems i on i.orderid = o.orderid 
left JOIN products p on i.productid = p.productid
left join ebaydata e on o.orderid = e.orderid
left join sstatuses s on o.statusid=s.statusid
where ( o.statusid in (0,0,0,0,0,0,0) and ( 1=0 or 1=0 or 1=0 ) )

) t1 
where (paymentdate between '04/26/2011' and '05/04/2011') or o.orderid like '%5678%' 
order by orderid

Open in new window

0
 
HainKurtSr. System AnalystCommented:
first of all you dont need outer select

select o.dateordered,o.company,o.orderid,RTRIM ( LTRIM( o.billfirstname + ' ' + o.billlastname ) ) billfullname,o.email,o.itemid as oitemid ,p.cost1,p.cost2,p.cost3,p.productid,p.title as ptitle ,e.title as etitle ,s.status ,i.sku,i.quantity,i.itemid as iitemid ,CASE sku WHEN p.internalsku1 THEN p.cost1*i.quantity WHEN p.internalsku2 THEN p.cost2*i.quantity WHEN p.internalsku3 THEN p.cost3*i.quantity ELSE 0 END as cost,
(select sum(subtotal) from orderitems where orderid=o.orderid) as subtotal,
(select SUM(debit) from payments where orderid=o.orderid and ((type='Paypal' and confirmation!='' and resultcode!='Denied' and resultcode!='unconfirmed') or (type!='Paypal') ) ) as debit,
(select count(*) orderid from payments where orderid=o.orderid) as paymentcount,
(select SUM(case when isnumeric(paymentfee)=1 then cast(cast(paymentfee as float) as decimal(18,2)) else 0 end) from payments where orderid=o.orderid) as paymentfee, 
(select top 1(type) from payments where orderid=o.orderid) as realtype,
(select case when min(type) is null then 'none' when min(type) = max(type) then min(type) else 'multiple' end from payments where orderid=o.orderid) as type,
(select top 1(email) from payments where orderid=o.orderid) as paymentemail,
(SELECT ISNULL((SELECT Min(dateentered) FROM payments WHERE orderid = o.orderid),(SELECT TOP 1 dateordered FROM orders WHERE orderid = o.orderid))) AS paymentdate,
(select min(itemid) from orderitems where orderid=o.orderid) as minitemid 

from orders o 
left JOIN orderitems i on i.orderid = o.orderid 
left JOIN products p on i.productid = p.productid
left join ebaydata e on o.orderid = e.orderid
left join sstatuses s on o.statusid=s.statusid
where ( o.statusid in (0,0,0,0,0,0,0) and ( 1=0 or 1=0 or 1=0 ) )
and
(paymentdate between '04/26/2011' and '05/04/2011') or o.orderid like '%5678%' 
order by orderid

Open in new window

0
 
SharathData EngineerCommented:
Or, you can move the orderid filter into the derived table sub query.
SELECT * 
  FROM (SELECT o.dateordered, 
               o.company, 
               o.orderid, 
               RTRIM (LTRIM(o.billfirstname + ' ' + o.billlastname)) 
                      billfullname, 
               o.email, 
               o.itemid 
                      AS oitemid, 
               p.cost1, 
               p.cost2, 
               p.cost3, 
               p.productid, 
               p.title 
                      AS ptitle, 
               e.title 
                      AS etitle, 
               s.status, 
               i.sku, 
               i.quantity, 
               i.itemid 
                      AS iitemid, 
               CASE sku 
                 WHEN p.internalsku1 THEN p.cost1 * i.quantity 
                 WHEN p.internalsku2 THEN p.cost2 * i.quantity 
                 WHEN p.internalsku3 THEN p.cost3 * i.quantity 
                 ELSE 0 
               END 
                      AS COST, 
               (SELECT SUM(subtotal) 
                  FROM orderitems 
                 WHERE orderid = o.orderid) 
                      AS subtotal, 
               (SELECT SUM(debit) 
                  FROM payments 
                 WHERE orderid = o.orderid 
                       AND ( ( TYPE = 'Paypal' 
                               AND confirmation != '' 
                               AND resultcode != 'Denied' 
                               AND resultcode != 'unconfirmed' ) 
                              OR ( TYPE != 'Paypal' ) )) 
                      AS debit, 
               (SELECT COUNT(*) orderid 
                  FROM payments 
                 WHERE orderid = o.orderid) 
                      AS paymentcount, 
               (SELECT SUM(CASE 
                             WHEN ISNUMERIC(paymentfee) = 1 THEN CAST( 
                             CAST(paymentfee AS FLOAT) AS DECIMAL(18, 2)) 
                             ELSE 0 
                           END) 
                  FROM payments 
                 WHERE orderid = o.orderid) 
                      AS paymentfee, 
               (SELECT TOP 1( TYPE ) 
                  FROM payments 
                 WHERE orderid = o.orderid) 
                      AS realtype, 
               (SELECT CASE 
                         WHEN MIN(TYPE) IS NULL THEN 'none' 
                         WHEN MIN(TYPE) = MAX(TYPE) THEN MIN(TYPE) 
                         ELSE 'multiple' 
                       END 
                  FROM payments 
                 WHERE orderid = o.orderid) 
                      AS TYPE, 
               (SELECT TOP 1( email ) 
                  FROM payments 
                 WHERE orderid = o.orderid) 
                      AS paymentemail, 
               (SELECT ISNULL((SELECT MIN(dateentered) 
                                 FROM payments 
                                WHERE orderid = o.orderid), 
                       (SELECT TOP 1 dateordered 
                          FROM orders 
                         WHERE orderid = o.orderid))) AS 
               paymentdate, 
               (SELECT MIN(itemid) 
                  FROM orderitems 
                 WHERE orderid = o.orderid) 
                      AS minitemid 
          FROM orders o 
               LEFT JOIN orderitems i 
                 ON i.orderid = o.orderid 
               LEFT JOIN products p 
                 ON i.productid = p.productid 
               LEFT JOIN ebaydata e 
                 ON o.orderid = e.orderid 
               LEFT JOIN sstatuses s 
                 ON o.statusid = s.statusid 
         WHERE ( o.statusid IN ( 0, 0, 0, 0, 
                                 0, 0, 0 ) 
                 AND ( 1 = 0 
                        OR 1 = 0 
                        OR 1 = 0 ) ) 
                 OR o.orderid LIKE '%5678%') t1 
 WHERE ( paymentdate BETWEEN '04/26/2011' AND '05/04/2011' ) 
 ORDER BY orderid  

Open in new window

0
 
HainKurtSr. System AnalystCommented:
second, this query should be re-written from scratch...
0
 
rgb192Author Commented:

invalid column 'paymentdate'

select o.dateordered,o.company,o.orderid,RTRIM ( LTRIM( o.billfirstname + ' ' + o.billlastname ) ) billfullname,o.email,o.itemid as oitemid ,p.cost1,p.cost2,p.cost3,p.productid,p.title as ptitle ,e.title as etitle ,s.status ,i.sku,i.quantity,i.itemid as iitemid ,CASE sku WHEN p.internalsku1 THEN p.cost1*i.quantity WHEN p.internalsku2 THEN p.cost2*i.quantity WHEN p.internalsku3 THEN p.cost3*i.quantity ELSE 0 END as cost,
(select sum(subtotal) from orderitems where orderid=o.orderid) as subtotal,
(select SUM(debit) from payments where orderid=o.orderid and ((type='Paypal' and confirmation!='' and resultcode!='Denied' and resultcode!='unconfirmed') or (type!='Paypal') ) ) as debit,
(select count(*) orderid from payments where orderid=o.orderid) as paymentcount,
(select SUM(case when isnumeric(paymentfee)=1 then cast(cast(paymentfee as float) as decimal(18,2)) else 0 end) from payments where orderid=o.orderid) as paymentfee,
(select top 1(type) from payments where orderid=o.orderid) as realtype,
(select case when min(type) is null then 'none' when min(type) = max(type) then min(type) else 'multiple' end from payments where orderid=o.orderid) as type,
(select top 1(email) from payments where orderid=o.orderid) as paymentemail,
(SELECT ISNULL((SELECT Min(dateentered) FROM payments WHERE orderid = o.orderid),(SELECT TOP 1 dateordered FROM orders WHERE orderid = o.orderid))) AS paymentdate,
(select min(itemid) from orderitems where orderid=o.orderid) as minitemid

from orders o
left JOIN orderitems i on i.orderid = o.orderid
left JOIN products p on i.productid = p.productid
left join ebaydata e on o.orderid = e.orderid
left join sstatuses s on o.statusid=s.statusid
where ( o.statusid in (0,0,0,0,0,0,0) and ( 1=0 or 1=0 or 1=0 ) )
and
(paymentdate between '04/26/2011' and '05/04/2011') or o.orderid like '%5678%'
order by orderid







no results

SELECT *
  FROM (SELECT o.dateordered,
               o.company,
               o.orderid,
               RTRIM (LTRIM(o.billfirstname + ' ' + o.billlastname))
                      billfullname,
               o.email,
               o.itemid
                      AS oitemid,
               p.cost1,
               p.cost2,
               p.cost3,
               p.productid,
               p.title
                      AS ptitle,
               e.title
                      AS etitle,
               s.status,
               i.sku,
               i.quantity,
               i.itemid
                      AS iitemid,
               CASE sku
                 WHEN p.internalsku1 THEN p.cost1 * i.quantity
                 WHEN p.internalsku2 THEN p.cost2 * i.quantity
                 WHEN p.internalsku3 THEN p.cost3 * i.quantity
                 ELSE 0
               END
                      AS COST,
               (SELECT SUM(subtotal)
                  FROM orderitems
                 WHERE orderid = o.orderid)
                      AS subtotal,
               (SELECT SUM(debit)
                  FROM payments
                 WHERE orderid = o.orderid
                       AND ( ( TYPE = 'Paypal'
                               AND confirmation != ''
                               AND resultcode != 'Denied'
                               AND resultcode != 'unconfirmed' )
                              OR ( TYPE != 'Paypal' ) ))
                      AS debit,
               (SELECT COUNT(*) orderid
                  FROM payments
                 WHERE orderid = o.orderid)
                      AS paymentcount,
               (SELECT SUM(CASE
                             WHEN ISNUMERIC(paymentfee) = 1 THEN CAST(
                             CAST(paymentfee AS FLOAT) AS DECIMAL(18, 2))
                             ELSE 0
                           END)
                  FROM payments
                 WHERE orderid = o.orderid)
                      AS paymentfee,
               (SELECT TOP 1( TYPE )
                  FROM payments
                 WHERE orderid = o.orderid)
                      AS realtype,
               (SELECT CASE
                         WHEN MIN(TYPE) IS NULL THEN 'none'
                         WHEN MIN(TYPE) = MAX(TYPE) THEN MIN(TYPE)
                         ELSE 'multiple'
                       END
                  FROM payments
                 WHERE orderid = o.orderid)
                      AS TYPE,
               (SELECT TOP 1( email )
                  FROM payments
                 WHERE orderid = o.orderid)
                      AS paymentemail,
               (SELECT ISNULL((SELECT MIN(dateentered)
                                 FROM payments
                                WHERE orderid = o.orderid),
                       (SELECT TOP 1 dateordered
                          FROM orders
                         WHERE orderid = o.orderid))) AS
               paymentdate,
               (SELECT MIN(itemid)
                  FROM orderitems
                 WHERE orderid = o.orderid)
                      AS minitemid
          FROM orders o
               LEFT JOIN orderitems i
                 ON i.orderid = o.orderid
               LEFT JOIN products p
                 ON i.productid = p.productid
               LEFT JOIN ebaydata e
                 ON o.orderid = e.orderid
               LEFT JOIN sstatuses s
                 ON o.statusid = s.statusid
         WHERE ( o.statusid IN ( 0, 0, 0, 0,
                                 0, 0, 0 )
                 AND ( 1 = 0
                        OR 1 = 0
                        OR 1 = 0 ) )
                 OR o.orderid LIKE '%5678%') t1
 WHERE ( paymentdate BETWEEN '04/26/2011' AND '05/04/2011' )
 ORDER BY orderid  
0
 
HainKurtSr. System AnalystCommented:
invalid column 'paymentdate'

--> try this : 35515703

you need a wrapper select to filter on this column :)
0
 
HainKurtSr. System AnalystCommented:
try the query that I posted @ 35515703 ^^^
0
 
HainKurtSr. System AnalystCommented:
what I suggest is create a view

and put all thesse into this view...

(select SUM(debit) from payments where orderid=o.orderid and ((type='Paypal' and confirmation!='' and resultcode!='Denied' and resultcode!='unconfirmed') or (type!='Paypal') ) ) as debit,
(select count(*) orderid from payments where orderid=o.orderid) as paymentcount,
(select SUM(case when isnumeric(paymentfee)=1 then cast(cast(paymentfee as float) as decimal(18,2)) else 0 end) from payments where orderid=o.orderid) as paymentfee,
(select top 1(type) from payments where orderid=o.orderid) as realtype,
(select case when min(type) is null then 'none' when min(type) = max(type) then min(type) else 'multiple' end from payments where orderid=o.orderid) as type,
(select top 1(email) from payments where orderid=o.orderid) as paymentemail,

then use this view as a left join on your original query

create view v_payments as
select ...
from payments
group by orderid

-->

select o.*, p.debit, p.paymentfee, ...
from orders o left join v_payments p on o.orderid=p.orderid

0
 
HainKurtSr. System AnalystCommented:
do you have multiple records in payments table per order id?

if yes,

email column  is repeated and same in all rows?
type column  is repeated and same in all rows?

if these are same for an order then they should go into order table not payments table...
0
 
SharathData EngineerCommented:
Did you try my suggestion?
0
 
rgb192Author Commented:
these work thanks
0

Featured Post

Important Lessons on Recovering from Petya

In their most recent webinar, Skyport Systems explores ways to isolate and protect critical databases to keep the core of your company safe from harm.

  • 7
  • 3
  • 3
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now