IIF(True, don't include row, includerow)?

[federalprogrammer]

Hi there,
is there a way to make this query work?

Select
iif(fieldName=2, do_not_include_this_row, fieldName)

From
Many_inner_joined_tables

cheers!

 

[jmcilhinney]

You would need to build your query dynamically using a StringBuilder and If statements. Here's an example but it is rough. You'd need to clean it up to make sure the correct number of commas were used:

Dim queryBuilder As New System.Text.StringBuilder

queryBuilder.Append("SELECT ")

If condition1 Then
    queryBuilder.Append("column1, ")
End If

If condition2 Then
    queryBuilder.Append("column2, ")
End If

queryBuilder.Append(" FROM Table1")

Dim sql As String = queryBuilder.ToString()

 

[federalprogrammer]

Thanx so much for replying. I think your suggestion will definitly work. However due to the nature of my query, I was hoping to write this query within the database itself (for efficiency matters)

In case you're wondering, here's the structure of my query:

qry1:
Field1 Field2 Field3 Sum
1 1 1 10
1 2 1 10
1 3 1 30

qry2:
Field1 Field2 Field3 Sum
1 1 2 10
1 2 2 10
1 3 2 30

I've written a 3rd query that sums up the "sum" field of the two queries on Field1 and Field2:

qry3
Field1 Field2 Field3 Sum (qry1.Sum + qry2.sum)
1 1 1 20
1 1 2 20
1 2 1 20
1 2 2 20
1 3 1 60
1 3 2 60

And what I really need is for this query to display:
qry3
Field1 Field2 Field3 Sum (qry1.Sum + qry2.sum)
1 1 1+2 20
1 2 1+2 20
1 3 1+2 60

I was really hoping to be able to this within the query rather than in code... but I guess, if there's no solution?....

cheers!

 

[sevenhalo]

I overthought your example; I was trying to make sense of the random data.

Couldn't you accomplish this with "GROUP BY/HAVING"s? I'm not even going to take a stab at drawing up an example; Many_inner_joined_tables' isn't exactly descriptive.

Here's a good tutorial on it:
http://www.w3schools.com/sql/sql_groupby.asp

 

[jmcilhinney]

I have no idea what those numbers mean so they are no help whatsoever. It was my initial understanding that you wanted to vary the actual number of columns returned depending on circumstances. As far as I'm aware that is impossible. You can use the CASE function to change each column depending on the situation but the number of columns cannot change, to the best of my knowledge. If my understanding of your requirements is incorretc then by all means restate them, but I'm afraid that that example isn't much help witout additional background.