on 01-05-2015 8:30 AM
Dear All,
I create a UDF on Inventory Transfer screen and make a FMS Query but a error occurred. please see below mention query and advice.
IF $[OWTR."U_grn"] > 0
SELECT T1.["Quantity"] FROM OPDN T0 INNER JOIN PDN1 T1 ON T0.["DocEntry"] = T1.["DocEntry"]
WHERE T1.["ItemCode"] = $[WTR1."ItemCode"] and T0.["DocNum"] = $[OWTR."U_grn"]
else if
$[OWTR."U_RFPN"] > 0
SELECT T1.["Quantity"] FROM OIGN T0 INNER JOIN "VISIONTEST"."IGN1" T1 ON T0.["DocEntry"] = T1.["DocEntry"]
WHERE T0.["DocNum"] = $[OWTR."U_RFPN"] and T1.["ItemCode"] = $[WTR1."ItemCode"]
Hi Asad,
Try this FMS
IF ($[OWTR."U_grn"] > 0) then
SELECT T1."Quantity" FROM OPDN T0 INNER JOIN PDN1 T1 ON T0."DocEntry" = T1."DocEntry"
WHERE T1.["ItemCode"] = $[WTR1."ItemCode"] and T0.["DocNum"] = $[OWTR."U_grn"];
elseif
($[OWTR."U_RFPN"] > 0) then
SELECT T1."Quantity" FROM OIGN T0 INNER JOIN "VISIONTEST"."IGN1" T1 ON T0."DocEntry" = T1."DocEntry"
WHERE T0.["DocNum"] = $[OWTR."U_RFPN"] and T1."ItemCode" = $[WTR1."ItemCode"];
end if;
--Manish
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
Sorry i forget to remove the [] from T0.["DocNum"]
IF ($[OWTR."U_grn"] > 0) then
SELECT T1."Quantity" FROM OPDN T0 INNER JOIN PDN1 T1 ON T0."DocEntry" = T1."DocEntry"
WHERE T1."ItemCode" = $[WTR1."ItemCode"] and T0."DocNum" = $[OWTR."U_grn"];
elseif
($[OWTR."U_RFPN"] > 0) then
SELECT T1."Quantity" FROM OIGN T0 INNER JOIN "VISIONTEST"."IGN1" T1 ON T0."DocEntry" = T1."DocEntry"
WHERE T0."DocNum"= $[OWTR."U_RFPN"] and T1."ItemCode" = $[WTR1."ItemCode"];
end if;
now try
--Manish
Did you check my last reply?
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
Did you check my reply?
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
Hi,
Try this:
SELECT CASE WHEN $[OWTR."U_grn"] > 0 THEN T1.["Quantity"] ELSE WHEN $[OWTR."U_RFPN"] > 0 THEN T2.["Quantity"] END
FROM "OPDN" T0 INNER JOIN "PDN1" T1 ON T0.["DocEntry"] = T1.["DocEntry"] and T0.["DocNum"] = (SELECT $[OWTR."U_grn"] FROM DUMMY), "OIGN" T2 INNER JOIN "IGN1" T3 ON T2.["DocEntry"] = T3.["DocEntry"]
WHERE T2.["DocNum"] = $[OWTR."U_RFPN"] and T3.["ItemCode"] = (SELECT $[WTR1."ItemCode"] FROM DUMMY)
Thanks,
Gordon
this error occurred show when remove all brackets
incorrect syntax near "WHEN": lin "(SWEI)
SELECT CASE WHEN $[OWTR."U_grn"] > 0 THEN T1."Quantity" ELSE WHEN $[OWTR."U_RFPN"] > 0 THEN T2."Quantity" END
FROM "OPDN" T0 INNER JOIN "PDN1" T1 ON T0."DocEntry" = T1."DocEntry" and T0."DocNum" = (SELECT $[OWTR."U_grn"] FROM DUMMY), "OIGN" T2 INNER JOIN "IGN1" T3 ON T2."DocEntry" = T3."DocEntry"
WHERE T2."DocNum" = $[OWTR."U_RFPN"] and T3."ItemCode" = (SELECT $[WTR1."ItemCode"] FROM DUMMY)
Hi,
Try this
IF $[OWTR."U_grn"] > 0
SELECT T1.["Quantity"] FROM OPDN T0 INNER JOIN PDN1 T1 ON T0.["DocEntry"] = T1.["DocEntry"]
WHERE T1.["ItemCode"] = $[WTR1."ItemCode"] and T0.["DocNum"] = $[OWTR."U_grn"]
else if
$[OWTR."U_RFPN"] > 0
SELECT T1.["Quantity"] FROM OIGN T0 INNER JOIN IGN1 T1 ON T0.["DocEntry"] = T1.["DocEntry"]
WHERE T0.["DocNum"] = $[OWTR."U_RFPN"] and T1.["ItemCode"] = $[WTR1."ItemCode"]
from dummy
Regards,
Manish
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
Hi,
Try this
IF $[OWTR."U_grn"] > 0
SELECT T1.["Quantity"] FROM OPDN T0 INNER JOIN PDN1 T1 ON T0.["DocEntry"] = T1.["DocEntry"]
WHERE T1.["ItemCode"] = (SELECT $[WTR1."ItemCode"] FROM DUMMY ) and T0.["DocNum"] = (SELECT $[OWTR."U_grn"] FROM DUMMY)
else if
$[OWTR."U_RFPN"] > 0
SELECT T1.["Quantity"] FROM OIGN T0 INNER JOIN IGN1 T1 ON T0.["DocEntry"] = T1.["DocEntry"]
WHERE T0.["DocNum"] = (SELECT $[OWTR."U_RFPN"] FROM DUMMY) and T1.["ItemCode"] = (SELECT $[WTR1."ItemCode"] FROM DUMMY)
Regards,
Manish
Hi,
Why do you have "VISIONTEST" in the query?
Thanks,
Gordon
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
Hi,
Try:
IF $[OWTR."U_grn"] > 0
SELECT T1.["Quantity"] FROM OPDN T0 INNER JOIN PDN1 T1 ON T0.["DocEntry"] = T1.["DocEntry"]
WHERE T1.["ItemCode"] = $[WTR1."ItemCode"] and T0.["DocNum"] = $[OWTR."U_grn"]
else if
$[OWTR."U_RFPN"] > 0
SELECT T1.["Quantity"] FROM OIGN T0 INNER JOIN IGN1 T1 ON T0.["DocEntry"] = T1.["DocEntry"]
WHERE T0.["DocNum"] = $[OWTR."U_RFPN"] and T1.["ItemCode"] = $[WTR1."ItemCode"]
Thanks & Regards,
Nagarajan
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
User | Count |
---|---|
100 | |
12 | |
11 | |
6 | |
6 | |
4 | |
3 | |
3 | |
3 | |
3 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.