How to achieve CROSS APPLY in NETEZZA?

Question

I wanted to share how we can achieve CROSS APPLY functionality without using it.[works in NETEZZA SQL].

FOLLOWING QUERY USES CROSS APPLY TO RETRIVE LAST 52 WEEK FOF EACH WEEK:

SELECT      t1.col, 
            t3.col          AS col_last52wks
FROM        TABLE1 t1
            CROSS APPLY (
                          SELECT TOP 52 t2.col
                          FROM  TABLE2 t2
                          WHERE t2.col <= t1.col
                          ORDER BY t2.col DESC
                ) t3
--Last 13 Weeks Condition
WHERE t1.col >= minvalue -- RANDOM
AND   t1.col <= maxvalue-- RANDOM

We can achieve this with the following modifications in the above code and get rid of the CROSS APPLY:

SELECT  t1.col,
        t2.col             AS col_last52wks
FROM    TABLE1 t1
        INNER JOIN TABLE2 t2
                ON t2.sequencecol BETWEEN (t1.sequencecol - 51) AND t1.sequencecol
WHERE t1.col >= minvalue
AND   t1.col <= maxvalue

`Cross Apply` will return top 10 `tradyrwkcode` from `t2` for each `tradyrwkcode` from `t1`. but `Cross Join` will return only 10 records no matter how many `tradyrwkcode` are present — Pரதீப், Jan 09 '18 at 02:44
Would I be wrong if I say CROSS APPLY works similar to INNER JOIN ? — , Jan 09 '18 at 02:57
Sort of, but they have their own uses. Outer table reference can be referenced inside a `CROSS APPLY` sub select, but in `Inner join` you cannot do the same. — Pரதீப், Jan 09 '18 at 03:04
There are many other differences present as well. Google it.. — Pரதீப், Jan 09 '18 at 03:11
Check this question https://stackoverflow.com/questions/1139160/when-should-i-use-cross-apply-over-inner-join — Pரதீப், Jan 09 '18 at 03:18
Cross apply is similar to inner join but will return a cartesian product related to the main table or whichever you relate it to. Cross join is similar to building a cube where to display any possible combination from both tables — RoMEoMusTDiE, Jan 09 '18 at 03:42
There is no `CROSS APPLY` in "SQL" - the standard query language. `cross apply` is specific for T-SQL from Microsoft. The equivalent to that in standard SQL is a `CROSS JOIN LATERAL` — , Jan 09 '18 at 06:59

score 1 · Answer 1 · answered Jan 09 '18 at 06:41

I use CROSS APPLY for such cases:

When I split a column data into multiple rows and make "join"s over this splitted data

The statements are as follows in general

FROM Table1
CROSS APPLY dbo.UDF_TableFunctionName(Table1Column) as t

So, if you are passing a string value to a table function, than you can simply use it as

select * from dbo.UDF_TableFunctionName(@strVariable)

But if you want to use this function on a row set instead of a single value, then you use CROSS APPLY

CROSS APPLY is useful if you want to return more than 1 column for a row column For example, in this table valued function, I parse a date column into its date parts and show in the same row

select
 o.SalesOrderID, o.OrderDate,
 d.[year], d.[month], d.[day]
from Sales.SalesOrderHeader o
cross apply dbo.udf_ParseDate(o.OrderDate) d

And on CROSS APPLY you don't use a join condition following ON You just pass a column as input parameter to a table-valued function

CROSS JOIN is simply a cartesian of the two tables. We used to code it without "CROSS JOIN" as follows

FROM TableA, TableB

How to achieve CROSS APPLY in NETEZZA?

1 Answers1