CODEWITHSUNDEEP
sqlt-sqldb2
Chad
Chad

Reputation: 24689

Confusing SQL. Short but weird

I'm using a reporting tool that goes against a DB2 mainframe database. when I examine the SQL that it produced, it is of the following (much simpified) format:

SELECT
    AL1.Field1,
    AL1.Field2
FROM
(
--#region 1
    Big SQL goes here
--#endregion end region 1
)  AL1
(
    Field1,
    Field2
)
WHERE
(AL1.Field1<>0)

I'm not at familiar with DB2 SQL as I am with T-SQL. This part confuses me:

AL1
(
    Field1,
    Field2
)

Would the "T-SQL" way of expressing the above SQL essentially be the following?

SELECT
    AL1.Field1,
    AL1.Field2
FROM
(
--#region 1
    Big SQL goes here
--#endregion end region 1
) AL1

WHERE
(AL1.Field1<>0)

Upvotes: 2

Views: 168

Answers (2)

Sergey Kalinichenko
Sergey Kalinichenko

Reputation: 726589

This part of the syntax is known as the Correlation Clause. It lets you provide alternative names to columns in the nested select. This is especially useful when the columns inside the big SQL are calculated, because it lets you refer to these columns as if they were named.

The corresponding syntax in tsql is Common Table Expressions. Your query would become this:

WITH AL1 (Field1, Field2) AS (
--#region 1
    Big SQL goes here
--#endregion end region 1
)
SELECT Field1, Field2
FROM AL1
WHERE AL1.Field1 <> 0

Upvotes: 3

Iłya Bursov
Iłya Bursov

Reputation: 24146

You need to restructure your query to see logic:

SELECT
    AL1.Field1,
    AL1.Field2
FROM
(
--#region 1
    Big SQL goes here
--#endregion end region 1
)  AL1 (Field1, Field2)

this query means, create temporary table "AL1", data in this table will go from big SQL, and you're assigning column names to this table (Field1, Field2), so then you can use these names in WHERE or SELECT

this is useful when your BIG SQL contains some functions, like:

select ???
from (
select max(a) + min(b) from othertbl
) temptbl

to refere to ??? outside subquery you can either add AS to each column in select or you can just use temptbl(mysupercol) and refere to temptbl.mysupercol later

Upvotes: 2

Related Questions