Kampanya Kartlarında SQLINFO Kullanımı

PG.001                            A

PG.002                            B

PG.003                            C

PG.004                            D

PG.005                            E

MS Kodu                      MS Açıklaması

———————-       ———————-

PG                                     PG

FROM

LG_010_ITEMS I (NOLOCK)

LEFT OUTER JOIN

LG_010_ITMCLSAS C (NOLOCK) ON I.LOGICALREF=C.CHILDREF AND C.UPLEVEL=0

LEFT OUTER JOIN

LG_010_ITEMS S (NOLOCK) ON C.PARENTREF=S.LOGICALREF

WHERE

I.CODE LIKE ‘PG.001’

AND S.CODE LIKE ‘PG’

 QUERY’ NIN FONKSİYON HALİNE GETİRİLMESİ

CREATE FUNCTION [dbo].[FN_MS_KAMPANYA_010] (@MALZ VARCHAR(21), @SINIF VARCHAR(21))

RETURNS FLOAT

AS

BEGIN

DECLARE @LG_FN_LINETOPLAM FLOAT

SET @LG_FN_LINETOPLAM=(

SELECT

COUNT(*)

FROM

LG_010_ITEMS I (NOLOCK)

LEFT OUTER JOIN LG_010_ITMCLSAS C (NOLOCK) ON I.LOGICALREF=C.CHILDREF AND C.UPLEVEL=0

LEFT OUTER JOIN

LG_010_ITEMS S (NOLOCK) ON C.PARENTREF=S.LOGICALREF

WHERE

I.CODE LIKE @MALZ+’%’

AND S.CODE LIKE @SINIF+’%’                    

)

;

RETURN(@LG_FN_LINETOPLAM)

END

Not : Fonksiyon SQL’ de bir kez çalıştırılarak sql tarafına fonksiyon olarak eklenmesi sağlanmalıdır.

FONKSİYON’ UN QUERY İLE ÇALIŞTIRILMASI

SELECT DBO.FN_MS_KAMPANYA_010(‘PG.004′,’PG’) FROM L_CAPIFIRM WHERE NR=10

FONKSİYON’ UN SQLINFO HALİNE GETİRİLMESİ

VAL(_SQLINFO(“DBO.FN_MS_KAMPANYA_010(‘”+P101+”‘,’PG’)”,”L_CAPIFIRM”,”NR=10″))