Are any of you using the v7.7 SQL.Function command to make a Direct DBMS Function Call? This is the only document I could find on the subject.
It's working well when I pass fixed values to the SQL Function but I can't figure out how to get it to accept an AmperVariable.
Any of you been able to do that? If so, what's the trick?
Thanks!
DanThis message has been edited. Last edited by: Dan Pinault,
7.7.05M/7.7.03 HF6 on Windows Server 2003 SP2 output to whatever is required.
September 06, 2011, 06:30 PM
Waz
Just a guess, but if you put .EVAL on the amper variables ?
Waz...
Prod:
WebFOCUS 7.6.10/8.1.04
Upgrade:
WebFOCUS 8.2.07
OS:
Linux
Outputs:
HTML, PDF, Excel, PPT
In Focus since 1984
Pity the lost knowledge of an old programmer!
September 07, 2011, 06:18 AM
<JJI>
Dan,
I'm using SQL pass through a lot an also using "user defined functions" in the SQL-code. First I test my code with fixed values from within SQL query analyzer and then from within my WebFOCUS procedure. If this is all working then I replace the fix values by the &vars and then I retest my code again in Webfocus.
This works very well for my. an example:
-* This example shows the use of an &var in a plain sql-code
SQL SQLMSS
SELECT TRANSLATION_LANGUAGE.TL_ID AS LANG_TL_ID,
CONVERT ( nvarchar ( 50 ) , [TRANSLATION].T_TEXT ) AS TRANS_TEXT
FROM TRANSLATION_LANGUAGE
INNER JOIN [TRANSLATION] ON TRANSLATION_LANGUAGE.TP_ID_LONG = [TRANSLATION].TP_ID
WHERE ([TRANSLATION].TL_ID = [COLOR:RED]&Lang[/COLOR])
ORDER BY TRANSLATION_LANGUAGE.TL_ID
;
TABLE ON TABLE HOLD AS TMP_LANG FORMAT XFOCUS INDEX LANG_TL_ID
END
or this example: -* Thes example uses a &var in a user defined sql function
SQL SQLMSS
SELECT ....
dbo.TRANSLATION_GET(dbo.ROLE.TP_ID, &Lang) AS ROLENAME_EN
...
END
Since you using WF 7.7X which is more strict than the WF7.1.7 I'm using, I would suggest Waz solution by using the .EVAL syntax.
Would it be possible to post your code so we can see what you are trying to do. Regards,
September 07, 2011, 11:07 AM
Dan Pinault
Sorry folks. I should have mentioned that I tried the .EVAL method. It doesn't like it. I get (FOC36342) INVALID TYPE OF ARGUMENT #2 FOR SQL. FUNCTION
What I like about this feature is that I can use it in a WebFOCUS context. I don't need to create a SQL Passthru procedure (BTW, I can call my functions and pass a variable using SQL Passthru). This method allows me to continue working with my metadata that is already set up on WebFOCUS. Plus, I can create the DEFINEd field right inside the master and make the field available to users.
Just need to get past this hurdle...
Dan
7.7.05M/7.7.03 HF6 on Windows Server 2003 SP2 output to whatever is required.
September 07, 2011, 11:51 AM
j.gross
quote:
(FOC36342) INVALID TYPE OF ARGUMENT #2 FOR SQL. FUNCTION
It's probably expecting a character value, either a character literal or a reference to a character-valued column.
Note that, in the example you cited, ...
DEFINE FILE GGORA
VALUE/I8 MISSING ON = SQL.COALESCE(Biscotti, Capuccino, Croissant, Espresso, Latte);
END
... the arguments (Biscotti, etc.) are column references, not literals.
Try &myvar.QUOTEDSTRING in place of &myvarThis message has been edited. Last edited by: j.gross,
- Jack Gross WF through 8.1.05
September 07, 2011, 12:02 PM
njsden
This may sound silly but, does your SQL function work if you pass an actual field as a parameter?
I so, you could try creating a DEFINE field, assign your amper variable to it and then use that virtual field as input to the SQL function.
Even though my function wants to receive an integer I had to use .QUOTEDSTRING after my amper variable to get it to work. Go figure!
Thanks all,
Dan
7.7.05M/7.7.03 HF6 on Windows Server 2003 SP2 output to whatever is required.
September 08, 2011, 01:11 PM
Dan Pinault
Here's an update with some helpful hints...
If you are calling the SQL function from inside a WebFOCUS procedure and you want to pass a variable into the function: - the variable can be either local (&) or global (&&) - regardless of what data type the SQL function is expecting to receive all variables must be coded as strings For example, assume I have a SQL function like this
CREATE FUNCTION [dbo].[fnSalesQty]
(
@prodCntry varchar(99), @salesCntry varchar(99), @prodQty int, @expFact float, @impFact float
)
RETURNS FLOAT
AS
BEGIN
DECLARE @SQL varchar(255)
IF (@prodCntry = @salesCntry)
BEGIN
SET @SQL = (SELECT (@prodQty - (@prodQty * @expFact))) /* If Production and Sales Country are same then solve for Captive Production */
END
ELSE
SET @SQL = (SELECT (@prodQty * @expFact * @impFact)) /* else solve for Imported Production */
RETURN (@SQL)
END
Note that the parameter @prodQty is declared as an integer. Let's assume I want to call this function from a WebFOCUS procedure and pass @prodQty as an amper-variable
Note how &PRODQTY needs to be in quotes even though fnsalesQty wants it to be an integer. Also note that EXPFACTOR and IMPFACTOR are real fields and thus can be passed in as-is even though they are float types. Now all you need to do is include the field named SALESQUANTITY in your report procedure. Just remember to provide a value for the &PRODQTY global variable. ----------------------------------------------------------------- If you are calling the SQL function from inside a MASTER file and you want to pass a variable into the function: - the variable must be declared in the MASTER file - the variable must be global (&&) - regardless of what data type the SQL function is expecting to receive all variables must be coded as strings For example, assume the same SQL function shown above Let's assume I want to call this function from a MASTER file and still pass @prodQty as an amper-variable. Entries in the MFD would look like
Note the variable declaration and then the inclusion of the variable in the function. This time it is in single quotes. The .QUOTEDSTRING method does not work in the MASTER file. Now all you need to do is include the field named SALESQUANTITY in your report procedure. Just remember to provide a value for the &&PRODQTY global variable. To prevent errors you can also add a default value for the variable in the master file. Be cautious though, defaulting the value could cause confusion in the results if it is not clear to the user that some value has been set without their knowledge.
I don't quite get why the variables need to be passed as strings as jgross pointed out earlier in this thread but it helps to know this when setting it all up!
Cheers,
Dan
7.7.05M/7.7.03 HF6 on Windows Server 2003 SP2 output to whatever is required.
September 09, 2011, 06:52 PM
njsden
Thanks for the detailed example Dan. Good points to keep in mind.
WHERE AGENCYCODE CONTAINS SQL.LOWER(&SDEALER1.QUOTEDSTRING) OR TEMPNAME CONTAINS SQL.LOWER(&SDEALERN1.QUOTEDSTRING);
In SQL Server, I get this error:
(FOC263) EXTERNAL FUNCTION OR LOAD MODULE NOT FOUND: SQL.LOWER
In DB2, I get this error:
FOC2565 - THE OBJECT OF IF/WHERE CANNOT BE CONVERTED TO SQL
FOC2566 - WHERE WH$$$1 CANNOT BE CONVERTED TO SQL
FOC2575 - COMBINATION OF OPERANDS FOR CONTAINS CANNOT BE CONVERTED TO SQL
FOC2590 - AGGREGATION NOT DONE FOR THE FOLLOWING REASON:
FOC2598 - FOCUS IF/WHERE TEST CANNOT BE PASSED TO SQL
Francis
Give me code, or give me retirement. In FOCUS since 1991
Production: WF 7.7.05M, Dev Studio, BID, MRE, WebSphere, DB2 / Test: WF 8.1.05M, App Studio, BI Portal, Report Caster, jQuery, HighCharts, Apache Tomcat, MS SQL Server
November 28, 2011, 11:52 AM
njsden
This works with Oracle:
TABLE FILE BLAH
...
WHERE MYFIELD EQ SQL.UPPER('VALUE');
...
END
SQL trace shows:
SELECT T1...
FROM BLAH T1
WHERE (T1."MYFIELD" = UPPER('VALUE'))
...
Attempting to use it in combination with CONTAINS however does not work, though the message I get is different to yours:
TABLE FILE BLAH
...
WHERE MYFIELD CONTAINS SQL.UPPER('VALUE');
...
END
Gives me the message below which sort of makes sense as CONTAINS not always translates to native SQL:
(FOC32605) NON OPTIMIZABLE EXPRESSION WITH SQL. SYNTAX