Jump to content
DJ_DP

Simplify the cross apply functionality

Recommended Posts

Hi,

I have to get some columns from two different tables but with those columns are having similar data but naming convention is different, can I re write the sql more simple way;

SELECT A AS A,

B AS B,      C AS C,     D AS D

FROM ( SELECT A AS A,

B AS B,   C AS C,   D AS D

FROM TABLE 123   A

CROSS APPLY

(SELECT DISTINCT E AS B FROM TABLE 456) B

WHERE E >= B

 

Can we simplify this more? Remove cross apply and use where clause instead?

Share this post


Link to post
Share on other sites

Hi, and welcome to the forum!

As to column naming conventions, you can fully- qualify the column name and then assign an alias:

select scott.emp myemp from employees;

The CROSS APPLY is rarely used, but your subquery could be re-written with a standard ANSI join, using the PARTITION and OVER operators.

Oracle guru Tim Hall has some great examples:

https://oracle-base.com/articles/12c/lateral-inline-views-cross-apply-and-outer-apply-joins-12cr1

Good luck!

Share this post


Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now

×