using complete sql statement in in() function

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.

All Forums

Other Forums

Other Topics

using complete sql statement in in() function

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.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources