Hi,
I am trying to write a webfocus table file statement but I have a define that references a sql function. I am getting an error on the syntax


-DEFAULT &DT_FROM = '07/01/2013';
-DEFAULT &DT_TO = '12/31/2013';

DEFINE FILE FACT
DATE_MYY/A6MYY = SQL.CONVERT(VARCHAR(7), REPORTDAY, 111);
END

TABLE FILE FACT
SUM
AVE.SCORE
BY DATE_MYY
WHERE REPORTDAY GE '&DT_FROM'
WHERE REPORTDAY LE '&DT_TO'
END


I get the error.
(FOC36342) INVALID TYPE OF ARGUMENT #1 FOR SQL. FUNCTION SQLMSS.CONVERT

But this function runs in SQl server.
I am actually trying to convert a datetime field REPORTDAY to myy format and do the aggregation on SQL server. I do not want a total passthu.
Any ideas??????????

This message has been edited. Last edited by: <Kathryn Henning>,

I believe that this is a known issue. It may be worth opening a case.

Doesn't one use the general DB_EXPR function when passing this type of operation to SQL?

Seen this before and it appears to be something to do with the nested braces. So because the CONVERT has braces as does the VARCHAR the SQL function appears to have issues.

I normally break SQL functions into discreet chunks, passing the result into the next etc. but with this type of call your options are very limited. I know that the datatype has an optional length but I believe that you still require the braces (e.g. varchar() ) so that doesn't get you any further either.

So I concur with Alan, best to open a case.

T

ISTR that with a similar issue we got the advice to set:

ENGINE SQLMSS SET CURSORS CLIENT

Thanks for the replies. The set cursors client did not help.I am opening a case. This is not resolved and seems to be a hard one to crack.