SELECT count(FileName) as number, FileName FROM mvt_Ods group by FileName
2/ My second input :
SELECT count(FileName) as numberCondition, FileName
FROM mvt_Ods where Return_Mark!=' ' and PackingList_Type!='P' group by FileName
3/ A table in my database : mvt_Ods which containt Return_Mark, PackingList_Type, FileName and other attributes
To summarize I must check the conditions:
Return_Mark! = '' And PackingList_Type! = 'P'
If both of these conditions are true I must get the FileName field
and then in a tMap I have to create a table that does not contain the rows that have been checked by both conditions
Note :
the number of rows that satisfy both conditions for the same FileName must be equal to the number of rows in FileName
Example:
Return_Mark! = '' And PackingList_Type! = 'P' => Result:
FileName A: 100 lines
FileName B: 200 Lines
Number of lines for:
100
B: 300
For my output I have to remove A and keep only B
This should give you list of filenames where both of conditions are true and number of filenames where both of conditions are true is the same as number of all records with this filename (another way, this is the list of filenames for which in 100% of cases both conditions are true)
SELECT FileName, count(*) as number FROM mvt_Ods group by FileName
HAVING SUM(CASE WHEN Return_Mark <> ' ' and PackingList_Type <> 'P' THEN 1 ELSE 0 END) = count(*)
I don't know which type of database do you use (MySQL, MSSQL, Oracle...)
I have a table wich contains Return_Mark, PackingList_Type and FileName and other columns.
For example the result of Return_Mark!=' ' && PackingList_Type !='P' is
for FileName ='A' : 100 rows
for FileName ='B' : 200 rows
And the result of distinct count(FileName) is
for FileName ='A' : 100 rows
for FileName ='B' : 300 rows
FileName='A' have the same number of rows for the two queries so I have create a new table wich contain the other FileName(s) and for this example it's FileName ='B'.
