Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
poncho4u
Starting Member
3 Posts |
 Posted - 2013-11-26 : 11:39:56
|
Hello everyone,
Is it possible to use a complete sql statement within an in() function? What is wrong with the query below please:
Select cell, sdate, PTTCH from stats_60
where ne = 'BSC4' and sdate in (
SELECT SDATE FROM (
SELECT TO_CHAR(SDATE,'YYYY-MM-DD'), SDATE
, MAX(ROUND(ERLANG,2))over(partition by TO_CHAR(SDATE,'YYYY-MM-DD')) BHERL, ERLANG
FROM
(
SELECT A.ELEMENT, A.SDATE SDATE, TO_CHAR(A.SDATE,'HH24'), A.ERLANG
FROM STATS_LAYER_60 A
WHERE A.ELEMENT='BSC4'
AND TO_CHAR(A.SDATE,'HH24') BETWEEN '00' AND '24'
AND A.SDATE >= to_date('25/11/2013','DD/MM/YYYY')
AND A.SDATE < to_date('26/11/2013','DD/MM/YYYY')
)
) where BHERL=ERLANG
)
Thanks
edit: moved to proper forum |
|
|
James K
Master Smack Fu Yak Hacker
3873 Posts |
Posted - 2013-11-26 : 12:55:03
|
quote:
Originally posted by poncho4u
Hello everyone,
Is it possible to use a complete sql statement within an in() function? What is wrong with the query below please:
Select cell, sdate, PTTCH from stats_60
where ne = 'BSC4' and sdate in (
SELECT SDATE FROM (
SELECT TO_CHAR(SDATE,'YYYY-MM-DD'), SDATE
, MAX(ROUND(ERLANG,2))over(partition by TO_CHAR(SDATE,'YYYY-MM-DD')) BHERL, ERLANG
FROM
(
SELECT A.ELEMENT, A.SDATE SDATE, TO_CHAR(A.SDATE,'HH24'), A.ERLANG
FROM STATS_LAYER_60 A
WHERE A.ELEMENT='BSC4'
AND TO_CHAR(A.SDATE,'HH24') BETWEEN '00' AND '24'
AND A.SDATE >= to_date('25/11/2013','DD/MM/YYYY')
AND A.SDATE < to_date('26/11/2013','DD/MM/YYYY')
)
) where BHERL=ERLANG
)
Thanks
While there may be more efficient ways to write the query, there is nothing syntactically wrong with having a complete sql statement within an IN construct.
However, are you using Microsoft SQL Server, or anther RDBMS? TO_CHAR is not a T-SQL function. |
 |
|
|
|
|
|
|
|
using complete sql statement in in() function