Click here to Skip to main content
15,120,326 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
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.


SQL
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

SQL
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
Posted
Updated 26-Oct-15 5:48am
v3
Comments
ZurdoDev 26-Oct-15 10:23am
   
I don't do MySql but it looks like you have it right. A real simple test is to comment out the select statement and replace with PRINT statements (if MySql supports that) or simple SELECT 1 in the first if and SELECT 2 in the else. That way you can at least be sure which sql is executing. Then debug from there.
sudevsu 26-Oct-15 10:43am
   
I may or may not pass the value for PRM_QTY . Meaning. If I don't pass the value it should ignore the comparison. But it throws me an error saying "Data conversion error"
ZurdoDev 26-Oct-15 11:01am
   
But you don't know which one of the 2 is even running.
sudevsu 26-Oct-15 10:50am
   
Basically, if I don't pass the value it should ignore that condition

1 solution

Instead of
SQL
IF PRM_QTY IS NULL

try the built in function
SQL
IF isnull(PRM_QTY) THEN


See MYSQL: ISNULL FUNCTION[^]
And don't forget the THEN in the IF statement.

[UPDATE]
You could also try the ifnull function.
SQL
SET PRM_QTY = ifnull(PRM_QTY, NULL);

or, as you convert to an integer anyway
SQL
DECLARE varQTY INT;
SET varQTY = ifnull(PRM_QTY, 0); -- Or whatever default value you want to have
   
v3
Comments
sudevsu 26-Oct-15 10:45am
   
Thanks George. I tried it works only if I pass the value. If I don't pass the value it should ignore this clause in where. but it throws me an error data conversion error
George Jonsson 26-Oct-15 10:53am
   
What do you mean you are not passing the value?
In MySQL there is unfortunately no construct for optional parameters.
sudevsu 26-Oct-15 10:59am
   
If PRM_QTY is '' then it should ignore the where clause for this condition alone.
It should just check VINBIN.BAQOH > 0
It PRM_QTY is some value '50' then it should give me results like
VINBIN.BAQOH >0 and VINBIN.BINBAOH < INTEGER(50)
George Jonsson 26-Oct-15 11:08am
   
See my updated solution.
Maybe it will help you forward.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)




CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900