MySQL Forums
Forum List  »  Stored Procedures

subquery return more than 1 row
Posted by: Kent Weyers
Date: October 28, 2008 01:02PM

Hi, I seemed to have run into some trouble with a SP it worked fine and out of the blue and no change to the SP I get (subquery return more than 1 row) and I cant find what is causing the error.

some assistance would be greatly appreciated.

Stored Proc is as follows



DELIMITER $$

DROP PROCEDURE IF EXISTS `jasprint`.`ps_auth`$$

CREATE DEFINER=`root`@`localhost` PROCEDURE `ps_auth`()
BEGIN
declare session_count int;
declare processed_count int;
-- declare variables
-- declare global variables
declare guid varchar(50);
-- declare variables for page counts
declare lifecount_bw int;
declare lifecount_colour int;
declare print_bw int;
declare print_colour int;
declare copy_bw int;
declare copy_colour int;
declare fax_count int;
declare scan_count int;
declare A4_count int;
declare A3_count int ;
-- declare variables for page costs
declare printA4_cost float;
declare printA3_cost float;
declare copyA4_cost float;
declare copyA3_cost float;
declare fax_cost float;
declare scan_cost float;

-- _____________________________________________________________________________________________________________________________________
-- set variables
set guid=(select distinct(p.guid) from bh_sessions p where p.processed=0 and p.sessionstate='closed' limit 1,1);
-- set page counts
set lifecount_bw=(select p.CLOSE_LIFE_COUNT_BW from bh_sessions p
where p.sessionstate='closed' and p.processed =0 and p.guid=guid);
set lifecount_colour=(select p.CLOSE_LIFE_COUNT_COLOUR from bh_sessions p
where p.sessionstate='closed' and p.processed=0 and p.guid=guid);
set print_bw=(select sum(p.CLOSE_PRINT_BW-p.OPEN_PRINT_BW) from bh_sessions p
where p.sessionstate='closed' and p.processed=0 and p.guid=guid);
set print_colour=(select sum(p.CLOSE_PRINT_Colour-p.OPEN_PRINT_Colour) from bh_sessions p
where p.sessionstate='closed' and p.processed=0 and p.guid=guid);
set copy_bw=(select sum(p.CLOSE_COPY_BW-p.OPEN_COPY_BW) from bh_sessions p
where p.sessionstate='closed' and p.processed=0 and p.guid=guid);
set copy_colour=(select sum(p.CLOSE_COPY_Colour-p.OPEN_COPY_Colour) from bh_sessions p
where p.sessionstate='closed' and p.processed=0 and p.guid=guid);
set fax_count=(select sum(p.CLOSE_FAX_BW-p.OPEN_FAX_BW) from bh_sessions p
where p.sessionstate='closed' and p.processed=0 and p.guid=guid);
set scan_count=(select sum(p.CLOSE_SCAN_BW-p.OPEN_SCAN_BW) from bh_sessions p
where p.sessionstate='closed' and p.processed=0 and p.guid=guid);
set A4_count=(select sum(p.CLOSE_A4_paper-p.OPEN_A4_paper) from bh_sessions p
where p.sessionstate='closed' and p.processed=0 and p.guid=guid);
set A3_count=(select sum(p.CLOSE_A3_paper-p.OPEN_A3_paper) from bh_sessions p
where p.sessionstate='closed' and p.processed=0 and p.guid=guid);
-- set page cost
-- commented out test purposes NB!!!! remember to uncomment--
-- Print A4
IF A4_count>0 and print_bw>0 THEN
-- BEGIN
set printA4_cost=(select distinct((print_bw*m.monocost)) from devices d join bh_sessions p on d.deviceid=p.deviceid
join medias m on m.devicename=d.devicename
where m.mediasize=9 and p.sessionstate='closed' and p.processed=0 and p.guid=guid);
-- END
END IF;
IF A4_count>0 and print_colour>0 THEN
-- BEGIN
set printA4_cost=(select distinct((print_colour*m.colourcost)) from devices d join bh_sessions p on d.deviceid=p.deviceid
join medias m on m.devicename=d.devicename
where m.mediasize=9 and p.sessionstate='closed' and p.processed=0 and p.guid=guid);
-- END
END IF;
IF printA4_cost is null THEN
-- BEGIN
set printA4_cost=0;
-- END
END IF;
-- Print A3
IF A3_count>0 and print_bw>0 THEN
-- BEGIN
set printA3_cost=(select distinct((print_bw*m.monocost)) from devices d join bh_sessions p on d.deviceid=p.deviceid
join medias m on m.devicename=d.devicename
where m.mediasize=8 and p.sessionstate='closed' and p.processed=0 and p.guid=guid);
-- END
END IF;
IF A3_count>0 and print_colour>0 THEN
-- BEGIN
set printA3_cost=(select distinct((print_colour*m.colourcost)) from devices d join bh_sessions p on d.deviceid=p.deviceid
join medias m on m.devicename=d.devicename
where m.mediasize=8 and p.sessionstate='closed' and p.processed=0 and p.guid=guid);
-- END
END IF;
IF printA3_cost is null THEN
-- BEGIN
set printA3_cost=0;
-- END
END IF;
-- Copy A4
IF A4_count>0 and copy_bw>0 THEN
-- BEGIN
set copyA4_cost=(select distinct((copy_bw*m.monocost)) from devices d join bh_sessions p on d.deviceid=p.deviceid
join medias m on m.devicename=d.devicename
where m.mediasize=9 and p.sessionstate='closed' and p.processed=0 and p.guid=guid);
-- END
END IF;
IF A4_count>0 and copy_colour>0 THEN
-- BEGIN
set copyA4_cost=(select distinct((copy_colour*m.colourcost)) from devices d join bh_sessions p on d.deviceid=p.deviceid
join medias m on m.devicename=d.devicename
where m.mediasize=9 and p.sessionstate='closed' and p.processed=0 and p.guid=guid);
-- END
END IF;
IF copyA4_cost is null THEN
-- BEGIN
set copyA4_cost=0;
-- END
END IF;
-- Copy A3
IF A3_count>0 and copy_bw>0 THEN
-- BEGIN
set copyA3_cost=(select distinct((copy_bw*m.monocost)) from devices d join bh_sessions p on d.deviceid=p.deviceid
join medias m on m.devicename=d.devicename
where m.mediasize=8 and p.sessionstate='closed' and p.processed=0 and p.guid=guid);
-- END
END IF;
IF A3_count>0 and copy_colour>0 THEN
-- BEGIN
set copyA3_cost=(select distinct((copy_colour*m.colourcost)) from devices d join bh_sessions p on d.deviceid=p.deviceid
join medias m on m.devicename=d.devicename
where m.mediasize=8 and p.sessionstate='closed' and p.processed=0 and p.guid=guid);
-- END
END IF;
IF copyA3_cost is null THEN
-- BEGIN
set copyA3_cost=0;
-- END
END IF;
-- FAX
set fax_cost=(select distinct((fax_count*m.monocost)) from devices d join bh_sessions p on d.deviceid=p.deviceid
join medias m on m.devicename=d.devicename
where m.mediasize=400 and p.sessionstate='closed' and p.processed=0 and p.guid=guid);

