Hi experts,

I have a problem in convertion to SQL a focus field using FTOA:

N_EMPREGADO2/P6 = N_EMPREGADO;
N_EMPREGADO_NOVO/A6 = IF N_EMPREGADO2 EQ '-1' THEN ' ' ELSE FTOA(N_EMPREGADO2, '(D6c)','A6');
N_PE_ORIG/P11=N_PE;
N_CLIENTE2/I11=N_CLIENTE;
N_CLIENTE_NOVO/A11=FTOA(N_CLIENTE2, '(D11c)','A11');
NM_EMPREGADO/A200 = IF N_EMPREGADO2 EQ '-1' THEN ' ' ELSE TMH00041.NM_PE;

When I put these fields in a table file:

TABLE FILE X
PRINT
N_EMPREGADO_NOVO
NM_EMPREGADO
BY N_CLIENTE_NOVO
END

I get in log that FTOA fields cannot be converted to SQL.

Is there any way to overcome this?

Thanks in advance.

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

I assume these are DEFINE columns. I would make them COMPUTE columns instead.

I have:

DEFINE FILE TIG00514 ADD
N_EMPREGADO2/P6 = ....
N_EMPREGADO_NOVO/A6 = ...
N_CLIENTE2/I11=...
N_CLIENTE_NOVO/A11=FTOA(N_CLIENTE2, '(D11c)','A11');
NM_EMPREGADO/A200 = ...
END

TABLE FILE TIG00514
SUM
NM_PE
N_EMPREGADO_NOVO
NM_EMPREGADO
COMPUTE
M_SLDO_COLOC/D19.2 = ....
BY N_CLIENTE_NOVO
WHERE Z_DIA EQ '20130814';
WHERE TMH00041.Z_INICIO LE '20130814' AND TMH00041.Z_FIM GT '20130814';
WHERE I_VARZ_DIRZ_EMP EQ 'S' OR I_VARZ_DIRZ_PRTL EQ 'S' OR I_VARZ_DGE EQ 'S' OR I_VARZ_DBI EQ 'S' OR I_VARZ_DRC EQ 'S';
IF I_DPNZ_INF_MES EQ 'S'
WHERE C_UO NE '09287';
ON TABLE SET CDN ON
ON TABLE HOLD AS FINAL1TEMP
END

You're saying is that i would put the fields defined below the compute?

TABLE FILE TIG00514
SUM
NM_PE
N_EMPREGADO_NOVO
NM_EMPREGADO
COMPUTE
N_EMPREGADO2/P6 = ....
N_EMPREGADO_NOVO/A6 = ...
N_CLIENTE2/I11=...
N_CLIENTE_NOVO/A11=FTOA(N_CLIENTE2, '(D11c)','A11');
NM_EMPREGADO/A200 = ...
M_SLDO_COLOC/D19.2 = ....
BY N_CLIENTE_NOVO
WHERE Z_DIA EQ '20130814';
WHERE TMH00041.Z_INICIO LE '20130814' AND TMH00041.Z_FIM GT '20130814';
WHERE I_VARZ_DIRZ_EMP EQ 'S' OR I_VARZ_DIRZ_PRTL EQ 'S' OR I_VARZ_DGE EQ 'S' OR I_VARZ_DBI EQ 'S' OR I_VARZ_DRC EQ 'S';
IF I_DPNZ_INF_MES EQ 'S'
WHERE C_UO NE '09287';
ON TABLE SET CDN ON
ON TABLE HOLD AS FINAL1TEMP
END

quote:

N_CLIENTE2/I11=...
N_CLIENTE_NOVO/A11=FTOA(N_CLIENTE2, '(D11c)','A11');

Yes, that's his suggestion.

I think, you are missing this part.

N_CLIENTE2/I11=...
N_CLIENTE2_A/D11c=N_CLIENTE2
N_CLIENTE_NOVO/A11=FTOA(N_CLIENTE2_A, '(D11c)','A11');

as2010,

The reason FTOA does not get translated to SQL is because it does not exist as a part of the adapter. Unfortunately I don't think there is a way for us to over come this, but you could put in a New Feature request for IBI to enhance the adapter for FTOA.

As Francis said, you should be able to make the field a compute and where as the FTOA won't translate to SQL, in theory it should process the request with the correct Group By's etc and do the function after it returns to the Reporting Server without bringing back extra rows.

I have found that this doesn't always work like that and what I have done to resolve it in the past is I would hold my data and apply FTOA on the second pass of the hold file. This way I can guarentee the most efficient execution of the query. Also if this is a field that you find you are converting often, perhaps consider creating a virtual field that would cast your field to the needed data type. As long as your DB supports that (Oravle does).

You could try replacing FTOA with a DB_EXPR function call to a SQL CAST function:

 N_CLIENTE_NOVO/A11 WITH NM_EMPREGADO (or other real field) = DB_EXPR(CAST("N_CLIENTE2" AS CHAR(11)));

Hi,
DB_EXPR solved my problem since i'vw upgraded to 7706M.
Now i have another question
I would like to do something like this: where a in(select b from fileB). As far as i know i could use something like this:

WHERE a IN FILE fileB;
But since there is no "a" field in fileB is there any way to do this?

thanks one more time.

If you cannot join the two tables, then you might be able to dump all b values into a SAVE file and use it in your WHERE clause. It depends on how big fileB is. If you run into limits on the number of B_VALS being passed, the NATVFLG setting will raise that limit.

TABLE FILE fileB
 BY b
 ON TABLE SAVE AS B_VALS
END

TABLE FILE fileA
 PRINT .....
 BY ....
 WHERE a IN FILE B_VALS ;
-*ON TABLE SET NATVFLG 256
END

I've tried to use join instead but i can't generate a outer join: (TIG00540 and TDW032 are two SQL masters):

JOIN CLEAR *

JOIN
TIG00540.C_UO_ORIG IN TIG00540 TO
TDW032.C_UO IN TDW032 AS J7
END


TABLE FILE TIG00540
PRINT *
WHERE Z_DIA EQ '20150528';
WHERE (I_NOVO_OPE EQ 'S' OR I_NOVO_OPE EQ '-');
WHERE M_CRED_VNCD GE 0.5;
WHERE TIG00540.C_UO_ORIG = WF_TDW032_RDT0295_20150608.C_UO OR C_UO_ORIG EQ '00993' OR '09420';
ON TABLE SET ASNAMES ON
ON TABLE HOLD AS TIG540 FORMAT SQLNEZ
END

The sql trace shows a inner join:

FROM
TIG00540 T1,
TDW032 T2
WHERE
(T2."C_UO" = T1."C_UO_ORIG")

Did you try specifying an outer join?

JOIN LEFT_OUTER
TIG00540.C_UO_ORIG IN TIG00540 TO
TDW032.C_UO IN TDW032 AS J7
END

The problem seems to be the

WHERE TIG00540.C_UO_ORIG=WF_TDW032_RDT0295_20150608.C_UO

condition. This leads to a inner join, if i delete this condition the left join is generated
since I need the

OR C_UO_ORIG EQ '00993' OR '09420';

I need to do a left join.

Thanks one more time.

Try adding this:

SET SHORTPATH = SQL

that works.
thank you