Kenneth Wilhelmsson (10/2/2008)

---

This is really another subject, but I feel I have to disagree anyway.

What you're essentially proposing here, is that the model doesn't count.

Whenever you're using 'select *' instead...

I do not have time to look at your code, but based on your latest explaination you could try something like:

DECLARE @TermYear char(4)

&nbsp&nbsp&nbsp&nbsp,@Term varchar(6)

SELECT @TermYear = '2008'

&nbsp&nbsp&nbsp&nbsp,@Term = 'Fall'

UPDATE academic

SET...

What you are attempting is far from clear. Maybe something like this:

DECLARE @YearStart char(4)

&nbsp&nbsp&nbsp&nbsp,@TermStart varchar(6)

&nbsp&nbsp&nbsp&nbsp,@YearEnd char(4)

&nbsp&nbsp&nbsp&nbsp,@TermEnd varchar(6)

SELECT @YearStart = '2006'

&nbsp&nbsp&nbsp&nbsp,@TermStart = 'Fall'

&nbsp&nbsp&nbsp&nbsp,@YearEnd = '2008'

&nbsp&nbsp&nbsp&nbsp,@TermEnd = 'Fall'

UPDATE academic

SET status = 'N'

WHERE...

This may help you.

-- *** Test Data **

DECLARE @t TABLE

(

&nbsp&nbsp&nbsp&nbspCol1 int NOT NULL

&nbsp&nbsp&nbsp&nbsp,Image1 varchar(10) NULL

&nbsp&nbsp&nbsp&nbsp,Image2 varchar(10) NULL

&nbsp&nbsp&nbsp&nbsp,Image3 varchar(10) NULL

&nbsp&nbsp&nbsp&nbsp,Image4 varchar(10) NULL

&nbsp&nbsp&nbsp&nbsp,Image5 varchar(10) NULL

&nbsp&nbsp&nbsp&nbsp,Image6 varchar(10) NULL

&nbsp&nbsp&nbsp&nbsp,Image7 varchar(10) NULL

&nbsp&nbsp&nbsp&nbsp,Image8 varchar(10) NULL

&nbsp&nbsp&nbsp&nbsp,Image9 varchar(10)...

I am not sure what you are trying to do, but the following may help.

SELECT C.Customer_ID, C.[Name], C.Country, R.Region

FROM Countries C

&nbsp&nbsp&nbsp&nbspJOIN

&nbsp&nbsp&nbsp&nbsp(

&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbspSELECT 1, 'East' UNION ALL

&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbspSELECT 2, 'West' UNION ALL

&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbspSELECT 3,...

Add the INSERT INTO @cte which I forgot.

SET ANSI_NULLS, QUOTED_IDENTIFIER ON

GO

CREATE FUNCTION dbo.GetLocationDescription

(

@location_id int

)

RETURNS varchar(8000)

AS

BEGIN

&nbsp&nbsp&nbsp&nbspDECLARE @result varchar(8000)

&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp,@Level int

&nbsp&nbsp&nbsp&nbspSELECT @result = ''

&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp,@Level = 1

&nbsp&nbsp&nbsp&nbspDECLARE @cte TABLE

&nbsp&nbsp&nbsp&nbsp(

&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbspHLevel int NOT NULL

&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp,Parent int NOT NULL

&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp,[Description] varchar(255) NOT...

SET ANSI_NULLS, QUOTED_IDENTIFIER ON

GO

CREATE FUNCTION dbo.GetLocationDescription

(

&nbsp&nbsp&nbsp&nbsp@location_id int

)

RETURNS varchar(8000)

AS

BEGIN

&nbsp&nbsp&nbsp&nbspDECLARE @result varchar(8000)

&nbsp&nbsp&nbsp&nbspSET @result = ''

&nbsp&nbsp WITH cte (HLevel, Parent, [Description])

&nbsp&nbsp&nbsp&nbspAS

&nbsp&nbsp&nbsp&nbsp(

&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbspSELECT 1 AS HLevel, T1.Parent, T1.[Description]

&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbspFROM dbo.Location T1

&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbspWHERE T1.location_id = @location_id

&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbspUNION ALL

&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbspSELECT C2.HLevel +...

This should get you started:

-- *** Test Data ***

CREATE TABLE #t

(

&nbsp&nbsp&nbsp&nbspInvNO int NOT NULL

&nbsp&nbsp&nbsp&nbsp,AccNo int NOT NULL

&nbsp&nbsp&nbsp&nbsp,TR_Age int NOT NULL

&nbsp&nbsp&nbsp&nbsp,Pay_Age int NOT NULL

&nbsp&nbsp&nbsp&nbsp,Amt int NOT NULL

&nbsp&nbsp&nbsp&nbsp,Pay_Amt int NOT NULL

&nbsp&nbsp&nbsp&nbsp,Run_Bal int...

It depends on the order of the tables. I prefer to think of the * being on the side where all the rows are returned.

--eg

SELECT *

FROM TableA A

&nbsp&nbsp&nbsp&nbspLEFT JOIN TableB...

The 'N' just means a nchar constant as opposed to a char constant.

You will need to cast to a float first.

SELECT CAST(CAST(N'2.00701E+16' AS float) AS bigint)

Your outer joins are effectively being converted to inner joins by the subsequent inner joins.

The joins should be nested so that the FROM clause looks something like:

FROM

&nbsp&nbsp&nbsp&nbspcopay...

DELETE D

FROM

(

&nbsp&nbsp&nbsp&nbspSELECT

&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbspROW_NUMBER() OVER (PARTITION BY RefNoColumn, RowNoColumn ORDER BY RefNoColumn) AS RowID

&nbsp&nbsp&nbsp&nbspFROM YourTable

) D

WHERE RowID > 1

I use the following code to do some rough validation when importing email addresses.

SELECT EMail

FROM

(

&nbsp&nbsp&nbsp&nbspSELECT

&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbspEMail

&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp,CHARINDEX('.', REVERSE(EMail)) AS DotPos

&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp,CHARINDEX('@', REVERSE(EMail)) AS AtPos

&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp,CHARINDEX('@', EMail) AS AtPosStart

&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp,REVERSE(LEFT(REVERSE(EMail), CHARINDEX('@', REVERSE(EMail)) - 1)) AS DomainName

&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp,REVERSE(SUBSTRING(REVERSE(EMail),...