subquery return more than 1 row

[Kent Weyers]

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 ;