topic SQL Statement doesn't work in QlikView

SQL Statement doesn't work

Tue, 03 Dec 2013 17:28:06 GMT

Hi to All!!!

i have one sql statement but it doesnt work on Qlikview, but it does in SQL:

Transac:

LOAD

IdentifEspacio,

HoraInicio_EC,

HoraFin_EC,

PautaId,

AnoMes,

HoraInicio_OC,

HoraFin_OC,

CantSalidas,

DuracionTot,

VigDesde_CV,

VigHasta_CV,

Frecuencia_CV,

Horario,

Version

;

SQL SELECT EspacioCont.IdentifEspacio, EspacioCont.HoraInicio as HoraInicio_EC, EspacioCont.HoraFin as HoraFin_EC,

OrdenadoCab.PautaId, OrdenadoCab.AnoMes, OrdenadoCab.HoraInicio as HoraInicio_OC, OrdenadoCab.HoraFin as HoraFin_OC, OrdenadoCab.CantSalidas, OrdenadoCab.DuracionTot,

CostoVersion.VigDesde as VigDesde_CV, CostoVersion.VigHasta as VigHasta_CV, CostoVersion.Frecuencia as Frecuencia_CV, CostoVersion.Horario, CostoVersion.Version

FROM

Publicidad.dbo.EspacioCont ,

Publicidad.dbo.OrdenadoCab ,

Publicidad.dbo.CostoVersion

where

EspacioCont.IdentifEspacio = 'AMER-INTR-PROG' and

OrdenadoCab.IdentifEspacio = EspacioCont.IdentifEspacio and

CostoVersion.IdentifEspacio = OrdenadoCab.IdentifEspacio and

CostoVersion.Version = (select max(CostoVersion.version)

from Publicidad.dbo.CostoVersion

where CostoVersion.IdentifEspacio = EspacioCont.IdentifEspacio

group by CostoVersion.IdentifEspacio)

order by

EspacioCont.IdentifEspacio;

Why doesnt return me any row??? In Sql the query HAVE 28 rows...

Any Idea?

Thanks to all!

Re: SQL Statement doesn't work

Tue, 03 Dec 2013 17:40:43 GMT

Hi Torress,

Instead of renaming in SQL script try to rename in LOAD statement.Remove alias in SQL use drirect fields to load and then rename the fields as per your requirement.

It may solve your problem.

Regards,

Vijay

Re: SQL Statement doesn't work

Anonymous — Tue, 03 Dec 2013 17:45:20 GMT

German,

What if your script contains this code but nothing else? Can you try it? I expect it to return the same 28 rows.

Check if there is something like INNER JOIN or INNER KEEP, or anything in your script that may affect records in the Transac table.

Regards,

Michael

Re: SQL Statement doesn't work

Tue, 03 Dec 2013 17:55:43 GMT

I made this change:

Transac:

LOAD

IdentifEspacio,

HoraInicio as HoraInicio_EC,

HoraFin as HoraFin_EC,

PautaId,

AnoMes,

HoraInicio as HoraInicio_OC,

HoraFin as HoraFin_OC,

CantSalidas,

DuracionTot,

VigDesde as VigDesde_CV,

VigHasta as VigHasta_CV,

Frecuencia as Frecuencia_CV,

Horario,

Version

;

SQL SELECT EspacioCont.IdentifEspacio, EspacioCont.HoraInicio, EspacioCont.HoraFin,

OrdenadoCab.PautaId, OrdenadoCab.AnoMes, OrdenadoCab.HoraInicio, OrdenadoCab.HoraFin, OrdenadoCab.CantSalidas, OrdenadoCab.DuracionTot,

CostoVersion.VigDesde, CostoVersion.VigHasta, CostoVersion.Frecuencia, CostoVersion.Horario, CostoVersion.Version

FROM

Publicidad.dbo.EspacioCont ,

Publicidad.dbo.OrdenadoCab ,

Publicidad.dbo.CostoVersion

where

EspacioCont.IdentifEspacio = 'AMER-INTR-PROG' and

OrdenadoCab.IdentifEspacio = EspacioCont.IdentifEspacio and

