Create a complex view
-
Hi everyone.
This is my first time posting. I'm trying to create a complex view. The reason it is complex is because I would like to be able to have a column created based on a udf (user-defined field). The udf would be used to handle an if statement. Unless there's a way to handle if statements in a view. Is that possible?
Thanks,
Debbie
-
"IF" is not allowed in a view. But you can try to use CASE expression.
-
Debra - are you talking about an user-defined datatype or a user-defined function...I'm a little confused because you said user-defined "field"!
If you post some sample code of what you're trying to achieve I'm sure someone will come up with a solution!
**ASCII stupid question, get a stupid ANSI !!!** -
Where did you get that quote Sue (really nice
)??? -
As peter said, use a CASE statement. However, if the logic is very complex i.e. if it is not simple conditional CASE statements and will involve complex sub-queries within the case statement, then look into avoiding it by making use of derived tables i.e. the sub-set in the FROM clause that acts as a derived table...the reason for that is because if you have too complex SQL in the select list (potentially sub-queries in conditional case statements), then that will get fired as many times as the number of records returned from the main query and will consume too much resources.
An example of CASE statement:
select
.
.
(CASE WHEN ... THEN ...
WHEN ... THEN ...
ELSE ...
END)
.
.
An example of derived table:
SELECT
.
.
.
FROM X
INNER JOIN (SELECT .... FROM... WHERE...) DT
ON X.COL1 = DT.COL2
WHERE ....
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply