CODEWITHSUNDEEP
sqlsql-server
Nobody
Nobody

Reputation: 539

Case query is not working

New to SQL and any help would be welcomed.

Can anyone advise on the best approach for the creating the following query.

   _hs_eb_code1 = 'bf', then set format = _hs_eb_det1
   _hs_eb_code# = 'bf', then set format = _hs_eb_det#

Repeat until _hs_eb_code# (1-9),

However if the character length of _hs_eb_det1 exceeds 100 characters, then prefix with '*'. In addition _hs_eb_code1 = 'bf' is one, while _hs_eb_det1 will provide many in terms of results.

So far, I can use case query to check for the first part, then build another query. I get lost.

The query:

 SELECT CASE 
    WHEN LEN(FORMAT) < 100 THEN LEFT(FORMAT,100)
    ELSE '*'+FORMAT 
 END AS FORMAT
FROM (SELECT CASE

           WHEN EXISTS (SELECT _HS_EB_CODE1 FROM DBO.SRC_HBL_CLNT_CAT  WHERE   _HS_EB_CODE1 = 'BF') THEN _HS_EB_DET1 
       WHEN EXISTS (SELECT _HS_EB_CODE2 FROM DBO.SRC_HBL_CLNT_CAT WHERE _HS_EB_CODE2 = 'BF') THEN _HS_EB_DET2
             WHEN EXISTS (SELECT _HS_EB_CODE3 FROM DBO.SRC_HBL_CLNT_CAT WHERE _HS_EB_CODE3 = 'BF') THEN _HS_EB_DET3     
             WHEN EXISTS (SELECT _HS_EB_CODE4 FROM DBO.SRC_HBL_CLNT_CAT WHERE _HS_EB_CODE4 = 'BF') THEN _HS_EB_DET4
           WHEN EXISTS (SELECT _HS_EB_CODE5 FROM DBO.SRC_HBL_CLNT_CAT WHERE _HS_EB_CODE5 = 'BF') THEN _HS_EB_DET5
             WHEN EXISTS (SELECT _HS_EB_CODE6 FROM DBO.SRC_HBL_CLNT_CAT WHERE _HS_EB_CODE6 = 'BF') THEN _HS_EB_DET6
           WHEN EXISTS (SELECT _HS_EB_CODE7 FROM DBO.SRC_HBL_CLNT_CAT WHERE _HS_EB_CODE7 = 'BF') THEN _HS_EB_DET7
           WHEN EXISTS (SELECT _HS_EB_CODE8 FROM DBO.SRC_HBL_CLNT_CAT WHERE _HS_EB_CODE8 = 'BF') THEN _HS_EB_DET8
            WHEN EXISTS (SELECT _HS_EB_CODE9 FROM DBO.SRC_HBL_CLNT_CAT WHERE  _HS_EB_CODE9 = 'BF') THEN _HS_EB_DET9
END ) AS FORMAT
    FROM DBO.SRC_TBM_BILLGRP

But I have a problem in that the query has one to many, therefore fails to work. WHEN EXISTS (SELECT _HS_EB_CODE1 FROM DBO.SRC_HBL_CLNT_CAT WHERE _HS_EB_CODE1 = 'BF') THEN _HS_EB_DET1

Upvotes: 0

Views: 108

Answers (1)

t-clausen.dk
t-clausen.dk

Reputation: 44316

select case when len(format) > 100 then left(format, 100) + '*' else format end as format from
(select case 'bf' when _hs_eb_code1 then _hs_eb_det1
              when _hs_eb_code2 then _hs_eb_det2
              when _hs_eb_code3 then _hs_eb_det3
              when _hs_eb_code4 then _hs_eb_det4
              when _hs_eb_code5 then _hs_eb_det5
              when _hs_eb_code6 then _hs_eb_det6
              when _hs_eb_code7 then _hs_eb_det7
              when _hs_eb_code8 then _hs_eb_det8
              when _hs_eb_code9 then _hs_eb_det9
              end) as format
    from <yourtable>) a

Upvotes: 2

Related Questions