I need to filter the records from a table in Toad Oracle

ghz 8months ago ⋅ 60 views

I need to filter the records from a table based on the logic outlined below, in Toad Oracle.

Logic is:

  • If customer.ID and customer.ORDERID combination has customer.CODE with values 'TT' and 'TP', load value 'TP' and ignore 'TT'.

Note: ID and ORDERID columns can have multiple rows.

I am unable to put this logic into a WHERE clause and also not able to understand how I can select and exclude the records based on the given logic. Please assist me on this.

I tried GROUP BY, but it's not working as I am fetching all columns of the table.

Answers

To filter the records from the table based on the given logic in Toad for Oracle, you can use a subquery or a common table expression (CTE) to identify the distinct combinations of ID and ORDERID where both 'TT' and 'TP' codes exist for the same customer. Then, you can use this result to filter the main table to include only the records where 'TP' code exists and 'TT' code does not exist for those combinations.

Here's how you can write the query:

SELECT *
FROM customer c
WHERE EXISTS (
    SELECT 1
    FROM customer
    WHERE ID = c.ID
      AND ORDERID = c.ORDERID
      AND CODE = 'TP'
)
AND NOT EXISTS (
    SELECT 1
    FROM customer
    WHERE ID = c.ID
      AND ORDERID = c.ORDERID
      AND CODE = 'TT'
);

In this query:

  • The outer query selects all columns from the customer table.
  • The first subquery checks if there exists at least one record for the same ID and ORDERID with CODE = 'TP'.
  • The second subquery checks if there does not exist any record for the same ID and ORDERID with CODE = 'TT'.
  • The EXISTS and NOT EXISTS conditions are correlated subqueries, meaning they refer to the ID and ORDERID columns from the outer query (c.ID and c.ORDERID). This ensures that the subqueries are evaluated for each row in the outer query.
  • This query will select all columns from the customer table where the given conditions are met. Adjust the column names as per your actual schema.
#sql