All-
Please advise as to what's the best way to display data in a column in a
grid view (and I'm using a dataset) in a table (say TABLE2) which:
- is non-zero only if a boolian field in the same row is true, and
- when the boolean field is true, gets its value FROM ANOTHER TABLE?
Say we have these two tables:
TABLE1
table1_id (int)
some_number (int)
TABLE2
table2_id (int)
table1_id (int, FK)
is_coming (bit)
Now, I display TABLE2 in a gridview and add, say, a calculated column named
"value". So again, I'd like the logic for the calculated field to be as
follows:
- If is_coming = False, then dispaly "0" or nothing
- If is_coming = True, then display the corresponding some_number value from
TABLE1.
So what I think I need is some type of conditional select statement. The
last message from someone on a forum suggested using this select statement in
a stored procedure:
1 SELECT
2
3 table2.table2_id,
4 table2.is_coming,
5 CASE table2.is_coming WHEN 'False' THEN '0' ELSE table1.some_number
END AS 'MyArtificialColumn'
6
7 FROM table2
8
9 INNER JOIN table1
10 ON table2.table1_id = table1.table1_id
However, I'm not sure how to display the value returned by the SP in the
gridview column. Note, too, that I'd want the ability to sum the column in
the gridview footer (but I think I can handle that).
Searching the internet, I've only been able to find out that Expressions in
calculated fields can only use values from the same table (no lookups in
other tables). Another suggested that the value of the field could be
populated by using INSERT or UPDATE trigger on table2. Not sure how to
proceed. Any ideas would be appreciated.
Thanks!
-Kurt
