Implicit outer apply in SQL

Question

thank you in advance for any assistance you can provide.

I am having to work in Oracle SQL and one issue I'm coming across is how to use applies in SQL. Specifically, hoping someone can help me write the code below to work in SQL. It's not a great example but I'm trying to figure out if there is a good way to:

do implicit applies, referencing columns from the "from" clause table, so they can be used in future applies/joins in the same query without having to do multiple temp tables, CTEs, or inner queries.
same question using top 1 in outer applies

Example:

--Drop Table #Users Drop Table #UsersLoginAttempts
Create table #Users(Username varchar(10), Password1 int)
Create Table #UsersLoginAttempts (Username varchar(10), Password1 int, AttemptDate date)


insert into #Users Values(' MAllen',123), ('SEllis ',124), ('MChen ',126)
insert into #UsersLoginAttempts Values
          ('MAllen ',123, '20221001'), (' SEllis ',124, '20221001')
        , (' MChen  ',126, '20221001'), ('MAllen ',126, '20221008')
        , (' SEllis ',123, '20221008'), (' MChen',128, '20221008')


Select t.*, u.*
from #Users t
outer apply ( select ltrim(rtrim(t.username)) as UserNameTrim) unt
Outer apply ( select top 1 * from #UsersLoginAttempts ula 
                    where ltrim(rtrim(ula.UserName)) = unt.UserNameTrim
                order by ula.AttemptDate desc) u

Oracle PLSQL is not Microsoft SQL Server (the `sql-server` tag), please fix your tags. — AlwaysLearning, Oct 17 '22 at 00:58
Are you sure you're using Oracle? PL/SQL *means* "Oracle". Because, you can't name tables that way (with a hash sign), INSERT INTO is wrong, there's no TOP in Oracle ... — Littlefoot, Oct 17 '22 at 05:59
Does this answer your question? [Oracle SQL query: Retrieve latest values per group based on time \[duplicate\]](https://stackoverflow.com/questions/2000908/oracle-sql-query-retrieve-latest-values-per-group-based-on-time) Or [Fetch the rows which have the Max value for a column for each distinct value of another column](https://stackoverflow.com/questions/121387/fetch-the-rows-which-have-the-max-value-for-a-column-for-each-distinct-value-of) — astentx, Oct 17 '22 at 07:23

score 0 · Answer 1 · answered Oct 17 '22 at 08:08

You do not need the first OUTER APPLY (and would need to add FROM DUAL if you did include it as, in Oracle, all queries must include a table name) and TOP is not supported syntax, you can use FETCH FIRST ROW ONLY instead:

Select t.*,
       u.*
from   Users u
       Outer apply (
         select *
         from   UsersLoginAttempts ula 
         where  TRIM(ula.UserName) = TRIM(u.UserName)
         order by ula.AttemptDate desc
         FETCH FIRST ROW ONLY
       ) t

Which, for your sample data:

*Note:

Do not use # as the first character of an identifier (otherwise you will need to use quoted identifiers throughout your code).
Your INSERT syntax is not valid for Oracle; use INSERT ALL ... OR INSERT INTO ... SELECT if you want to insert multiple rows.
Use VARCHAR2 and not VARCHAR.
'20221001' is not a date; it is a string literal. If you want to insert a date then either use TO_DATE, with a format model, to explicitly convert it or use a date literal DATE '2022-10-01'.

Create table Users (
  Username varchar2(10),
  Password1 int
)

Create Table UsersLoginAttempts (
  Username    varchar2(10),
  Password1   int,
  AttemptDate date
)

insert into Users (username, password1)
  SELECT ' MAllen', 123 FROM DUAL UNION ALL
  SELECT 'SEllis ', 124 FROM DUAL UNION ALL
  SELECT 'MChen ',  126 FROM DUAL;

insert into UsersLoginAttempts (username, password1, attemptdate)
  SELECT 'MAllen ',  123, DATE '2022-10-01' FROM DUAL UNION ALL
  SELECT ' SEllis ', 124, DATE '2022-10-01' FROM DUAL UNION ALL
  SELECT ' MChen  ', 126, DATE '2022-10-01' FROM DUAL UNION ALL
  SELECT 'MAllen ',  126, DATE '2022-10-08' FROM DUAL UNION ALL
  SELECT ' SEllis ', 123, DATE '2022-10-08' FROM DUAL UNION ALL
  SELECT ' MChen',   128, DATE '2022-10-08' FROM DUAL;

Outputs:

USERNAME	PASSWORD1	ATTEMPTDATE	USERNAME	PASSWORD1
MAllen	126	08-OCT-22	MAllen	123
SEllis	123	08-OCT-22	SEllis	124
MChen	128	08-OCT-22	MChen	126

Note: this is not PL/SQL, which is Oracle Procedural Language (similar to T-SQL for SQL Server), it is just Oracle's dialect of SQL.

fiddle

Implicit outer apply in SQL

1 Answers1