INNER JOIN ROW_NUMBER OVER - SQL Server Forums

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

General SQL Server Forums

New to SQL Server Programming

INNER JOIN ROW_NUMBER OVER

Author

Topic

Marcos Cruz
Starting Member

8 Posts

Posted - 2010-01-26 : 05:47:48

Hi everybody,

I´m new with SQL Server Programming, and I was building a stored procedure to return a results of one table based on the results of the condition of another table.

When a run this query I get a message SqlException was caught Incorrect syntax near 'EmpresaPesquisas'.

I need produce a stored procedure that return a results pagened.

SELECT * FROM (
SELECT ROW_NUMBER() OVER(ORDER BY EmpresaPesquisas.PesquisaId DESC) As row
EmpresaPesquisas.PesquisaId, EmpresaPesquisas.EmpresaId,
EmpresaPesquisas.TituloPesquisa, EmpresaPesquisas.MoedaIdPesquisa,
EmpresaPesquisas.CambioPesquisa, EmpresaPesquisas.DataCambioPesquisa,
EmpresaPesquisas.AmostraPesquisa, EmpresaPesquisas.CargosPesquisa,
EmpresaPesquisas.PesquisadorPesquisa, EmpresaPesquisas.DataCadastroPesquisa,
EmpresaPesquisas.StatusPesquisa
FROM
EmpresaPesquisas INNER JOIN Empresas ON EmpresaPesquisas.EmpresaId = Empresas.EmpresaId
WHERE
(Empresas.RazaoSocialEmp LIKE '%teste%')) AS tbl
WHERE
row > CONVERT(varchar(9), @firstRecord) AND
row < CONVERT(varchar(9), @lastRecord)

Kristen
Test

22859 Posts

Posted - 2010-01-26 : 05:50:34

Missing comma:


