Can anyone give me a hand on why i get this error from the following DB2 query.
Its on the cast command, but im not sure why? It works fine in DB2 but not in the query for WF..



(FOC1400) SQLCODE IS -170 (HEX: FFFFFF56)
: [42605] [IBM][CLI Driver][DB2] SQL0170N The number of arguments for fun
: ction "CHAR" is incorrect. SQLSTATE=42605
L (FOC1405) SQL PREPARE ERROR.
(FOC205) THE DESCRIPTION CANNOT BE FOUND FOR FILE NAMED: CONFIG1
BYPASSING TO END OF COMMAND
(FOC205) THE DESCRIPTION CANNOT BE FOUND FOR FILE NAMED: CONFIG1
BYPASSING TO END OF COMMAND

select case_ID,S.STAGE_DESC, O.office_desc || ' - ' || O.office_Code as strOffice, Closed_Date,
cast(Month(Closed_Date) AS char(2)) || '-' || cast(year(Closed_Date) AS char(4)) Date1,

quote:

SQL0170N The number of arguments for function "CHAR" is incorrect. SQLSTATE=42605

looks like this is the main problem, difficult to know what is wrong without the query and the used formulas

First of all, you must use the SQL concatenation character '+' and not the FOCUS one '|'.

Francis this is hitting db2 tables so the '|' are a necessity?

Im fairly sure the error is on
cast(Month(Closed_Date) AS char(2)) || '-' || cast(year(Closed_Date) AS char(4)) Date1

as the query worked fine until i am trying to select the month and year form the 'Closed_date' value coming in. Im just not sure if there is a focus command to replace the cast command.

Erney,

Don't know if Closed_Date is a date-time stamped column or not, I'll assume it is. Also, you have a JOIN somewhere based on your 2nd and 3rd SELECT columns, why not the other columns??

Anyways,

select
case_ID,
S.STAGE_DESC,
O.office_desc || ' - ' || O.office_Code as strOffice,
DATE(Closed_Date),
cast(Month(Closed_Date) AS char(2)) || '-' || cast(year(Closed_Date) AS char(4)) as Date1,

OR

SUBSTRING(Closed_Date 6,7) + '-' + SUBSTRING(Closed_Date 1,4) AS Date1


Syntax, probably...

select 
case_ID,
S.STAGE_DESC, 
O.office_desc || ' - ' || O.office_Code as strOffice, 
Closed_Date,
cast(Month(Closed_Date) AS char(2)) || '-' || cast(year(Closed_Date) AS char(4)) Date1,
...

Are you sure that '|' is DB2 SQL syntax?

quote:

cast(Month(Closed_Date) AS char(2)) || '-' || cast(year(Closed_Date) AS char(4)) as Date1,

I attached the whole query just for reference
but i am still recieveing a error.
The date is formatted date-time. Thanks for the help, im getting closer.


(FOC14027) UNRECOGNIZED MINIPARSER COMMAND 'APP'
(FOC1400) SQLCODE IS -170 (HEX: FFFFFF56)
: [42605] [IBM][CLI Driver][DB2] SQL0170N The number of arguments for fun
: ction "CHAR" is incorrect. SQLSTATE=42605
L (FOC1405) SQL PREPARE ERROR.



SELECT CASE_ID,S.STAGE_DESC, O.OFFICE_DESC || ' - ' || O.OFFICE_CODE AS STROFFICE, DATE(CLOSED_DATE),
T.CASE_DESC,U.ESIGNATURE_PIN,
cast(Month(Closed_Date) AS char(2)) || '-' || cast(year(Closed_Date) AS char(4)) as Date1,
O.OFFICE_ID,U.FIRST_NAME,U.LAST_NAME,O.OFFICE_DESC FROM DB2ADMIN_TKCASE C
INNER JOIN DB2ADMIN_TKSTAGE S ON C.STAGE_ID = S.STAGE_ID
INNER JOIN DB2ADMIN_TKUSERS U ON C.USER_ID = U.USER_ID
INNER JOIN DB2ADMIN_TKOFFIC O ON O.OFFICE_ID = C.OFFICE_ID
INNER JOIN DB2ADMIN_TKCASET T ON T.CASE_TYPE_ID = C.CASE_TYPE_ID
WHERE C.CASE_TYPE_ID IN(1,2)
AND DATE(CLOSED_DATE) BETWEEN '07/01/2006' AND '10/01/2007'
AND U.LAST_NAME = 'Billy'
AND U.FIRST_NAME = 'Bob'
GROUP BY O.OFFICE_ID,O.OFFICE_DESC,O.OFFICE_CODE,U.FIRST_NAME,U.LAST_NAME,CASE_ID,
CLOSED_DATE,T.CASE_DESC,S.STAGE_DESC,U.ESIGNATURE_PIN;

