PLSQL select statement with variable help
13,518
Solution 1
If you want to do this in SQL, you'd want something like
SELECT alerts.*
FROM iphone_alerts alerts,
users
WHERE alerts.user_id = users.user_id
AND users.email = '[email protected]'
AND alerts.date_added = (SELECT MAX(date_added)
FROM iphone_alerts alerts2
WHERE alerts2.user_id = user.user_id)
Probably more efficient would be something like this that lets us hit the IPHONE_ALERTS table just once.
SELECT <<list of columns in IPHONE_ALERTS>>
FROM (
SELECT alerts.*,
RANK() OVER (PARTITION BY alerts.user_id ORDER BY date_added DESC) rnk
FROM iphone_alerts alerts,
users
WHERE alerts.user_id = users.user_id
AND users.email = '[email protected]'
)
WHERE rnk = 1
Solution 2
You need:
declare
userEmail varchar2(200);
begin
select user_id into userEmail
from users
where email = '[email protected]';
-- you will need a cursor to itare these results
select *
from iphone_alerts
where user_id = userEmail
and date_added = (select max(date_added) from iphone_alerts WHERE user_id);
end;
Edit after comment:
If select should return only ONE row, you don't need a cursor, but you need an into
clause to store each retrieved value into a variable. Something like:
declare
userEmail varchar2(200);
v_field1 number;
v_field2 date;
v_field3 varchar2(200);
begin
select user_id into userEmail
from users
where email = '[email protected]';
-- you will need a cursor to itare these results
select field1, field2, field3
into v_field1, v_field2, v_field3
from iphone_alerts
where user_id = userEmail
and date_added = (select max(date_added) from iphone_alerts WHERE user_id);
end;
Author by
Matt
Besides being a developer i enjoy Music, Guitar, Drawing, Hiking, Movies, and Camping
Updated on June 17, 2022Comments
-
Matt almost 2 years
Hello i am trying to do this simple statement but i want to add a variable that comes from a select. Here is what i have.
userEmail varChar(50) := SELECT user_id FROM users WHERE email = '[email protected]'; SELECT * FROM iphone_alerts WHERE user_id = userEmail AND date_added = (SELECT MAX(date_added) FROM iphone_alerts WHERE user_id = userEmail
Do i need to use something along the lines of Declare and Begins? I am new to the sql stuff and am having trouble finding answers.