on 09-16-2008 12:21 PM
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.
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
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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!
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
I tried it out. It works. But why isn't the result shown automatically?
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
Have you tried by Shift + F2? Will you get result by this manual way?
Thanks,
Gordon
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
I tried out your query. Every document number gives a result. Well, it's very confused, that it doesn't in the formatted search...
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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
When I press the SHIFT + F2 button I get an internal error. But the query is okay, isn't it?
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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,
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
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
Thank you.
But the result isn't shown in the UDF.
I tried out the query with a docnum and it works.
Any idea????
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
$[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.
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]
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
User | Count |
---|---|
85 | |
7 | |
6 | |
4 | |
3 | |
3 | |
3 | |
3 | |
3 | |
2 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.