1

I am looking for a better approach to writing highly repetitive sql statements. I know I can do this by, for example, using PHP methods, but I would like a pure SQL solution. I am using Sybase.

select
"SvHKeyId"=sh.KeyId,
"Scale"="Partial PTSD",
"ScoreText"=convert(varchar(3),
    (case
        when
            (case
                when
                    1 in (select Resp from SurvResp sr where sh.KeyId=sr.SvHKeyId and sr.QuestNum between 1 and 13)
                    and 1 in (select Resp from SurvResp sr where sh.KeyId=sr.SvHKeyId and sr.QuestNum between 15 and 21)
                    and 1 in (select Resp from SurvResp sr where sh.KeyId=sr.SvHKeyId and sr.QuestNum between 22 and 26)
                then 'Yes'
                else 'No'
            end)
        = 'Yes'
        and
        (case
            when
                (case
                    when
                        (select Resp from SurvResp sr where sh.KeyId=sr.SvHKeyId and sr.QuestNum=30) >= 2
                        or (select Resp from SurvResp sr where sh.KeyId=sr.SvHKeyId and sr.QuestNum=32) >= 2
                        or (select Resp from SurvResp sr where sh.KeyId=sr.SvHKeyId and sr.QuestNum=33) >= 2
                        or (select Resp from SurvResp sr where sh.KeyId=sr.SvHKeyId and sr.QuestNum=29) >= 2
                        or (select Resp from SurvResp sr where sh.KeyId=sr.SvHKeyId and sr.QuestNum=45) >= 2
                    then 1
                    else 0
                end)
                +
                (case
                    when
                        (case when (select Resp from SurvResp sr where sh.KeyId=sr.SvHKeyId and sr.QuestNum=36) >= 2 then 1 else 0 end)
                        + (case when (select Resp from SurvResp sr where sh.KeyId=sr.SvHKeyId and sr.QuestNum=44) >= 2 then 1 else 0 end)
                        + (case when (select Resp from SurvResp sr where sh.KeyId=sr.SvHKeyId and sr.QuestNum=42) >= 2 then 1 else 0 end)
                        + (case when (select Resp from SurvResp sr where sh.KeyId=sr.SvHKeyId and sr.QuestNum=34) >= 2 then 1 else 0 end)
                        + (case when (select Resp from SurvResp sr where sh.KeyId=sr.SvHKeyId and sr.QuestNum=35) >= 2 then 1 else 0 end)
                        + (case when (select Resp from SurvResp sr where sh.KeyId=sr.SvHKeyId and sr.QuestNum=46) >= 2 then 1 else 0 end)
                    >= 3 then 1
                    else 0
                end)
                +
                (case
                    when
                        (case when (select Resp from SurvResp sr where sh.KeyId=sr.SvHKeyId and sr.QuestNum=40) >= 2 then 1 else 0 end)
                        + (case when (select Resp from SurvResp sr where sh.KeyId=sr.SvHKeyId and sr.QuestNum=31) >= 2 then 1 else 0 end)
                        + (case when (select Resp from SurvResp sr where sh.KeyId=sr.SvHKeyId and sr.QuestNum=43) >= 2 then 1 else 0 end)
                        + (case when (select Resp from SurvResp sr where sh.KeyId=sr.SvHKeyId and sr.QuestNum=28) >= 2 then 1 else 0 end)
                        + (case when (select Resp from SurvResp sr where sh.KeyId=sr.SvHKeyId and sr.QuestNum=39) >= 2 then 1 else 0 end)
                    >= 2 then 1
                    else 0
                end)
            >=2
            then 'Yes'
            else 'No'
        end)
        = 'Yes'
        then 'Yes'
        else 'No'
    end)
    ),
 "ScoreNum"=
    (case
        when
            (case
                when
                    1 in (select Resp from SurvResp sr where sh.KeyId=sr.SvHKeyId and sr.QuestNum between 1 and 13)
                    and 1 in (select Resp from SurvResp sr where sh.KeyId=sr.SvHKeyId and sr.QuestNum between 15 and 21)
                    and 1 in (select Resp from SurvResp sr where sh.KeyId=sr.SvHKeyId and sr.QuestNum between 22 and 26)
                then 'Yes'
                else 'No'
            end)
        = 'Yes'
        and
        (case
            when
                (case
                    when
                        (select Resp from SurvResp sr where sh.KeyId=sr.SvHKeyId and sr.QuestNum=30) >= 2
                        or (select Resp from SurvResp sr where sh.KeyId=sr.SvHKeyId and sr.QuestNum=32) >= 2
                        or (select Resp from SurvResp sr where sh.KeyId=sr.SvHKeyId and sr.QuestNum=33) >= 2
                        or (select Resp from SurvResp sr where sh.KeyId=sr.SvHKeyId and sr.QuestNum=29) >= 2
                        or (select Resp from SurvResp sr where sh.KeyId=sr.SvHKeyId and sr.QuestNum=45) >= 2
                    then 1
                    else 0
                end)
                +
                (case
                    when
                        (case when (select Resp from SurvResp sr where sh.KeyId=sr.SvHKeyId and sr.QuestNum=36) >= 2 then 1 else 0 end)
                        + (case when (select Resp from SurvResp sr where sh.KeyId=sr.SvHKeyId and sr.QuestNum=44) >= 2 then 1 else 0 end)
                        + (case when (select Resp from SurvResp sr where sh.KeyId=sr.SvHKeyId and sr.QuestNum=42) >= 2 then 1 else 0 end)
                        + (case when (select Resp from SurvResp sr where sh.KeyId=sr.SvHKeyId and sr.QuestNum=34) >= 2 then 1 else 0 end)
                        + (case when (select Resp from SurvResp sr where sh.KeyId=sr.SvHKeyId and sr.QuestNum=35) >= 2 then 1 else 0 end)
                        + (case when (select Resp from SurvResp sr where sh.KeyId=sr.SvHKeyId and sr.QuestNum=46) >= 2 then 1 else 0 end)
                    >= 3 then 1
                    else 0
                end)
                +
                (case
                    when
                        (case when (select Resp from SurvResp sr where sh.KeyId=sr.SvHKeyId and sr.QuestNum=40) >= 2 then 1 else 0 end)
                        + (case when (select Resp from SurvResp sr where sh.KeyId=sr.SvHKeyId and sr.QuestNum=31) >= 2 then 1 else 0 end)
                        + (case when (select Resp from SurvResp sr where sh.KeyId=sr.SvHKeyId and sr.QuestNum=43) >= 2 then 1 else 0 end)
                        + (case when (select Resp from SurvResp sr where sh.KeyId=sr.SvHKeyId and sr.QuestNum=28) >= 2 then 1 else 0 end)
                        + (case when (select Resp from SurvResp sr where sh.KeyId=sr.SvHKeyId and sr.QuestNum=39) >= 2 then 1 else 0 end)
                    >= 2 then 1
                    else 0
                end)
            >=2
            then 'Yes'
            else 'No'
        end)
        = 'Yes'
        then 1
        else 0
    end)
from
    SurvHeader sh
where
    sh.KeyId=105101

This is a scoring procedure. I am comparing a series of sub-scores to derive an overall score. Is there a better SQL approach? I suppose I could use a stored procedure?

Here is an even more extreme example where I use so many subqueries I exceed the limit:

select
"SvHKeyId"=sh.KeyId,
"Scale"="Total",
"ScoreText"=convert(varchar(3),
       (select sum(Resp) from SurvResp sr where sh.KeyId=sr.SvHKeyId
       and QuestNum in (28,29,30,31,32,33,34,35,36,39,40,42,43,44,45,46) and Resp <> 5)
       + 
       (case
            when
                (select Resp from SurvResp sr where sh.KeyId=sr.SvHKeyId and sr.QuestNum=37) <> 5
                and (select Resp from SurvResp sr where sh.KeyId=sr.SvHKeyId and sr.QuestNum=38) <> 5
            then
                (case
                    when
                        (select Resp from SurvResp sr where sh.KeyId=sr.SvHKeyId and sr.QuestNum=37)
                        > (select Resp from SurvResp sr where sh.KeyId=sr.SvHKeyId and sr.QuestNum=38)
                    then
                        (select Resp from SurvResp sr where sh.KeyId=sr.SvHKeyId and sr.QuestNum=37)
                    when
                        (select Resp from SurvResp sr where sh.KeyId=sr.SvHKeyId and sr.QuestNum=37)
                        < (select Resp from SurvResp sr where sh.KeyId=sr.SvHKeyId and sr.QuestNum=38)
                    then
                        (select Resp from SurvResp sr where sh.KeyId=sr.SvHKeyId and sr.QuestNum=38)
                    when
                        (select Resp from SurvResp sr where sh.KeyId=sr.SvHKeyId and sr.QuestNum=37)
                        = (select Resp from SurvResp sr where sh.KeyId=sr.SvHKeyId and sr.QuestNum=38)
                    then
                        (select Resp from SurvResp sr where sh.KeyId=sr.SvHKeyId and sr.QuestNum=38)
                end)
            else
                (case
                    when
                        (select Resp from SurvResp sr where sh.KeyId=sr.SvHKeyId and sr.QuestNum=37) = 5
                        and (select Resp from SurvResp sr where sh.KeyId=sr.SvHKeyId and sr.QuestNum=38) = 5
                    then 0
                    else
                        (case
                            when (select Resp from SurvResp sr where sh.KeyId=sr.SvHKeyId and sr.QuestNum=37) = 5
                            then (select Resp from SurvResp sr where sh.KeyId=sr.SvHKeyId and sr.QuestNum=38)
                            when (select Resp from SurvResp sr where sh.KeyId=sr.SvHKeyId and sr.QuestNum=38) = 5
                            then (select Resp from SurvResp sr where sh.KeyId=sr.SvHKeyId and sr.QuestNum=37)
                            else 0
                        end)
                 end)
        end)
        +
       (case
            when
                (select Resp from SurvResp sr where sh.KeyId=sr.SvHKeyId and sr.QuestNum=31) <> 5
                and (select Resp from SurvResp sr where sh.KeyId=sr.SvHKeyId and sr.QuestNum=47) <> 5
            then
                (case
                    when
                        (select Resp from SurvResp sr where sh.KeyId=sr.SvHKeyId and sr.QuestNum=31)
                        > (select Resp from SurvResp sr where sh.KeyId=sr.SvHKeyId and sr.QuestNum=47)
                    then
                        (select Resp from SurvResp sr where sh.KeyId=sr.SvHKeyId and sr.QuestNum=31)
                    when
                        (select Resp from SurvResp sr where sh.KeyId=sr.SvHKeyId and sr.QuestNum=31)
                        < (select Resp from SurvResp sr where sh.KeyId=sr.SvHKeyId and sr.QuestNum=47)
                    then
                        (select Resp from SurvResp sr where sh.KeyId=sr.SvHKeyId and sr.QuestNum=47)
                    when
                        (select Resp from SurvResp sr where sh.KeyId=sr.SvHKeyId and sr.QuestNum=31)
                        = (select Resp from SurvResp sr where sh.KeyId=sr.SvHKeyId and sr.QuestNum=47)
                    then
                        (select Resp from SurvResp sr where sh.KeyId=sr.SvHKeyId and sr.QuestNum=47)
                end)
            else
                (case
                    when
                        (select Resp from SurvResp sr where sh.KeyId=sr.SvHKeyId and sr.QuestNum=31) = 5
                        and (select Resp from SurvResp sr where sh.KeyId=sr.SvHKeyId and sr.QuestNum=47) = 5
                    then 0
                    else
                        (case
                            when (select Resp from SurvResp sr where sh.KeyId=sr.SvHKeyId and sr.QuestNum=31) = 5
                            then (select Resp from SurvResp sr where sh.KeyId=sr.SvHKeyId and sr.QuestNum=47)
                            when (select Resp from SurvResp sr where sh.KeyId=sr.SvHKeyId and sr.QuestNum=47) = 5
                            then (select Resp from SurvResp sr where sh.KeyId=sr.SvHKeyId and sr.QuestNum=31)
                            else 0
                        end)
                 end)
        end)
        +
       (case
            when
                (select Resp from SurvResp sr where sh.KeyId=sr.SvHKeyId and sr.QuestNum=46) <> 5
                and (select Resp from SurvResp sr where sh.KeyId=sr.SvHKeyId and sr.QuestNum=48) <> 5
            then
                (case
                    when
                        (select Resp from SurvResp sr where sh.KeyId=sr.SvHKeyId and sr.QuestNum=46)
                        > (select Resp from SurvResp sr where sh.KeyId=sr.SvHKeyId and sr.QuestNum=48)
                    then
                        (select Resp from SurvResp sr where sh.KeyId=sr.SvHKeyId and sr.QuestNum=46)
                    when
                        (select Resp from SurvResp sr where sh.KeyId=sr.SvHKeyId and sr.QuestNum=46)
                        < (select Resp from SurvResp sr where sh.KeyId=sr.SvHKeyId and sr.QuestNum=48)
                    then
                        (select Resp from SurvResp sr where sh.KeyId=sr.SvHKeyId and sr.QuestNum=48)
                    when
                        (select Resp from SurvResp sr where sh.KeyId=sr.SvHKeyId and sr.QuestNum=46)
                        = (select Resp from SurvResp sr where sh.KeyId=sr.SvHKeyId and sr.QuestNum=48)
                    then
                        (select Resp from SurvResp sr where sh.KeyId=sr.SvHKeyId and sr.QuestNum=48)
                end)
            else
                (case
                    when
                        (select Resp from SurvResp sr where sh.KeyId=sr.SvHKeyId and sr.QuestNum=46) = 5
                        and (select Resp from SurvResp sr where sh.KeyId=sr.SvHKeyId and sr.QuestNum=48) = 5
                    then 0
                    else
                        (case
                            when (select Resp from SurvResp sr where sh.KeyId=sr.SvHKeyId and sr.QuestNum=46) = 5
                            then (select Resp from SurvResp sr where sh.KeyId=sr.SvHKeyId and sr.QuestNum=48)
                            when (select Resp from SurvResp sr where sh.KeyId=sr.SvHKeyId and sr.QuestNum=48) = 5
                            then (select Resp from SurvResp sr where sh.KeyId=sr.SvHKeyId and sr.QuestNum=46)
                            else 0
                        end)
                 end)
        end)
    ),
 "ScoreNum"=
       (select sum(Resp) from SurvResp sr where sh.KeyId=sr.SvHKeyId
       and QuestNum in (28,29,30,31,32,33,34,35,36,39,40,42,43,44,45,46) and Resp <> 5)
       + 
       (case
            when
                (select Resp from SurvResp sr where sh.KeyId=sr.SvHKeyId and sr.QuestNum=37) <> 5
                and (select Resp from SurvResp sr where sh.KeyId=sr.SvHKeyId and sr.QuestNum=38) <> 5
            then
                (case
                    when
                        (select Resp from SurvResp sr where sh.KeyId=sr.SvHKeyId and sr.QuestNum=37)
                        > (select Resp from SurvResp sr where sh.KeyId=sr.SvHKeyId and sr.QuestNum=38)
                    then
                        (select Resp from SurvResp sr where sh.KeyId=sr.SvHKeyId and sr.QuestNum=37)
                    when
                        (select Resp from SurvResp sr where sh.KeyId=sr.SvHKeyId and sr.QuestNum=37)
                        < (select Resp from SurvResp sr where sh.KeyId=sr.SvHKeyId and sr.QuestNum=38)
                    then
                        (select Resp from SurvResp sr where sh.KeyId=sr.SvHKeyId and sr.QuestNum=38)
                    when
                        (select Resp from SurvResp sr where sh.KeyId=sr.SvHKeyId and sr.QuestNum=37)
                        = (select Resp from SurvResp sr where sh.KeyId=sr.SvHKeyId and sr.QuestNum=38)
                    then
                        (select Resp from SurvResp sr where sh.KeyId=sr.SvHKeyId and sr.QuestNum=38)
                end)
            else
                (case
                    when
                        (select Resp from SurvResp sr where sh.KeyId=sr.SvHKeyId and sr.QuestNum=37) = 5
                        and (select Resp from SurvResp sr where sh.KeyId=sr.SvHKeyId and sr.QuestNum=38) = 5
                    then 0
                    else
                        (case
                            when (select Resp from SurvResp sr where sh.KeyId=sr.SvHKeyId and sr.QuestNum=37) = 5
                            then (select Resp from SurvResp sr where sh.KeyId=sr.SvHKeyId and sr.QuestNum=38)
                            when (select Resp from SurvResp sr where sh.KeyId=sr.SvHKeyId and sr.QuestNum=38) = 5
                            then (select Resp from SurvResp sr where sh.KeyId=sr.SvHKeyId and sr.QuestNum=37)
                            else 0
                        end)
                 end)
        end)
        +
       (case
            when
                (select Resp from SurvResp sr where sh.KeyId=sr.SvHKeyId and sr.QuestNum=31) <> 5
                and (select Resp from SurvResp sr where sh.KeyId=sr.SvHKeyId and sr.QuestNum=47) <> 5
            then
                (case
                    when
                        (select Resp from SurvResp sr where sh.KeyId=sr.SvHKeyId and sr.QuestNum=31)
                        > (select Resp from SurvResp sr where sh.KeyId=sr.SvHKeyId and sr.QuestNum=47)
                    then
                        (select Resp from SurvResp sr where sh.KeyId=sr.SvHKeyId and sr.QuestNum=31)
                    when
                        (select Resp from SurvResp sr where sh.KeyId=sr.SvHKeyId and sr.QuestNum=31)
                        < (select Resp from SurvResp sr where sh.KeyId=sr.SvHKeyId and sr.QuestNum=47)
                    then
                        (select Resp from SurvResp sr where sh.KeyId=sr.SvHKeyId and sr.QuestNum=47)
                    when
                        (select Resp from SurvResp sr where sh.KeyId=sr.SvHKeyId and sr.QuestNum=31)
                        = (select Resp from SurvResp sr where sh.KeyId=sr.SvHKeyId and sr.QuestNum=47)
                    then
                        (select Resp from SurvResp sr where sh.KeyId=sr.SvHKeyId and sr.QuestNum=47)
                end)
            else
                (case
                    when
                        (select Resp from SurvResp sr where sh.KeyId=sr.SvHKeyId and sr.QuestNum=31) = 5
                        and (select Resp from SurvResp sr where sh.KeyId=sr.SvHKeyId and sr.QuestNum=47) = 5
                    then 0
                    else
                        (case
                            when (select Resp from SurvResp sr where sh.KeyId=sr.SvHKeyId and sr.QuestNum=31) = 5
                            then (select Resp from SurvResp sr where sh.KeyId=sr.SvHKeyId and sr.QuestNum=47)
                            when (select Resp from SurvResp sr where sh.KeyId=sr.SvHKeyId and sr.QuestNum=47) = 5
                            then (select Resp from SurvResp sr where sh.KeyId=sr.SvHKeyId and sr.QuestNum=31)
                            else 0
                        end)
                 end)
        end)
        +
       (case
            when
                (select Resp from SurvResp sr where sh.KeyId=sr.SvHKeyId and sr.QuestNum=46) <> 5
                and (select Resp from SurvResp sr where sh.KeyId=sr.SvHKeyId and sr.QuestNum=48) <> 5
            then
                (case
                    when
                        (select Resp from SurvResp sr where sh.KeyId=sr.SvHKeyId and sr.QuestNum=46)
                        > (select Resp from SurvResp sr where sh.KeyId=sr.SvHKeyId and sr.QuestNum=48)
                    then
                        (select Resp from SurvResp sr where sh.KeyId=sr.SvHKeyId and sr.QuestNum=46)
                    when
                        (select Resp from SurvResp sr where sh.KeyId=sr.SvHKeyId and sr.QuestNum=46)
                        < (select Resp from SurvResp sr where sh.KeyId=sr.SvHKeyId and sr.QuestNum=48)
                    then
                        (select Resp from SurvResp sr where sh.KeyId=sr.SvHKeyId and sr.QuestNum=48)
                    when
                        (select Resp from SurvResp sr where sh.KeyId=sr.SvHKeyId and sr.QuestNum=46)
                        = (select Resp from SurvResp sr where sh.KeyId=sr.SvHKeyId and sr.QuestNum=48)
                    then
                        (select Resp from SurvResp sr where sh.KeyId=sr.SvHKeyId and sr.QuestNum=48)
                end)
            else
                (case
                    when
                        (select Resp from SurvResp sr where sh.KeyId=sr.SvHKeyId and sr.QuestNum=46) = 5
                        and (select Resp from SurvResp sr where sh.KeyId=sr.SvHKeyId and sr.QuestNum=48) = 5
                    then 0
                    else
                        (case
                            when (select Resp from SurvResp sr where sh.KeyId=sr.SvHKeyId and sr.QuestNum=46) = 5
                            then (select Resp from SurvResp sr where sh.KeyId=sr.SvHKeyId and sr.QuestNum=48)
                            when (select Resp from SurvResp sr where sh.KeyId=sr.SvHKeyId and sr.QuestNum=48) = 5
                            then (select Resp from SurvResp sr where sh.KeyId=sr.SvHKeyId and sr.QuestNum=46)
                            else 0
                        end)
                 end)
        end)
from
    SurvHeader sh
where sh.KeyId=105101
Tim Lehner
  • 14,813
  • 4
  • 59
  • 76
MaxPowers
  • 474
  • 1
  • 4
  • 18
  • 1
    "I would like a pure SQL solution" - why? what you currently do is pretty much unreadable... – Karoly Horvath May 10 '13 at 17:24
  • Perhaps pure SQL is not the way to go. The reason I wanted to explore this was that with a pure SQL solution the scoring procedure becomes independent of the application - so it is more portable. – MaxPowers May 10 '13 at 17:27
  • And similarly, you could say that this SQL code is very restircting, moving the logic to the application would create a solution that's independent of the SQL engine... Or, in short, it boils down to this: do you need that independence? – Karoly Horvath May 10 '13 at 22:35

1 Answers1

2

Most of these literals should be data, not code. I strongly recommend refactoring these giant switches into data so a much simpler query can evaluate it. A "pure SQL" solution would then be much more feasible, and both performance and maintenance will be better in the long run. Maybe a Scoring table that you can join on and compare to?

Scoring (Question, Answer, Result) is one conceptual possibility, hopefully not too oversimplified.

As has been said, "You can write FORTRAN in any language."

Even T-SQL, it seems.

UPDATE

If these calculations have already been done and you've updated tables within your current transaction, there is no need to re-do them simply because they aren't committed yet. Any query within this transaction will get this transaction's updated data if it was updated earlier in the transaction.

