November 5, 2018 at 3:18 am
I'm trying to use the GROUP BY clause in a sub query that's part of my WHERE CLAUSE, but when I run the query i get this error
'Each GROUP BY expression must contain at least one column that is not an outer reference.'
this is the line causing the problem
and feetimeperiod.feetimeperiod IN (select MAX(feetimeperiod) AS feetimeperiod from feetimeperiod AS ftp where (CONVERT(date,ftp.begtime) = CONVERT(date,loadshapeprofile.begtime)) GROUP BY http://ftp.feetimeperiod,loadshapeprofile.offset)
I've searched for possible solutions and including one column from the inner query is supposed to do the trick, but it doesn't work,. I'm clearly doing something wrong here. Whats the best way to achieve the results I want ?
**I want to group the results of my subquery
November 5, 2018 at 5:35 am
Can you provide the entire query?
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
November 5, 2018 at 5:55 am
apologies, here's the complete sql code
select Trade.tradedate AS TradeDate,
Trade.status,
contract.contract AS ContractID,
fee.feemode,
position.counterparty,
position.positiontype,
Trade.cstcontractstart AS ContractStartDate,
Trade.cstcontractend as ContractEndDate,
contract.contracttype,
CONVERT(int,Trade.trade) AS TradeID,
feetimeperiod.feetimeperiod,
feetimeperiod.dbcolumn,
feetimeperiod.dbvalue,
feetimeperiod.begtime AS FeeTimePeriodBeginTime,
feetimeperiod.endtime AS FeeTimePeriodEndTime,
loadshapeprofile.begtime As StartDateTime,
loadshapeprofile.endtime AS EndDateTime,
loadshapeprofile.offset,
loadshape.timeunit,
CASE WHEN Cast((loadshapeprofile.endtime - loadshapeprofile.begtime) as int) = 1 THEN 'D'
WHEN Cast((loadshapeprofile.endtime - loadshapeprofile.begtime) as int) > 1 AND Cast((loadshapeprofile.endtime - loadshapeprofile.begtime) as int) <= 31 THEN 'M'
WHEN Cast((loadshapeprofile.endtime - loadshapeprofile.begtime) as int) > 31 AND Cast((loadshapeprofile.endtime - loadshapeprofile.begtime) as int) <= 93 THEN 'Q'
WHEN Cast((loadshapeprofile.endtime - loadshapeprofile.begtime) as int) > 93 AND Cast((loadshapeprofile.endtime - loadshapeprofile.begtime) as int) <= 183 THEN 'S'
ELSE 'Y'
END AS BlockDescription,
position.block,
CAST((loadshapeprofile.endtime - loadshapeprofile.begtime) AS FLOAT) * 24.0 AS HoursInPeriod,
CAST((loadshapeprofile.endtime - loadshapeprofile.begtime) AS int) AS Days,
position.unit,
ISNULL(fee.pricediff,0) AS GBPMWh,
CASE
WHEN loadshape.timeunit = 'HALFHOUR' THEN
loadshapeprofile.he1 / 2
ELSE
loadshapeprofile.he1
END AS loadshapeprofilehe1,
CASE
WHEN loadshape.timeunit = 'HALFHOUR' THEN
loadshapeprofile.he2 / 2
ELSE
loadshapeprofile.he2
END AS loadshapeprofilehe2,
CASE
WHEN loadshape.timeunit = 'HALFHOUR' THEN
loadshapeprofile.he3 / 2
ELSE
loadshapeprofile.he3
END AS loadshapeprofilehe3,
CASE
WHEN loadshape.timeunit = 'HALFHOUR' THEN
loadshapeprofile.he4 / 2
ELSE
loadshapeprofile.he4
END AS loadshapeprofilehe4,
CASE
WHEN loadshape.timeunit = 'HALFHOUR' THEN
loadshapeprofile.he5 / 2
ELSE
loadshapeprofile.he5
END AS loadshapeprofilehe5,
CASE
WHEN loadshape.timeunit = 'HALFHOUR' THEN
loadshapeprofile.he6 / 2
ELSE
loadshapeprofile.he6
END AS loadshapeprofilehe6,
CASE
WHEN loadshape.timeunit = 'HALFHOUR' THEN
loadshapeprofile.he7 / 2
ELSE
loadshapeprofile.he7
END AS loadshapeprofilehe7,
CASE
WHEN loadshape.timeunit = 'HALFHOUR' THEN
loadshapeprofile.he8 / 2
ELSE
loadshapeprofile.he8
END AS loadshapeprofilehe8,
CASE
WHEN loadshape.timeunit = 'HALFHOUR' THEN
loadshapeprofile.he9 / 2
ELSE
loadshapeprofile.he9
END AS loadshapeprofilehe9,
CASE
WHEN loadshape.timeunit = 'HALFHOUR' THEN
loadshapeprofile.he10 / 2
ELSE
loadshapeprofile.he10
END AS loadshapeprofilehe10,
CASE
WHEN loadshape.timeunit = 'HALFHOUR' THEN
loadshapeprofile.he11 / 2
ELSE
loadshapeprofile.he11
END AS loadshapeprofilehe11,
CASE
WHEN loadshape.timeunit = 'HALFHOUR' THEN
loadshapeprofile.he12 / 2
ELSE
loadshapeprofile.he12
END AS loadshapeprofilehe12,
CASE
WHEN loadshape.timeunit = 'HALFHOUR' THEN
loadshapeprofile.he13 / 2
ELSE
loadshapeprofile.he13
END AS loadshapeprofilehe13,
CASE
WHEN loadshape.timeunit = 'HALFHOUR' THEN
loadshapeprofile.he14 / 2
ELSE
loadshapeprofile.he14
END AS loadshapeprofilehe14,
CASE
WHEN loadshape.timeunit = 'HALFHOUR' THEN
loadshapeprofile.he15 / 2
ELSE
loadshapeprofile.he15
END AS loadshapeprofilehe15,
CASE
WHEN loadshape.timeunit = 'HALFHOUR' THEN
loadshapeprofile.he16 / 2
ELSE
loadshapeprofile.he16
END AS loadshapeprofilehe16,
CASE
WHEN loadshape.timeunit = 'HALFHOUR' THEN
loadshapeprofile.he17 / 2
ELSE
loadshapeprofile.he17
END AS loadshapeprofilehe17,
CASE
WHEN loadshape.timeunit = 'HALFHOUR' THEN
loadshapeprofile.he18 / 2
ELSE
loadshapeprofile.he18
END AS loadshapeprofilehe18,
CASE
WHEN loadshape.timeunit = 'HALFHOUR' THEN
loadshapeprofile.he19 / 2
ELSE
loadshapeprofile.he19
END AS loadshapeprofilehe19,
CASE
WHEN loadshape.timeunit = 'HALFHOUR' THEN
loadshapeprofile.he20 / 2
ELSE
loadshapeprofile.he20
END AS loadshapeprofilehe20,
CASE
WHEN loadshape.timeunit = 'HALFHOUR' THEN
loadshapeprofile.he21 / 2
ELSE
loadshapeprofile.he21
END AS loadshapeprofilehe21,
CASE
WHEN loadshape.timeunit = 'HALFHOUR' THEN
loadshapeprofile.he22 / 2
ELSE
loadshapeprofile.he22
END AS loadshapeprofilehe22,
CASE
WHEN loadshape.timeunit = 'HALFHOUR' THEN
loadshapeprofile.he23 / 2
ELSE
loadshapeprofile.he23
END AS loadshapeprofilehe23,
CASE
WHEN loadshape.timeunit = 'HALFHOUR' THEN
loadshapeprofile.he24 / 2
ELSE
loadshapeprofile.he24
END AS loadshapeprofilehe24,
CASE
WHEN loadshape.timeunit = 'HALFHOUR' THEN
loadshapeprofile.he25 / 2
ELSE
loadshapeprofile.he25
END AS loadshapeprofilehe25
from Trade
inner join position on trade.trade = position.trade
inner join contract on position.contract = contract.contract
inner join loadshape on position.loadshape = loadshape.loadshape
inner join loadshapeprofile on loadshape.loadshape = loadshapeprofile.loadshape
inner join fee on position.position = fee.dbvalue
inner join feetimeperiod on fee.feetimeperiod = feetimeperiod.feetimeperiod and feetimeperiod.dbvalue = position.position
where contract.contract = '1111111'
and position.loadshape is not null
and fee.dbcolumn = 'POSITION'
and fee.feemethod = 'COMMODITY PRICE'
and (CAST(DATEPART(MINUTE, feetimeperiod.begtime ) as decimal) = loadshapeprofile.offset)
and Trade.status = 'ACTIVE'
and Trade.tradestatus IN ('FO Approval','TC Approval')
and feetimeperiod.feetimeperiod IN (select MAX(feetimeperiod) AS feetimeperiod from feetimeperiod AS ftp where (CONVERT(date,ftp.begtime) = CONVERT(date,loadshapeprofile.begtime)) GROUP BY http://ftp.feetimeperiod, loadshapeprofile.offset)
and feetimeperiod.begtime >= '2017-03-01 00:00:00.000' and CONVERT(date,feetimeperiod.endtime) <= '2017-03-31'
order by feetimeperiod.begtime,feetimeperiod.endtime
November 5, 2018 at 6:44 am
That subquery is rather confusing. Can you explain what that part of the query is trying to do?
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
November 5, 2018 at 7:04 am
Im matching up the 2 begtime columns, but within the matches found are 30 minute periods. (up to 48) I only need 2 of these so I figured if I grouped by the offset (which is 0 - on the hour or 30 - on the half hour) that will eliminate all the time variations for each day effectively giving me 2 feetimeperiod values in the subquery. There may not always be 1 '0' offset and 1 '30' offset so I cant use TOP 2. I cant think of any other way to eliminate the other unwanted time periods for the same date
November 5, 2018 at 7:32 am
solus - Monday, November 5, 2018 3:18 AMI'm trying to use the GROUP BY clause in a sub query that's part of my WHERE CLAUSE, but when I run the query i get this error'Each GROUP BY expression must contain at least one column that is not an outer reference.'
this is the line causing the problem
and feetimeperiod.feetimeperiod IN (select MAX(feetimeperiod) AS feetimeperiod from feetimeperiod AS ftp where (CONVERT(date,ftp.begtime) = CONVERT(date,loadshapeprofile.begtime)) GROUP BY http://ftp.feetimeperiod,loadshapeprofile.offset)
I've searched for possible solutions and including one column from the inner query is supposed to do the trick, but it doesn't work,. I'm clearly doing something wrong here. Whats the best way to achieve the results I want ?
**I want to group the results of my subquery
loadshapeprofile is a table in the main query, so you cannot group by it in the subquery.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
November 5, 2018 at 7:37 am
How would I be able to achieve the results I'm after ? This has had me stumped for quite a while now
November 5, 2018 at 7:56 am
solus - Monday, November 5, 2018 7:37 AMHow would I be able to achieve the results I'm after ? This has had me stumped for quite a while now
You've been around long enough to know that we always ask for sample data and expected results. The first link in my signature shows you how to provide it.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
November 5, 2018 at 8:12 am
solus - Monday, November 5, 2018 3:18 AMI'm trying to use the GROUP BY clause in a sub query that's part of my WHERE CLAUSE, but when I run the query i get this error'Each GROUP BY expression must contain at least one column that is not an outer reference.'
this is the line causing the problem
and feetimeperiod.feetimeperiod IN (select MAX(feetimeperiod) AS feetimeperiod from feetimeperiod AS ftp where (CONVERT(date,ftp.begtime) = CONVERT(date,loadshapeprofile.begtime)) GROUP BY http://ftp.feetimeperiod,loadshapeprofile.offset)
I've searched for possible solutions and including one column from the inner query is supposed to do the trick, but it doesn't work,. I'm clearly doing something wrong here. Whats the best way to achieve the results I want ?
**I want to group the results of my subquery
This isn't going to do what you're expecting: you're not going to get MAX(feetimeperiod) if you GROUP BY feetimeperiod.
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
November 5, 2018 at 9:04 am
drew.allen - Monday, November 5, 2018 7:32 AMsolus - Monday, November 5, 2018 3:18 AMI'm trying to use the GROUP BY clause in a sub query that's part of my WHERE CLAUSE, but when I run the query i get this error'Each GROUP BY expression must contain at least one column that is not an outer reference.'
this is the line causing the problem
and feetimeperiod.feetimeperiod IN (select MAX(feetimeperiod) AS feetimeperiod from feetimeperiod AS ftp where (CONVERT(date,ftp.begtime) = CONVERT(date,loadshapeprofile.begtime)) GROUP BY http://ftp.feetimeperiod,loadshapeprofile.offset)
I've searched for possible solutions and including one column from the inner query is supposed to do the trick, but it doesn't work,. I'm clearly doing something wrong here. Whats the best way to achieve the results I want ?
**I want to group the results of my subquery
loadshapeprofile is a table in the main query, so you cannot group by it in the subquery.
Drew
With 6 joins on 7 tables and an aggregated correlated subquery in the WHERE clause, I'm not sure I want to see any readily consumable test data for this one. π
--Jeff Moden
Change is inevitable... Change for the better is not.
November 5, 2018 at 9:08 am
Jeff Moden - Monday, November 5, 2018 9:04 AMdrew.allen - Monday, November 5, 2018 7:32 AMsolus - Monday, November 5, 2018 3:18 AMI'm trying to use the GROUP BY clause in a sub query that's part of my WHERE CLAUSE, but when I run the query i get this error'Each GROUP BY expression must contain at least one column that is not an outer reference.'
this is the line causing the problem
and feetimeperiod.feetimeperiod IN (select MAX(feetimeperiod) AS feetimeperiod from feetimeperiod AS ftp where (CONVERT(date,ftp.begtime) = CONVERT(date,loadshapeprofile.begtime)) GROUP BY http://ftp.feetimeperiod,loadshapeprofile.offset)
I've searched for possible solutions and including one column from the inner query is supposed to do the trick, but it doesn't work,. I'm clearly doing something wrong here. Whats the best way to achieve the results I want ?
**I want to group the results of my subquery
loadshapeprofile is a table in the main query, so you cannot group by it in the subquery.
Drew
With 6 joins on 7 tables and an aggregated correlated subquery in the WHERE clause, I'm not sure I want to see any readily consumable test data for this one. π
It is numberplate-compliant though π
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
November 5, 2018 at 9:32 am
Jeff Moden - Monday, November 5, 2018 9:04 AMdrew.allen - Monday, November 5, 2018 7:32 AMsolus - Monday, November 5, 2018 3:18 AMI'm trying to use the GROUP BY clause in a sub query that's part of my WHERE CLAUSE, but when I run the query i get this error'Each GROUP BY expression must contain at least one column that is not an outer reference.'
this is the line causing the problem
and feetimeperiod.feetimeperiod IN (select MAX(feetimeperiod) AS feetimeperiod from feetimeperiod AS ftp where (CONVERT(date,ftp.begtime) = CONVERT(date,loadshapeprofile.begtime)) GROUP BY http://ftp.feetimeperiod,loadshapeprofile.offset)
I've searched for possible solutions and including one column from the inner query is supposed to do the trick, but it doesn't work,. I'm clearly doing something wrong here. Whats the best way to achieve the results I want ?
**I want to group the results of my subquery
loadshapeprofile is a table in the main query, so you cannot group by it in the subquery.
Drew
With 6 joins on 7 tables and an aggregated correlated subquery in the WHERE clause, I'm not sure I want to see any readily consumable test data for this one. π
this is the world I have to deal with, not my database structure π
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply