topic Re: SQL query in QlikView

SQL query

spividori — Fri, 13 Oct 2017 20:00:07 GMT

Hi.

On a client they updated the version of SQL and began to fail the following SQL:

Select

USR_TVESTA.USR_TVESTA_USERID,

USR_TVESTA.USR_TVESTA_FECHA,

USR_USUWEB.USR_USUWEB_NOMBRE,

USR_USUWEB.USR_USUWEB_USUTIP,

VTMCLH.VTMCLH_NROCTA,

VTTVND.VTTVND_VNDDOR,

VTTVND.VTTVND_DESCRP,

(COUNT(*)) as CantIng

From USR_TVESTA,

USR_USUWEB,

VTMCLH,

VTTVND

Where USR_TVESTA.USR_TVESTA_USERID = USR_USUWEB.USR_USUWEB_NOMBRE

And USR_USUWEB.USR_USUWEB_NROCTA *= VTMCLH.VTMCLH_NROCTA

And USR_USUWEB.USR_USUWEB_VNDDOR *= VTTVND.VTTVND_VNDDOR

Group By USR_TVESTA.USR_TVESTA_USERID,

USR_TVESTA.USR_TVESTA_FECHA,

USR_USUWEB.USR_USUWEB_NOMBRE,

USR_USUWEB.USR_USUWEB_USUTIP,

USR_USUWEB.USR_USUWEB_NROCTA,

VTMCLH.VTMCLH_NROCTA,

VTTVND.VTTVND_VNDDOR,

VTTVND.VTTVND_DESCRP,

USR_USUWEB.USR_USUWEB_VNDDOR;

I investigate about the error and the sql does not recognize "* =".

I tried several alternatives but without success. Some guró of sql that help me.

Regards.

Re: SQL query

vishsaggi — Fri, 13 Oct 2017 20:49:42 GMT

I think this type of coding has become obsolete. May be try using joins as mentioned here.?

SQL Server *= Operator? - Stack Overflow

I would use proper left or right outer joins in your script. Comma separated table names in your from clause is an old coding style and it confuses us too. So just use left of Inner joins properly or using Join with conditions on LIke

SELECT .....

FROM

Table1

Left Join

Table2 on Table1.ID = Table2.ID .....;

Re: SQL query

spividori — Tue, 17 Oct 2017 14:53:49 GMT

Hi.

Thanks for answering.

I have tried but I can not make them work.

Where is the syntax error?

Select

USR_TVESTA.USR_TVESTA_USERID,

USR_TVESTA.USR_TVESTA_FECHA,

USR_USUWEB.USR_USUWEB_NOMBRE,

USR_USUWEB.USR_USUWEB_USUTIP,

VTMCLH.VTMCLH_NROCTA,

VTTVND.VTTVND_VNDDOR,

VTTVND.VTTVND_DESCRP,

(COUNT(*)) as CantIng

From USR_TVESTA,

USR_USUWEB,

left join VTMCLH on USR_USUWEB.USR_USUWEB_NROCTA = VTMCLH.VTMCLH_NROCTA,

left join VTTVND on USR_USUWEB.USR_USUWEB_VNDDOR = VTTVND.VTTVND_VNDDOR

Where USR_TVESTA.USR_TVESTA_USERID = USR_USUWEB.USR_USUWEB_NOMBRE

Group By USR_TVESTA.USR_TVESTA_USERID,

USR_TVESTA.USR_TVESTA_FECHA,

USR_USUWEB.USR_USUWEB_NOMBRE,

USR_USUWEB.USR_USUWEB_USUTIP,

USR_USUWEB.USR_USUWEB_NROCTA,

VTMCLH.VTMCLH_NROCTA,

VTTVND.VTTVND_VNDDOR,

VTTVND.VTTVND_DESCRP,

USR_USUWEB.USR_USUWEB_VNDDOR;

Regards.

Re: SQL query

effinty2112 — Tue, 17 Oct 2017 15:32:17 GMT

Hi Sandro,

Maybe a From statement like:

FROM USR_TVESTA INNER JOIN USR_USUWEB ON SR_TVESTA.USR_TVESTA_USERID = USR_USUWEB.USR_USUWEB_NOMBRE

LEFT JOIN VTMCLH ON USR_USUWEB.USR_USUWEB_NROCTA = VTMCLH.VTMCLH_NROCTA

LEFT JOIN VTTVND ON USR_USUWEB.USR_USUWEB_VNDDOR = VTTVND.VTTVND_VNDDOR

Good luck

Andrew

Re: SQL query

spividori — Tue, 17 Oct 2017 20:17:31 GMT

It works perfect. Thank you.

Regards.