More on Sybase ASE transactions.

Also, you shouldn't expect GO to work in a stored procedure. It is used as a batch separator in your IDE, not transaction control.

Community
  • 1
  • 1
Tim Lehner
  • 14,813
  • 4
  • 59
  • 76
  • They actually are being saved as data into a scoring table - previous queries are generating scores for the same sub-scales in an earlier statement. Perhaps my folly was doing it in the same transaction - because I cannot select the result until the transaction is complete - which is why I had the redundancy. Is there a way to select a value from a transaction that is not yet committed when you are in that same transaction? Otherwise I could potentially have orphan values if I use separate transactions and something goes awry. – MaxPowers May 10 '13 at 17:52
  • It would still seem to me that you'd want to represent all of these `case`s as rows in a table. – Tim Lehner May 10 '13 at 17:59
  • While you're inside a transaction, all of the prior changes done within that transaction will be visible to you. – Tim Lehner May 10 '13 at 18:01
  • I believe referencing the sub-scores I already scored would be more effective - it would greatly reduce the amount of switches without fragmenting the sql statements unnecessarily. – MaxPowers May 10 '13 at 18:03
  • So if I..."begin transaction insert into table select 'KeyId'=123, 'Score'=45 go select * from table where KeyId = 123 ...I can get the score? – MaxPowers May 10 '13 at 18:06
  • I wouldn't use `GO` [inside a transaction](http://stackoverflow.com/questions/971177/using-go-within-a-transaction) like that, but you will get the inserted record from that select while you're within that transaction. No harm in actually trying it, yeah? – Tim Lehner May 10 '13 at 18:13
  • Indeed - was just a dumb example, and yes I tested it a second after posting :). Thank you very much for solving my issue. Can you please add something about transactions to your answer and I'll accept it! – MaxPowers May 10 '13 at 18:15