Problem with SELECT

[Ted]

I constructed the following SQL statement by studying the example on
page 392 in Itzik Ben Gan et al.'s "Inside Microsoft SQL Server 2005:
T-SQL Querying"

SELECT s_supplier_code , s_supplier_name FROM suppliers AS S1
WHERE s_supplier_code =
(
SELECT TOP(1) * FROM suppliers AS S2
WHERE S2.s_supplier_c ode = S1.s_supplier_c ode
ORDER BY s_supplier_name
)
;

However, it generates the following error.

Msg 116, Level 16, State 1, Line 4
Only one expression can be specified in the select list when the
subquery is not introduced with EXISTS.

This isn't a showstopper since the following statement does much the
same thing and it works (even though it is a little less flexible).

SELECT s_supplier_code ,MAX(s_supplier _name) FROM suppliers GROUP BY
s_supplier_code ;

My code can continue on, but I want to understand why the statement I
constructed by following the example in my book was rejected. Did I
miss something? Or is there an error in the book? Or is there a bug
in SQL Server 2005?

Thanks

Ted

[--CELKO--]

Re: Problem with SELECT

>My code can continue on, but I want to understand why the statement I constructed by following the example in my book was rejected. Did I miss something? Or is there an error in the book? Or is there a bug in SQL Server 2005? <<

This is just how God punishes people for using proprietary code :)
Since this is MS-only
syntax, they can do anything they wish from release to release. Ergo,
you are screwed and live at their whim without recourse to a
higherauthority .

Drop an email to the publisher, so they can fix it in the book and use
the Standard. MAX() syntax you provided. Since it is Standard SQL, MS
has to keep it the same from release to release.

[Ted]

Re: Problem with SELECT


--CELKO-- wrote:

My code can continue on, but I want to understand why the statement I constructed by following the example in my book was rejected. Did I miss something? Or is there an error in the book? Or is there a bug in SQL Server 2005? <<

>
This is just how God punishes people for using proprietary code :)
Since this is MS-only
syntax, they can do anything they wish from release to release. Ergo,
you are screwed and live at their whim without recourse to a
higherauthority .
>
Drop an email to the publisher, so they can fix it in the book and use
the Standard. MAX() syntax you provided. Since it is Standard SQL, MS
has to keep it the same from release to release.

I think I feel a headache coming on!

The publisher is Microsoft Press. It is for SQL Server 2005 and I AM
RUNNING SQL Server 2005! You'd think they'd run sample code from their
own book against their own SQL Server.

Cheers,

Ted

