Hi All, I am trying to do a remote to our AS/400 to use native SQL and its not working. here is the syntax I am sending:

-SET &ECHO= 'ALL';
-*APP MAP FOCHOLD C:\Inetpub\wwwroot\Output_Hold\
-*APP HOLD FOCHOLD
-DEFAULT &OUTPUT = 'HTML';
-DEFAULT &TIMELINE 	= 'WTD';
-DEFAULT &report_date = '20060114';
-DEFAULT &report_date_month  = '01';
-DEFAULT &report_date_day  = '14';
-DEFAULT &report_date_year = '06';
-*
-*
-**********************************************************************************
-*SET REPORT OUTPUT
-SET &HLD = '-*';
-IF &OUTPUT EQ 'REPORT' GOTO SKIPHOLD;
-SET  &HLD = IF &OUTPUT EQ 'PDF' THEN 'ON TABLE PCHOLD FORMAT PDF'
-      	ELSE IF &OUTPUT EQ 'EXL2K' THEN 'ON TABLE PCHOLD FORMAT EXL2K'
-		ELSE &HLD ;
-SKIPHOLD
-*GET CALENDAR DATA
-IF &report_date = '$*' GOTO SKIP_DT;
-IF &report_date = ' ' GOTO SKIP_DT;
-SET &report_date_month  = EDIT(&report_date, '$$$$99$$');
-SET &report_date_day  = EDIT(&report_date, '$$$$$$99');
-SET &report_date_year = EDIT(&report_date, '$$99$$$$');


DEFINE FILE CALMST
REPORT_DATE/I6 = &report_date - 20000000;
END

REMOTE DEST = ******;
REMOTE USER = ******;
REMOTE PASS = ******;
-MRNOEDIT BEGIN
-REMOTE BEGIN
SQL  
SELECT  MIN((CALFYR*1000000)+(CALFMT*10000)+(CALFWK*100)+CALFDY)
,MAX((CALFYR*1000000)+(CALFMT*10000)+(CALFWK*100)+CALFDY)
FROM CALMST
-IF &TIMELINE NE 'LTD'  GOTO SKIP_LTD;
;
-GOTO SKIPP;
-SKIP_LTD
-IF &TIMELINE NE 'YTD'  GOTO SKIP_YTD;
WHERE CALYER = (&report_date_year);
-GOTO SKIPP;
-SKIP_YTD
-IF &TIMELINE NE 'MTD'   GOTO SKIP_MTD;
WHERE CALYER = (&report_date_year) AND CALMTH = (&report_date_month);
-GOTO SKIPP;
-SKIP_MTD
-IF &TIMELINE NE 'WTD'   GOTO SKIP_WTD;
-**WHERE CALYER = (&report_date_year) AND 
-**CALMTH = (&report_date_month) AND 
-**CALDAY = &report_date_day;
WHERE WEEK(TRIM(CHAR(CALMTH)) || '/' || TRIM(CHAR(CALDAY)) || '/' 
|| TRIM(CHAR(2000 + CALYER))) = WEEK('01/14/2006') AND CALYER = &report_date_year;              

-GOTO SKIPP;
-SKIP_WTD
-SKIPP
 
END
-REMOTE END
-MRNOEDIT END

and it generates the following error:

  

 (FOC14067) STATEMENT CONTAINS UNKNOWN SCALAR FUNCTION 'WEEK'
 (FOC1400) SQLCODE IS 14067 (HEX: 000036F3)
 : STATEMENT CONTAINS UNKNOWN SCALAR FUNCTION 'WEEK'
 L    (FOC1405) SQL PREPARE ERROR.
 -*-MRNOEDIT END

I am under the assumption that -remote sends the SQL to the remote server without checking the SQL syntax. I want the AS400 to execute the SQl so can use the WEEK function in the where clause.

This message has been edited. Last edited by: Lloyd,

Looks to me that it's not a WebFOCUS issue, but a SQL one. Have you tried to run the SQL via a DBMS client - Toad, AQT, Enterprise Manager, etc?

Francis, this code works just fine on my client, which is on my AS/400.

WEEK, I beleive is an ANSI standard, so this should work on most standard DBMS's.

Lloyd,

It looks as though that scalar function is not supported via WF. This is from the iWay SQL Reference manual -

The following scalar functions for queries that are candidates for Dialect Translation if
the RDBMS engine supports the scalar function type: CHAR, DATE, DAY, DAYS, DIGITS,
HEX, HOUR, LENGTH, MICROSECOND, MINUTE, MONTH, SECOND, TIME, TIMESTAMP,
VALUE, VARGRAPHIC, and YEAR.

Also, it looks as though you have a spurious END in your code snippet.

T

if the code works on your host, then run it on and from your host.
use a remote call to execute the host-resident fex, and not pass the entire server-resident fex up to the host.