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

[Kushal_Chawda]

try another one  which I have suggested

[andrea90casa]

Even the other one gave to me an error.

I tried to create qvd file with this script:

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 CxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxTOrdine.qvd(qvd);

Ordine:

SQL Select

CODICE,

CODMAGG,

VAROPZ,

NUMERO,

RIGA,

id,

Tipo,

pr,

NOTE,

VARIANTI

From dbo.PORDINE

;

Store Ordine into Ordine.qvd(qvd);

Drop Table Ordine;

Ordine:

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

From xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx\Ordine.qvd(qvd)

;

And everything it's correct, but when I insert the

Where Exists function it gave to me the error because it doesn't find the field Progetto_Numero and i don't know why.

[edmondo_tassi]

Why don't use left join or left keep?

[Peter_Cammaert · Accepted solution]

Try again but replace your current WHERE Exists condition with something like:

WHERE Exists(Progetto_Numero, NUMERO)

when loading the last table from QVD. To avoid getting an AUTOCONCATENATE in the last LOAD, you can add a NOCONCATENATE prefix to the LOAD as well.

[Peter_Cammaert]

Indeed, LEFT KEEP would be the better choice in this situation.

[andrea90casa]

Thanks to all,

The problem was the where sintax.

I don't know why but maybe he read the old name and so i wanted NUMERO and not Progetto_Numero as second parameter