cancel
Showing results for 
Search instead for 
Did you mean: 

How to insert a variable to a sql query?

Former Member
0 Kudos

Hi,

I need to know how I can insert a variable to a sql query.

I created the following query:

SELECT T2.NumOfDays FROM OQUT T0, OCTG T1, CDC1 T2 WHERE T0.GroupNum = T1.GroupNum AND T1.DiscCode = T2.CdcCode AND T0.DocNum = current OQUT-Docnum

How can I realize that I get the current DocNum from OQUT?

The query is for UDFs.

Accepted Solutions (1)

Accepted Solutions (1)

Former Member
0 Kudos

That is because the auto refresh may not work base on your setting. Try to change the field for refresh to the one works most. Otherwise, you have to manually update when necessary.

Thanks,

Gordon

Answers (11)

Answers (11)

Former Member
0 Kudos

Well, I guess that I got the solution:

I changed the field of the property "auto refresh when field changes" from payment terms code to DocNum.

It works!!!! Thank you sooooo much!

Former Member
0 Kudos

I tried it out. It works. But why isn't the result shown automatically?

Former Member
0 Kudos

Have you tried by Shift + F2? Will you get result by this manual way?

Thanks,

Gordon

Former Member
0 Kudos

I tried out your query. Every document number gives a result. Well, it's very confused, that it doesn't in the formatted search...

Former Member
0 Kudos

You can check by this query to see if any document numbers have no info you are looking for. Then to find the broken link to the discount field.


SELECT T3.DocNum, T1.Discount FROM CDC1 T1
INNER JOIN OCDC T0 ON T0.Code = T1.CDCCode
INNER JOIN OCTG T2 ON T2.DiscCode = T0.Code
INNER JOIN OQUT T3 ON T3.GroupNum = T2.GroupNum
WHERE T3.DocNum BETWEEN '[%0]' AND '[%1]'

Thanks,

Gordon

Former Member
0 Kudos

I did it already. for example:

SELECT CDC1.Discount FROM OQUT, OCTG, CDC1 WHERE OQUT.GroupNum = OCTG.GroupNum AND OCTG.DiscCode = CDC1.CdcCode AND OQUT.DocNum = 2

and it gives a result

Edited by: Meike Schlenker on Sep 17, 2008 8:34 PM

Former Member
0 Kudos

Thank you for helping me. I created the following query:

SELECT CDC1.Discount FROM OQUT, OCTG, CDC1 WHERE OQUT.GroupNum = OCTG.GroupNum AND OCTG.DiscCode = CDC1.CdcCode AND OQUT.DocNum = $[OQUT.DocNum]

It works, but sometimes there is no result of the formatted search. Sometimes it works and sometimes it doesn't. I don't understand that.

I can't use this solution if there isn't guaranteed that I get allways a result.

former_member583013
Active Contributor
0 Kudos

A query like the one you have should work the same way all the time. May be sometimes it does not have the results based on the conditions but you can never have a query thats works sometimes and not during others.

The best way would be to run the query outside either in the Query window of SAP / in SQL Server and use the actual Sales Quotes DocNum

Former Member
0 Kudos

When I press the SHIFT + F2 button I get an internal error. But the query is okay, isn't it?

Former Member
0 Kudos

Hello Meike,

In which screen do you want to execute the formatted search??

I think you query isn't it right, because you have linked some tables that are nog directly related, but maybe you could explain what your result should be..

SELECT T2.NumOfDays FROM OQUT T0, OCTG T1, CDC1 T2 WHERE T0.GroupNum = T1.GroupNum 
AND T1.DiscCode = T2.CdcCode AND T0.DocNum = $[OQUT.Docnum]

I think this is query is better:

SELECT T3.[NumOfDays] FROM OQUT T0  
INNER JOIN OCTG T1 ON T0.GroupNum = T1.GroupNum 
INNER JOIN OCDC T2 ON T1.DiscCode = T2.Code 
INNER JOIN CDC1 T3 ON T2.Code = T3.CdcCode 
WHERE T0.[DocNum] =[%0]

You could use the "querygenerator" for creating your query's, for more info about the tables take a look in the sdk helpfile...

Hope this helps...

Regards,

former_member583013
Active Contributor
0 Kudos

Most times a query when fully qualified and used with an Alias works correctly.

Please represent your tables as [dbo\].[OQUT\] T0

Using [dbo\].[tablename\] will help

Former Member
0 Kudos

Yes I do understand. I created a UDF and insert a formatted search with the following query:

SELECT T2.Discount FROM OQUT T0, OCTG T1, CDC1 T2 WHERE T0.GroupNum = T1.GroupNum AND T1.DiscCode = T2.CdcCode AND T0.DocNum = $[T0.DocNum]

But I don't get a result. I don't understand that.

Properties of the formatted search:

Search in Existing User-Defined Values according to saved query

Autorefresh when field changes --> Payment terms code

refresh regularly

Former Member
0 Kudos

Check if FMS is executed when SHIFT + F2 is pressed.

Former Member
0 Kudos

Thank you.

But the result isn't shown in the UDF.

I tried out the query with a docnum and it works.

Any idea????

Former Member
0 Kudos


$[OQUT.Docnum] is a System Variable

As with any system variable, if you only run it within the Query 
Generator, it will give you an error, as within the query 
generator the system cannot retrieve the correct current value for 
the system variable. 

Therefore, you need to run the query either from the menu: Tools -> 
User Queries, or from within a document as formatted search. 

Hope you understood.

Former Member
0 Kudos

SELECT T2.NumOfDays FROM OQUT T0, OCTG T1, CDC1 T2 WHERE T0.GroupNum = T1.GroupNum 
AND T1.DiscCode = T2.CdcCode AND T0.DocNum = $[OQUT.Docnum]