SQLTeam.com | Weblogs | Forums

Case or value expression in openrowset

#1

Table have columns OpenAmount, Period 1, Period2, Period3.... so on.

I need to write SELECT statement that will require Period input runtime.
If entered value=1
OpenAmount + Period1
If entered value=2
OpenAmount + Period1+Period2
If entered value=3
OpenAmount + Period1+Period2+Period3

I am using openrowset

#2

You'll have to use dynamic sql to achieve this. I would ask why. If this is for reporting, then the report can control what is shown and not shown. Otherwise, when constructing the dynamic sql, I would make sure the column names you are generating exist in the database (i.e. Period87)

#3

It may not require dynamic SQL, but that depends entirely on how the OPENROWSET is structured. An example query, with sample data and expected output, would really help here, especially regarding how that entered value is passed (parameter?)

If it's in the most basic form, you could do this:

SELECT OpenAmount + CASE @value
WHEN 1 THEN Period1
WHEN 2 THEN Period1+Period2
WHEN 3 THEN Period1+Period2+Period3
WHEN 4 THEN Period1+Period2+Period3+Period4
WHEN 5 THEN Period1+Period2+Period3+Period4+Period5
-- continue with all Period columns
ELSE 0 END FullAmount
FROM OPENROWSET(openrowset_parameters) alias_name