Select using inner join, group by and having

[Former Member]

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

[Former Member]

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

[Former Member]

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,

[Former Member]

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.

[Former Member]

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

or

group by: b~nr_validacao, nr_Revisao

as per the requirement.

Thanks

sapd

[Former Member]

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