Click here to Skip to main content
15,867,453 members
Please Sign up or sign in to vote.
1.00/5 (1 vote)
See more:
Hello Experts,

I really want a quick help for the below stored procedure...
My SP works absolutely fine.
DELIMITER $$

DROP PROCEDURE IF EXISTS `spm_itemlocations` $$
CREATE DEFINER=`mysqluser`@`%` PROCEDURE `spm_itemlocations`(tLoc   character(4),
  tItem  character(21),
  tTLine character(52))
BEGIN
set tItem = concat(tItem, '%');

  select icitem, icdsc1, icdsc2, ifloc, ifqoh, ifqcm
  from TESTDATA.VINITMB
  join TESTDATA.VINITEM on icitem = ifitem
  where ifcomp = 1
  and ifdel = 'A'
  and (tLoc = '' or tLoc = ifloc)
  and ifqoh = 0
  and (tItem = '' or icitem like tItem)
  and UCASE(ictrln) like tTLine

  order by ifloc, ifitem;

end $$

DELIMITER ;



When someone says "Please group and use a summary where sum(ifqoh) = 0 to be included in this."

What does that mean? where should I sum and group ?

Can someone help me with this please?
Posted
Updated 26-Jun-15 4:24am
v3
Comments
[no name] 26-Jun-15 10:05am    
Are you asking us to read the mind of "someone" and tell you what they meant? Why don't you ask them?
sudevsu 26-Jun-15 10:07am    
Hehe lol no. I mean how can we sum with a condition?
sudevsu 26-Jun-15 10:08am    
Anyway I did something like this.

select icitem, icdsc1, icdsc2, ifloc, ifqoh, ifqcm, count(ifqoh) as 'count'
from TESTDATA.VINITMB
join TESTDATA.VINITEM on icitem = ifitem
where ifcomp = 1
and ifdel = 'A'
and ifqoh = 0
and icitem like 'VP%'
group by ifitem having count>0
order by ifloc, ifitem;


But that doesn't make any sense to me. sum (ifqoh)=0 ??? Whaaaattttt

1 solution

The best way to find out what grouping means is to read the documentation: 12.18.1 GROUP BY (Aggregate) Functions[^]. There you'll find examples.
 
Share this answer
 

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