Hey Francis,

No, never used the concat bar in DB2, although, I never tried; always used +, as in, the SUBSTRING example, AND, in your suggestion....

Weird... I have tried adding the line SUBSTRING(Closed_Date 6,7) + '-' + SUBSTRING(Closed_Date 1,4) AS Date1

And got the following error. It seems like i have my syntax messed up somewhere.


(FOC14069) SYNTAX ERROR ON LINE 3 AT '6' -- Expected ')'

SQL
SELECT CASE_ID,S.STAGE_DESC, O.OFFICE_DESC || ' - ' || O.OFFICE_CODE AS STROFFICE, DATE(CLOSED_DATE),
T.CASE_DESC,U.ESIGNATURE_PIN,
SUBSTRING(Closed_Date 6,7) + '-' + SUBSTRING(Closed_Date 1,4) AS Date1,
O.OFFICE_ID,U.FIRST_NAME,U.LAST_NAME,O.OFFICE_DESC FROM DB2ADMIN_TKCASE C
INNER JOIN DB2ADMIN_TKSTAGE S ON C.STAGE_ID = S.STAGE_ID
INNER JOIN DB2ADMIN_TKUSERS U ON C.USER_ID = U.USER_ID
INNER JOIN DB2ADMIN_TKOFFIC O ON O.OFFICE_ID = C.OFFICE_ID
INNER JOIN DB2ADMIN_TKCASET T ON T.CASE_TYPE_ID = C.CASE_TYPE_ID
WHERE C.CASE_TYPE_ID IN(1,2)
AND DATE(CLOSED_DATE) BETWEEN '07/01/2006' AND '10/01/2007'
AND U.LAST_NAME = 'Billy'
AND U.FIRST_NAME = 'Bob'
GROUP BY O.OFFICE_ID,O.OFFICE_DESC,O.OFFICE_CODE,U.FIRST_NAME,U.LAST_NAME,CASE_ID,
CLOSED_DATE,T.CASE_DESC,S.STAGE_DESC,U.ESIGNATURE_PIN;

Erney,

Chasing your tail - split up the code then CONCAT them:


DATE(CLOSED_DATE) AS 'C_DATE'
cast(Month(C_DATE) AS char(2)) AS 'MTH',
cast(year(C_DATE) AS char(4)) AS 'YR',
MTH CONCAT ‘-‘ CONCAT YR AS 'Date1',

Also, which file is Closed_Date in, I assume C table???

Erney,

MY BAD, I gave bad info:

