Share via

Return Query Line Reference from Case When Line = True

Ross Crill 0 Reputation points
2023-03-21T10:25:11.6766667+00:00

I have a query that returns values from a "Case When" statement with hundreds of conditions. I want to include in the Select statement, the code row reference used to retrieve the correct condition (shown in Code Line Ref 16).

Here is an example: The green line is the line reference of the correct condition (#8): Which is 'MN' which came from line Ref #8 in the case statement.

How can I return the code line used from the Case statement?

User's image

SQL Server Other

2 answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Erland Sommarskog 121.4K Reputation points MVP Volunteer Moderator
    2023-03-21T22:06:43.5+00:00

    Sorry, there is no support for this.

    Then again, CASE expressions with that many conditions suggests that your design is the best. Looking at your example, a much better solution is to have a lookup table States with two columns, Code and Name. If you go that route, you may no longer desire to have the line number.

  2. Olaf Helper 47,436 Reputation points
    2023-03-22T06:36:42.6233333+00:00

    And if a comment line, should the "line number" result change as well? Not possible.

    Add the same CASE statement to return the information, which CASE condition meats.

    0 comments

Your answer

Answers can be marked as Accepted Answers by the question author, which helps users to know the answer solved the author's problem.