Etiket arşivi: WHERE

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

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);