Someone pass me the super strength Motrin! :-(

[boa]

Re: Problem with SELECT

* Ted wrote, On 21.08.2006 17:28:

I constructed the following SQL statement by studying the example on
page 392 in Itzik Ben Gan et al.'s "Inside Microsoft SQL Server 2005:
T-SQL Querying"
>
SELECT s_supplier_code , s_supplier_name FROM suppliers AS S1
WHERE s_supplier_code =
(
SELECT TOP(1) * FROM suppliers AS S2
WHERE S2.s_supplier_c ode = S1.s_supplier_c ode
ORDER BY s_supplier_name
)
;
>
However, it generates the following error.
>
Msg 116, Level 16, State 1, Line 4
Only one expression can be specified in the select list when the
subquery is not introduced with EXISTS.

What happens if you replace
select top(1) *
with this?
select top(1) some_column_nam e



Boa
[snip]

[Alexander Kuznetsov]

Re: Problem with SELECT


Ted wrote:

I constructed the following SQL statement by studying the example on
page 392 in Itzik Ben Gan et al.'s "Inside Microsoft SQL Server 2005:
T-SQL Querying"
>
SELECT s_supplier_code , s_supplier_name FROM suppliers AS S1
WHERE s_supplier_code =
(
SELECT TOP(1) * FROM suppliers AS S2
WHERE S2.s_supplier_c ode = S1.s_supplier_c ode
ORDER BY s_supplier_name
)
;
>

Ted,

The example in Listing 7-1 is different from your query, and the
example works (and it makes perfect sense to me). I am not sure I
understand what you were trying to accomplish. Note that:
1. Listing 7-1 uses SELECT TOP(1) OrderId, as opposed to your top(1) *
2. Listing 7-1 uses different columns to filter the result set
(OrderId) and to correlate the subquery (EmployeeId). Using one and the
same solumn s_supplier_code in both cases makes little sense to me.

Good luck!

[Ted]

Re: Problem with SELECT


boa wrote:

* Ted wrote, On 21.08.2006 17:28:

I constructed the following SQL statement by studying the example on
page 392 in Itzik Ben Gan et al.'s "Inside Microsoft SQL Server 2005:
T-SQL Querying"

SELECT s_supplier_code , s_supplier_name FROM suppliers AS S1
WHERE s_supplier_code =
(
SELECT TOP(1) * FROM suppliers AS S2
WHERE S2.s_supplier_c ode = S1.s_supplier_c ode
ORDER BY s_supplier_name
)
;

However, it generates the following error.

Msg 116, Level 16, State 1, Line 4
Only one expression can be specified in the select list when the
subquery is not introduced with EXISTS.

>
>
What happens if you replace
select top(1) *
with this?
select top(1) some_column_nam e
>

Nothing changes. The same error is generated.

Thanks

Ted

[Alexander Kuznetsov]

Re: Problem with SELECT

Since this is MS-only

syntax, they can do anything they wish from release to release. Ergo,
you are screwed and live at their whim without recourse to a
higherauthority .
>
Drop an email to the publisher, so they can fix it in the book and use
the Standard. MAX() syntax you provided. Since it is Standard SQL, MS
has to keep it the same from release to release.

Joe,

Oracle and DB2 also provide proprietary features for this purpose, and
those features are also widely used. Looks like something is missing in
the standard, right?

[Tony Rogerson]

Re: Problem with SELECT

You have incorrectly reproduced the query from Itziks book (just checked
myself)

Check back again, you will see that you have put * (all columns) you should
have used a single column key in Itziks example it was OrderId.

SELECT s_supplier_code , s_supplier_name
FROM suppliers AS S1
WHERE s_supplier_code =
(
SELECT TOP(1) OrderId FROM suppliers AS S2
WHERE S2.s_supplier_c ode = S1.s_supplier_c ode
ORDER BY s_supplier_name
)

Tony.

--
Tony Rogerson
SQL Server MVP
http://sqlblogcasts.com/blogs/tonyrogerson - technical commentary from a SQL
Server Consultant
http://sqlserverfaq.com - free video tutorials


"Ted" <r.ted.byers@ro gers.comwrote in message
news:1156177703 .807874.29310@b 28g2000cwb.goog legroups.com...

>I constructed the following SQL statement by studying the example on
page 392 in Itzik Ben Gan et al.'s "Inside Microsoft SQL Server 2005:
T-SQL Querying"
>
SELECT s_supplier_code , s_supplier_name FROM suppliers AS S1
WHERE s_supplier_code =
(
SELECT TOP(1) * FROM suppliers AS S2
WHERE S2.s_supplier_c ode = S1.s_supplier_c ode
ORDER BY s_supplier_name
)
;
>
However, it generates the following error.
>
Msg 116, Level 16, State 1, Line 4
Only one expression can be specified in the select list when the
subquery is not introduced with EXISTS.
>
This isn't a showstopper since the following statement does much the
same thing and it works (even though it is a little less flexible).
>
SELECT s_supplier_code ,MAX(s_supplier _name) FROM suppliers GROUP BY
s_supplier_code ;
>
My code can continue on, but I want to understand why the statement I
constructed by following the example in my book was rejected. Did I
miss something? Or is there an error in the book? Or is there a bug
in SQL Server 2005?
>
Thanks
>
Ted
>

[Tony Rogerson]

Re: Problem with SELECT

This is just how God punishes people for using proprietary code :)

Since this is MS-only
syntax, they can do anything they wish from release to release. Ergo,
you are screwed and live at their whim without recourse to a
higherauthority .
>

As ever you are talking utter rubbish.

It takes 2 or 3 or sometimes more releases to deprecate and then discontinue
a feature, actually - thats better than the standard SQL cycle where you
only get 4 year was it celko? And you don't get any warning there either
unless you subscribe and pay the fees.....

Drop an email to the publisher, so they can fix it in the book and use
the Standard. MAX() syntax you provided. Since it is Standard SQL, MS
has to keep it the same from release to release.
>

So, just how would you using standard SQL do the equivelant of this without
using a 'scratch tape' or relying on the application to dig you out of the
featureless ansi sql standard?

-- Get last 3 most recent sales...
select ...
from sales
where orderid in (
select top 3 orderid
from sales
order by orderdate desc
)

--
Tony Rogerson
SQL Server MVP
http://sqlblogcasts.com/blogs/tonyrogerson - technical commentary from a SQL
Server Consultant
http://sqlserverfaq.com - free video tutorials


"--CELKO--" <jcelko212@eart hlink.netwrote in message
news:1156180035 .849964.264800@ b28g2000cwb.goo glegroups.com.. .

>>My code can continue on, but I want to understand why the statement I
>>constructed by following the example in my book was rejected. Did I
>>miss something? Or is there an error in the book? Or is there a bug in
>>SQL Server 2005? <<

>
This is just how God punishes people for using proprietary code :)
Since this is MS-only
syntax, they can do anything they wish from release to release. Ergo,
you are screwed and live at their whim without recourse to a
higherauthority .
>
Drop an email to the publisher, so they can fix it in the book and use
the Standard. MAX() syntax you provided. Since it is Standard SQL, MS
has to keep it the same from release to release.
>

[Ted]

Re: Problem with SELECT

Thanks Tony and Alexander

Thanks to your posts, I now understand what I missed and I now have it
working.

Thanks

Ted