query formation

Question

User364480375 posted

i have to add GRPNAME in below query.

"select 0, disp_office_code as office_code,
(select office_name from offices where office_code=disp_office_code) 
as office_name from disp_offices where office_code=''Prgm2''
 and disp_office_code in (select office_code from offices where office_level<>'STOP') order by id"

 table structure are as follows:

1.ADDGRP

DEPTNAME    VARCHAR2(200 BYTE)
DEPTUSER    VARCHAR2(1000 BYTE)
GRPNAME    VARCHAR2(50 BYTE)
OFFICE_CODE    VARCHAR2(50 BYTE)

2.DISP_OFFICES

ID    NUMBER(11,0)
OFFICE_CODE    VARCHAR2(20 BYTE)
DISP_OFFICE_CODE    VARCHAR2(20 BYTE)
DISP_OFFICE_NAME    VARCHAR2(70 BYTE)

3.OFFICES

OFFICE_CODE    VARCHAR2(20 BYTE)
OFFICE_NAME    VARCHAR2(70 BYTE)
DEPARTMENT_CODE    VARCHAR2(20 BYTE)

I tried this but getting error:

select DO.disp_office_code as office_code,O.office_name ,G.GRPNAME 
FROM  disp_offices DO, offices O, ADDGRP G 
WHERE  O.office_code=DO.disp_office_code AND 
office_code='Prgm2' and disp_office_code in
 (select office_code from offices where office_level<>'STOP') 
 AND G.OFFICE_CODE=O.OFFICE_CODE  

error:ORA-00918: column ambiguously defined
00918. 00000 -  "column ambiguously defined"

Answer 1

User1401801381 posted

hi,

in your where close you omit to specify the table,
since you have a column named "office_code" in two tables you have to specify the table

So replace

office_code='Prgm2'

by

O.office_code='Prgm2'

or

DO.office_code='Prgm2'

Answer 2

User364480375 posted

sam

actally the query that is working i have posted at top . now in that query i have add grpname.

the query i have tried that was not working so assist.

Answer 3

User1401801381 posted

Try this

select 
DO.disp_office_code as office_code
,O.office_name
,G.GRPNAME 
FROM  disp_offices DO, offices O, ADDGRP G 
WHERE O.office_code=DO.disp_office_code 
AND G.office_code='Prgm2' 
and disp_office_code in
 (select office_code from offices where office_level<>'STOP') 
AND G.OFFICE_CODE=O.OFFICE_CODE  

Answer 4

User79986525 posted

Hi ,

Try this 

select DO.disp_office_code as office_code,O.office_name ,G.GRPNAME 
FROM  disp_offices DO, offices O, ADDGRP G 
WHERE  O.office_code=DO.disp_office_code AND 
O.office_code='Prgm2' and DO.disp_office_code in
 (select office_code from offices where office_level<>'STOP') 
 AND G.OFFICE_CODE=O.OFFICE_CODE  

Answer 5

User364480375 posted

guys not getting proper result

Answer 6

User-1166487226 posted

I dont see Office_level column defined in your table structure.

Use Alias name in your conditions so as to be specific from which table you want to filter content as there are same columns defined in two or more tables. 

Answer 7

User269602965 posted

And office_code is ambiguously defined in your WHERE IN clause as well

select 
  DO.disp_office_code as office_code,
  O.office_name,
  G.GRPNAME 
FROM  
  disp_offices DO, 
  offices      O, 
  addgrp       G 
WHERE  
  DO.disp_office_code = O.office_code 
  AND 
  O.office_code       = G.office_code 
  AND 
  O.office_code       = 'Prgm2' 
  AND
  DO.disp_office_code in
                  (select 
                    a.office_code as disp_office_code
                   from 
                     offices a
                   where 
                     a.office_level <> 'STOP')
/

Answer 8

User269602965 posted

Although I am not familiar with what result you are trying to extract, this makes more sense to me:

SELECT 
  DO.disp_office_code as office_code,
  O.office_name,
  G.grpname 
FROM  
  disp_offices DO 
INNER JOIN offices O ON DO.disp_office_code = O.office_code
INNER JOIN grpname G ON O.office_code       = G.office_code
WHERE  
  O.office_level <> 'STOP'
  AND
  G.office_code  =  'Prgm2'
/