PB DW report SELECT : ERROR - WHY ?

[semoG]

Hi there guys, I'm having a problem it seems that I have a problem with and DW SQL.

The same SQL query works flawless on toad returning 2900 lines, but when executed in PB it returns an error:

"SELECT ERROR:" with an ok button.

I have tried regenerate the DW, also tried rebuild it, I have double-checked my field limits and everything seems legit.

If I reduce the dw rownum to something like rownum <1500 it does work.

Can someone help me ?

thanks in advance.

 

[mbalent]

You need to provide more information such as the database and the select statement itself. Also, have you tried to capture the exact sql being sent to the database from within the datawindow sqlpreview event (use debugger)? Does this statement execute when submitted in Toad?

Matt

"Nature forges everything on the anvil of time"

http://www.anvil-of-time.com

 

[semoG]

Hi there, thanks for your answer.

The Database is Oracle.

Yes in toad the sql statement executes:

SELECT
M.NIS_RAD,
M.SEC_NIS,
M.NUM_ACU,
M.F_ACU,
(SELECT DESC_EST
FROM ESTADOS
WHERE ESTADO = M.EST_ACU) AS ESTADO_ACORDO,
M.F_RENOV,
M.CANT_CUOTAS,
(SELECT NVL(SUM(IMP_CUOTA),0)
FROM CUOTAS_IMPTE_FIJO
WHERE NIS_RAD = M.NIS_RAD AND SEC_NIS = M.SEC_NIS AND
NUM_ACU = M.NUM_ACU)/M.CANT_CUOTAS AS VALOR_MEDIO,
(SELECT DESC_TAR
FROM MTARIFAS
WHERE COD_TAR = S.COD_TAR) AS ESCALAO_CONSUMO,
(SELECT DESC_TIPO
FROM TIPOS
WHERE TIPO = C.TIP_CLI) AS TIPO_CLIENTE,
R.F_ACTUAL,
(SELECT NVL(SUM(IMP_CUOTA),0)
FROM CUOTAS_IMPTE_FIJO
WHERE NIS_RAD = M.NIS_RAD AND SEC_NIS = M.SEC_NIS AND NUM_ACU = M.NUM_ACU AND EST_CUOTA = 'CF006') AS TOTAL_PAGO,
(SELECT NVL(SUM(IMP_TOT_REC),0)
FROM RECIBOS
WHERE NIS_RAD = M.NIS_RAD AND SEC_NIS = M.SEC_NIS AND
NUM_ACU = M.NUM_ACU AND TIP_REC = 'TR016' AND
EST_ACT <> 'ER605') AS TOTAL_FACTURADO,
(SELECT IMP_CUOTA
FROM CUOTAS_IMPTE_FIJO
WHERE NIS_RAD = M.NIS_RAD AND SEC_NIS = M.SEC_NIS AND
NUM_ACU = (M.NUM_ACU + 1) AND NUM_CUOTA=1 ) AS NOVA_QUOTA
FROM MACUERDOS_IMPTE_FIJO M,
RECIBOS R,
CLIENTES C,
SUMCON S
WHERE
M.NIS_RAD = R.NIS_RAD(+) AND
M.SEC_NIS = R.SEC_NIS(+) AND
M.NUM_ACU = R.NUM_ACU(+) AND
M.NIS_RAD = S.NIS_RAD AND
M.SEC_NIS = S.SEC_NIS AND
S.COD_CLI = C.COD_CLI AND
R.TIP_REC(+) = 'TR015' AND m.est_acu IN ('PF001')
ORDER BY M.NIS_RAD, M.SEC_NIS, M.NUM_ACU

 

[semoG]

Just checked the sqlsyntax parameter in sqlpreview event and it seems okay.

I in fact executed that in toad and it works.

 

[miguelleeuwe]

the only thing that occurs to me, is to break down the statement in smaller parts in a test datawindow.

I'd start trying to see if it doesn't give you the error eliminating the outer joins. (there might be such a funny thing that they simply need a white space around the "(+)" as I vaguely seem to remember).

Do you have all EBF's installed?

sorry can't be of more use ...

regards,
Miguel L.

 

[miguelleeuwe]

Ahh! You said reducing the output using "rownum <1500" works.

wouldn't it simply be a memory problem?

try using the "retrieve as needed" property of the datawindow.

If that helps, your problem is that you haven't got enough memory available.

In TOAD, it'll work since TOAD might not retrieve all rows when executing your Select if I'm not wrong.

regards,
Miguel L.