Abap Native SQL de DateTime ile sorgu

DATA:
LRC_ROOT  TYPE REF TO CX_ROOT,
LFD_TEXT  TYPE STRING,
l_time TYPE char8,
tp_time1(19TYPE c,
tp_time2(19TYPE c.

CONCATENATE s_uzeit+0(2s_uzeit+2(2s_uzeit+4(2)
INTO l_time SEPARATED BY ‘:’.

CONCATENATE s_crdatlow+0(4s_crdatlow+4(2s_crdatlow+6(2)
INTO   tp_time1 SEPARATED BY ‘-‘.

CONCATENATE tp_time1 l_time INTO tp_time1 SEPARATED BY space.

CONCATENATE s_crdathigh+0(4s_crdathigh+4(2s_crdathigh+6(2)
INTO   tp_time2 SEPARATED BY ‘-‘.

CONCATENATE tp_time2 l_time INTO tp_time2 SEPARATED BY space.
TRY.

EXEC SQL.
OPEN FOR
SELECT Sicil,Giris,Cikis
FROM VwPdks
WHERE Giris >= :tp_time1
AND Cikis <= :tp_time2
ENDEXEC.

DO.

EXEC SQL.
FETCH NEXT into :ls_pdksSicil,
:ls_pdksGiris,
:ls_pdksCikis
ENDEXEC.

IF SYSUBRC 0.
APPEND ls_pdks TO lt_pdks.
CLEAR ls_pdks.
ELSE.
EXIT.
ENDIF.
ENDDO.

CATCH CX_ROOT INTO LRC_ROOT.
LFD_TEXT LRC_ROOT->GET_TEXT).
MESSAGE ID ‘ZPP’ TYPE ‘E’ NUMBER ‘000’
WITH LFD_TEXT SYMSGV2 SYMSGV3 SYMSGV4.

ENDTRY .

SQL WHERE CASE kullanımı

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

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)+’%’
ELSEEND

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