How can this query be rewitten?
-
Hi Experts,
I have written a query based on our requirement using co-related sub-query.
Checking if this can re-written using a JOIN instead of co-related subquery? Mainly, 5th column in the SELECT list. How can we implement using a join, I tried but it was throwing error saying, columns which aren't in GROUP BY cannot be
selected in the select list. So, I have written using a co-related subquery. There is nothing wrong in this approach, would like to know, if it has to rewritten using a join in the FROM clause, then how to do that?SELECT
t1.[Accno],
t2.[CustomerName],
t2.[High Rate] AS Ceiling_Rate,
t3.[Low Rate] AS Floor_Rate,
(SELECT max(b.[Maturity_Date]) FROM t4 as b WHERE b.[Account Number] = t1.[Accno] and b.[Maturity_Date] is not null and len(b.[Maturity_Date])>0 ) AS [Payoff_Date]
FROM AS t1
LEFT JOIN t2 ON t1.[Accno]=t2.[Accno]
LEFT OUTER JOIN t3 on t1.[Accno] = t3.[Account Number];Thank you.
Sam
- vsamantha35 - Saturday, September 30, 2017 1:36 PM
We need more information before we can help you. Did you read the part in the front of this form about posting DDL? You need to do that before anything else can help you.
You have four tables, which you name as if they were 1950 tape drives. The difference is we usually started with "MT0:" and then increased it by one. In SQL. These tables should have meaningful names, based on ISO 11179 naming rules. It looks like the account information is separated from the customer order. That makes no sense. Then the rate of something has its high and low ranges spread out over two separate tables. Again, that makes no sense. What you're saying is that all of these things are so totally different and unrelated, they go in separate tables. But this looks like a design flaw called attribute splitting, where things that are attributes of the same entity appear in multiple places in the schema instead of with that entity.
SQL has a date data type, which is nothing whatsoever like a string. This means looking for the link of this data element is absurd on the face of it. It's like asking what color is your favorite letter of the alphabet on a scale from 1 to 10.
Please stop using the square brackets and embedded spaces. This is more of your tape file mentality showing up; you're embedding spaces so you can do display formatting in the database layer, instead of in the presentation layer layer where might belong. You also use aliases to rename data elements; one of the first principles of RDBMS is at a data element has one and only one name everywhere. What makes a "maturity date" a totally different attribute from a "payoff date" in your data model?
This is why we use what is called a role as a prefix on a data element, we need to distinguish between things like the various rates
SELECT T1.account_nbr, T2.customer_name,
T2.high_something_rate, T3.low_something_rate,
(SELECT MAX(T4.maturity_date)
FROM T4
WHERE T4.account_nbr = T1.account_nbr
AND T4.maturity_date IS NOT NULL
AND LEN(B.maturity_date) > 0) AS payoff_date
FROM (T1
LEFT OUTER JOIN
T2
ON T1.account_nbr = T2.account_nbr
LEFT OUTER JOIN T3
ON T1.account_nbr = T3.account_nbr; nameBased on nothing I've got to work with here, I'm going to make some guesses about tables that we can see, keys, references and constraints we were not given. My first guess is that if your design was correct. You're probably have all this information in a single table probably called accounts.
SELECT A.account_nbr, A.customer_name,
A.high_something_rate, A.low_something_rate,
A.maturity_date
FROM Accounts AS A;Please post DDL and follow ANSI/ISO standards when asking for help.
-
Without an actual execution plan. table structure or index info, we're just stabbing in the wind... Based on the code pattern alone... Here a few alternatives that are logically equivalent but likely to produce different execution plans,
Test them all against you data... One or two is likely to produce a plan that is is far better than the others...-- option 1... sinle scan of T4
-- good option if account number has low density.
-- meaning lots of different numbers, none of which represent a large proportion of the total rows.
-- or there isn't a great index available for T5
SELECT
t1.Accno,
t2.CustomerName,
Ceiling_Rate = t2.[High Rate],
Floor_Rate = t3.[Low Rate],
Payoff_Date
FROM
dbo.t1
LEFT JOIN t2
ON t1.Accno = t2.Accno
LEFT JOIN t3
ON t1.Accno = t3.[Account Number]
LEFT JOIN (
SELECT
b.[Account Number],
Payoff_Date = MAX(b.Maturity_Date)
FROM
t4 AS b
WHERE
b.Maturity_Date IS NOT NULL
) mmd
ON t1.Accno = mmd.[Account Number];-- option 2... another single scan option ... BUT...
-- that scan will be an ordered scan which will be stupid fast with a "POC" index on T4
-- the POC indec would have account number in the 1st position and maturity date in the 2nd position.
WITH
cte_MaxMaturity AS (
SELECT
b.[Account Number],
b.Maturity_Date,
RN = ROW_NUMBER() OVER (PARTITION BY b.[Account Number] ORDER BY b.Maturity_Date DESC)
FROM
t4 AS b
WHERE
b.Maturity_Date IS NOT NULL
)
SELECT
t1.Accno,
t2.CustomerName,
Ceiling_Rate = t2.[High Rate],
Floor_Rate = t3.[Low Rate],
Payoff_Date
FROM
dbo.t1
LEFT JOIN t2
ON t1.Accno = t2.Accno
LEFT JOIN t3
ON t1.Accno = t3.[Account Number]
LEFT JOIN cte_MaxMaturity mm
ON ON t1.Accno = mm.[Account Number];-- option 3... Better for high density
-- or there is a light wight index on T4 with Account number in the 1st key position
-- and Maturity_Date as the second key position (prefferably with Maturity_Date in DESC order)
-- sort will be nast without the afformentioned index...
SELECT
t1.Accno,
t2.CustomerName,
Ceiling_Rate = t2.[High Rate],
Floor_Rate = t3.[Low Rate],
Payoff_Date
FROM
dbo.t1
LEFT JOIN t2
ON t1.Accno = t2.Accno
LEFT JOIN t3
ON t1.Accno = t3.[Account Number]
OUTER APPLY (
SELECT TOP (1)
Payoff_Date = b.Maturity_Date
FROM
t4 AS b
WHERE
t1.Accno = b.[Account Number]
AND b.Maturity_Date IS NOT NULL
ORDER BY
b.Maturity_Date DESC
) mmd;-- option 4... Better for high density
-- or there is a light wight index on T4 with Account number in the 1st key position
-- better option than option 3 IF... the index doesn't have Maturity_Date in the 2nd key position.
SELECT
t1.Accno,
t2.CustomerName,
Ceiling_Rate = t2.[High Rate],
Floor_Rate = t3.[Low Rate],
Payoff_Date
FROM
dbo.t1
LEFT JOIN t2
ON t1.Accno = t2.Accno
LEFT JOIN t3
ON t1.Accno = t3.[Account Number]
OUTER APPLY (
SELECT
Payoff_Date = MAX(b.Maturity_Date)
FROM
t4 AS b
WHERE
t1.Accno = b.[Account Number]
AND b.Maturity_Date IS NOT NULL
) mmd; -
Thanks Jason for the help.
- vsamantha35 - Sunday, October 1, 2017 11:50 AM
Glad to help. Hopefully one of those should help.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply