error in sql query

Question

         (SELECT SUM(CONVERT(FLOAT, CASE '01'     WHEN '01' THEN       CASE SIGN((sltr_tran_amt - ISNULL(sltr_matched_amt, 0)))        WHEN 1 THEN          CASE DBO.glas_aging_oth(CONVERT(DATETIME, '2009/04/04') - FLOOR(sltr_pstng_date))           WHEN 0 THEN (sltr_tran_amt - ISNULL(sltr_matched_amt, 0))           ELSE 0          END        ELSE 0       END     WHEN '02' THEN       CASE SIGN((sltr_tran_amt - ISNULL(sltr_matched_amt, 0)))        WHEN - 1 THEN          CASE DBO.glas_aging_oth(CONVERT(DATETIME, '2009/04/04') - FLOOR(sltr_doc_date))           WHEN 0 THEN (sltr_tran_amt - ISNULL(sltr_matched_amt, 0))           ELSE 0          END        ELSE 0       END    END)) FROM   WHERE     CASE '01'     WHEN '01' THEN sltr_pstng_date     ELSE sltr_doc_date    END  =    CASE '01'     WHEN '01' THEN sltr_pstng_date     ELSE sltr_doc_date    END ) thirty_days,
         from glas_sl_transactions

hai friends this is my query when i execute this query error is ocurring near 'where' how to avoid this if possible pls tel me how to shorten this query

Answer 1

Somewhere deep in the middle of that query, you have FROM WHERE - ie. you are missing a table name after the FROM.

Also, you can make it shorter by removing all the CASE '1' WHEN '1' THEN bits, since '1' is always '1'.

Edit: Ok, here's my reformatting of it:

(   SELECT 
        SUM(
            CONVERT(
                FLOAT, 
                CASE '01' 
                    WHEN '01' THEN 
                        CASE SIGN((sltr_tran_amt - ISNULL(sltr_matched_amt, 0)))
                            WHEN 1 THEN
                                CASE DBO.glas_aging_oth(CONVERT(DATETIME, '2009/04/04') - FLOOR(sltr_pstng_date))
                                    WHEN 0 THEN (sltr_tran_amt - ISNULL(sltr_matched_amt, 0))
                                    ELSE 0
                                END
                            ELSE 0
                        END
                    WHEN '02' THEN
                        CASE SIGN((sltr_tran_amt - ISNULL(sltr_matched_amt, 0)))
                            WHEN -1 THEN
                                CASE DBO.glas_aging_oth(CONVERT(DATETIME, '2009/04/04') - FLOOR(sltr_doc_date))
                                    WHEN 0 THEN (sltr_tran_amt - ISNULL(sltr_matched_amt, 0))
                                    ELSE 0
                                END
                            ELSE 0
                        END
                    END
            )
        ) 
    FROM
    WHERE
        CASE '01' 
            WHEN '01' THEN sltr_pstng_date
            ELSE sltr_doc_date
        END  = 
        CASE '01' 
            WHEN '01' THEN sltr_pstng_date
            ELSE sltr_doc_date
        END 
) thirty_days,
from glas_sl_transactions

So it looks like you are also missing an END in that gap before the first closing bracket above the FROM.

Also, it contains a lot of code that will never be executed. Eg.

CASE '01' 
   WHEN '01' /* always executed */ 
   WHEN '02' /* never executed */ 
END

The where-clause also boils down to 1=1, and can therefore be removed.

In conclusion, here's what I think it should look like.

SELECT SUM( CONVERT(
    FLOAT, 
    CASE SIGN((sltr_tran_amt - ISNULL(sltr_matched_amt, 0)))
        WHEN 1 THEN
            CASE DBO.glas_aging_oth(CONVERT(DATETIME, '2009/04/04') - FLOOR(sltr_pstng_date))
                WHEN 0 THEN (sltr_tran_amt - ISNULL(sltr_matched_amt, 0))
                ELSE 0
            END
        ELSE 0
    END
)) 
from glas_sl_transactions