SUBSTR(Closed_Date 6,2) + '-' + SUBSTR((Closed_Date 1,4) AS Date1

Sorry!!

Did a quick GOOGLE search for you, should help you - need to know how the date is being stored/extracted:

DB2 Info

Edit: Add URL

This message has been edited. Last edited by: Tom Flynn,

quote:

SUBSTR(Closed_Date 6,2) + '-' + SUBSTR((Closed_Date 1,4) AS Date1

I changed it and still cannot get it to work


(FOC14069) SYNTAX ERROR ON LINE 3 AT '6' -- Expected ')'
(FOC205) THE DESCRIPTION CANNOT BE FOUND FOR FILE NAMED: CONFIG1
BYPASSING TO END OF COMMAND
(FOC205) THE DESCRIPTION CANNOT BE FOUND FOR FILE NAMED: CONFIG1
BYPASSING TO END OF COMMAND



SQL
select case_ID,S.STAGE_DESC, O.office_desc || ' - ' || O.office_Code as strOffice,
C.Closed_Date,T.Case_Desc,U.Esignature_pin,O.Office_ID,U.First_Name,U.Last_Name,O.Office_Desc,
SUBSTR(C.Closed_Date 6,2) + '-' + SUBSTR(C.Closed_Date 1,4) AS Date1
from db2admin_tkcase C
inner join db2admin_tkstage S on C.Stage_ID = S.Stage_ID
inner join db2admin_tkusers U on C.User_Id = U.User_Id
inner join db2admin_tkoffic O on O.Office_Id = C.Office_ID
inner join db2admin_tkcaset T on T.Case_Type_Id = C.Case_Type_Id
where C.CASE_TYPE_ID IN(1,2) and DATE(C.closed_date) BETWEEN '07/01/2006' AND '10/01/2007'
AND U.FIRST_NAME like 'Billy' AND U.LAST_NAME like 'Bob'
group by Closed_Date,o.office_ID,O.Office_Desc,O.OFFICE_CODE,U.First_Name,U.Last_Name,case_ID,T.Case_Desc,S.STAGE_DESC,U.Esignature_pin;


Im going to have to try and figure out a different way to do this, guys, seriously, thanks a million for the help.

quote:

(SUBSTR(C.Closed_Date 6,2) + '-' + SUBSTR(C.Closed_Date 1,4)) AS Date1

how about these brackets....

I'm not too sure why we're trying to solve a DB2 SQL issue here, but I think you may require a refresher on DB2 SQL.

The syntax for the SUBSTR function looks wrong - you're missing a comma.

SUBSTR(C.Closed_Date 6,2)

should be

SUBSTR(C.Closed_Date, 6, 2)

This book will help:

DB2 SQL Reference

Francis,

GOOD CATCH, MY TYPO:

quote:

SUBSTR(C.Closed_Date 6,2)
should be
SUBSTR(C.Closed_Date, 6, 2)

But, for your other question,

We assist with SQL because WF now utilizes SQL extensively.
We assist with Javascript because WF now utilizes JS extensively.
We assist with HTML/DHTML because WF now utilizes HTML/DHTML extensively.
We assist with XML because WF now utilizes XML extensively.

Not trying to be rude, callous, insensitive, etc. but WF is the reason for non-FOCUS related questions.

Tom

P.S. Congrats on the 2000 posts

That's why I use WF, never have to learn all the different S(tandard)QL dialects for MySQL, MSQL, ORACLE, Sybase and all the others....

Just TABLE FILE xxx....

(I know it is not that easy....)

Tom, of course, you're right. But when the issue is pure DB2 SQL and unrelated to WebFOCUS, perhaps some digging around on the web for answers is the smart way to go. I still think the | is not a concatenation character in DB2 SQL.

Agreed. Next...

Guess we all made a "mountian out of a molehill".

I fixed the problem with a simple define statement.

Define
NewDate/MYY = Closed_Date;


By the way, Francis, if you were to run my original DB2 query it would run perfectly fine against DB2, but copy and past the exact query into web focus, change the table names to match web focus synonyms, and web focus did not like it, at all. Why im not sure.

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

Erney,

No, just a hill.

Francis is one of the most diverse contributors to this forum; his solutions are not only efficient, they assist a tremendous number of other programmers in their daily tasks.

I always try to assist in SQL because WebFOCUS/FOCUS code is actually converted to SQL against RDBMS. I also mentor on using SQL exclusively when crossing platforms. I thought that may be what you were doing; Use SQL to extract, WebFOCUS to produce. Obviously, with your final result, that actually may be the case.

At any rate, don't become a hermit; too much info here to assist...

P.S. Don't know why SUBSTR wouldn't have worked, either!!!!

This message has been edited. Last edited by: Tom Flynn,

One thing i forgot to do was thank Frank, Francis and Tom. Thanks guys, you are the best!

Erney,

I'm wondering why you are using the WebFOCUS synonyms in your SQL passthru request - that may be the reason why the SQL works outside WebFOCUS but not inside WebFOCUS.

The following code accesses a DB2 table not using a WF synonym

-SET &ECHO=ALL;
SET SQLENGINE=DB2
-RUN
SQL
SELECT 
T1.PERIOD_END_DT, T1.DAY_OF_WEEK, T1.DAY_OF_MTH
FROM BASEL.TIME_D T1
FETCH FIRST 100 ROWS ONLY
FOR FETCH ONLY;
END
-RUN

When I run the following code which references a WF synonym, I get a syntax error

(FOC14069) SYNTAX ERROR ON LINE 4 AT 'FETCH' -- Semi-colon or END expected

probably because FETCH is not recognized when running SQL against a WF synonym

-SET &ECHO=ALL;
SQL
SELECT 
T1.PERIOD_END_DT, T1.DAY_OF_WEEK, T1.DAY_OF_MTH
FROM BASEL_TIME_D T1
FETCH FIRST 100 ROWS ONLY
FOR FETCH ONLY;
END
-RUN

In your code, do you specify the engine? If not, then perhaps the SQL request is going against the WF synonym and WF is expecting a particular flavour of SQL that isn't DB2.

I always set the engine and then use the engine's SQL syntax.

(You may need to set the server as well, if you have multiple servers:

SQL SET SERVER data-adapter-name

Cheers,

This message has been edited. Last edited by: Francis Mariani,

quote:

SET SQLENGINE=DB2

This is very interesting. I am curious to see if the sqlengine is actually set by the WF admin on the wf server?. Since we are not admins we do not get to play around with the settings of the server itself.

We are running about 75 reports everyday out to the report library through report caster, using this SQL, so its not like we havent done this before. But we were always wondering why we had to change the db2 table names to match the synonyms on webfocus.

I am going to try and add this sqlengine=db2 command to a report and see how it goes.

Thanks a bunch

Well spotted Francis!

I am guessing that wf permits ANSI standard sql against ANY type of wf synonym (including XFOCUS files as we saw in an earlier posting).

The CAST is certainly DB2 specific (never seen in Oracle or SQL Server) and thus is not recognised.

Erney, as Francis says, just change the table name from the wf synonym name (tablename only) to the db2 name schema.tablename.

This probably explains a whole raft of weirdness!

quote:

I am curious to see if the sqlengine is actually set by the WF admin on the wf server?.

Erney this will be set in EDASPROF.PRF probably by your wf admin. You can override this by coding it explicitly in your code.

John,

Almost let this go:

quote:

The CAST is certainly DB2 specific (never seen in Oracle or SQL Server) and thus is not recognised.

CAST is a universal SQL89,92,99 function:

ORACLE: CAST(RETAIL_COST - DEALER_COST as decimal(9,2)) AS 'TOT_PROFIT‘
MSSQL : CAST(RETAIL_COST - DEALER_COST as decimal(9,2)) AS 'TOT_PROFIT‘
DB2 : CAST(RETAIL_COST - DEALER_COST as decimal(9,2)) AS 'TOT_PROFIT‘

ORACLE: ('$' || TOT_PROFIT) PROFIT --- ORACLE implicity converts numeric data to character strings when Concatenating character strings.
MSSQl : '$' + CAST(TOT_PROFIT as VARCHAR(10)) PROFIT
DB2 : CONCAT('$', CAST(TOT_PROFIT as CHAR(12))) PROFIT

FYI...

You're spot on there Tom.

What I meant to say...

WebFOCUS SQL is ANSI Level 2 SQL compliant (how this translates to various years standards I hope someone can enlighten me).

It does not include the CAST amongst the list of scalar functions it supports.

As explained in the Creating Report manual, SQL Chapter

We use SQL passthru against DB2 UDB. The syntax of ||'-'|| is a proper manner of concatenation. Hope this helps,

Kevin

Yes thank you, it appears to be. I did not know that.

it's sql99, use two pipes 'a' || '-' || 'b'
yields a-b

+ is a ms sql operator as far as I know. IBM thinks it's a plus sign.

sql99 will implicitly typecast your values for you in a concatenation, so you could just do this
do a month(datefield) || '-' || year(datefield)

but, when it implicitly typecasts a date (and this is in *blah* db2 UDB for an iseries) it keeps the month at 8 flippin characters, padding it was what seem to be ASCII space characters (don't get me started on a rant about the UDB db2 version)
so,
select
trim(char(month(current_date))) || '-' || trim(char(year(current_date)))
from system.sysdummyx

This works on an iSeries with *blah* DB2 UDB, so it WILL work on an actual database DB2 database.

This message has been edited. Last edited by: Jason K.,