COACHMAN99
asked on
sql 2012 syntax
Hi Folks
I have to maintain some old embedded SQL code (see section-3 below). It has redundant join syntax, so I replaced it with section-2 below.
When I do the (SQL Server 2012) syntax check all is good.
When I execute the query I get an error (1 below). 'APOpen' is a valid table.
thanks in advance
1.
Msg 208, Level 16, State 1, Line 1
Invalid object name 'APOpen'
--------------------------
2.
select APPayments.ApplyToID,isnul
sum(case when APOpen.trxtype = 1 or APOpen.trxtype = 4 or APOpen.trxtype = 6 then (APOpen.docAmount + APOpen.docOther + APPayments.MiscAmount) else 0 end) as DocAmount,
sum(case when APOpen.trxtype = 1 or APOpen.trxtype = 4 or APOpen.trxtype = 6 then (APOpen.docAmount) else 0 end) as USInvoiceAmount,
sum(case when APOpen.trxtype = 1 or APOpen.trxtype = 4 or APOpen.trxtype = 6 then (APOpen.docOther) else 0 end) as USExchange,
sum(case when APOpen.trxtype = 2 or APOpen.trxtype = 3 then (APOpen.docAmount + APOpen.docOther) else 0 end) as LessDebits,
(APPayments.AppliedAmount + APPayments.MiscAmount) as AppliedAmount,APPayments.A
from APOpen INNER JOIN APPayments ON APOpen.ApplytoId = APPayments.ApplytoId
Where APOpen.vendid = 9500 and printedflag = '0' group by APPayments.ApplyToID, APPayments.AppliedAmount, APPayments.ActualApplied, APPayments.Discount, APPayments.MiscAmount, APPayments.CheckAmount
order by APOpen.DocAmount
--------------------------
3.
sqlCode = "select P.ApplyToID,isnull(max(O.I
' "sum(case when O.trxtype = 1 or O.trxtype = 4 or O.trxtype = 6 then (O.docAmount + O.docOther + P.MiscAmount) else 0 end) as DocAmount," & _
' "sum(case when O.trxtype = 1 or O.trxtype = 4 or O.trxtype = 6 then (O.docAmount) else 0 end) as USInvoiceAmount," & _
' "sum(case when O.trxtype = 1 or O.trxtype = 4 or O.trxtype = 6 then (O.docOther) else 0 end) as USExchange," & _
' "sum(case when O.trxtype = 2 or O.trxtype = 3 then (O.docAmount + O.docOther) else 0 end) as LessDebits," & _
' "(P.AppliedAmount + P.MiscAmount) as AppliedAmount,P.ActualAppl
' " Where O.vendid = " & txtVendID.Text & _
' " And O.ApplytoId =* P.ApplytoId and printedflag = '0'" & _
' " group by P.ApplyToID, P.AppliedAmount,P.ActualAp
I have to maintain some old embedded SQL code (see section-3 below). It has redundant join syntax, so I replaced it with section-2 below.
When I do the (SQL Server 2012) syntax check all is good.
When I execute the query I get an error (1 below). 'APOpen' is a valid table.
thanks in advance
1.
Msg 208, Level 16, State 1, Line 1
Invalid object name 'APOpen'
--------------------------
2.
select APPayments.ApplyToID,isnul
sum(case when APOpen.trxtype = 1 or APOpen.trxtype = 4 or APOpen.trxtype = 6 then (APOpen.docAmount + APOpen.docOther + APPayments.MiscAmount) else 0 end) as DocAmount,
sum(case when APOpen.trxtype = 1 or APOpen.trxtype = 4 or APOpen.trxtype = 6 then (APOpen.docAmount) else 0 end) as USInvoiceAmount,
sum(case when APOpen.trxtype = 1 or APOpen.trxtype = 4 or APOpen.trxtype = 6 then (APOpen.docOther) else 0 end) as USExchange,
sum(case when APOpen.trxtype = 2 or APOpen.trxtype = 3 then (APOpen.docAmount + APOpen.docOther) else 0 end) as LessDebits,
(APPayments.AppliedAmount + APPayments.MiscAmount) as AppliedAmount,APPayments.A
from APOpen INNER JOIN APPayments ON APOpen.ApplytoId = APPayments.ApplytoId
Where APOpen.vendid = 9500 and printedflag = '0' group by APPayments.ApplyToID, APPayments.AppliedAmount, APPayments.ActualApplied, APPayments.Discount, APPayments.MiscAmount, APPayments.CheckAmount
order by APOpen.DocAmount
--------------------------
3.
sqlCode = "select P.ApplyToID,isnull(max(O.I
' "sum(case when O.trxtype = 1 or O.trxtype = 4 or O.trxtype = 6 then (O.docAmount + O.docOther + P.MiscAmount) else 0 end) as DocAmount," & _
' "sum(case when O.trxtype = 1 or O.trxtype = 4 or O.trxtype = 6 then (O.docAmount) else 0 end) as USInvoiceAmount," & _
' "sum(case when O.trxtype = 1 or O.trxtype = 4 or O.trxtype = 6 then (O.docOther) else 0 end) as USExchange," & _
' "sum(case when O.trxtype = 2 or O.trxtype = 3 then (O.docAmount + O.docOther) else 0 end) as LessDebits," & _
' "(P.AppliedAmount + P.MiscAmount) as AppliedAmount,P.ActualAppl
' " Where O.vendid = " & txtVendID.Text & _
' " And O.ApplytoId =* P.ApplytoId and printedflag = '0'" & _
' " group by P.ApplyToID, P.AppliedAmount,P.ActualAp
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
the syntax checker gave an invalid error until the utility was reloaded and indicated a missing 'order by'.
Lajuan's comment is valid under different circumstances, but not when the query is run via VB/ADO or in the current database in Management Studio.
Lajuan's comment is valid under different circumstances, but not when the query is run via VB/ADO or in the current database in Management Studio.
Surely because your ORDER BY has "order by O.DocAmount" which should surely read as "order by DocAmount" which is the alias of your summed field. Then you should be able to remove that field as well from the GROUP By clause.