CostoVersion.IdentifEspacio = OrdenadoCab.IdentifEspacio and

CostoVersion.Version = (select max(CostoVersion.version)

from Publicidad.dbo.CostoVersion

where CostoVersion.IdentifEspacio = EspacioCont.IdentifEspacio

group by CostoVersion.IdentifEspacio)

order by

EspacioCont.IdentifEspacio;

And it doesnt solve the problem...

Thanks anyway Vijay

Re: SQL Statement doesn't work

Tue, 03 Dec 2013 18:04:37 GMT

Michael

Im trying with this instruction only... nothing else.

And if i bring only one table, it does return me results.

But when i add other table in the same query... no results...

Re: SQL Statement doesn't work

Anonymous — Tue, 03 Dec 2013 18:09:45 GMT

Not clear, sorry...

Is there any difference if you use only this (and nothing else in whole script):

LOAD

...

SQL SELECT

...

;

And if you use only this (and nothing else in whole script):

SQL SELECT

...

;

Re: SQL Statement doesn't work

Tue, 03 Dec 2013 18:25:21 GMT

i tryied and rerutns mi ODBC Error...

Re: SQL Statement doesn't work

Anonymous — Tue, 03 Dec 2013 18:28:44 GMT

What exactly the error is?

Is it for SQL SELECT without LOAD?

Re: SQL Statement doesn't work

Tue, 03 Dec 2013 18:36:50 GMT

The message say: Fail reading ODBC

Re: SQL Statement doesn't work

Anonymous — Tue, 03 Dec 2013 18:40:54 GMT

Hmm... Can't help with this... I assume you haven't changed the CONNECT statement.

ODBCs sometimes not reliable. I recommend OLEDB instead, if possible.

Re: SQL Statement doesn't work

Tue, 03 Dec 2013 18:43:29 GMT

When i uncomment the LOAD part... it works with no errors, but doesnt returns me any row...

But only the SQL statement returns me ODBC Error...

Thanks anyway Michael

Re: SQL Statement doesn't work

Anonymous — Tue, 03 Dec 2013 18:49:33 GMT

I see... It is a known bug - script doesn't return error and loads nothing, although there is an error. At least you found out that the error is on the SQL SELECT level.

If this SELECT works outside of QV, the problem most likely with ODBC. I hope you'll be able to resolve it by using OLEDB. Or fix the query if needed.

Regards,

Michael

Re: SQL Statement doesn't work

rbecher — Tue, 03 Dec 2013 18:51:25 GMT

Hi German,

I think the group by clause could be a problem. I would omit it because it could lead into a return of multiple rows which then could fail the condition CostoVersion.Version = max(CostoVersion.version):

CostoVersion.Version = (select max(CostoVersion.version)

from Publicidad.dbo.CostoVersion

where CostoVersion.IdentifEspacio = EspacioCont.IdentifEspacio

group by CostoVersion.IdentifEspacio)

- Ralf

Re: SQL Statement doesn't work

Anonymous — Tue, 03 Dec 2013 19:04:29 GMT

Makes sense, "group by" is not needed here.

My understanding was that the query worked in SQL. But it is data-sensitive, hence not reliable.

Re: SQL Statement doesn't work

rbecher — Tue, 03 Dec 2013 19:06:19 GMT

Maybe different handling with ODBC then in database itself..

Re: SQL Statement doesn't work

Anonymous — Tue, 03 Dec 2013 19:09:13 GMT

Exactly what I think

Re: SQL Statement doesn't work

Tue, 03 Dec 2013 19:13:23 GMT

But... it works well in SQL... why doesnt in Qlikview????

Re: SQL Statement doesn't work

Tue, 03 Dec 2013 19:32:27 GMT

Nop... i had try with OLEDB and doesnt work either....

Re: SQL Statement doesn't work

Anonymous — Tue, 03 Dec 2013 19:32:47 GMT

Because, as Ralf Becher said, "different handling with ODBC then in database itself".

Try OLEDB connection.

Re: SQL Statement doesn't work

Tue, 03 Dec 2013 19:44:03 GMT

Can you post sample data from three table and sample qvw