Re: Handle return of empty set from select
Mark Addinall Wrote:
-------------------------------------------------------
> Apologies in advance. I know how to do this in
> Perl and PHP CONNECTED to mySQL, and I know how to
> do it in PL/SQL but I can't get something SIMPLE
> working! And it isn't for the want of a few hours
> looking.
>
> I want to do something very basic. Run a query on
> ONE rather flat table. If I get an empty set
> returned, then translate that to the string
> "EMPTY"
>
> viz.
> <code>
> delimiter //
> drop procedure if exists `Best`//
> create procedure Best( IN in_state
> VARCHAR(255))
> begin
> drop table if exists has_cases;
> create temporary table has_cases
> select Hospital_Name,Death_Rates_Heart_Attack,
> State from
> hospital_outcomes
> where Death_Rates_Heart_Attack > 0 and State =
> in_state;
>
> if ( EXISTS (select Hospital_Name from has_cases
>
> order by Death_Rates_Heart_Attack, Hospital_Name
>
> limit 1), Hospital_Name, 'EMPTY');
> end //
> delimiter ;
> </code>
>
> I have tried IFNULL (until I realised that an
> empty set is NOT NULL (I think), I have tried
> CASE, and about a zillion other things... I'm
> stuck! It can't be difficult!
>
> mysql> select version();
> +-------------------------+
> | version() |
> +-------------------------+
> | 5.5.37-0ubuntu0.13.10.1 |
> +-------------------------+
> 1 row in set (0.27 sec)
>
>
>
>
> Ta!
I got over the first question of mine by using if (count()), thank you.
Perhaps you could help me on another problem. Now I have the basis for my procedure I want to add into the IN argument list, the COLUMN to use in the SELECT statement, ie:
call Best("QLD", "Heart_Attack", @result);
select @result;
1.03345
call Best("VIC", "Heart_Failure", @result);
select @result;
0.09022
That is what I want to see
I am unsure how to do this in mySQL.
my code (not working)
delimiter //
-- vim: set tabstop=4
drop procedure if exists `Best`//
create procedure Best( IN p_state VARCHAR(255),
IN p_illnness VARCHAR(255),
OUT p_name VARCHAR(255))
begin
drop table if exists has_cases;
create temporary table has_cases
select Hospital_Name, @p_illness, State from
hospital_outcomes
where @p_illness > 0 and State = p_state;
if ((select count(Hospital_Name) from has_cases) = 0) then
begin
set p_name = 'NA';
end;
else
begin
select Hospital_Name from has_cases
order by @p_illness, Hospital_Name
limit 1
into p_name;
end;
end if;
end //
delimiter ;
returns this.
mysql> call Best("QLD","Death_Rates_Pneumonia",@result);
ERROR 1054 (42S22): Unknown column 'p_illness' in 'where clause'
mysql> source Best.SQL;
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
mysql> call Best("VIC","Death_Rates_Pneumonia",@result);
Query OK, 0 rows affected (0.08 sec)
mysql> select @result;
+---------+
| @result |
+---------+
| NA |
+---------+
1 row in set (0.00 sec)
mysql> explain has_cases;
+---------------+--------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+---------------+--------------+------+-----+---------+-------+
| Hospital_Name | varchar(255) | NO | | NULL | |
| @p_illness | longblob | YES | | NULL | |
| State | varchar(255) | YES | | NULL | |
+---------------+--------------+------+-----+---------+-------+
3 rows in set (0.00 sec)
Any hints appreciated.