I get this error when i run this query

SQL DB2
SELECT
SUM(NET_RDMP_CT) AS RDM_COUNT,
SUM(NET_RDMP_AM) AS RDM_AMT,
CNCY_NM,
A.PRMT_ID AS PROMO_ID,
PRMT_DSCR_TX AS PROMO_DSCR,
YEAR(PRMT_CRET_DT) AS PROMO_DATE,
'YEAR ' AS MEASURE
FROM HOPSRPT.VL_VW_ENHANCED_TRAN_LOG A
INNER JOIN HOPSRPT.VL_TB_BASE_CALENDAR B
ON A.TRAN_CLND_VERS_ID = B.CLND_ID
INNER JOIN HOPSRPT.VL_TB_PROMO C
ON A.PRMT_ID = C.PRMT_ID
INNER JOIN HOPSRPT.VL_TB_CURRENCY D
ON D.CNCY_CD = '&CNCY'
WHERE A.CNSR_ID = '&CONSORTIUM'
AND B.CLND_DT = '&FORTESTING'
AND A.TRAN_TYPE_CD IN ('1','2')
AND A.TRAN_RESN_CD = '0'
AND (A.TRAN_BSNS_DT >= B.CLND_YEAR_BGN_DT AND A.TRAN_BSNS_DT <= '&FORTESTING')
GROUP BY CNCY_NM, PRMT_CRET_DT, A.PRMT_ID, PRMT_DSCR_TX;
-*
TABLE FILE SQLOUT
PRINT
RDM_COUNT
RDM_AMT
CNCY_NM
PROMO_ID
PROMO_DSCR
PROMO_DATE
MEASURE
ON TABLE HOLD AS HLDBORYL FORMAT FOCUS
END


(FOC1400) SQLCODE IS -420 (HEX: FFFFFE5C)
: [22018] [IBM][CLI Driver][DB2/AIX64] SQL0420N Invalid character found i
: n a character string argument of the function "DECFLOAT". SQLSTATE=2201
: 8
(FOC1407) SQL FETCH CURSOR ERROR. : SQLOUT
(FOC1400) SQLCODE IS -420 (HEX: FFFFFE5C)
: [22018] [IBM][CLI Driver][DB2/AIX64] SQL0420N Invalid character found i
: n a character string argument of the function "DECFLOAT". SQLSTATE=2201
: 8
(FOC1407) SQL FETCH CURSOR ERROR. : SQLOUT
(FOC1400) SQLCODE IS -420 (HEX: FFFFFE5C)
: [22018] [IBM][CLI Driver][DB2/AIX64] SQL0420N Invalid character found i
: n a character string argument of the function "DECFLOAT". SQLSTATE=2201
: 8
(FOC1407) SQL FETCH CURSOR ERROR. : SQLOUT



when I change the CNCY_NM to CNCY_CD... my report works fine
Please help

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

Which values do you have for &CNCY, &CONSORTIUM and &FORTESTING?

Doing a

-SET ECHO=ON;

before your code and running again will display the resolved SQL passthru.

As to why changing CNCY_NM to CNCY_CD works ... well, only you know your database tables and field datatypes. Can you really interchange them in your logic like that? By the way, where exacty are you changing it? In the JOIN condition or in the list of fields to retrieve?

Posting your table structure (fields, datatypes) might also help us to better understand what's going on.

Please, don't forget to use

[code]

tags when posting your code and sample output. It's really difficult to read when you just paste your code as part of the message. You can use the nice </> button in the tiny toolbar that appears when posting your message.

-DEFAULT &CONSORTIUM='301';
-SET &FORTESTING='2010-03-20';
-SET &CNCY= IF TRAN_BASE_CNCY_CD EQ '0' OR TRAN_BASE_CNCY_CD IS MISSING THEN TRAN_LOCL_CNCY_CD ELSE TRAN_BASE_CNCY_CD;


CNCY_NM is A60v
CNCY_CD is I6

how to get the data structure of a table through sql passthru in webfocus.

I dont have any sqlplus tool to do a describe on the table.
there is no synonym created for that table

for querying i do

 
sql db2
select * from table;

table file sqlout
print *
on table set online-fmt html
end 

Based on this:

quote:

ON D.CNCY_CD = '&CNCY'

and this:

quote:

CNCY_NM is A60v
CNCY_CD is I6

Your JOIN condition would be wrong as you are comparing CNCY_CD which is a numeric field with a string value.

In addition to that, the following line of code is semantically wrong:

-SET &CNCY= IF TRAN_BASE_CNCY_CD EQ '0' OR TRAN_BASE_CNCY_CD IS MISSING THEN TRAN_LOCL_CNCY_CD ELSE TRAN_BASE_CNCY_CD;

You are trying to use a database field inside a Dialog Manager -SET structure. That just can't be done. Dialog Manager is some kind of "meta language" that gets resolved *before* WebFOCUS processes the code in the stack.

If you did as I suggested initially, that is, adding -SET &ECHO=ON; you would see what *actual* code is WebFOCUS sending to DB2 and therefore making your statement fail. I'll take a guess, that SQL line is probably being resolved as:

.
.
INNER JOIN HOPSRPT.VL_TB_CURRENCY D
ON D.CNCY_CD = 'TRAN_BASE_CNCY_CD'  <-- See this?
.
.

That will obviously fail as a numeric field cannot be compared with a string value. This is just a syntax error. In reality though, you have a semantic error in the way you are attempting to build your code and to approach your problem. As mentioned, you're attempting to mix Dialog Manager with WebFOCUS code in a way they were not designed to be used. They work at different "context levels".

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

quote:

how to get the data structure of a table through sql passthru in webfocus

I don't think that can be done unless your database supports some kind of access to the internal data dictionary (such as Oracle does) that can be queried via SQL, but that information about your table structure is not relevant now. See my post above for what I think is the real issue.

quote:

I dont have any sqlplus tool to do a describe on the table.

SQL*Plus is exclusive to Oracle so you wouldn't be able to use it to connect to DB2. If you don't have access to a particular DB2 client tool, you could try some of the very nice freeware database browser tools to access your database. Take a look at SQuirreL which I've used in the past to connect to DB2 databases via JDBC.

If i am not looking for the currency name and just want the currency code my query is working fine
all i have to do is just replace the CNCY_NM to CNCY_CD

the data type for TRAN_BASE_CNCY_CD is also I6.