1. I have 16 columns on my database.
2. I need to open 16 empty box on user interface.
3. User can fill any number of them. (For example; 10 of 16)
4. User will select a number from a combobox. (2,3,4,5...) (For example; 4)
(HERE IS THE PROBLEM!)
5. I have to make a query with this filled boxes that get result of ANY 4 OF THAT 10 COLUMS MATCH.
There is 10!/4!*(10-4)! combination in math.
Is there any simple way instead of using nested queries?
1. i know that tables are database objects.
2. i know columns are on tables not on databases.
3. After my writing faults...
Yes, dynamic sql.
And the selection critera for each "box" go against only that column not all columns.
A | B | C | D | E | F | G | H |
1 | 2 | 3 | 4 | 5 | 6 | 7 | 8 |
User can say;
A=1 and C=3 and D=4 and E=5 and F=6
And if he say "any of 3", query can be;
I don't want to write complex sql queries. If anybody can find an another solution it will be perfect, because the column number and data can increase.
IBM DB2 Certified
Returns a table of zero, one, or more rows for those columns containing character-based data types for precise or fuzzy (less precise) matches to single words and phrases, the proximity of words within a certain distance of one another, or weighted matches. CONTAINSTABLE can be referenced in the FROM clause of a SELECT statement as if it were a regular table name.
Queries using CONTAINSTABLE specify contains-type full-text queries that return a relevance ranking value (RANK) for each row. The CONTAINSTABLE function uses the same search conditions as the CONTAINS predicate.
i am not making a search engine.
and also i am not sure that Contains or Containstable statements will help my problem. Because they are for text searching and they can get the result like that: 'APPLE' word in 'APPLE TREE' or in 'OLD APPLE TREE'
You have to get the result of all combinations. For example;
You have 10 column in a table,
User fills 6 boxes, and says "AT LEAST 3",
Query must get the result of ALL;
As you see that i have to write a very complex query.
I have to say;
select ... from ... where COL1=1 and COL2=2 and COL3=3
select ... from ... where COL1=1 and COL2=2 and COL3=3 and COL4=4
select ... from ... where COL1=1 and COL2=2 and COL3=3 and COL4=4 and COL5=5
select ... from ... where COL1=1 and COL2=2 and COL3=3 and COL4=4 and COL5=5 and COL6=6
It can seem as simple but, this is only a sample. I have more columns and user can say "AT LEAST 1","AT LEAST 5","AT LEAST 10" ... etc
In that case, it will be hard to create the query.
Actually i think that, i have to do it programatically but, does anyone has a good sql trick to help me?
where (case when Column1 = @Combo1 then 1 else 0 end
+ case when Column2 = @Combo2 then 1 else 0 end
+ case when Column3 = @Combo3 then 1 else 0 end
+ case when Column4 = @Combo4 then 1 else 0 end
+ case when Column5 = @Combo5 then 1 else 0 end)
Yeah...it's doing a clustered index scan (beats me..didn't know there was an index on these columns..
DECLARE @ShipName nvarchar(80), @ShipAddress nvarchar(120), @ShipCity nvarchar(30)
, @ShipRegion nvarchar(30), @ShipPostalCode nvarchar(20), @ShipCountry nvarchar(30)
, @MinimumMatches int
SELECT @ShipName = 'QUICK-Stop', @ShipAddress = '123 Main', @ShipCity = 'Cunewalde'
, @ShipRegion = 'NE', @ShipPostalCode = '12345', @ShipCountry = 'Germany'
, @MinimumMatches = 3
WHERE ( CASE WHEN ShipName = @ShipName THEN 1 ELSE 0 END
+ CASE WHEN ShipAddress = @ShipAddress THEN 1 ELSE 0 END
+ CASE WHEN ShipCity = @ShipCity THEN 1 ELSE 0 END
+ CASE WHEN ShipRegion = @ShipRegion THEN 1 ELSE 0 END
+ CASE WHEN ShipPostalCode = @ShipPostalCode THEN 1 ELSE 0 END
+ CASE WHEN ShipCountry = @ShipCountry THEN 1 ELSE 0 END)