9 Replies Latest reply: Apr 3, 2013 6:30 PM by rp0428 RSS

    query error ORA-00904: "SS"."SHIP_GID": invalid identifier

    832426
      The following query gives me an error. I want to count all shipments and the shipments (shipment_status table) having RELEASED TO CARRIER_YES' .

      I was doing this in one query but it was giving me same counts for the two columns. Please help me.

      Thanks.
         SELECT  sl.location_name Facility,  count(s.ship_gid) Total_Shipments
        FROM 
             ship s,
             location sl,
            (Select  update_date UDate, count(distinct ship_gid)  Rel_Carrier_Count 
            FROM    ship_status 
            WHERE   STATUS_VALUE_GID = 'RELEASED TO CARRIER_YES'
            Group by update_date ) ss
        WHERE 1=1
             and s.source_location_gid = sl.location_gid
             and s.ship_gid = ss.ship_gid  
        GROUP BY  sl.location_name ;
        • 1. Re: query error ORA-00904: "SS"."SHIP_GID": invalid identifier
          rp0428
          (Select  update_date UDate, count(distinct ship_gid)  Rel_Carrier_Count 
                FROM    ship_status 
                WHERE   STATUS_VALUE_GID = 'RELEASED TO CARRIER_YES'
                Group by update_date ) ss
          Do you see any 'SHIP_GID' in there anywhere? I don't.

          How can you use something that doesn't exist?

          You gave the count the alias of 'Rel_Carrier_Count' so that is what you have to use.
          • 2. Re: query error ORA-00904: "SS"."SHIP_GID": invalid identifier
            832426
            I have tried this but same error.... :(
            SELECT  sl.location_name Facility,  count(s.ship_gid) Total_Shipments
              FROM 
                   ship s,
                   location sl,
                  (Select  a.update_date UDate, count(distinct a.ship_gid)  Rel_Carrier_Count 
                  FROM    ship_status a , ship s
                  WHERE   a.STATUS_VALUE_GID = 'RELEASED TO CARRIER_YES'
                          and s.ship_gid = ss.ship_gid
                  Group by a.update_date ) ss
              WHERE 1=1
                   and s.source_location_gid = sl.location_gid
                   and s.ship_gid = ss.ship_gid  
              GROUP BY  sl.location_name ;
            • 3. Re: query error ORA-00904: "SS"."SHIP_GID": invalid identifier
              rp0428
              That doesn't change anything.
              >
              (Select a.update_date UDate, count(distinct a.ship_gid) Rel_Carrier_Count
              FROM ship_status a , ship s
              WHERE a.STATUS_VALUE_GID = 'RELEASED TO CARRIER_YES'
              and s.ship_gid = ss.ship_gid
              Group by a.update_date ) ss
              >
              Reread my reply
              >
              Do you see any 'SHIP_GID' in there anywhere? I don't.

              How can you use something that doesn't exist?

              You gave the count the alias of 'Rel_Carrier_Count' so that is what you have to use.
              >
              You have no SHIP_GID!
              count(distinct a.ship_gid)  Rel_Carrier_Count
              you have 'Rel_Carrier_Count'.
              • 4. Re: query error ORA-00904: "SS"."SHIP_GID": invalid identifier
                832426
                I am counting the shipments which have a.STATUS_VALUE_GID = 'RELEASED TO CARRIER_YES', (count(distinct a.ship_gid is ship_gid for ship_status table) . 
                Please explain I didn't understand...
                • 5. Re: query error ORA-00904: "SS"."SHIP_GID": invalid identifier
                  rp0428
                  Select a.update_date UDate, count(distinct a.ship_gid) Rel_Carrier_Count 
                  The only TWO columns you now have are 'UDate' and 'Rel_Carrier_Count'. That is ALL you can reference.
                  • 6. Re: query error ORA-00904: "SS"."SHIP_GID": invalid identifier
                    832426
                    Oh I understand, yes I can reference these columns but I don't wwant to use a.ship_gid here because it effects my count result so how I do this ?
                    • 7. Re: query error ORA-00904: "SS"."SHIP_GID": invalid identifier
                      rp0428
                      >
                      Oh I understand, yes I can reference these columns but I don't wwant to use a.ship_gid here because it effects my count result so how I do this ?
                      >
                      Go back to your original post.

                      This is the reason you ask the question to begin with
                      >
                      Re: query error ORA-00904: "SS"."SHIP_GID": invalid identifier
                      >
                      You got that error because there is no SHIP_ID. You renamed the count to 'Rel_Carrier_Count'.,
                      • 8. Re: query error ORA-00904: "SS"."SHIP_GID": invalid identifier
                        832426
                        ship_id the primary key for ship and foreign key for ship_status, I have the only relation between these two tables. I used the "'Rel_Carrier_Count' " column as

                        and s.ship_gid = ss.Rel_Carrier_Count
                        but giving me error.
                        ORA-01722: invalid number
                        01722. 00000 -  "invalid number"
                        *Cause:    
                        *Action:
                        • 9. Re: query error ORA-00904: "SS"."SHIP_GID": invalid identifier
                          rp0428
                          Use something like this as a template
                          select count(empno) over () all_emps,
                                 sum (case when deptno = 20 then 1 else 0 end) over () dept20_emps 
                          from emp
                          
                          ALL_EMPS     DEPT20_EMPS
                          14     5
                          14     5
                          14     5
                          14     5
                          14     5
                          14     5
                          14     5
                          14     5
                          14     5
                          14     5
                          14     5
                          14     5
                          14     5
                          14     5
                          Then just select one row from that query
                          with q as (select count(empno) over () all_emps,
                                 sum (case when deptno = 20 then 1 else 0 end) over () dept20_emps 
                          from emp)
                          select all_emps, dept20_emps from q 
                          where rownum = 1
                          
                          ALL_EMPS     DEPT20_EMPS
                          14     5