-* 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

SQL SQLMSS
SELECT ....
dbo.TRANSLATION_GET(dbo.ROLE.TP_ID, &Lang)  AS ROLENAME_EN
...
END

quote:

(FOC36342) INVALID TYPE OF ARGUMENT #2 FOR SQL. FUNCTION

DEFINE FILE GGORA
VALUE/I8 MISSING ON = SQL.COALESCE(Biscotti, Capuccino, Croissant, Espresso, Latte);
END

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

DEFINE FILE MYFILE
SALESQTY/D20.2 = SQL.MYDB.MYSCHEMA.fnsalesQty(PRODCNTRY, SALESCNTRY,  &PRODQTY.QUOTEDSTRING, EXPFACTOR, IMPFACTOR);
END 

VARIABLE NAME=&&PRODQTY,  USAGE=I11, $
DEFINE SALESQUANTITY/D20.2=SQL.MYDB.MYSCHEMA.fnsalesQty(UN_COUNTRY, SALESCOUNTRY, '&&PRODQTY', EXP_FACTOR, IMP_FACTOR);
      TITLE='Sales Quantity', $

VARIABLE NAME=&&PRODQTY,  USAGE=I11, DEFAULT='10000', $

WHERE AGENCYCODE CONTAINS SQL.LOWER(&SDEALER1.QUOTEDSTRING) OR TEMPNAME CONTAINS SQL.LOWER(&SDEALERN1.QUOTEDSTRING);

(FOC263) EXTERNAL FUNCTION OR LOAD MODULE NOT FOUND: SQL.LOWER

 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

TABLE FILE BLAH
...
WHERE MYFIELD EQ SQL.UPPER('VALUE');
...
END

SELECT T1...
FROM BLAH T1
WHERE (T1."MYFIELD" = UPPER('VALUE'))
...

TABLE FILE BLAH
...
WHERE MYFIELD CONTAINS SQL.UPPER('VALUE');
...
END

(FOC32605) NON OPTIMIZABLE EXPRESSION WITH SQL. SYNTAX