Hello Experts,
If
PRM_QTY
value is null then it should execure certain part of query other if should check for this condition in sotred procedure
Seems pretty simple but I don't know why this is not working.
ALTER SPECIFIC PROCEDURE R34FILES.HBINCONRAC REPLACE (IN PRMLOC CHARACTER (2) , IN PRM_ITEM CHARACTER (20) , IN PRM_TRLN CHARACTER (50) , IN PRM_CC CHARACTER (50) , IN PRM_QTY CHARACTER (5) ) MODIFIES SQL DATA CONCURRENT ACCESS RESOLUTION DEFAULT DYNAMIC RESULT SETS 2 OLD SAVEPOINT LEVEL COMMIT ON RETURN NO DISALLOW DEBUG MODE BEGIN
DECLARE CR1 CURSOR WITH RETURN FOR
IF PRM_QTY IS NULL
BEGIN
SELECT
VINBINI . BALOC AS BABLOC , VINBINI . BAITEM AS BAITEM , VINITEM . ICDSC1 AS ICDSC1 ,
VINITEM . ICDSC2 AS ICDSC2 , VINBINI . BABLOC AS BABLOC ,
INTEGER ( VINBINI . BAQOH ) AS QOH , INTEGER ( VINBINI . BAQCM ) AS QCM ,
VINBINI . BACCTU , PALBINPF . PALLETID , PALBINPF . NBRRCKBINB ,
PALLITEMPF . UNITSPER , VININDEX . I001004
FROM PRODASP . R34FILES . PALBINPF PALBINPF , PRODASP . R34FILES . PALLITEMPF PALLITEMPF , PRODASP . R34FILES . VINBINI VINBINI ,PRODASP . R34FILES . VININDEX VININDEX , PRODASP . R34FILES . VINITEM VINITEM WHERE VINBINI . BABLOC = PALBINPF . IMBLOC AND VINBINI . BACMP = PALBINPF . TACOMP AND VINBINI . BAITEM = VINITEM . ICITEM AND VINITEM . ICTRLN = VININDEX . I001TRLN AND PALLITEMPF . ICITEM = VINITEM . ICITEM AND PALLITEMPF . PALLETID = PALBINPF . PALLETID AND VINBINI . BAITEM =PALLITEMPF . ICITEM AND
( VINBINI . BAITEM LIKE UPPER ( CONCAT ( TRIM ( PRM_ITEM ) , '%' ) ) OR PRM_ITEM IS NULL ) AND
( UPPER ( VINITEM . ICTRLN ) LIKE UPPER ( CONCAT ( TRIM ( PRM_TRLN ) , '%' ) ) OR PRM_TRLN IS NULL ) AND
( VINBINI . BALOC = PRMLOC ) AND ( VINBINI . BAQOH > 0 ) AND ( VININDEX . I001004 LIKE ( CONCAT ( TRIM ( PRM_CC ) , '%' ) ) ORPRM_CC IS NULL )
ORDER BY BAITEM ASC ;
END;
ELSE
BEGIN
SELECT
VINBINI . BALOC AS BABLOC , VINBINI . BAITEM AS BAITEM , VINITEM . ICDSC1 AS ICDSC1 ,
VINITEM . ICDSC2 AS ICDSC2 , VINBINI . BABLOC AS BABLOC ,
INTEGER ( VINBINI . BAQOH ) AS QOH , INTEGER ( VINBINI . BAQCM ) AS QCM ,
VINBINI . BACCTU , PALBINPF . PALLETID , PALBINPF . NBRRCKBINB ,
PALLITEMPF . UNITSPER , VININDEX . I001004
FROM PRODASP . R34FILES . PALBINPF PALBINPF , PRODASP . R34FILES . PALLITEMPF PALLITEMPF , PRODASP . R34FILES . VINBINI VINBINI ,PRODASP . R34FILES . VININDEX VININDEX , PRODASP . R34FILES . VINITEM VINITEM WHERE VINBINI . BABLOC = PALBINPF . IMBLOC AND VINBINI . BACMP = PALBINPF . TACOMP AND VINBINI . BAITEM = VINITEM . ICITEM AND VINITEM . ICTRLN = VININDEX . I001TRLN AND PALLITEMPF . ICITEM = VINITEM . ICITEM AND PALLITEMPF . PALLETID = PALBINPF . PALLETID AND VINBINI . BAITEM =PALLITEMPF . ICITEM AND
( VINBINI . BAITEM LIKE UPPER ( CONCAT ( TRIM ( PRM_ITEM ) , '%' ) ) OR PRM_ITEM IS NULL ) AND
( UPPER ( VINITEM . ICTRLN ) LIKE UPPER ( CONCAT ( TRIM ( PRM_TRLN ) , '%' ) ) OR PRM_TRLN IS NULL ) AND
( VINBINI . BALOC = PRMLOC ) AND ( VINBINI . BAQOH > 0 ) AND (VINBINI.BAQOH < (INTEGER(PRM_QTY))) AND ( VININDEX . I001004 LIKE ( CONCAT ( TRIM ( PRM_CC ) , '%' ) ) ORPRM_CC IS NULL )
ORDER BY BAITEM ASC ;
END;
OPEN CR1 ;
END ;
Or can I completely ignore If else and do something like below
SELECT
VINBINI . BALOC AS BABLOC , VINBINI . BAITEM AS BAITEM , VINITEM . ICDSC1 AS ICDSC1 ,
VINITEM . ICDSC2 AS ICDSC2 , VINBINI . BABLOC AS BABLOC ,
INTEGER ( VINBINI . BAQOH ) AS QOH , INTEGER ( VINBINI . BAQCM ) AS QCM ,
VINBINI . BACCTU , PALBINPF . PALLETID , PALBINPF . NBRRCKBINB ,
PALLITEMPF . UNITSPER , VININDEX . I001004
FROM PRODASP . R34FILES . PALBINPF PALBINPF , PRODASP . R34FILES . PALLITEMPF PALLITEMPF , PRODASP . R34FILES . VINBINI VINBINI , PRODASP . R34FILES . VININDEX VININDEX , PRODASP . R34FILES . VINITEM VINITEM WHERE VINBINI . BABLOC = PALBINPF . IMBLOC AND VINBINI . BACMP = PALBINPF . TACOMP AND VINBINI . BAITEM = VINITEM . ICITEM AND VINITEM . ICTRLN = VININDEX . I001TRLN AND PALLITEMPF . ICITEM = VINITEM . ICITEM AND PALLITEMPF . PALLETID = PALBINPF . PALLETID AND VINBINI . BAITEM = PALLITEMPF . ICITEM AND
( VINBINI . BAITEM LIKE UPPER ( CONCAT ( TRIM ( PRM_ITEM ) , '%' ) ) OR PRM_ITEM IS NULL ) AND
( UPPER ( VINITEM . ICTRLN ) LIKE UPPER ( CONCAT ( TRIM ( PRM_TRLN ) , '%' ) ) OR PRM_TRLN IS NULL ) AND
( VINBINI . BALOC = PRMLOC ) AND ( VINBINI . BAQOH > 0 ) AND ( VINBINI . BAQOH < INTEGER ( PRM_QTY ) OR PRM_QTY IS NULL ) AND ( VININDEX . I001004 LIKE ( CONCAT ( TRIM ( PRM_CC ) , '%' ) ) OR PRM_CC IS NULL )
ORDER BY BAITEM ASC ;
Can you please help me with this