Etiket arşivi: case

SQL WHERE CASE kullanımı

Yayın tarihi
Cevapla

WHERE koşulu içinde CASE WHEN kullanımı:

SELECT     TOP (100) PERCENT A.INVDOCTYPE, A.INVDOCNUM, A.DOCDATE
, A.MATERIAL, A.PROJECT, A.QUANTITY, A.QUNIT, A.WAREHOUSE
, A.STOCKPLACE,A.CONFIRMATION, A.CONFIRMPOS
, B.WAREHOUSE AS WAREHOUSE2, B.STOCKPLACE AS STOCKPLACE2, C.CUSTOMER
, A.BATCHNUM, A.QPOSTWAY
FROM dbo.IASINVITEM AS A WITH (NOLOCK) LEFT OUTER JOIN
     dbo.IASINVITEM AS B WITH (NOLOCK) ON A.CLIENT = B.CLIENT 
     AND A.COMPANY = B.COMPANY AND A.INVDOCTYPE = B.INVDOCTYPE AND 
     A.INVDOCNUM = B.INVDOCNUM AND 
     B.INVDOCITEM = 
(CASE WHEN A.QPOSTWAY = 1 THEN A.INVDOCITEM + 1 ELSE A.INVDOCITEM - 1 END) 
AND B.ISCANCELED = 0 AND B.QPOSTWAY <> A.QPOSTWAY LEFT OUTER JOIN
dbo.PNTFBATCHNUM AS C WITH (NOLOCK) 
ON CONVERT(VARCHAR, A.CONFIRMATION) + CONVERT(VARCHAR, A.CONFIRMPOS) = C.BATCHNUM
WHERE (A.CLIENT = '00') AND (A.COMPANY = '01') AND (A.WAREHOUSE LIKE 'IP_') 
AND (A.STOCKPLACE LIKE '___%') AND (A.ISCANCELED = 0)
ORDER BY A.DOCDATE, A.QPOSTWAY, A.WAREHOUSE, A.STOCKPLACE, A.MATERIAL

SQL de CASE — WHEN kullanımı

Yayın tarihi
Cevapla

Belirli koşullara göre bir sütunda yer almasını istediğimiz değeri getirmek.

SELECT IASPURINF.[DOCUMENT], IASPURINF.COMPANY, IASPURINF.PLANT, IASPURINF.MATERIAL, IASPURINF.VENDOR, IASPURINF.BUSAREA, IASPURINF.VALIDFROM,
IASPURINF.VALIDUNTIL, IASPURINF.PURDEPT, IASPURINF.NET, IASPURINF.PUNIT, IASPURINF.CURRENCY, IASPURINF.PURUNIT, IASPURINF.STEXT,
IASPURINF.DELIVERYDAY, IASPURINF.MINORDER, IASPURINF.VMATERIAL, IASCUSTOMER.NAME1
,V.EXCHRATESAL
,CASE WHEN IASPURINF.CURRENCY =’TL’ THEN IASPURINF.NET ELSE IASPURINF.NET * V.EXCHRATESAL END AS FTL
FROM IASCUSTOMER INNER JOIN
IASPURINF ON IASCUSTOMER.CLIENT = IASPURINF.CLIENT AND IASCUSTOMER.COMPANY = IASPURINF.COMPANY AND
IASCUSTOMER.CUSTOMER = IASPURINF.VENDOR AND IASCUSTOMER.BUSAREA = IASPURINF.BUSAREA LEFT OUTER JOIN
VW_IAS_KUR AS V ON IASPURINF.CURRENCY = V.CURRENCY
WHERE (IASPURINF.CLIENT = ’00′) AND (IASPURINF.COMPANY LIKE ’01′) AND (IASPURINF.PLANT LIKE ‘%’) AND (IASPURINF.VENDOR LIKE ‘%’) AND
(IASPURINF.MATERIAL LIKE ‘WU17X13′) AND (IASPURINF.BUSAREA LIKE ‘%’) AND (IASPURINF.[DOCUMENT] = 4) AND (IASPURINF.ITEMNUM = 1) AND
(IASCUSTOMER.ISCUSTORVEND >= 2) AND (IASPURINF.STEXT LIKE ‘%’) AND (IASCUSTOMER.NAME1 LIKE ‘%’) AND (IASPURINF.PURDEPT LIKE ‘%’) AND
(IASPURINF.VALIDFROM <= GETDATE()) AND (IASPURINF.VALIDUNTIL >= GETDATE()) AND (IASPURINF.ISDELETE = 0)
ORDER BY IASPURINF.COMPANY, IASPURINF.PLANT, IASPURINF.MATERIAL, IASPURINF.VENDOR, IASPURINF.BUSAREA

MS SQL WHERE içinde CASE WHEN kullanımı ve CAHRINDEX

Yayın tarihi
Cevapla

CHARINDEX : Bir alan içindeki kaydın içinde karakerin kaçıncı sırada olduğunu bulmak.

 

SELECT     F.CUSTOMER,F.NAME1,F.TAXDEPT,F.TAXNUM

FROM         IASVERHEAD AS A WITH (NOLOCK) LEFT OUTER JOIN
IASCUSTOMER AS F WITH (NOLOCK) ON
F.NAME1 LIKE CASE WHEN CHARINDEX(‘//’,A.STEXT)>1 THEN SUBSTRING(A.STEXT,1,CHARINDEX(‘//’,A.STEXT)-1)+’%’
ELSE ” END

WHERE     (A.DOCDATE > GETDATE() – 10);