5 Replies Latest reply on Feb 24, 2017 4:39 PM by rp0428

    Oracle Carts by Where Exists clause

    2710685

      Hi

       

      I'm trying to get a subsection of data from some pretty big tables. So what I did was get a small sub section of customers and put them in their own table. It returns 5-6k rows and I'm trying to then get a portion of the orders table, related to those customers using an exists clause like so within a cart:

       

      In my customers table cart in the Where box I'm trying

       

      EXISTS (

        SELECT 1

        FROM REBUILD_TESTING_CUSTOMERS

        WHERE CUSTOMER_ID = REBUILD_TESTING_CUSTOMERS.CUSTOMER_ID

        )

       

      This seems to be ignored, which I'm not overly surprised at. But just wondering if there is away to achieve data in insert statements using something around an EXIST() clause?

        • 1. Re: Oracle Carts by Where Exists clause
          thatJeffSmith-Oracle

          I just tried an EXISTS clause in the cart WHERE filter dialog - and it worked for me.

           

          I tried

           

          exists (select 1 from dual where 1=1) -- got rows!

           

          exists (select 1 from dual where 1=2) -- got 0 rows!

          • 2. Re: Oracle Carts by Where Exists clause
            2710685

            Hi Jeff

             

            Thanks for replying to me .

            For me, it doesn't seem able to do exists when pulling from another table though?

            It's either ignored if I do this:

            EXISTS (

              SELECT 1

              FROM REBUILD_TESTING_CUSTOMERS

              WHERE CUSTOMER_ID = REBUILD_TESTING_CUSTOMERS.CUSTOMER_ID

              )

            * Also tried aliasing the table like REBUILD_TESTING_CUSTOMERS R

             

            And If I explicitly name the customer table like

             

            EXISTS (

              SELECT 1

              FROM REBUILD_TESTING_CUSTOMERS

              WHERE CUSTOMER.CUSTOMER_ID = REBUILD_TESTING_CUSTOMERS.CUSTOMER_ID

              )

             

            It comes back with invalid identifier "CUSTOMER.CUSTOMER_ID" vendor code 904. The Cart is for my Customer table (just for reference)

            • 3. Re: Oracle Carts by Where Exists clause
              thatJeffSmith-Oracle

              i have an itunes table where there's a track_count number - some of these kinda look like department_id's from the employees table...dumb example, but whatever

               

              exists.png

               

              did the export, it grabbed 900 or so of 30,000 records - the same 900 showing in the test query dialog above

              • 4. Re: Oracle Carts by Where Exists clause
                2710685

                Thanks Jeff. In my case I referenced the owner of the table and altered my column name in the REBUILD_TESTING_CUSTOMERS just in case of ambiguity.

                • 5. Re: Oracle Carts by Where Exists clause

                  It comes back with invalid identifier "CUSTOMER.CUSTOMER_ID" vendor code 904. The Cart is for my Customer table (just for reference)

                  Why not just query the table directly and pull the rows you need into a new table using CTAS?.

                  CREATE REBUILD_TESTING_CUSTOMERS
                  SELECT O.* FROM  ORDERS O WHERE

                  EXISTS (

                  SELECT 1

                  FROM REBUILD_TESTING_CUSTOMERS

                  WHERE O.CUSTOMER_ID = REBUILD_TESTING_CUSTOMERS.CUSTOMER_ID

                  )

                  Why do you need to use the cart?