...
- ----------------------*
PG.001 A
PG.002 B
PG.003 C
PG.004 D
PG.005 E
MC Code MC Description Wiki Markup ----------------------* PG PG When a common campaign will be applied to material cards, they must be connected to material class card. *+THE QUERY THAT LISTS MATERIAL – MATERIAL CLASS CONNECTION+* 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 'PG.001' AND S.CODE LIKE 'PG' \\ *+TRANSFORM QUERY TO FUNCTION+* 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 *Note:* .The function should be added to sql part as function by running it in SQL once. \\ *+TO RUN FUNCTION WITH QUERY+* SELECT DBO.FN_MS_KAMPANYA_010('PG.004','PG') FROM L_CAPIFIRM WHERE NR=10 *+TRANSFORMING FUNCTION TO SQLINFO+* VAL(_SQLINFO("DBO.FN_MS_KAMPANYA_010('"+P101+"','PG')","L_CAPIFIRM","NR=10")) *+USING SQLINFO FUNCTION IN CAMPAIGN CARD+* *P101:* Material Code info Campaign application is provided by using IF(V5>0,1,0) function in condition field, 1 value is turned from *V5* variable. \\ Parent material class code that the campaign will be applied to, has to be indicated in *PG{*} field located in V5 variable. \\
- ----------------------*