Art

That's why I said to search this site - there are some ingenious ways of generating the numbers/dates if you look for them!

John

I've just seen another question answered something like this:

select TOP 100 * from table1 where

...

Karthik

First off, if @abc is varchar, then -1 isn't a valid value, although '-1' is.

Second, in the code snippet you posted, you were only using @abc as a means to...

As I said, if you can't change your function to a stored procedure then you're going to have to change the design of your database. Perhaps you could create...

Art

The best way is to have a separate "numbers table" or "tally table", and to join to that. They have a multitude of uses and usually come out on...

...

LEFT OUTER JOIN sys.types types

ON ...

Karthik

What is the data type of @abc?

John

The first part of your WHERE clause is effectively turning your second OUTER JOIN into an INNER JOIN. Try putting this condition as a join predicate instead.

John

OK, well you will have to replace the "..." in the snippet Gail gave to get you started with some actual code. Don't forget ROW_NUMBER always has a "()"...

You could combine the two, something like this:

SELECT

FROM Address a JOIN Publication p

ON LEFT(a.Addr_PostCode, CHARINDEX(' ',a.Addr_Postcode)-1)

= LEFT(p.Pub_PostCode, CHARINDEX(' ',p.Pub_Postcode)-1)

WHERE p.Pub_PublicationID =

John

OK, now please show us what you expect the query to return, and what you've tried so far.

John

Ganesh

Please supply table DDL in the form of a CREATE TABLE statement, sample data in the form of INSERT statements, and the result set you expect to see. If...

Example 1 fails because you can't have a variable setting the database name. Example 2 fails because the dynamic SQL that you use executes in a different context from...

First of all, you need already to have a full backup and a differential backup of your database. If you do, then restore from the full backup with no...

You could try something like this:

DECLARE @tv table (reviewed char(1))

IF @reviewed IN ('P','B','N') INSERT INTO @tv VALUES ('P')

IF @reviewed IN ('F','B','N') INSERT INTO @tv VALUES ('F')

IF @reviewed = 'N' INSERT...