-- _____________________________________________________________________________________________________________________________________
-- Update page count and life count of device
*/
update devices d, bh_sessions p
set
d.currentcount=d.currentcount+(print_bw+print_colour)+(copy_bw+copy_colour),
d.lifecount=(p.CLOSE_LIFE_COUNT_BW+ p.CLOSE_LIFE_COUNT_COLOUR)
where
p.deviceid=d.deviceid and
sessionstate='closed' and p.processed=0 and p.guid=guid;
-- _____________________________________________________________________________________________________________________________________
-- Update users balance
update accounts a, bh_sessions p
set
a.balance=a.balance+(copyA3_cost+copyA4_cost)+(printA4_cost+printA3_cost)
where
p.accountid=a.accountid and
sessionstate='closed' and p.processed=0 and p.guid=guid;
-- _____________________________________________________________________________________________________________________________________
-- insert transactions into jobs table
-- print bw
-- A4
IF A4_count>0 and print_bw>0 THEN
-- BEGIN
INSERT INTO JASPRINT.jobs
(MEDIA ,COLOR,ACCOUNT,GROUPNAME,DEVICENAME,LOCATION,JOBDATE ,PRINTJOB,
PAGECOUNT,MONOCOUNT,COLOURCOUNT,JOBCOST,DOCUMENT)
SELECT
'9', '0', a.account, a.groupname, d.devicename,d.location,p.closestamp,'1',
A4_count,A4_count,null,printA4_cost,'Print Job'
FROM bh_sessions p join accounts a on p.accountid=a.accountid join devices d on p.deviceid=d.deviceid
WHERE p.sessionstate='closed' and p.processed =0 and p.guid=guid;
-- END
END IF;
-- A3
IF A3_count>0 and print_bw>0 THEN
-- BEGIN
INSERT INTO JASPRINT.jobs
(MEDIA ,COLOR,ACCOUNT,GROUPNAME,DEVICENAME,LOCATION,JOBDATE ,PRINTJOB,
PAGECOUNT,MONOCOUNT,COLOURCOUNT,JOBCOST,DOCUMENT)
SELECT
'8', '0', a.account, a.groupname, d.devicename,d.location,p.closestamp,'1',
A3_count,A3_count,null,printA3_cost,'Print Job'
FROM bh_sessions p join accounts a on p.accountid=a.accountid join devices d on p.deviceid=d.deviceid
WHERE p.sessionstate='closed' and p.processed =0 and p.guid=guid;
-- END
END IF;
-- print colour
-- A4
IF A4_count>0 and print_colour>0 THEN
-- BEGIN
INSERT INTO JASPRINT.jobs
(MEDIA ,COLOR,ACCOUNT,GROUPNAME,DEVICENAME,LOCATION,JOBDATE ,PRINTJOB,
PAGECOUNT,MONOCOUNT,COLOURCOUNT,JOBCOST,DOCUMENT)
SELECT
'9', '1', a.account, a.groupname, d.devicename,d.location,p.closestamp,'1',
A4_count,null,A4_count,printA4_cost,'Print Job'
FROM bh_sessions p join accounts a on p.accountid=a.accountid join devices d on p.deviceid=d.deviceid
WHERE p.sessionstate='closed' and p.processed =0 and p.guid=guid;
-- END
END IF;
-- A3
IF A3_count>0 and print_colour>0 THEN
-- BEGIN
INSERT INTO JASPRINT.jobs
(MEDIA ,COLOR,ACCOUNT,GROUPNAME,DEVICENAME,LOCATION,JOBDATE ,PRINTJOB,
PAGECOUNT,MONOCOUNT,COLOURCOUNT,JOBCOST,DOCUMENT)
SELECT
'8', '1', a.account, a.groupname, d.devicename,d.location,p.closestamp,'1',
A3_count,null,A3_count,printA3_cost,'Print Job'
FROM bh_sessions p join accounts a on p.accountid=a.accountid join devices d on p.deviceid=d.deviceid
WHERE p.sessionstate='closed' and p.processed =0 and p.guid=guid;
-- END
END IF;
-- copy bw
-- A4
IF A4_count>0 and copy_bw>0 THEN
-- BEGIN
INSERT INTO JASPRINT.jobs
(MEDIA ,COLOR,ACCOUNT,GROUPNAME,DEVICENAME,LOCATION,JOBDATE ,COPYJOB,
PAGECOUNT,MONOCOUNT,COLOURCOUNT,JOBCOST,DOCUMENT)
SELECT
'9', '0', a.account, a.groupname, d.devicename,d.location,p.closestamp,'1',
A4_count,null,A4_count,copyA4_cost,'Copy Job'
FROM bh_sessions p join accounts a on p.accountid=a.accountid join devices d on p.deviceid=d.deviceid
WHERE p.sessionstate='closed' and p.processed =0 and p.guid=guid;
-- END
END IF;
-- A3
IF A3_count>0 and copy_bw>0 THEN
-- BEGIN
INSERT INTO JASPRINT.jobs
(MEDIA ,COLOR,ACCOUNT,GROUPNAME,DEVICENAME,LOCATION,JOBDATE ,COPYJOB,
PAGECOUNT,MONOCOUNT,COLOURCOUNT,JOBCOST,DOCUMENT)
SELECT
'8', '0', a.account, a.groupname, d.devicename,d.location,p.closestamp,'1',
A3_count,null,A3_count,copyA3_cost,'Copy Job'
FROM bh_sessions p join accounts a on p.accountid=a.accountid join devices d on p.deviceid=d.deviceid
WHERE p.sessionstate='closed' and p.processed =0 and p.guid=guid;
-- END
END IF;
-- copy colour
-- A4
IF A4_count>0 and copy_colour>0 THEN
-- BEGIN
INSERT INTO JASPRINT.jobs
(MEDIA ,COLOR,ACCOUNT,GROUPNAME,DEVICENAME,LOCATION,JOBDATE ,COPYJOB,
PAGECOUNT,MONOCOUNT,COLOURCOUNT,JOBCOST,DOCUMENT)
SELECT
'9', '1', a.account, a.groupname, d.devicename,d.location,p.closestamp,'1',
A4_count,null,A4_count,copyA4_cost,'Copy Job'
FROM bh_sessions p join accounts a on p.accountid=a.accountid join devices d on p.deviceid=d.deviceid
WHERE p.sessionstate='closed' and p.processed =0 and p.guid=guid;
-- END
END IF;
-- A3
IF A3_count>0 and copy_colour>0 THEN
-- BEGIN
INSERT INTO JASPRINT.jobs
(MEDIA ,COLOR,ACCOUNT,GROUPNAME,DEVICENAME,LOCATION,JOBDATE ,COPYJOB,
PAGECOUNT,MONOCOUNT,COLOURCOUNT,JOBCOST,DOCUMENT)
SELECT
'8', '1', a.account, a.groupname, d.devicename,d.location,p.closestamp,'1',
A3_count,null,A3_count,copyA3_cost,'Copy Job'
FROM bh_sessions p join accounts a on p.accountid=a.accountid join devices d on p.deviceid=d.deviceid
WHERE p.sessionstate='closed' and p.processed =0 and p.guid=guid;
-- END
END IF;
-- _____________________________________________________________________________________________________________________________________
-- set processed to 1
update
bh_sessions b
set
b.processed=1
where
b.sessionstate='closed' and b.processed=0 and b.guid=guid;
-- _____________________________________________________________________________________________________________________________________
-- END
END$$

DELIMITER ;

Options: ReplyQuote


Subject
Views
Written By
Posted
subquery return more than 1 row
3610
October 28, 2008 01:02PM
2046
October 28, 2008 03:34PM
1351
October 29, 2008 11:21AM
1636
October 29, 2008 01:35PM
1419
October 29, 2008 02:34PM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.