Abap Native SQL de DateTime ile sorgu
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
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);
Koşul cümlesinde OR veya LIKE yerine IN kullanmak
UPDATE IASINVITEM SET STOCKPLACE='05' WHERE CLIENT='00' AND COMPANY='01' AND INVDOCTYPE='SG' AND INVDOCNUM IN ('00042323','00042324','00042325')