One approach here would be to aggregate by COLA, and then add three assertions for the key/values you expect:

SELECT
    COLA
FROM yourTable
GROUP BY
    COLA
HAVING
    COUNT(CASE WHEN COLB = 'CODE' AND COLC = 'AAA' THEN 1 END) > 0 AND
    COUNT(CASE WHEN COLB = 'DESC' AND COLC = 'BBB' THEN 1 END) > 0 AND
    COUNT(CASE WHEN COLB = 'TYPE' AND COLC = 'CCC' THEN 1 END) > 0;

Filter functions (DAX), ALL, Returns all the rows in a table, or all the values in a column, ignoring any filters that might CALCULATE, Evaluates an expression in a modified filter context. The function can apply one or more search conditions. Hi all, I need to drop a row from my table in power query (Excel). I presume I need to filter that row out. The trouble is that other rows also have than same value and I dont want to filter out the other ones too. For example, I want to filter out [Total]<>26 where in the same row the [Indicator]

You can re-write query using common table expression.

WITH CTE_TableB(ColA, Code, DESC,TYPE) AS
(
SELECT COLA, CASE WHEN COLB = 'Code' THEN COLC ELSE NULL AS Code
           , CASE WHEN COLB = 'DESC' THEN COLC ELSE NULL AS DESC
           , CASE WHEN COLB = 'TYPE' THEN COLC ELSE NULL AS TYPE
FROM TABLEB
)
SELECT *
FROM CTE_TableB
WHERE  CODE IN ('AAA') AND DESC IN ('BBB') AND TYPE IN ('CCC');

Or using derived table

SELECT *
FROM
(
SELECT COLA, CASE WHEN COLB = 'Code' THEN COLC ELSE NULL AS Code
           , CASE WHEN COLB = 'DESC' THEN COLC ELSE NULL AS DESC
           , CASE WHEN COLB = 'TYPE' THEN COLC ELSE NULL AS TYPE
FROM TABLEB
) AS t
WHERE  CODE IN ('AAA') AND DESC IN ('BBB') AND TYPE IN ('CCC');

Filter by using advanced criteria - Excel, If the Excel data you want to filter requires complex criteria you can filter by using on the worksheet and above the range of cells or table that you want to filter. and define the name Extract for the area where you want to paste the rows, and� In this tutorial, we’ll go over the various ways to update rows in a table using SQL progressing from more general updates to more specific methods.. Full Update. If every field needs to be updated to the same value, you can do that using a simple UPDATE command.

To filter table A using the values in table B, and assuming that a column without values in table B means unfiltered on that column, you can do it like this:

SELECT *
  FROM A
 WHERE ( NOT EXISTS ( SELECT * FROM B WHERE B.COLB = 'CODE' )
      OR A.CODE IN ( SELECT B.COLC FROM B WHERE B.COLB = 'CODE' ) )
   AND ( NOT EXISTS ( SELECT * FROM B WHERE B.COLB = 'DESC' )
      OR A.DESC IN ( SELECT B.COLC FROM B WHERE B.COLB = 'DESC' ) )
   AND ( NOT EXISTS ( SELECT * FROM B WHERE B.COLB = 'TYPE' )
      OR A.TYPE IN ( SELECT B.COLC FROM B WHERE B.COLB = 'TYPE' ) )

Filtering – Databases and SQL, Write queries that select records that satisfy user-specified conditions. We can select these records from the Visited table by using a WHERE clause in our query : It then uses the column names following the SELECT keyword to determine� Imagine I have the following table, giving Bugs by date and if they are still open (not fixed) or not. So in this table I want to exclude Bugs that were never opened, but for the ones opened, I want to see the row appearing when they were closed. were never had a value of Open = 1. Thanks!

You can use in or exists for this purpose:

select a.*
from a 
where a.code in (select b.colc from b where b.colb = 'CODE') and
      a.desc in (select b.colc from b where b.colb = 'DESC') and
      a.type in (select b.colc from b where b.colb = 'TYPE');

The one caveat is that this assumes that there is at least one of each value in table b.

You can handle missing values. One method uses additional subqueries:

select a.*
from a 
where (a.code in (select b.colc from b where b.colb = 'CODE') or
       not exists (select 1 from b where b.colb = 'CODE')
      ) and
      (a.desc in (select b.colc from b where b.colb = 'DESC') or
       not exists (select 1 from b where b.colb = 'DESC')
      ) and
      (a.type in (select b.colc from b where b.colb = 'TYPE') or
       not exists (select 1 from b where b.colb = 'TYPE')
      );

Using the FILTER Function in DAX - Simple Talk, The FILTER function works quite differently than the CALCULATE It returns a table of the filtered rows, and sometimes it is the better approach to take. doesn 't have to iterate down each row in the table testing a condition. It means that if a row causes the condition to evaluate to FALSE or NULL, the row will not be returned. Note that the logical expression that follows the WHERE clause is also known as a predicate. You can use various operators to form the row selection criteria used in the WHERE clause. The following table shows the SQL comparison operators:

Filtering and Limiting Data, When you run the query, Looker will only return rows for which that condition is true. Adding a Custom Filter. To add a custom filter, expand the Filters section and� Well organized and easy to understand Web building tutorials with lots of examples of how to use HTML, CSS, JavaScript, SQL, PHP, Python, Bootstrap, Java and XML.

Chris Webb's BI Blog: Complex Filter Conditions In Power BI , If there are rows in the SameCustomerSOs table then the current row in Internet Sales should not be displayed because it is not the most recent� Derived Table in SQL. A derived table is an example of a subquery that is used in the FROM clause of a SELECT statement. We can use derived tables to break a complex query into separate logical steps. A derived table i s often used as an alternative solution to a temporary table or a CTE (Common Table Expression).

Filter Arguments in CALCULATE, A filter argument in CALCULATE is always an iterator. NOTE: This article is about table filter arguments, and does not consider directive It could be potentially faster than the table scan for a complex filter condition, but in terms of and the “cost” of the filter is the number of rows you have in such a table. Hi Everyone This is my table Name Status A Active B Inactive C Active D Active E Inactive I need the result like this Name Status A Active C Active D Active I need to show only active employee.

Comments

• Add some more sample data and also specify the expected result.
• How many tables actually exist here?
• It looks like you're trying to compare the value of COLB and COLC within a row. If that's the case, then you could possibly use a LIKE operator or an array comparison. What is the data type of COLC? What is your definition of IN in your pseudo-query?
• This seems to be a request to use Table B to generate dynamic SQL with varying number of predicates referencing arbitrary column names and potentially lists of column values. While it may be possible to do that in BTEQ, you are probably better off using some procedural language (stored procedure in the database, Python script on the client, etc.) to build the query.
• COLB and COLC is in the B table, and OP want to filter table A. You're only querying one table, so how is that supposed to work?
• COLB and COLC is in the B table, and OP want to filter table A. You're not doing anything with table A, so how is that supposed to work?
• I see no relationship between the "A" and "B" tables whatsoever, and would assume it is just a typo, and that there is only one table.
• @TimBiegeleisen The relationship is that B.COLB contains the name of a column in table A, and B.COLC contains the value to match in that table A column. By guess is that the integer value of B.COLA represents the A table. It's the only way the question makes any sense to me, but it does make lots of sense that way.