SQL query

[spividori]

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.

[vishsaggi]

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 .....;

[spividori]

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.

[effinty2112 · Accepted solution]

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

[spividori]

It works perfect. Thank you.

Regards.