Using subqueries in sql functions

Question

I want to ask whether we can use subqueries inside a sql function (consider both group and single functions). I've been trying out this simple query -

select count(select empno from emp where sal<3000) from emp;

It's giving error "missing expression". This is just a simple example.

I just want to know if this query logic is wrong or if it is not allowed in sql and if we can use subqueries inside sql function please care to mention an example.

Thanks.

Matt Ball · Accepted Answer · 2013-02-24 05:50:55Z

1

Your query simply does not make sense. Why wouldn't you just do this?

select count(empno) from emp where sal<3000;

answered Feb 24, 2013 at 5:50

Matt Ball

361k102 gold badges655 silver badges725 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Joe Over a year ago

While the query is silly, I think he's trying to ask if the concept of select count(subquery) is possible.

Anu Over a year ago

I know that I can use this query rather than that one. But I used this example to make people understand what my question is about. In nutshell, I want to know whether we can use subqueries inside functions or not.

Joe · Accepted Answer · 2013-02-24 05:57:21Z

0

I think it is possible. See http://www.akadia.com/services/sqlsrv_subqueries.html for an example (find "DATEDIFF" on the page to see it). However, I'm not sure it is possible in all flavors of SQL; for example, I use SAS, which has its own fairly bare-bones SQL implementation, and it does not seem to allow it.

answered Feb 24, 2013 at 5:57

Joe

63.5k7 gold badges51 silver badges70 bronze badges

Comments

marc_s · Accepted Answer · 2017-01-25 17:12:49Z

0

Even if you are able to use a subquery it will have to essentially return an expression or column name which can be evaluated and counted by looping through the records. Hence you should try to look at using a subquery result as a column in your query or any other combination of joins to do your job.

edited Jan 25, 2017 at 17:12

marc_s

760k186 gold badges1.4k silver badges1.5k bronze badges

answered Feb 24, 2013 at 6:44

sfq

1

1 Comment

evil otto Over a year ago

This is the right answer, but it's confusingly stated. A subquery returns a result set - essentially another relation. In general, functions take values or columns, not relations. If you need to use a subquery, use it as a relation and apply your function to the proper parts of that relation.

Collectives™ on Stack Overflow

Using subqueries in sql functions

3 Answers 3

2 Comments

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

2 Comments

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related