locked
query formation RRS feed

  • 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"

    Thursday, February 13, 2014 4:42 AM

Answers

  • 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')
    /
    
    

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Sunday, July 9, 2017 1:45 PM

All replies

  • 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'

    Thursday, February 13, 2014 5:13 AM
  • 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.

    Thursday, February 13, 2014 7:03 AM
  • 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
    Thursday, February 13, 2014 7:29 AM
  • 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  

    Thursday, February 13, 2014 8:15 AM
  • User364480375 posted

    guys not getting proper result

    Monday, February 17, 2014 2:18 AM
  • 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. 

    Monday, July 3, 2017 7:56 AM
  • 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')
    /
    
    

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Sunday, July 9, 2017 1:45 PM
  • 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'
    /
    

    Sunday, July 9, 2017 1:57 PM