Is the meaning of this SQL and this data flow correct?

Question

Is the meaning of this SQL and this data flow correct?

I would like to reproduce sql with synapse data flow.

【SQL】
WHERE
A <> '' AND
B LIKE '5%'

【data flow】filter　Expression Builder
A != ' ' && like(B,'5%')

SELECT  
  AA     AS A,   
  AA___T AS AT,   
  BB   AS B,   
  CC   AS C,   
  DD   AS D,   
  EE   AS E,   
  FF   AS F   
FROM  
  (   
    SELECT  
      AA,   
      AA___T,   
      BB,   
      CC,   
      DD,   
      EE,   
      FF,   
      ROW_NUMBER() OVER (PARTITION BY AA ORDER BY COUNT(*) DESC) AS ROW_NUM,   
      count(*)   
    FROM  
      test   
    WHERE  
      AA <> '' AND  
      GG LIKE '5%'   
    GROUP BY  
      AA,   
      AA___T,   
      BB,   
      CC,   
      DD,   
      EE,   
      FF  
  )  
WHERE  
  ROW_NUM = 1  
;  

Answer 1

Hi,

Does this sql and this expression make sense?

What makes sense to one person does not necessarily makes sense to the other

If you can describe your goal then we probably can discuss the format of the filtering

You are using two tags which are related to different services that based on different products behind the scenes (SQL Server vs Parallel Data Warehouse) which are using a (bit) different language!

A query which fit Azure SQL Database does not necessarily will fit Azure Synapse Analytics

You should inform us which service/product you use and select only the relevant tag.

A <> '' AND B LIKE '5%'

This makes sense if this your goal! This filter return what is not empty string (remember that empty string "" is not the same as null) and if the B value string starts with 5 (note that you are using string filtering and not math on numbers here)

A != ' ' && like(B,'5%')

Again, it makes sense if this is your goal, but is this supported what you ned?!?

The operator && is Logical AND and it can fit when you use languages that support it lime C# for example or using SSIS and such, but it is not an SQL operator.

AND is Standard SQL operator, && is proprietary syntax

Answer 2

Hi @NishimuraChinatsu-9854 ,

Thank you for posting query in Microsoft Q&A Platform.

If I understand correctly you want to filter rows based on columns AA and GG. If AA column is not empty AND GG column value starts with 5. Please correct me If I am wrong.

You can write your filter transformation expression as below for the same.
(AA!='' && startsWith(GG,'5'))

Hope this helps. Please let me know if any further queries.

-----------

Please consider hitting Accept Answer button. Accepted answers help community as well.