12 Replies Latest reply: Nov 12, 2012 3:49 PM by Frank Kulash RSS

    query returning no rows

    967148
      Hi all,
      This query is not returning any rows but ideally it should.below i am attaching the query
      select * FROM SERIAL_NO WHERE  serialno not in (SELECT SERIALNO FROM COB_T_SERIAL_NO
                                                                                  union select serialno from genealogy
                                                                                  union select lastarchivedby from genealogy
                                                                                  union select serialno from asset
                                                                                  union select serialno from code_serial_number
                                                                                  union select serialno from cost
                                                                                  union select serialno from cost_detail
                                                                                  union select serialno from disposition
                                                                                  union select serialno from disposition_content
                                                                                  union select serialno from disposition_line
                                                                                  union select serialno from disposition_test_reason
                                                                                  union select serialno from inventory_count_serial_no
                                                                                  union select serialno from inventory_serial_no
                                                                                  union select serialno from inventory_serial_transit
                                                                                  union select serialno from material_order_serial_no
                                                                                  union select serialno from material_content_serial_no
                                                                                  union select serialno from resource_content
                                                                                  union select serialno from receipt_container_serial_no
                                                                                  union select serialno from resource_serial_no
                                                                                  union select serialno from sequence_queue_item
                                                                                  union select serialno from serial_no_hold
                                                                                   union select serialno from wip_order_content_serial
                                                                                   union select serialno from wip_serial_no
                                                                                   union select serialno from wip_serial_no_content
                                                                                   union
                                                                                   select a.serialno from resource_labor_detail a  inner join resource_labor_detail_approval b on b.resourcelabordetailid=a.id
                                                                                    union
                                                                                    select c.serialno from labor_detail c inner join labor_detail_approval d on d.labordetailid = c.id
                                                                                     union 
                                                                                     select e.serialno from disposition_reading e  inner join disposition_resource f on f.dispositionreadingid = e.id                              
                                                                                     union select g.serialno from quality_defect g 
                                                                                      inner join   quality_defect_location h on h.qualitydefectid=g.id 
                                                                                      inner join quality_defect_loc_dimension i on i.qualitydefectlocationid=h.id )                          
                                                                                      
                                                                                                                   
                                                                                                                   
        • 1. Re: query returning no rows
          SomeoneElse
          If there's a NULL in any of the serial numbers in your subquery, your outer query will return no rows.

          Gotta watch those NOT IN subqueries.
          • 2. Re: query returning no rows
            967148
            i tried with not exists also but it is not returning rows
            • 3. Re: query returning no rows
              Frank Kulash
              Hi,

              As mentioned above, "NOT IN (subquery)" will never return TRUE if the subquery includes any NULLs.

              You may want to do something like this:
              WITH     union_data   AS
              (
                         SELECT  serialno   FROM  cob_t_serial_no
                  UNION  SELECT  serialno   FROM  genealogy
                  UNION  ...
              )
              SELECT     * 
              FROM      serial_no 
              WHERE   serialno  NOT IN (
                                        SELECT  serialno
                                  FROM    union_data
                                  WHERE   serialno     IS NOT NULL
                                    ) 
              ;
              • 4. Re: query returning no rows
                SomeoneElse
                ...also, in your subquery you should use UNION ALL since there's no need to sort and eliminate duplicates.

                As to why you're not getting any results...we don't have your tables and data so there's nothing we can do.
                • 5. Re: query returning no rows
                  967148
                  right....i understand it is difficult for you to help without data...is it syntactically correct? also i want only distinct values so i used union instead of union all
                  • 6. Re: query returning no rows
                    SomeoneElse
                    .is it syntactically correct?
                    If it isn't you'll get an error.
                    also i want only distinct values so i used union instead of union all
                    The you'll need a distinct in your outer query. Having them distinct in your subquery requires unnecessary sorting.
                    • 7. Re: query returning no rows
                      967148
                      can i use like this? please suggest
                      with union_data as (SELECT SERIALNO FROM COB_T_SERIAL_NO
                                                                                                  union select serialno from genealogy
                                                                                                  union select lastarchivedby from genealogy
                                                                                                  union select serialno from asset
                                                                                                  union select serialno from code_serial_number
                                                                                                  union select serialno from cost
                                                                                                  union select serialno from cost_detail
                                                                                                  union select serialno from disposition
                                                                                                  union select serialno from disposition_content
                                                                                                  union select serialno from disposition_line
                                                                                                  union select serialno from disposition_test_reason
                                                                                                  union select serialno from inventory_count_serial_no
                                                                                                  union select serialno from inventory_serial_no
                                                                                                  union select serialno from inventory_serial_transit
                                                                                                  union select serialno from material_order_serial_no
                                                                                                  union select serialno from material_content_serial_no
                                                                                                  union select serialno from resource_content
                                                                                                  union select serialno from receipt_container_serial_no
                                                                                                  union select serialno from resource_serial_no
                                                                                                  union select serialno from sequence_queue_item
                                                                                                  union select serialno from serial_no_hold
                                                                                                   union select serialno from wip_order_content_serial
                                                                                                   union select serialno from wip_serial_no
                                                                                                   union select serialno from wip_serial_no_content
                                                                                                   union
                                                                                                   select a.serialno from resource_labor_detail a  inner join resource_labor_detail_approval b on b.resourcelabordetailid=a.id
                                                                                                    union
                                                                                                    select c.serialno from labor_detail c inner join labor_detail_approval d on d.labordetailid = c.id
                                                                                                     union 
                                                                                                     select e.serialno from disposition_reading e  inner join disposition_resource f on f.dispositionreadingid = e.id                              
                                                                                                     union select g.serialno from quality_defect g 
                                                                                                      inner join   quality_defect_location h on h.qualitydefectid=g.id 
                                                                                                      inner join quality_defect_loc_dimension i on i.qualitydefectlocationid=h.id )           
                                                                                                      
                                                                                                      
                      DELETE serial_no where serialno not in  (SELECT  serialno
                                       FROM    union_data
                                       WHERE   serialno    IS NOT NULL
                                         ) ;
                                     
                                                                                                      
                                                                                                                                   
                                                                                   
                      • 8. Re: query returning no rows
                        Frank Kulash
                        Hi,
                        964145 wrote:
                        right....i understand it is difficult for you to help without data...is it syntactically correct?
                        Yes, the syntax is fine. The fact that it runs (regardless of how many rows it returns, if any) tells you that there is no syntax error.
                        also i want only distinct values so i used union instead of union all
                        Why do you want only distinct values? The reulsts will be the same whether there are duplicates or not.
                        can i use like this? please suggest
                        with union_data as (SELECT SERIALNO FROM COB_T_SERIAL_NO
                        union select serialno from genealogy
                        ...
                        inner join quality_defect_loc_dimension i on i.qualitydefectlocationid=h.id )           
                        
                        
                        DELETE serial_no where serialno not in  (SELECT  serialno
                        FROM    union_data
                        WHERE   serialno    IS NOT NULL
                        ) ;
                        What happens when you try it?

                        A query can start with the keyword WITH.
                        A DELETE statement can't.
                        You can do soemthing like this:
                        DELETE  serial_no 
                        WHERE     serialno   NOT IN  (
                                                     WITH    union_data   AS 
                                              (
                                                  SELECT  serialno   FROM  cob_t_serial_no   UNION
                                               SELECT  serialno   FROM  genealogy            UNION ...
                                              )
                                                     SELECT  serialno
                                                      FROM    union_data
                                                      WHERE   serialno    IS NOT NULL
                                                   ) ;
                        Edited by: Frank Kulash on Nov 12, 2012 4:22 PM
                        • 9. Re: query returning no rows
                          967148
                          i could get the rows as you suggest with with caluse but how do i delete from the temporary table defined by with clause
                          • 10. Re: query returning no rows
                            967148
                            I am getting select statement missing when i do that way
                            • 11. Re: query returning no rows
                              SomeoneElse
                              but how do i delete from the temporary table defined by with clause
                              Eh? What exactly are you trying to do?

                              At first you posted a select statement, now you want a delete?

                              Are you trying to delete duplicates or something like that?
                              • 12. Re: query returning no rows
                                Frank Kulash
                                Hi,
                                964145 wrote:
                                i could get the rows as you suggest with with caluse but how do i delete from the temporary table defined by with clause
                                Sorry, I don't understand.

                                The result_set produced by the WITH clause is not a temporary table. In many ways, it behaves like a table (for example, you can use its name in a FROM clause), and it may even be materialized, but it's still not a table. You can't delete from it, and, in this case, I don't believe you want to. In this problem, don't you want to delete from a table called serial_no?
                                If you really had to remove some rows from that result set, you could use a WITH clause, or perhaps do a MINUS operation, or join it to another table.

                                 

                                Whenever you have a problem, please post a little sample data (CREATE TABLE and INSERT statements, relevant columns only) for all the tables involved, and the results you want from that data.
                                In the case of a DML operation (such as DELETE) the sample data should show what the tables are like before the DML, and the results will be the contents of the changed table(s) after the DML.
                                Simplify the problem as much as possible. For example, if you really need to do a 20-way UNION, post a problem where you only have a 2-way UNION. If we know you really need a 20-way UNION, we'll find a solution that words for either a 2- or 20-way UNION. Just make sure the simplified version has the same problem as your real problem.
                                Explain, using specific examples, how you get those results from that data.
                                Always say what version of Oracle you're using (e.g. 11.2.0.2.0).
                                See the forum FAQ {message:id=9360002}