topic Re: Select using inner join, group by and having in Application Development and Automation Discussions

Select using inner join, group by and having

Former Member — Mon, 26 May 2008 13:47:18 GMT

Hi experts.

I have the following select, and I don't know why appear the message 'The column NR_VALIDACAO has two meanings'.

SELECT a_{nr_validacao a}nr_revisao a_{atnam a}atwrt

b_{cd_gr_regra b}id_sit_regra

INTO TABLE t065

FROM ztbvc_065 AS a INNER JOIN ztbvc_064 AS b

ON a_{nr_validacao = b}nr_validacao AND

a_{nr_revisao = b}nr_revisao AND

b~cd_gr_regra = v_regra

FOR ALL ENTRIES IN tdados

WHERE atnam = tdados-atnam

AND atwrt = tdados-atwrt

group by: nr_validacao, nr_Revisao

having cont >= 2.

endselect.

in table tdados i have 2 atnam and 2 atwrt.

in this select i want only nr_validacao tha have this 2 atnam and atwrt.

Can anyone help me??

Tks in advance...

Reward will be given!

Gabriel

Re: Select using inner join, group by and having

Former Member — Mon, 26 May 2008 13:54:46 GMT

Hello,

Change the fields in the group by clause as follow:


SELECT a~nr_validacao a~nr_revisao a~atnam a~atwrt
b~cd_gr_regra b~id_sit_regra
INTO TABLE t065
FROM ztbvc_065 AS a INNER JOIN ztbvc_064 AS b
ON a~nr_validacao = b~nr_validacao AND
a~nr_revisao = b~nr_revisao AND
b~cd_gr_regra = v_regra
FOR ALL ENTRIES IN tdados
WHERE atnam = tdados-atnam
AND atwrt = tdados-atwrt

group by: a~nr_validacao, a~nr_Revisao

having cont >= 2.
endselect.

Regards,

Re: Select using inner join, group by and having

Former Member — Mon, 26 May 2008 13:58:54 GMT

hi,

Specify the table reference for nr_validacao as a or b as shown below....

SELECT a_{nr_validacao a}nr_revisao a_{atnam a}atwrt

b_{cd_gr_regra b}id_sit_regra

INTO TABLE t065

FROM ztbvc_065 AS a INNER JOIN ztbvc_064 AS b

ON a_{nr_validacao = b}nr_validacao AND

a_{nr_revisao = b}nr_revisao AND

b~cd_gr_regra = v_regra

FOR ALL ENTRIES IN tdados

WHERE atnam = tdados-atnam

AND atwrt = tdados-atwrt

group by: a_{nr_validacao, a}nr_Revisao

having cont >= 2.

endselect.

Re: Select using inner join, group by and having

Former Member — Mon, 26 May 2008 14:11:56 GMT

Hi Gabriel,

The filed nr_validacao is present in two of the tables (ztbvc_065 and ztbvc_064 ) So when using this field for grouping in the selection, you have to specify the table name explicitly.

use

group by: a~nr_validacao, nr_Revisao

group by: b~nr_validacao, nr_Revisao

as per the requirement.

Thanks

sapd

Re: Select using inner join, group by and having

Former Member — Tue, 27 May 2008 01:51:15 GMT

Hi,

Plz try to avoid using GROUPBY and ORDERBY clause in SELECT querry as due to the PERFORMANCE issue.

better use SORT stmt on the Internal table after data extraction.

Reward if helpful,

Thanks