Where Sql

[andrea90casa]

Hi at all,

I have this issue:

I load a table called TOrdine in this way:

TOrdine:

SQL Select

numero                                as Progetto_Numero,

CONFERMATO

From dbo.TORDINE

WHERE CONFERMATO = 1

;

I need to use where function because i filter only data in "numero" field with field "CONFERMATO" = 1.

Then i have a second table called Ordini:

Ordini:

SQL Select

CODICE                                        as COD_ORD,

CODMAGG,

VAROPZ                                       as VAROPZ_01,

NUMERO                                      as Progetto_Numero,

CODMAGG + VAROPZ                as COD_MAGG,

RIGA,

id,

Tipo,

pr,

NOTE                                             as QUANTITA,

VARIANTI

From dbo.PORDINE

;

I would like to create a unique table called Ordini with only records with CONFERMATO = 1 (so only the article of the first table).

I tried to use a preceding load like this one, but i gave to me errors:

TOrdine:

SQL Select

numero                                as Progetto_Numero,

CONFERMATO

From dbo.TORDINE

WHERE CONFERMATO = 1

;

Ordini:

Load

CODICE                                        as COD_ORD,

CODMAGG,

VAROPZ                                       as VAROPZ_01,

NUMERO                                      as Progetto_Numero,

CODMAGG + VAROPZ                as COD_MAGG,

RIGA,

id,

Tipo,

pr,

NOTE                                             as QUANTITA,

VARIANTI

WHERE EXISTS (Progetto_Numero);

SQL Select

CODICE                                        as COD_ORD,

CODMAGG,

VAROPZ                                       as VAROPZ_01,

NUMERO                                      as Progetto_Numero,

CODMAGG + VAROPZ                as COD_MAGG,

RIGA,

id,

Tipo,

pr,

NOTE                                             as QUANTITA,

VARIANTI

From dbo.PORDINE

;

DROP TABLE TOrdine;

----- Is there any way in order to get the expected result?----

Thanks in advance

Andrea

[Anil_Babu_Samineni]

Exist function is for Qlikview not SQL. You could check simple Preceding Load to achieve that? OR Resident

[its_anandrjs]

Try this could worked for you

TOrdine:

Load

numero                                as Progetto_Numero,

CONFERMATO

WHERE CONFERMATO = 1;

SQL Select

numero,

CONFERMATO

From dbo.TORDINE

WHERE CONFERMATO = 1;

Ordini:

Load

CODICE                                        as COD_ORD,

CODMAGG,

VAROPZ                                       as VAROPZ_01,

NUMERO                                      as Progetto_Numero,

CODMAGG + VAROPZ                as COD_MAGG,

RIGA,

id,

Tipo,

pr,

NOTE                                             as QUANTITA,

VARIANTI;

SQL Select

CODICE ,

CODMAGG,

VAROPZ,

NUMERO ,

CODMAGG + VAROPZ,

RIGA,

id,

Tipo,

pr,

NOTE ,

VARIANTI

From dbo.PORDINE;

[andrea90casa]

Hi Anand,

But in this script there isn't the where exist function so I can't filter the records in the second table according to the records of first table (with value of CONFERMATO = 1).

Or i missed something?

Andrea

[its_anandrjs]

Then try this way or you can create the QVD of this tables or resident load any one of this

TOrdine:

Load

numero                                as Progetto_Numero,

CONFERMATO

WHERE CONFERMATO = 1;

SQL Select

numero,

CONFERMATO

From dbo.TORDINE

WHERE CONFERMATO = 1;

Ordini:

Load

CODICE                                        as COD_ORD,

CODMAGG,

VAROPZ                                       as VAROPZ_01,

NUMERO,

CODMAGG + VAROPZ                as COD_MAGG,

RIGA,

id,

Tipo,

pr,

NOTE                                             as QUANTITA,

VARIANTI;

SQL Select

CODICE ,

CODMAGG,

VAROPZ,

NUMERO ,

CODMAGG + VAROPZ,

RIGA,

id,

Tipo,

pr,

NOTE ,

VARIANTI

From dbo.PORDINE;

//////////////////////////////////

Noconcatenate

Load

Progetto_Numero,CONFERMATO

Resident TOrdine;

Drop Table TOrdine;

FinalTable:
Load

*

Resident Ordini Where Exists(Progetto_Numero,NUMERO);

Drop Table Ordini;

[andrea90casa]

Hi Anand,

It is not working with the last script so i tried to change in qvd file. For example first table:

TOrdine:

SQL Select

numero,

CONFERMATO

From dbo.TORDINE

WHERE CONFERMATO = 1

;

Store TOrdine into TOrdine.qvd(qvd);

Drop Table TOrdine;

TOrdine:

LOAD

    numero                    as Progetto_Numero,

    CONFERMATO

From TOrdine.qvd;

But it doesn't find the field numero, do you know why?

[andrea90casa]

Sorry Anand,

I find now the issue.

So i can create the second qvd with the second table.

But then how can i filter by CONFERMATO?

[Peter_Cammaert]

Probably because a RDBMS may decide to return all field names in upper case (ANSI standard), even if you specify their names in lower case in the original SQL SELECT.

Replace the current LOAD ... FROM QVD with a LOAD * FROM QVD and check the field names in the QlikView Table Viewer.

[Kushal_Chawda]

TOrdine:

LOAD *;

SQL Select

numero                                as Progetto_Numero,

CONFERMATO

From dbo.TORDINE

WHERE CONFERMATO = 1

Ordini:

LOAD *

where exists(Progetto_Numero);

SQL Select

CODICE                                        as COD_ORD,

CODMAGG,

VAROPZ                                      as VAROPZ_01,

NUMERO                                      as Progetto_Numero,

CODMAGG + VAROPZ                as COD_MAGG,

RIGA,

id,

Tipo,

pr,

NOTE                                            as QUANTITA,

VARIANTI

From dbo.PORDINE;

drop table TOrdine;

OR

TOrdine:

LOAD *;

SQL Select

numero                                as Progetto_Numero,

CONFERMATO

From dbo.TORDINE

WHERE CONFERMATO = 1

Ordini1:

LOAD *;

SQL Select

CODICE                                        as COD_ORD,

CODMAGG,

VAROPZ                                      as VAROPZ_01,

NUMERO                                      as Progetto_Numero1

CODMAGG + VAROPZ                as COD_MAGG,

RIGA,

id,

Tipo,

pr,

NOTE                                            as QUANTITA,

VARIANTI

From dbo.PORDINE;

Ordini:

noconcatenate

LOAD *

resident Ordini1

where exists(Progetto_Numero,Progetto_Numero1);

drop tables Ordini1,TOrdine;

[andrea90casa]

Hi tried before a solution equal to the first one but it gaves to me this error:

Field not found - <Progetto_Numero>

SQL Select

CODICE,

CODMAGG,

VAROPZ,

NUMERO,

RIGA,

id,

Tipo,

pr,

NOTE,

VARIANTI

From dbo.PORDINE