SELECT * FROM (
SELECT ROW_NUMBER() OVER(ORDER BY EmpresaPesquisas.PesquisaId DESC) As row,
EmpresaPesquisas.PesquisaId, EmpresaPesquisas.EmpresaId,

DonAtWork
Master Smack Fu Yak Hacker

2167 Posts

Posted - 2010-01-26 : 07:24:37

This is why i put all my commas at the FRONT. Very easy to spot a missed one that way.
(If English is your first language, left to right reading and all that)

http://weblogs.sqlteam.com/jeffs/archive/2008/05/13/question-needed-not-answer.aspx
How to ask: http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx
For ultra basic questions, follow these links.
http://www.sql-tutorial.net/
http://www.firstsql.com/tutor.htm
http://www.w3schools.com/sql/default.asp

Kristen
Test

22859 Posts

Posted - 2010-01-26 : 07:42:28

Yeah, instead of


Long.............Expression............Stuff AS [MyAlias]

I do


[MyAlias] = Long.............Expression............Stuff

for the same reason. Also


FROM MyTable1 AS T1
    JOIN MyTable2 AS T2
        ON T2.MyPK1 = T1.SomeColumn1
       AND T2.MyPK2 = T1.SomeColumn2

i.e. express T2 columns first, to ensure that I have considered all the columns for the JOINed table, rather than the columns for the "Parent" table

I always have AND / OR at the start of the line - I don't want them hangling on the end of the line, scrolled off the right-of-screen, such that I miss spotting that I have done AND and OR without parenthesis

And long lines of concatenation etc. I start continuation lines with the operator, so instead of this:


SELECT @foo = long............line.........of...........stuff +
       More.......Stuff

I do


SELECT @foo = long............line.........of...........stuff
     + More.......Stuff

Marcos Cruz
Starting Member

8 Posts

Posted - 2010-01-26 : 14:43:27

Oh My, what a stupid error!

I lost all my night making experiencies, and the error was smalling to me!

Thanks GOD

Thanks Kristen and DonAtWork

English is not my mother language, sorry!

Again thanks

By that

quote:

Originally posted by Kristen

Missing comma:


SELECT * FROM (
SELECT ROW_NUMBER() OVER(ORDER BY EmpresaPesquisas.PesquisaId DESC) As row,
EmpresaPesquisas.PesquisaId, EmpresaPesquisas.EmpresaId,

Kristen
Test

22859 Posts

Posted - 2010-01-26 : 14:47:46

"Oh My, what a stupid error!"

Yeah, well, years of experience tells me where to look. With only "days" of experience it takes you years to find the problem!!

Glad I could help

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2010-01-26 : 14:52:38

quote:
Originally posted by Kristen

Yeah, instead of

Long.............Expression............Stuff AS [MyAlias]

I do

[MyAlias] = Long.............Expression............Stuff

FYI, from BOL:

quote:
The AS clause is the syntax defined in the SQL-92 standard for assigning a name to a result set column. This is the preferred syntax to use in Microsoft SQL Server 2005.

It may not matter to some (or even most), but I think coding as close to ANSI standards is a best practice.

Kristen
Test

22859 Posts

Posted - 2010-01-26 : 16:21:48

"I think coding as close to ANSI standards is a best practice"

For myself it makes no odds, I'm not planning to migrate to any other SQL

The benefit of spotting an issue, that I might otherwise miss and would cost valuable debugging time, is a higher priority for me.

But I'm sure your point will be valid to others.

I wish the "AS" was compulsory ... SELECT Col1 Col2 is missing an comma and interpreted wrongly, whereas SELECT Col1 AS MyAlias1 is a different thing ...

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2010-01-26 : 16:49:34

quote:
Originally posted by Kristen

I wish the "AS" was compulsory ... SELECT Col1 Col2 is missing an comma and interpreted wrongly, whereas SELECT Col1 AS MyAlias1 is a different thing ...

I'll second that! :)

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-01-27 : 01:45:45

quote:
Originally posted by Kristen

"I think coding as close to ANSI standards is a best practice"

For myself it makes no odds, I'm not planning to migrate to any other SQL

The benefit of spotting an issue, that I might otherwise miss and would cost valuable debugging time, is a higher priority for me.

But I'm sure your point will be valid to others.

I wish the "AS" was compulsory ... SELECT Col1 Col2 is missing an comma and interpreted wrongly, whereas SELECT Col1 AS MyAlias1 is a different thing ...

You may need to read this too
http://sqlblogcasts.com/blogs/madhivanan/archive/2008/09/09/should-alias-names-be-preceded-by-as-part-2.aspx

Madhivanan

Failing to plan is Planning to fail

Kristen
Test

22859 Posts

Posted - 2010-01-27 : 03:26:26

I've said it before ... I think SQL should have a "STRICT" mode that complains or fails if such things are "loose" in the code. Or a LINT that will detect, and warn, of such things - including implicit cast, absence of ORDER BY on a Select, and so on. (LINT in other languages has the ability to disable messages globally, or for sections of code, so where an intention is deliberate LINT could be made silent on the matter.

Sadly I don't have the YACC skills to build such a thing ...

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-01-27 : 03:42:08

quote:
Originally posted by Kristen

I've said it before ... I think SQL should have a "STRICT" mode that complains or fails if such things are "loose" in the code. Or a LINT that will detect, and warn, of such things - including implicit cast, absence of ORDER BY on a Select, and so on. (LINT in other languages has the ability to disable messages globally, or for sections of code, so where an intention is deliberate LINT could be made silent on the matter.

Sadly I don't have the YACC skills to build such a thing ...

Yes. A spelling mistake can lead to different result

select cols from table1
UNOIN
select cols from table2

Madhivanan

Failing to plan is Planning to fail

Kristen
Test

22859 Posts

Posted - 2010-01-27 : 04:32:27

Hahaha ... not come across that one before. Classic!

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-01-27 : 06:17:21

quote:
Originally posted by Kristen

Hahaha ... not come across that one before. Classic!

Proper use of AS before alias name would not do that

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources