şartlı sorgu

[erhan2484]

Merhaba,aşağıdaki sorguda değişkeni sorguya nasıl eklemeliyim


declare @logicalref int
select CLCARD.CODE,CLCARD.LOGICALREF=@logicalref   
from   LG_038_01_CLFLINE CLFLINE   LEFT JOIN LG_038_CLCARD CLCARD ON CLCARD.LOGICALREF=CLFLINE.CLIENTREF
WHERE   CLFLINE.CANCELLED=0   AND ((CLFLINE.TRCODE IN (38) AND
CLFLINE.DATE_ BETWEEN GETDATE()-180
AND GETDATE())  AND CLFLINE.CLIENTREF NOT IN (SELECT CLIENTREF FROM LG_038_01_CLFLINE
WHERE TRCODE IN (1 ,20,61,62,70) AND DATE_ BETWEEN GETDATE()-105 AND GETDATE()) AND CANCELLED=0)


if('LG_038_CLCARD.CODE'<>'')
begin
yasak LG_038_CLCARD SET SPECODE=1
end
ELSE
begin
yasak LG_038_CLCARD SET SPECODE=''
end

[MehmetYavuz]

burdaki declare ile tek referans alırsınız, bunu döngüye sokup sonrasında tek tek upd-ate yapılması gerekir. onun yerine 2 ayrı upd-ate yapın. daha kolay olur.

örneğin aşağıdaki gibi: (clcarda bile gerek kalmadı, clfline yetti)
upd-ate lg_038_clcard
set specode=1
where logicalref in (
select clfline.clientref
from   LG_038_01_CLFLINE CLFLINE   WHERE   CLFLINE.CANCELLED=0   AND ((CLFLINE.TRCODE IN (38) AND
CLFLINE.DATE_ BETWEEN GETDATE()-180
AND GETDATE())  AND CLFLINE.CLIENTREF NOT IN (SELECT CLIENTREF FROM LG_038_01_CLFLINE
WHERE TRCODE IN (1 ,20,61,62,70) AND DATE_ BETWEEN GETDATE()-105 AND GETDATE()) AND CANCELLED=0))

specode='' içinde şöyle:

upd-ate lg_038_clcard
set specode=''
where logicalref NOT in (
select clfline.clientref
from   LG_038_01_CLFLINE CLFLINE -- LEFT JOIN LG_038_CLCARD CLCARD ON CLCARD.LOGICALREF=CLFLINE.CLIENTREF
WHERE   CLFLINE.CANCELLED=0   AND ((CLFLINE.TRCODE IN (38) AND
CLFLINE.DATE_ BETWEEN GETDATE()-180
AND GETDATE())  AND CLFLINE.CLIENTREF NOT IN (SELECT CLIENTREF FROM LG_038_01_CLFLINE
WHERE TRCODE IN (1 ,20,61,62,70) AND DATE_ BETWEEN GETDATE()-105 AND GETDATE()) AND CANCELLED=0))

[erhan2484]

teşekkür ederim, bu sorguyu trigger a ekleyip çalıştırıcam.yalnız trigger işlemi log dosyasını aşırı arttırırmı şu anda 5 GB. 3 ay önce 40 GB olduğunda boyutu düşürmüştüm.

[erhan2484]

clientref i logicalref olarak değiştirdim çalıştı teşekkür ederim 
yasak lg_038_clcard
set specode=''
where LG_038_CLCARD.LOGICALREF NOT in (
select CLCARD.LOGICALREF
from   LG_038_01_CLFLINE CLFLINE  LEFT JOIN LG_038_CLCARD CLCARD ON CLCARD.LOGICALREF=CLFLINE.CLIENTREF
WHERE   CLFLINE.CANCELLED=0   AND ((CLFLINE.TRCODE IN (38) AND
CLFLINE.DATE_ BETWEEN GETDATE()-180
AND GETDATE())  AND CLFLINE.CLIENTREF NOT IN (SELECT CLIENTREF FROM LG_038_01_CLFLINE
WHERE TRCODE IN (1 ,20,61,62,70) AND DATE_ BETWEEN GETDATE()-105 AND GETDATE()) AND CANCELLED=0))