SELECT TOP 100 PERCENT
X.Ay,
X.Gun,
X.Ambar,
x.Stok_AktifPasif,
x.Stok_Kodu,
x.Stok_Adı,
x.MalzemeAciklamasi2,
x.MalzemeAciklamasi3,
x.MalzemeAciklamasi4,
x.[Bırım],
x.Malzeme_Turu,
x.GrupKodu,
x.OzelKod,
x.OzelKod2,
x.OzelKod3,
x.OzelKod4,
x.OzelKod5,
GunHareketMiktar =SUM([Miktar]),
Stok = round((select sum(CAST((CASE WHEN S.IOCODE IN (1, 2) THEN 1 ELSE -1 END) * S.AMOUNT * (CASE WHEN ISNULL(UINFO2, 0) = 0 THEN 1 ELSE UINFO2 END) / (CASE WHEN ISNULL(UINFO1, 0) = 0 THEN 1 ELSE UINFO1 END) AS float))
from LG_211_01_STLINE S where S.STFICHEREF IN (SELECT STF.LOGICALREF FROM LG_211_01_STFICHE STF
WHERE
STF.LOGICALREF = S.STFICHEREF AND STF.CANCELLED=0) AND S.STOCKREF=X.ITEMLOGICALREF AND S.DATE_<=X.Gun AND S.SOURCEINDEX=X.SOURCEINDEX AND S.CANCELLED = 0 AND S.LINETYPE = 0 AND S.IOCODE IN (1, 2, 3, 4) AND S.LPRODSTAT = 0 ),4)
FROM (SELECT
STLINE.SOURCEINDEX,
ITEMLOGICALREF=STLINE.STOCKREF,
Ay=CAST(YEAR(STFICHE.DATE_) AS VARCHAR)+(CASE WHEN LEN(MONTH(STFICHE.DATE_))=1 THEN '0'+CAST(MONTH(STFICHE.DATE_) AS VARCHAR) ELSE CAST(MONTH(STFICHE.DATE_) AS VARCHAR) END),
Gun=STLINE.DATE_,
STFICHE.FICHENO,
Ambar = (SELECT
CAST(NR AS varchar) + '-' + NAME
FROM L_CAPIWHOUSE CAPIWHOUSE
WHERE CAPIWHOUSE.NR = STLINE.SOURCEINDEX
AND CAPIWHOUSE.FIRMNR = 211),
Isyeri = (SELECT
CAST(NR AS varchar) + '-' + NAME
FROM L_CAPIDIV CAPIDIV
WHERE CAPIDIV.NR = STFICHE.BRANCH
AND CAPIDIV.FIRMNR = 211),
STFICHE.LOGICALREF [STFICHE_REFERANS],
Stok_AktifPasif =case when ITEMS.ACTIVE=0 then 'Aktif' else 'Pasif' end,
Stok_Kodu = ITEMS.CODE,
Stok_Adı = ITEMS.NAME,
MalzemeAciklamasi2 = ITEMS.NAME2,
MalzemeAciklamasi3 = ITEMS.NAME3,
MalzemeAciklamasi4 = ITEMS.NAME4,
[Bırım] = BIRIM.CODE,
Malzeme_Turu =
CASE ITEMS.CARDTYPE
WHEN 1 THEN 'TICARI MAL'
WHEN 2 THEN 'KARMA KOLI'
WHEN 3 THEN 'DEPOZITOLU MAL'
WHEN 4 THEN 'SABIT KIYMET'
WHEN 10 THEN 'HAMMADDE'
WHEN 11 THEN 'YARI MAMUL'
WHEN 12 THEN 'MAMUL'
WHEN 13 THEN 'TUKETIM MALI'
WHEN 20 THEN 'GENEL MALZEME SINIFI'
WHEN 21 THEN 'TABLOLU MALZEME SINIFI'
ELSE CONVERT(varchar(20), CARDTYPE)
END,
GrupKodu = ITEMS.STGRPCODE,
OzelKod = ITEMS.SPECODE,
OzelKod2 = ITEMS.SPECODE2,
OzelKod3 = ITEMS.SPECODE3,
OzelKod4 = ITEMS.SPECODE4,
OzelKod5 = ITEMS.SPECODE5,
[Miktar] = CAST((CASE
WHEN STLINE.IOCODE IN (1, 2) THEN 1
ELSE -1
END) * STLINE.AMOUNT * (CASE
WHEN ISNULL(UINFO2, 0) = 0 THEN 1
ELSE UINFO2
END) / (CASE
WHEN ISNULL(UINFO1, 0) = 0 THEN 1
ELSE UINFO1
END) AS float)
FROM LG_211_01_STFICHE STFICHE WITH (NOLOCK)
LEFT OUTER JOIN LG_211_01_STLINE STLINE WITH (NOLOCK)
ON STFICHE.LOGICALREF = STLINE.STFICHEREF
LEFT OUTER JOIN LG_211_ITEMS ITEMS WITH (NOLOCK)
ON STLINE.STOCKREF = ITEMS.LOGICALREF
LEFT OUTER JOIN LG_211_UNITSETL BIRIM WITH (NOLOCK)
ON BIRIM.UNITSETREF = ITEMS.UNITSETREF
AND BIRIM.MAINUNIT = 1
WHERE STFICHE.CANCELLED = 0
AND STLINE.CANCELLED = 0
AND STLINE.LINETYPE = 0
AND STLINE.IOCODE IN (1, 2, 3, 4)
AND STLINE.LPRODSTAT = 0
AND YEAR(STLINE.DATE_)>=2021 AND MONTH(STLINE.DATE_)=10 ) AS X
GROUP BY
X.Ay,
X.Gun,
X.Ambar,
x.Stok_AktifPasif,
x.SOURCEINDEX,
x.ITEMLOGICALREF,
x.Stok_Kodu,
x.Stok_Adı,
x.MalzemeAciklamasi2,
x.MalzemeAciklamasi3,
x.MalzemeAciklamasi4,
x.Bırım,
x.Malzeme_Turu,
x.GrupKodu,
x.OzelKod,
x.OzelKod2,
x.OzelKod3,
x.OzelKod4,
x.OzelKod5
ORDER BY X.Stok_Kodu , X.Ambar, X.Gun
LOGO SQL İŞYERİ AMBAR BAZLI ANLIK STOKLAR
SELECT
X.Isyeri,
X.Ambar,
x.Stok_Kodu,
x.Stok_Adı,
x.MalzemeAciklamasi2,
x.MalzemeAciklamasi3,
x.MalzemeAciklamasi4,
x.[Bırım],
x.Malzeme_Turu,
x.GrupKodu,
x.OzelKod,
x.OzelKod2,
x.OzelKod3,
x.OzelKod4,
x.OzelKod5,
Miktar = ROUND(SUM(x.[Miktar]), 4)
FROM (SELECT
Ambar = (SELECT
CAST(NR AS varchar) + '-' + NAME
FROM L_CAPIWHOUSE CAPIWHOUSE
WHERE CAPIWHOUSE.NR = STLINE.SOURCEINDEX
AND CAPIWHOUSE.FIRMNR = 211),
Isyeri = (SELECT
CAST(NR AS varchar) + '-' + NAME
FROM L_CAPIDIV CAPIDIV
WHERE CAPIDIV.NR = STFICHE.BRANCH
AND CAPIDIV.FIRMNR = 211),
STFICHE.LOGICALREF [STFICHE_REFERANS],
Stok_Kodu = ITEMS.CODE,
Stok_Adı = ITEMS.NAME,
MalzemeAciklamasi2 = ITEMS.NAME2,
MalzemeAciklamasi3 = ITEMS.NAME3,
MalzemeAciklamasi4 = ITEMS.NAME4,
[Bırım] = BIRIM.CODE,
Malzeme_Turu =
CASE ITEMS.CARDTYPE
WHEN 1 THEN 'TICARI MAL'
WHEN 2 THEN 'KARMA KOLI'
WHEN 3 THEN 'DEPOZITOLU MAL'
WHEN 4 THEN 'SABIT KIYMET'
WHEN 10 THEN 'HAMMADDE'
WHEN 11 THEN 'YARI MAMUL'
WHEN 12 THEN 'MAMUL'
WHEN 13 THEN 'TUKETIM MALI'
WHEN 20 THEN 'GENEL MALZEME SINIFI'
WHEN 21 THEN 'TABLOLU MALZEME SINIFI'
ELSE CONVERT(varchar(20), CARDTYPE)
END,
GrupKodu = ITEMS.STGRPCODE,
OzelKod = ITEMS.SPECODE,
OzelKod2 = ITEMS.SPECODE2,
OzelKod3 = ITEMS.SPECODE3,
OzelKod4 = ITEMS.SPECODE4,
OzelKod5 = ITEMS.SPECODE5,
[Miktar] = CAST((CASE
WHEN STLINE.IOCODE IN (1, 2) THEN 1
ELSE -1
END) * STLINE.AMOUNT * (CASE
WHEN ISNULL(UINFO2, 0) = 0 THEN 1
ELSE UINFO2
END) / (CASE
WHEN ISNULL(UINFO1, 0) = 0 THEN 1
ELSE UINFO1
END) AS float)
FROM LG_211_01_STFICHE STFICHE WITH (NOLOCK)
LEFT OUTER JOIN LG_211_01_STLINE STLINE WITH (NOLOCK)
ON STFICHE.LOGICALREF = STLINE.STFICHEREF
LEFT OUTER JOIN LG_211_ITEMS ITEMS WITH (NOLOCK)
ON STLINE.STOCKREF = ITEMS.LOGICALREF
LEFT OUTER JOIN LG_211_UNITSETL BIRIM WITH (NOLOCK)
ON BIRIM.UNITSETREF = ITEMS.UNITSETREF
AND BIRIM.MAINUNIT = 1
WHERE STFICHE.CANCELLED = 0
AND STLINE.CANCELLED = 0
AND STLINE.LINETYPE = 0
AND STLINE.IOCODE IN (1, 2, 3, 4)
AND STLINE.LPRODSTAT = 0
AND STLINE.DATE_ <= GETDATE()) AS X
GROUP BY X.Isyeri,
X.Ambar,
x.Stok_Kodu,
x.Stok_Adı,
x.MalzemeAciklamasi2,
x.MalzemeAciklamasi3,
x.MalzemeAciklamasi4,
x.Bırım,
x.Malzeme_Turu,
x.GrupKodu,
x.OzelKod,
x.OzelKod2,
x.OzelKod3,
x.OzelKod4,
x.OzelKod5
Not: İşyerinin olması akıl karıştırabilir(toplamda bu ambarda şu stok olmalı gibi carpraz kotnrol yapılırsa logodan stok bazen farklı işyerinden düşünce toplam diğer işyerinde olduğu için) dolayısıyla select ve group bydan kaldırılırsa daha sağlıklı kontrol sağlanabilir (ambar şu stok şu)
SQL SERVER TABLODAN MAX TARİHE GÖRE ALANLARI GETİRTME
select *
from (select *,
row_number() over(partition by URUN_NO order by BASLANGIC_TARIHI desc) as rn
from BM_T_V_FIYAT_TANIM_BILGI WITH(NOLOCK)) as T
where rn = 1
ÖRNEK:
LOGO Mobilsales tabloları sil
Collation sorunundan dolayı karşıma çıktı:
Ayrıca yine veritabanı altında Programmability->Store Procedures altından getParameters ve WOR_ ile başlayan prosedürleri, Functions->Scalar-Valued Functions altından GetWebOrderDateInt, Views altındadan VGetdate viewini siliniz. Sildikten sonra yeniden tabloların oluşturulmasını sağlayabilirsiniz?(Mobile Sales Genel Ayarlar açılarak veritabanı tablolarını ekleyebilirsiniz.)
bunlarıda sil:
spWebOrderParseBarkod
splitString
SQL SERVER SONUCU HTML OLARAK KAYDET
--CREATE PROCEDURE [dbo].[P_HTML]
--as
declare @bodyOzet varchar(max)
declare @bodyDetay varchar(max)
/*
CREATE TABLE [dbo].[HTML](
[HTMLCODE] [nvarchar](max) NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
--DOSYAYA KAYDETMESI ICIN AYAR ACILMALI
sp_configure 'show advanced options', '1'
RECONFIGURE
GO
-- this enables xp_cmdshell
sp_configure 'xp_cmdshell', '1'
RECONFIGURE
GO
--DOSYAYA KAYDETME KOMUTU
declare @sql varchar(8000)
select @sql = 'bcp "select * from [DATABASE].[dbo].HTML" queryout E:\SONUC.html -c -t, -T -S' + @@servername
exec master..xp_cmdshell @sql
--ISLEM SORGUSU
exec dbo.P_HTML
*/
set @bodyDetay = cast( (
select td = cast(day(Tarih) as varchar)+'/'+cast(month(Tarih) as varchar)+'/'+cast(Year(Tarih) as varchar)+'</td><td>' + UrunKodu + '</td><td>' + UrunAdi + '</td><td>' + Barkod + '</td><td>' + cast( Miktar as varchar(30) ) + '</td><td>' + cast( SatirNetTutar as varchar(30) )
from (
select top 100 percent
Tarih= (select SF.DATE FROM StockFiches SF WHERE SF.Id=SL.StockFicheId),
UrunKodu= (select P.Code FROM Products P WHERE P.Id=SL.StockId),
UrunAdi= (select P.ProductName FROM Products P WHERE P.Id=SL.StockId),
Barkod= (select P.Barcode FROM Barcodes P WHERE P.ProductId=SL.StockId),
Miktar= SL.Amount,
SatirNetTutar=SL.LineNet
from StockLines SL
) as d order by Tarih desc
for xml path( 'tr' ), type ) as varchar(max) )
set @bodyDetay = N'Malzeme Bazlı Satışlar ('+cast(GETDATE() as varchar)+') <table cellpadding="2" cellspacing="0" border="1">'
+ '<tr><th>Tarih</th><th>Ürün Kodu</th><th>Ürün Adı</th><th>Barkod</th><th>Miktar</th><th>Tutar</th></tr>'
+ replace( replace( @bodyDetay, '<', '<' ), '>', '>' )
+ '</table>'
/*aşağısı özet*/
set @bodyOzet = cast( (
select td = cast(day(Tarih) as varchar)+'/'+cast(month(Tarih) as varchar)+'/'+cast(Year(Tarih) as varchar)+'</td><td>' + OdemeTuru + '</td><td>' + Cast(Toplam as Varchar) + '</td><td>' + Cast(IndirimToplam as Varchar) + '</td><td>' + cast( NetToplam as varchar(30) )
from (
select Tarih,OdemeTuru,Sum(Toplam) Toplam, Sum(IndirimToplam) IndirimToplam,Sum(NetToplam) NetToplam from (
SELECT
CONVERT(DATE, F.CreatedDate) AS Tarih
, CASE F.IO WHEN 0 THEN L.Total ELSE 0 - L.Total END AS Toplam
, CASE F.IO WHEN 0 THEN L.TotalDiscount ELSE 0 - L.TotalDiscount END AS IndirimToplam
, CASE F.IO WHEN 0 THEN L.LineNet ELSE 0 - L.LineNet END AS NetToplam
, L.VatInc KDVDH
, L.Vat KDV
, CASE F.PayType
WHEN 0 THEN 'Nakit Kasa'
WHEN 1 THEN 'Banka Pos'
WHEN 2 THEN 'Kredi Kartı'
WHEN 3 THEN 'İkram'
END OdemeTuru
FROM StockLines L
Left Join StockFiches F ON F.Id = L.StockFicheId
WHERE
F.FicheType in (0,1)
) as t group by Tarih,OdemeTuru
) as d order by Tarih desc
for xml path( 'tr' ), type ) as varchar(max) )
set @bodyOzet = N'Özet Bazlı Satışlar ('+cast(GETDATE() as varchar)+') <table cellpadding="2" cellspacing="0" border="1">'
+ '<tr><th>Tarih</th><th>Odeme Turu</th><th>Toplam</th><th>Indirim Toplam</th><th>NetToplam</th></tr>'
+ replace( replace( @bodyOzet, '<', '<' ), '>', '>' )
+ '</table><br>' + @bodyDetay
truncate table HTML
insert into HTML(HTMLCODE) VALUES(@bodyOzet)
GO
LOGO FİRMA VE BAĞLANTILARINI SİLMEK(SQL)
/*birinci yöntem logo ve sql server üzerinden:
hangi firma silinecekse dönemi silinir sonra firması silinir
örnek ilk önce 531li viewleri sil
sonrada tablolardan 531 sil
*/
/*İŞLEM ÖNCESİ VERİTABANININ YEDEK ALINMASI TAVSİYE EDİLİR*/
/*ÖNCE SYSDEN İLGİLİ FİRMANIN DÖNEMİ ÇIKARILIR VE SONRASINDA FİRMA ÇIKAR YAPILIR*/
/*AŞAĞIDAKİ İŞLEMLERE FİRMAYA BAĞLI VİEW,TABLO,PROSEDUR,FONKSIYONLARI SİLMEKTEDİR*/
DECLARE @firmno varchar(3)='999' /*İŞLEM YAPILMASI İSTENİLEN FİRMA NOSU YAZ*/
DECLARE @view_name SYSNAME
DECLARE @table_name SYSNAME
DECLARE @procedure_name SYSNAME
DECLARE @function_name SYSNAME
/*VIEWLERI SIL BAŞLANGIÇ*/
DECLARE Logo_objs CURSOR FOR
SELECT name
FROM sysobjects
WHERE (name LIKE 'LV_' + @firmno + '%' AND xtype='V' )
ORDER BY NAME
OPEN Logo_objs
FETCH NEXT FROM Logo_objs INTO @view_name
WHILE @@FETCH_STATUS = 0
BEGIN
EXECUTE('DROP VIEW '+ @view_name)
FETCH NEXT FROM Logo_objs into @view_name
END
CLOSE Logo_objs
DEALLOCATE Logo_objs
/*VIEWLERI SIL BİTİŞ*/
/*TABLOLARI SIL BAŞLANGIÇ*/
DECLARE Logo_objs CURSOR FOR
SELECT name
FROM sysobjects
WHERE (name LIKE 'LG_' + @firmno + '%' AND XTYPE = 'U')
ORDER BY NAME
OPEN Logo_objs
FETCH NEXT FROM Logo_objs INTO @table_name
WHILE @@FETCH_STATUS = 0
BEGIN
EXECUTE('DROP TABLE '+ @table_name)
FETCH NEXT FROM Logo_objs into @table_name
END
CLOSE Logo_objs
DEALLOCATE Logo_objs
/*TABLOLARI SIL BİTİŞ*/
/*PROSEDURLERİ SIL BAŞLANGIÇ*/
DECLARE Logo_objs CURSOR FOR
SELECT name
FROM sysobjects
WHERE (name LIKE '%' + @firmno + '%' AND XTYPE = 'P')
ORDER BY NAME
OPEN Logo_objs
FETCH NEXT FROM Logo_objs INTO @procedure_name
WHILE @@FETCH_STATUS = 0
BEGIN
EXECUTE('DROP PROCEDURE '+ @procedure_name)
FETCH NEXT FROM Logo_objs into @procedure_name
END
CLOSE Logo_objs
DEALLOCATE Logo_objs
/*PROSEDURLERİ SIL BİTİŞ*/
/*FONKSİYONLARI SIL BAŞLANGIÇ*/
DECLARE Logo_objs CURSOR FOR
SELECT name
FROM sys.sql_modules m
INNER JOIN sys.objects o ON m.object_id=o.object_id
WHERE (name like 'LG_' + @firmno + '%' AND type ='FN')
ORDER BY NAME
OPEN Logo_objs
FETCH NEXT FROM Logo_objs INTO @function_name
WHILE @@FETCH_STATUS = 0
BEGIN
EXECUTE('DROP FUNCTION '+ @function_name)
FETCH NEXT FROM Logo_objs into @function_name
END
CLOSE Logo_objs
DEALLOCATE Logo_objs
/*FONKSİYONLARI SIL BİTİŞ*/
logo Cari Bakiyeler (RD Kontrollü)
SELECT
OGRENCI_NO= CLCARD.CODE ,
OGRENCI_ADI= CLCARD.DEFINITION_,
DOVIZ_TURU= CASE CCURRENCY WHEN 0 THEN 'TL' WHEN 160 THEN 'TL' WHEN 1 THEN 'USD' WHEN 17 THEN 'GBP' WHEN 20 THEN 'EUR' ELSE '' END,
/*TL_BAKIYE= ROUND((SUM((1 - CLFLINE.SIGN) * CLFLINE.AMOUNT) - SUM(CLFLINE.SIGN * CLFLINE.AMOUNT)),2),
RD_BAKIYE= ROUND((SUM((1 - CLFLINE.SIGN) * (CLFLINE.AMOUNT/NULLIF(CLFLINE.REPORTRATE,0)))
-
SUM(CLFLINE.SIGN * (CLFLINE.AMOUNT/NULLIF(CLFLINE.REPORTRATE,0)))),2),*/
/*BAKIYE= CASE WHEN CCURRENCY IN (0,160) THEN
ROUND((SUM((1 - CLFLINE.SIGN) * CLFLINE.AMOUNT) - SUM(CLFLINE.SIGN * CLFLINE.AMOUNT)),2)
ELSE
ROUND((SUM((1 - CLFLINE.SIGN) * (CLFLINE.AMOUNT/NULLIF(CLFLINE.REPORTRATE,0)))
-
SUM(CLFLINE.SIGN * (CLFLINE.AMOUNT/NULLIF(CLFLINE.REPORTRATE,0)))),2)
END ,*/
BAKIYE= CASE WHEN CCURRENCY IN (0,160) THEN
ROUND((SUM((1 - CLFLINE.SIGN) * CLFLINE.AMOUNT) - SUM(CLFLINE.SIGN * CLFLINE.AMOUNT)),2)
ELSE
ROUND((SUM((1 - CLFLINE.SIGN) * (CLFLINE.REPORTNET))
-
SUM(CLFLINE.SIGN * (CLFLINE.REPORTNET))),2)
END
FROM
LG_118_01_CLFLINE CLFLINE INNER JOIN
LG_118_CLCARD CLCARD ON CLFLINE.CLIENTREF = CLCARD.LOGICALREF AND CLFLINE.CANCELLED = 0
WHERE CLCARD.CYPHCODE='UNI' AND CLFLINE.CANCELLED=0
and CLFLINE.DATE_<='09/01/2020' /*ay/gun/yıl*/
GROUP BY CLCARD.CCURRENCY, CLCARD.CODE, CLCARD.DEFINITION_, CLCARD.ACTIVE
HAVING (CLCARD.CODE LIKE '%') and (CLCARD.DEFINITION_ LIKE '%')
AND (CLCARD.ACTIVE = 0) ORDER BY CLCARD.CODE DESC
LOGO MOBILESALES CARI ZIYARET RAPORU
SELECT DISTINCT M.P_KODU,
M.CARI_KODU,M.CARI_ADI,M.GUN,
(
SELECT TOP 1 WP.DATE_ FROM WOR_PROCESS WP ,WOR_USERS WU WHERE WP.FIRM = '121'
AND WU.APPTYPE=1 AND WP.USERID=WU.USERID
AND ( WP.DATE_ BETWEEN ? AND ? )
AND WU.USERNAME=M.P_KODU AND WP.CLCODE=M.CARI_KODU
ORDER BY WP.DATE_ DESC
) SON_GIDILEN_TARIH,
(SELECT W2.FNAME FROM WOR_USERS W2 WHERE W2.CODE=M.P_KODU) AS P_ADI
FROM E_WINGS_121_SCS3 M WHERE 1=1
LOGO CARI RISK SIPARIS KAPAT SQL
SENARYO
- Bir Carinin borcu 50 gün ve daha fazlaysa job ile risk bilgilerinde siparişe kapatılsın güncellenmesi yapılacak (LOGO FIRMA ADI 210).
- Job çalışma süresi her gün sabah saat 7
İŞLEYİŞ
- Önce bu kritere uyan cariler view ile belirlenecek (VIEW ADI: CARIRISKSIPARISKAPAT_210)
- Job ile bu view çağrılıp gerekli güncelleme işlemi yapılacak
CREATE VIEW CARIRISKSIPARISKAPAT_210 AS
SELECT * FROM (
SELECT
CLLOGICALREF= C.LOGICALREF,
[Cari Kodu]= C.CODE,
[Cari Ünvanı]= C.DEFINITION_,
[İşlem]= ISNULL((
SELECT TOP 1
CASE WHEN DATEDIFF(DAY,P.DATE_,GETDATE())>=50 THEN 'Siparişe Kapat' END ISLEM
FROM LG_210_01_PAYTRANS P WITH(NOLOCK)
WHERE
P.MODULENR=4 AND P.TRCODE IN (7,8) AND P.PAID=0 AND P.CARDREF=C.LOGICALREF AND P.CANCELLED=0 ORDER BY P.DATE_ ASC
),''),
[Siparişte]= ISNULL((SELECT
CASE CLR.ORDRISKOVER
WHEN 0 THEN 'İşleme Devam Edilecek'
WHEN 1 THEN 'Kullanıcı Uyarılacak'
WHEN 2 THEN 'İşlem Durdurulacak'
END FROM LG_210_01_CLRNUMS CLR WITH(NOLOCK) WHERE CLR.CLCARDREF=C.LOGICALREF),'')
FROM LG_210_CLCARD C WITH(NOLOCK) WHERE ACTIVE=0
) AS T WHERE T.İşlem<>'' and Siparişte not like '%Durdurulacak'
/*JOB SORGUSU UPDATE İŞLEMİ*/
SET QUOTED_IDENTIFIER ON
UPDATE LG_210_01_CLRNUMS SET ORDRISKOVER = 2
WHERE CLCARDREF IN (SELECT B.CLLOGICALREF FROM CARIRISKSIPARISKAPAT_210 B )
Blog of Sem 