Why it doesnt work??

Question

Post reply

Why it doesnt work??

igngua

SSCrazy

Points: 2510
More actions
January 21, 2011 at 8:50 am

#232525

Hi! I can´t understand why it doesnt work!.
select count (codaux) from
(select codaux,nomaux from aqua.softland.cwtauxi)
except
(select codaux,nomaux from 0718.softland.cwtauxi)
i get:
'Incorrect syntax near the keyword 'except'.'
thanks in advance.

Viewing 10 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply

Cadavre SSC-Forever Points: 41690 More actions · Answer 1

select count (codaux) from

(select codaux,nomaux from aqua.softland.cwtauxi

except

select codaux,nomaux from 0718.softland.cwtauxi)

Do you understand?

Forever trying to learn
My blog - http://www.cadavre.co.uk/
For better, quicker answers on T-SQL questions, click on the following...http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better, quicker answers on SQL Server performance related questions, click on the following...http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

igngua SSCrazy Points: 2510 More actions · Answer 2

skcadavre (1/21/2011)
select count (codaux) from (select codaux,nomaux from aqua.softland.cwtauxi except
select codaux,nomaux from 0718.softland.cwtauxi)
Do you understand?

hi!.

Since i am getting a sintax error i thought that no other info was needed.

But if it´s needed i will provide it.

I have to tables in diferent databases.

The tables are identical to each other.

The tables are:

- aqua.softland.cwtauxi

- 0718.softland.cwtauxi

I need to retrieve the resulting amount of rows from the query;

(select codaux,nomaux from aqua.softland.cwtauxi

except

select codaux,nomaux from 0718.softland.cwtauxi)

The above query does exactly what i need.

So then i started looking information to acomplish what i want.

And i found this:

http://forums.devshed.com/firebird-sql-development-61/how-to-count-the-rows-of-a-query-result-382290.html

And got:

select count (codaux) from

(select codaux,nomaux from aqua.softland.cwtauxi

except

select codaux,nomaux from 0718.softland.cwtauxi)

But it doesnt work.

Maybe i got wrong the info.

thanks ind advance.

---------------

I got the info wrong the above query is for firebird.

My bad.

Johan Bijnens SSC Guru Points: 134873 More actions · Answer 3

Just assign an alias to your nested query expression and it will be OK

select count (*) from

(select codaux,nomaux from aqua.softland.cwtauxi

except

select codaux,nomaux from 0718.softland.cwtauxi) as x

Johan

Learn to play, play to learn !

Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:

- How to post Performance Problems
- How to post data/code to get the best help[/url]

- How to prevent a sore throat after hours of presenting ppt

press F1 for solution, press shift+F1 for urgent solution 😀

Need a bit of Powershell? How about this

Who am I ? Sometimes this is me but most of the time this is me

igngua SSCrazy Points: 2510 More actions · Answer 4

ALZDBA (1/21/2011)
Just assign an alias to your nested query expression and it will be OK
select count (*) from
(select codaux,nomaux from aqua.softland.cwtauxi
except
select codaux,nomaux from 0718.softland.cwtauxi) as x

thanks it worked.!

Now,..can you explain why do you hace to use 'as'?

LutzM SSC Guru Points: 107049 More actions · Answer 5

igngua (1/21/2011)
ALZDBA (1/21/2011)
Just assign an alias to your nested query expression and it will be OK
select count (*) from
(select codaux,nomaux from aqua.softland.cwtauxi
except
select codaux,nomaux from 0718.softland.cwtauxi) as x
thanks it worked.!
Now,..can you explain why do you hace to use 'as'?

The keyword 'as' is optional. You can assign the alias without 'as'. Some would consider using it being more descriptive, others don't. I think the most important part is to be consistent within the code.

Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question[/url]
How to post performance related questions[/url]
Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

Johan Bijnens SSC Guru Points: 134873 More actions · Answer 6

Lutz covered it perfectly :w00t:

Further info can be found in books online topic "Using Table Aliases"

Johan

Learn to play, play to learn !

Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:

- How to post Performance Problems
- How to post data/code to get the best help[/url]

- How to prevent a sore throat after hours of presenting ppt

press F1 for solution, press shift+F1 for urgent solution 😀

Need a bit of Powershell? How about this

Who am I ? Sometimes this is me but most of the time this is me

homebrew01 SSC Guru Points: 55454 More actions · Answer 7

homebrew01

SSC Guru

Points: 55454

January 24, 2011 at 9:35 am

#1277300

Perhaps the OP meant why an alias is required ?

Johan Bijnens SSC Guru Points: 134873 More actions · Answer 8

books online topic "FROM (Transact-SQL) " states

[ FROM { <table_source> } [ ,...n ] ]
<table_source> ::=
{
table_or_view_name [ [ AS ] table_alias ] [ <tablesample_clause> ]
[ WITH ( < table_hint > [ [ , ]...n ] ) ]
| rowset_function [ [ AS ] table_alias ]
[ ( bulk_column_alias [ ,...n ] ) ]
| user_defined_function [ [ AS ] table_alias ] [ (column_alias [ ,...n ] ) ]
| OPENXML <openxml_clause>
| derived_table [ AS ] table_alias [ ( column_alias [ ,...n ] ) ]
| <joined_table>
| <pivoted_table>
| <unpivoted_table>
| @variable [ [ AS ] table_alias ]
| @variable.function_call ( expression [ ,...n ] ) [ [ AS ] table_alias ] [ (column_alias [ ,...n ] ) ]
}