CASE içinde SELECT sorunu

CASE 
WHEN P.MODULENR=4 THEN (SELECT BRANCH FROM LG_044_01_INVOICE (nolock) WHERE LG_044_01_INVOICE.LOGICALREF=P.FICHEREF)
WHEN P.MODULENR=7 THEN (SELECT BRANCH FROM LG_044_01_BNFLINE (nolock) WHERE LG_044_01_BNFLINE.LOGICALREF=P.FICHEREF) 
WHEN P.MODULENR=10 THEN (SELECT BRANCH FROM LG_044_01_KSLINES (nolock) WHERE LG_044_01_KSLINES.LOGICALREF=P.FICHEREF) 
WHEN P.MODULENR=6 THEN (SELECT BRANCH FROM LG_044_01_CSROLL CSR (nolock) WHERE CSR.CARDREF=P.CARDREF AND P.FICHEREF=CSR.LOGICALREF)
WHEN P.MODULENR IN (5,61,62) THEN (SELECT BRANCH FROM LG_044_01_CLFLINE (nolock) WHERE LG_044_01_CLFLINE.LOGICALREF=P.FICHEREF) 
ELSE '' 
END AS ISYERI,

yukarıdaki sorguda BRANCH sütunlarından smallint değerler dönüyor (2901,2902 gibi). Ben bu değerlere string ifade tanımlamak istiyorum ilk satır için sorguyu şu şekilde değiştirmek istediğimde;

WHEN P.MODULENR=4 THEN (SELECT CASE BRANCH WHEN 2901 THEN 'TEST1' WHEN 2902 THEN 'TEST2' ELSE '' END FROM LG_044_01_INVOICE (nolock) WHERE LG_044_01_INVOICE.LOGICALREF=P.FICHEREF)

Conversion failed when converting the varchar value 'TEST2' to data type smallint.
hatası alıyorum. Çözümü var mıdır?

sutun veri yapisi her satirda ayni olmali
tum when case ler string dondurmeli

bir satirda branch integer
sonraki satirda string dondurdugu icin olabilir

her alt sorgu icin case yazmak yerine mevcut case blogunun disina tek seferde yazabilirsiniz

genel bir ornek

create table branches(
  branch_id int,
  branch_name varchar(50)
);

insert into branches (branch_id,branch_name) values (1,'test1');
insert into branches (branch_id,branch_name) values (2,'test2');
insert into branches (branch_id,branch_name) values (3,'test3');
insert into branches (branch_id,branch_name) values (4,'test4');

select
case
  when branch_id = 1 then 'dukkan1'  
  when branch_id = 2 then 'dukkan2'  
  when branch_id = 3 then 'dukkan3'  
  when branch_id = 4 then cast((select branch_id from branches where branch_id=1) as varchar(100))
  else ''
end ,
cast((select branch_id from branches where branch_id=1) as varchar(100))
from branches

Merhaba,

CASE bloğu içerisinden en az 1 nümerik tip dönüyorsa, tüm CASE sonucu nümerik tipte dönecektir. Bu sebeple CASE içerisindeki tüm nümerik tiplerin VARCHAR tipine dönüştürülmesi gerekiyor.

CASE içerisinde yer alan tüm BRANCH kolonlarını kısaca LTRIM() içerisine alarak VARCHAR tipine dönüştürmek sorunu çözebilir.

SELECT
    CASE
        WHEN P.MODULENR = 4 THEN (SELECT CASE INV.BRANCH WHEN 2901 THEN 'TEST1' WHEN 2902 THEN 'TEST2' ELSE '' END FROM LG_044_01_INVOICE INV WHERE INV.LOGICALREF = P.FICHEREF)
        WHEN P.MODULENR = 7 THEN (SELECT LTRIM(BNF.BRANCH) FROM LG_044_01_BNFLINE BNF WHERE BNF.LOGICALREF = P.FICHEREF)
        WHEN P.MODULENR = 10 THEN (SELECT LTRIM(KSL.BRANCH) FROM LG_044_01_KSLINES KSL WHERE KSL.LOGICALREF = P.FICHEREF)
        WHEN P.MODULENR = 6 THEN (SELECT LTRIM(CSR.BRANCH) FROM LG_044_01_CSROLL CSR WHERE CSR.LOGICALREF = P.FICHEREF)
        WHEN P.MODULENR IN (5, 61, 62) THEN (SELECT LTRIM(CLF.BRANCH) FROM LG_044_01_CLFLINE CLF WHERE CLF.LOGICALREF = P.FICHEREF)
        ELSE ''
    END AS ISYERI,

Örnek 1:

SELECT
    CASE
        WHEN T.X = 1 THEN 'TEST'
        WHEN T.X = 2 THEN T.X
    END AS Y
FROM
    (SELECT 1 AS X UNION ALL SELECT 2 AS X) T

Conversion failed when converting the varchar value 'TEST' to data type int.

Örnek 2:

SELECT
    CASE
        WHEN T.X = 2 THEN T.X
        WHEN T.X = 1 THEN 'TEST'
    END AS Y
FROM
    (SELECT 1 AS X UNION ALL SELECT 2 AS X) T

Conversion failed when converting the varchar value 'TEST' to data type int.

Örnek 3:

SELECT
    CASE
        WHEN T.X = 1 THEN 'TEST'
        WHEN T.X = 2 THEN LTRIM(T.X)
    END AS Y
FROM
    (SELECT 1 AS X UNION ALL SELECT 2 AS X) T

Hata dönmedi