January 8, 2013 at 6:22 am
Hi experts,
I am trying to create this query and essentially trying to pass parameter (@CardName) in table created by another query but it's giving error. I know I am doing something basic wrong in passing parameters because without them the query is running fine. Could someone please guide me? Thank you in advance.
Best Regards.
KD
Use EMMCLIVE;
DECLARE @CardName AS nvarchar(100)
/* WHERE */
SET @CardName = '[%0]'
SELECT
TB.InvoiceRef, TB.InvoiceDate, TB.Customer, TB.GrossAmount, TB.PayRef, TB.PayDate, TB.PaymentMadeOn, TB.DaystoPay, AVG(TB.DaystoPay) OVER () AS 'Avg Days'
FROM
(SELECT T2.[DocNum] as 'InvoiceRef', T2.[DocDate] as 'InvoiceDate', T2.[CardName] as 'Customer', T2.[DocTotal] as 'GrossAmount', T0.[DocNum] as 'PayRef', T1.[DocDate] as 'PayDate',
CASE WHEN T0.InvType = 203 THEN'A/R Down Payment'
WHEN T0.InvType = 13 THEN'A/R Invoice'
WHEN T0.InvType = 14 THEN'A/R Credit Memo'
WHEN T0.InvType = 204 THEN'A/P Down Payment'
WHEN T0.InvType = 18 THEN'A/P Invoice'
WHEN T0.InvType = 19 THEN'A/P Credit Memo'
WHEN T0.InvType = 24 THEN'Incoming Payment'
WHEN T0.InvType = 25 THEN'Deposit'
WHEN T0.InvType = 46 THEN'Payment Advice'
WHEN T0.InvType = 57 THEN'Checks for Payment'
WHEN T0.InvType = 76 THEN'Postdated Deposit'
WHEN T0.InvType = -2 THEN'Opening Balance'
WHEN T0.InvType = -3 THEN'Closing Balance'
WHEN T0.InvType = 30 THEN'Journal Entry'
WHEN T0.InvType = -1 THEN'All Transactions'
WHEN T0.InvType = 163 THEN'A/P Correction Invoice'
WHEN T0.InvType = 165 THEN'A/R Correction Invoice'
END AS 'PaymentMadeOn', DATEDIFF(DAY, T2.[DocDate], T1.[DocDate]) AS 'DaystoPay', AVG(DATEDIFF(DAY, T2.[DocDate], T1.[DocDate])) OVER () AS 'OverallAvg'
FROM
[dbo].[RCT2] T0 INNER JOIN [dbo].[ORCT] T1 ON T1.DocNum = T0.DocNum
INNER JOIN [dbo].[OINV] T2 ON T2.DocEntry = T0.DocEntry
WHERE
T2.[DocStatus] like 'C' and T2.cardname LIKE @CardName
UNION ALL
SELECT
T2.[DocNum] AS 'InvoiceRef', T2.DocDate as 'InvoiceDate', T2.CardName as 'Customer', T2.[DocTotal] as 'GrossAmount', T0.[ReconNum] as 'PayRef', T0.[ReconDate] as 'PayDate', 'Account (or Internal Rec)' as 'PaymentMadeOn', DATEDIFF(DAY, T2.DocDate, T0.[ReconDate]) AS 'DaystoPay', AVG(DATEDIFF(DAY, T2.DocDate, T0.[ReconDate])) OVER () AS 'OverallAvg'
FROM
OITR T0 INNER JOIN ITR1 T1 ON T0.ReconNum = T1.ReconNum INNER JOIN OINV T2 on T1.TransId=T2.TransId
WHERE
T2.[CardCode] = T1.[ShortName] AND T0.[ReconType] = 0 and T2.cardname LIKE @CardName
) TB
WHERE TB.CUSTOMER LIKE @CardName
January 8, 2013 at 6:34 am
What error?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
January 8, 2013 at 6:42 am
Sorry for missing that out. The error is:
1). [Microsoft][SQL Server Native Client 10.0][SQL Server]Must declare the scalar variable "@CardName". 2). [Microsoft][SQL Server Native Client 10.0][SQL Server]Statement 'Service Contracts' (OCTR) (s) could not be prepared.
January 8, 2013 at 7:12 am
And exactly how are you calling that piece of code?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
January 8, 2013 at 7:17 am
I am running it directly in the application's (SAP in this case) query generator.
Tried to run it SQL server management studio but it runs without error but with no result, so thought its not the place to run it.
January 8, 2013 at 7:24 am
How *exactly* are you calling that? Are you calling the entire piece as you've written there? Are you calling just the query? If so, have you defined the parameters?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
January 8, 2013 at 7:29 am
I am calling the entire piece as written, with parameter @CardName declared exactly how I have above.
Regards.
January 8, 2013 at 7:30 am
As you have it above, @CardName is not a parameter, it's a local variable.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
January 8, 2013 at 7:48 am
Thanks for your reply.
I have brain freeze right now. Without disputing what you just said, I have more queries successfully running with the same syntax; what's uniquly stupid I am doing?
January 8, 2013 at 8:01 am
Fair warning. I only know the barest bit about SAP because I work with someone in another state who uses it. That being said, does this article help?
January 8, 2013 at 9:10 am
Hi Brandie,
You pointed me to the right direction and sometimes that's the best thing to do.
Added this in the start of my query and everything was good:
/* SELECT FROM [dbo].[OINV] T2 */
DECLARE @Cust AS nvarchar(100)
/* WHERE */
SET @Cust = /* T2.CardName */ '[%0]'
[Although I must admit I spent more time reading your website and profile then working on the solution.]
Thank you.
Best Regards
Kanu
January 8, 2013 at 9:22 am
I thought you said you were calling the exact piece of code as you gave above, with the declare and set in it?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
January 8, 2013 at 9:31 am
Glad I could help. @=)
January 8, 2013 at 9:32 am
GilaMonster (1/8/2013)
I thought you said you were calling the exact piece of code as you gave above, with the declare and set in it?
Gail, believe it or not, that additional comment is required in the SAP interface. The code failed because it wasn't in his original code.
January 8, 2013 at 9:37 am
Brandie Tarvin (1/8/2013)
GilaMonster (1/8/2013)
I thought you said you were calling the exact piece of code as you gave above, with the declare and set in it?Gail, believe it or not, that additional comment is required in the SAP interface. The code failed because it wasn't in his original code.
And I am looking for the reason why this extra bit is required, or rather how does a comment make so much difference to a program.
Viewing 15 posts - 1 through 15 (of 16 total)
You must be logged in to reply to this topic. Login to reply