Description:
When executing the procedure below, the call to the proc is removed in the SQL text window and the five result sets cannot be viewed - only one to two can be.
How to repeat:
DROP TABLE IF EXISTS `gim2`.`broker`;
CREATE TABLE `broker` (
`broker_id` int(11) NOT NULL,
`office_location_id` int(11) default NULL,
`broker_last_name` varchar(40) NOT NULL,
`broker_first_name` varchar(20) NOT NULL,
`broker_middle_initial` char(1) default NULL,
`manager_id` int(11) default NULL,
`years_with_firm` decimal(3,1) NOT NULL,
PRIMARY KEY (`broker_id`),
KEY `broker_location` (`office_location_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
DROP TABLE IF EXISTS `gim2`.`client`;
CREATE TABLE `client` (
`client_id` int(11) NOT NULL,
`client_first_name` varchar(20) NOT NULL,
`client_last_name` varchar(40) NOT NULL,
`client_gender` char(1) NOT NULL,
`client_year_of_birth` int(4) NOT NULL,
`client_marital_status` varchar(20) default NULL,
`client_street_address` varchar(40) NOT NULL,
`client_postal_code` varchar(10) NOT NULL,
`client_city` varchar(30) NOT NULL,
`client_state_province` varchar(40) NOT NULL,
`client_phone_number` varchar(25) NOT NULL,
`client_household_income` bigint(30) default NULL,
`client_country` varchar(40) default NULL,
`broker_id` int(11) NOT NULL,
PRIMARY KEY (`client_id`),
KEY `client_broker` (`broker_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
DROP TABLE IF EXISTS `gim2`.`client_transaction`;
CREATE TABLE `client_transaction` (
`client_transaction_id` int(11) NOT NULL,
`client_id` int(11) NOT NULL,
`investment_id` int(11) NOT NULL,
`action` varchar(10) NOT NULL,
`price` decimal(12,2) NOT NULL,
`number_of_units` int(11) NOT NULL,
`transaction_status` varchar(10) NOT NULL,
`transaction_sub_timestamp` datetime NOT NULL,
`transaction_comp_timestamp` datetime NOT NULL,
`description` varchar(200) default NULL,
`broker_id` bigint(10) default NULL,
`broker_commission` decimal(10,2) default NULL,
PRIMARY KEY (`client_transaction_id`),
KEY `client_transaction_broker` (`broker_id`),
KEY `client_transaction_client` (`client_id`),
KEY `client_transaction_investment` (`investment_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
DROP TABLE IF EXISTS `gim2`.`client_transaction_hist`;
CREATE TABLE `client_transaction_hist` (
`client_transaction_id` int(11) NOT NULL default '0',
`client_id` int(11) NOT NULL default '0',
`investment_id` int(11) NOT NULL default '0',
`action` varchar(10) NOT NULL default '',
`price` decimal(12,2) NOT NULL default '0.00',
`number_of_units` int(11) NOT NULL default '0',
`transaction_status` varchar(10) NOT NULL default '',
`transaction_sub_timestamp` datetime NOT NULL default '0000-00-00 00:00:00',
`transaction_comp_timestamp` datetime NOT NULL default '0000-00-00 00:00:00',
`description` varchar(200) default NULL,
`broker_id` bigint(10) default NULL,
`broker_commission` decimal(10,2) default NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
DROP TABLE IF EXISTS `gim2`.`investment`;
CREATE TABLE `investment` (
`investment_id` int(11) NOT NULL,
`investment_type_id` int(11) default NULL,
`investment_vendor` varchar(30) default NULL,
`investment_name` varchar(200) default NULL,
`investment_unit` varchar(20) default NULL,
`investment_duration` varchar(10) default NULL,
PRIMARY KEY (`investment_id`),
KEY `investment_investment_type` (`investment_type_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
DROP TABLE IF EXISTS `gim2`.`investment_type`;
CREATE TABLE `investment_type` (
`investment_type_id` int(11) NOT NULL,
`investment_type_name` varchar(30) NOT NULL,
PRIMARY KEY (`investment_type_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
DROP TABLE IF EXISTS `gim2`.`office_location`;
CREATE TABLE `office_location` (
`office_location_id` int(11) NOT NULL,
`office_name` varchar(20) NOT NULL,
`office_address` varchar(50) NOT NULL,
`office_city` varchar(30) NOT NULL,
`office_state_province` varchar(40) NOT NULL,
`office_postal_code` varchar(10) NOT NULL,
`office_country` varchar(40) default NULL,
PRIMARY KEY (`office_location_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
DELIMITER $$
DROP PROCEDURE IF EXISTS `gim2`.`CORPORATE_ANALYSIS`$$
CREATE PROCEDURE `gim2`.`CORPORATE_ANALYSIS`(IN START_DATE DATETIME,
IN END_DATE DATETIME)
BEGIN
select a.broker_id,
a.broker_first_name,
a.broker_last_name,
sum(broker_commission) total_commissions
from broker a,
client_transaction b
where b.transaction_comp_timestamp between start_date and end_date and
a.broker_id = b.broker_id
group by a.broker_id,
a.broker_first_name,
a.broker_last_name
order by 4 desc;
select c.office_name,
sum(broker_commission) total_commissions
from broker a,
client_transaction b,
office_location c
where b.transaction_comp_timestamp between start_date and end_date and
a.broker_id = b.broker_id and
a.office_location_id = c.office_location_id
group by c.office_name
order by 2 desc;
select b.investment_vendor,
b.investment_name,
sum(price) * sum(number_of_units) total_invested_dollars
from client_transaction a,
investment b
where a.transaction_comp_timestamp between start_date and end_date and
b.investment_id = a.investment_id and
a.action = 'buy'
group by b.investment_vendor,
b.investment_name
order by 3 desc
limit 20;
select c.investment_type_name,
sum(price) * sum(number_of_units) total_invested_dollars
from client_transaction a,
investment b,
investment_type c
where a.transaction_comp_timestamp between start_date and end_date and
b.investment_id = a.investment_id and
c.investment_type_id = b.investment_type_id and
a.action = 'buy'
group by c.investment_type_name
order by 2 desc;
select b.client_first_name,
b.client_last_name,
sum(price) * sum(number_of_units) total_invested_dollars
from client_transaction a,
client b
where a.transaction_comp_timestamp between start_date and end_date and
b.client_id = a.client_id and
a.action = 'buy'
group by b.client_first_name,
b.client_last_name
order by 3 desc
limit 20;
END$$
DELIMITER ;
call CORPORATE_ANALYSIS('1990-01-01','2006-01-01');
Suggested fix:
Retain call to proc in SQL text window and handle multiple result set panes - perhaps in one large grid?
Description: When executing the procedure below, the call to the proc is removed in the SQL text window and the five result sets cannot be viewed - only one to two can be. How to repeat: DROP TABLE IF EXISTS `gim2`.`broker`; CREATE TABLE `broker` ( `broker_id` int(11) NOT NULL, `office_location_id` int(11) default NULL, `broker_last_name` varchar(40) NOT NULL, `broker_first_name` varchar(20) NOT NULL, `broker_middle_initial` char(1) default NULL, `manager_id` int(11) default NULL, `years_with_firm` decimal(3,1) NOT NULL, PRIMARY KEY (`broker_id`), KEY `broker_location` (`office_location_id`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1; DROP TABLE IF EXISTS `gim2`.`client`; CREATE TABLE `client` ( `client_id` int(11) NOT NULL, `client_first_name` varchar(20) NOT NULL, `client_last_name` varchar(40) NOT NULL, `client_gender` char(1) NOT NULL, `client_year_of_birth` int(4) NOT NULL, `client_marital_status` varchar(20) default NULL, `client_street_address` varchar(40) NOT NULL, `client_postal_code` varchar(10) NOT NULL, `client_city` varchar(30) NOT NULL, `client_state_province` varchar(40) NOT NULL, `client_phone_number` varchar(25) NOT NULL, `client_household_income` bigint(30) default NULL, `client_country` varchar(40) default NULL, `broker_id` int(11) NOT NULL, PRIMARY KEY (`client_id`), KEY `client_broker` (`broker_id`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1; DROP TABLE IF EXISTS `gim2`.`client_transaction`; CREATE TABLE `client_transaction` ( `client_transaction_id` int(11) NOT NULL, `client_id` int(11) NOT NULL, `investment_id` int(11) NOT NULL, `action` varchar(10) NOT NULL, `price` decimal(12,2) NOT NULL, `number_of_units` int(11) NOT NULL, `transaction_status` varchar(10) NOT NULL, `transaction_sub_timestamp` datetime NOT NULL, `transaction_comp_timestamp` datetime NOT NULL, `description` varchar(200) default NULL, `broker_id` bigint(10) default NULL, `broker_commission` decimal(10,2) default NULL, PRIMARY KEY (`client_transaction_id`), KEY `client_transaction_broker` (`broker_id`), KEY `client_transaction_client` (`client_id`), KEY `client_transaction_investment` (`investment_id`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1; DROP TABLE IF EXISTS `gim2`.`client_transaction_hist`; CREATE TABLE `client_transaction_hist` ( `client_transaction_id` int(11) NOT NULL default '0', `client_id` int(11) NOT NULL default '0', `investment_id` int(11) NOT NULL default '0', `action` varchar(10) NOT NULL default '', `price` decimal(12,2) NOT NULL default '0.00', `number_of_units` int(11) NOT NULL default '0', `transaction_status` varchar(10) NOT NULL default '', `transaction_sub_timestamp` datetime NOT NULL default '0000-00-00 00:00:00', `transaction_comp_timestamp` datetime NOT NULL default '0000-00-00 00:00:00', `description` varchar(200) default NULL, `broker_id` bigint(10) default NULL, `broker_commission` decimal(10,2) default NULL ) ENGINE=InnoDB DEFAULT CHARSET=latin1; DROP TABLE IF EXISTS `gim2`.`investment`; CREATE TABLE `investment` ( `investment_id` int(11) NOT NULL, `investment_type_id` int(11) default NULL, `investment_vendor` varchar(30) default NULL, `investment_name` varchar(200) default NULL, `investment_unit` varchar(20) default NULL, `investment_duration` varchar(10) default NULL, PRIMARY KEY (`investment_id`), KEY `investment_investment_type` (`investment_type_id`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1; DROP TABLE IF EXISTS `gim2`.`investment_type`; CREATE TABLE `investment_type` ( `investment_type_id` int(11) NOT NULL, `investment_type_name` varchar(30) NOT NULL, PRIMARY KEY (`investment_type_id`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1; DROP TABLE IF EXISTS `gim2`.`office_location`; CREATE TABLE `office_location` ( `office_location_id` int(11) NOT NULL, `office_name` varchar(20) NOT NULL, `office_address` varchar(50) NOT NULL, `office_city` varchar(30) NOT NULL, `office_state_province` varchar(40) NOT NULL, `office_postal_code` varchar(10) NOT NULL, `office_country` varchar(40) default NULL, PRIMARY KEY (`office_location_id`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1; DELIMITER $$ DROP PROCEDURE IF EXISTS `gim2`.`CORPORATE_ANALYSIS`$$ CREATE PROCEDURE `gim2`.`CORPORATE_ANALYSIS`(IN START_DATE DATETIME, IN END_DATE DATETIME) BEGIN select a.broker_id, a.broker_first_name, a.broker_last_name, sum(broker_commission) total_commissions from broker a, client_transaction b where b.transaction_comp_timestamp between start_date and end_date and a.broker_id = b.broker_id group by a.broker_id, a.broker_first_name, a.broker_last_name order by 4 desc; select c.office_name, sum(broker_commission) total_commissions from broker a, client_transaction b, office_location c where b.transaction_comp_timestamp between start_date and end_date and a.broker_id = b.broker_id and a.office_location_id = c.office_location_id group by c.office_name order by 2 desc; select b.investment_vendor, b.investment_name, sum(price) * sum(number_of_units) total_invested_dollars from client_transaction a, investment b where a.transaction_comp_timestamp between start_date and end_date and b.investment_id = a.investment_id and a.action = 'buy' group by b.investment_vendor, b.investment_name order by 3 desc limit 20; select c.investment_type_name, sum(price) * sum(number_of_units) total_invested_dollars from client_transaction a, investment b, investment_type c where a.transaction_comp_timestamp between start_date and end_date and b.investment_id = a.investment_id and c.investment_type_id = b.investment_type_id and a.action = 'buy' group by c.investment_type_name order by 2 desc; select b.client_first_name, b.client_last_name, sum(price) * sum(number_of_units) total_invested_dollars from client_transaction a, client b where a.transaction_comp_timestamp between start_date and end_date and b.client_id = a.client_id and a.action = 'buy' group by b.client_first_name, b.client_last_name order by 3 desc limit 20; END$$ DELIMITER ; call CORPORATE_ANALYSIS('1990-01-01','2006-01-01'); Suggested fix: Retain call to proc in SQL text window and handle multiple result set panes - perhaps in one large grid?