Question about Neural Networks

I created a test table (name - "Nset") with the columns:
id (int), n1 (float), n2 (float), n3 (float) and c1 (varchar).
Then filled a table the followings information:
id n1 n2 n3 c1

1 0,1 0,1 0,6 one

2 0,2 0,1 0,5 one

3 0,7 0,5 0,1 two

4 0,4 0,9 0,3 two

5 0,5 0,1 0,5 three

And created a neural network with tuning by default. "id"-field is the key. n1, n2 and n3 are inputs. c1 - predict.

Then i tryed predict query, like:

SELECT

PREDICT([Nset].[c1])

FROM

[Nset]

NATURAL PREDICTION JOIN

(SELECT 0,5 AS [n1], 0,1 AS [n2], 0,5 AS [n3]) AS t

The result is "three". This is correct. And some other tests appeared correct.

But, when I filled the column c1 with numerical values (one = 1, two=2, three=3) and changed type to int, a predict query left off to work correctly.

Previous query return 4.

And other tests showed that a value returned large on unit.

Is this correct?

Thanks.

Hello

It can be explained. The algorithm actually works correctly, the models are different.

Here is what happens:

first case: "one", "two", "three" are strings, so the mining structure / model creation wizard decides that they should be treated as discrete. The net is trained to fit particular states of a distinct attribute (c1). State "three" is only described by one training point (the last row) so the weights of the network are optimized (actually, over-trained) to predict "three" for the combination of inputs that appears in the last row. This is why the prediction result is "three"

second case: 1, 2 and 3 are this time numbers. The wizard decides that, as numeric values, they should be treated as Continuous. You can change this behavior (the wizard only makes a suggestion) in one of the wizard pages. As the target is now a continuous variable, the network is not optimized independently for the distinct state '3'. It actually is optimized for the whole surface defined by the training points.

You can fix it immediately by changing, in the wizard, the content type for the target column, c1, from Continuous to Discrete.

The differences between results should reduce as more training cases are presented to the network

Hope this clarifies the issue

|||Thanks! Now working properly!|||

And how it is better to work with variable neurons number?

It is similar to the previous case, but the table in a following kind:

case_id

input_name

input_weight

output_val

1

n1

0,1

1

1

n2

0,1

1

1

n3

0,6

1

2

n1

0,2

1

2

n2

0,1

1

2

n3

0,5

1

3

n1

0,7

2

3

n2

0,5

2

3

n3

0,1

2

4

n1

0,4

2

4

n2

0,9

2

4

n3

0,3

2

5

n1

0,5

3

5

n2

0,1

3

5

n3

0,5

3

I can't use PIVOT of SQL Server, because i don't know neurons count...

I suppose, that i need to build the cube. And after that to build Neural Network model.

It is correct? There Is any other way?

|||

You could model like below:

case_id n1 n2 n3 output

1 0.1 0.1 0.6 1

2 ...

Alternatively, you could use the nested table feature and create a model like below. This would work if you do not know the number of inputs before modeling

(
Case_ID long key,
Output long continuous predict,
inputs Table
(
neuron text key,
weight double continuous
)
)

Assuming that you have a data source view containing the table above, you can train the mining structure/model self-joining the table with itself (using Case_ID as Key and then as Foreign Key).

In the wizard, you can specify the same table as both Case and Nested as long as you define a one-to-many reflexive relationship from case_id to case_id.

|||

Thank you for help again!

All works except for a Neural Network Viewer.

The NN Viewer gives out a message:

"Execution of the managed stored procedure GetAttributeScores failed with the following error: Exception has been thrown by the target of an invocation.Input string was not in a correct format..."

But it is not important. Prediction query works correct.

Once again thanks!

|||

Glad to hear it works

> All works except for a Neural Network Viewer

Some viewers problems were fixed in SP2 of SQL Server 2005, which should be available publicly soon

Question about Neural Networks

I created a test table (name - "Nset") with the columns:
id (int), n1 (float), n2 (float), n3 (float) and c1 (varchar).
Then filled a table the followings information:
id n1 n2 n3 c1

1 0,1 0,1 0,6 one

2 0,2 0,1 0,5 one

3 0,7 0,5 0,1 two

4 0,4 0,9 0,3 two

5 0,5 0,1 0,5 three

And created a neural network with tuning by default. "id"-field is the key. n1, n2 and n3 are inputs. c1 - predict.

Then i tryed predict query, like:

SELECT

PREDICT([Nset].[c1])

FROM

[Nset]

NATURAL PREDICTION JOIN

(SELECT 0,5 AS [n1], 0,1 AS [n2], 0,5 AS [n3]) AS t

The result is "three". This is correct. And some other tests appeared correct.

But, when I filled the column c1 with numerical values (one = 1, two=2, three=3) and changed type to int, a predict query left off to work correctly.

Previous query return 4.

And other tests showed that a value returned large on unit.

Is this correct?

Thanks.

Hello

It can be explained. The algorithm actually works correctly, the models are different.

Here is what happens:

first case: "one", "two", "three" are strings, so the mining structure / model creation wizard decides that they should be treated as discrete. The net is trained to fit particular states of a distinct attribute (c1). State "three" is only described by one training point (the last row) so the weights of the network are optimized (actually, over-trained) to predict "three" for the combination of inputs that appears in the last row. This is why the prediction result is "three"

second case: 1, 2 and 3 are this time numbers. The wizard decides that, as numeric values, they should be treated as Continuous. You can change this behavior (the wizard only makes a suggestion) in one of the wizard pages. As the target is now a continuous variable, the network is not optimized independently for the distinct state '3'. It actually is optimized for the whole surface defined by the training points.

You can fix it immediately by changing, in the wizard, the content type for the target column, c1, from Continuous to Discrete.

The differences between results should reduce as more training cases are presented to the network

Hope this clarifies the issue

|||Thanks! Now working properly!|||

And how it is better to work with variable neurons number?

It is similar to the previous case, but the table in a following kind:

case_id

input_name

input_weight

output_val

1

n1

0,1

1

1

n2

0,1

1

1

n3

0,6

1

2

n1

0,2

1

2

n2

0,1

1

2

n3

0,5

1

3

n1

0,7

2

3

n2

0,5

2

3

n3

0,1

2

4

n1

0,4

2

4

n2

0,9

2

4

n3

0,3

2

5

n1

0,5

3

5

n2

0,1

3

5

n3

0,5

3

I can't use PIVOT of SQL Server, because i don't know neurons count...

I suppose, that i need to build the cube. And after that to build Neural Network model.

It is correct? There Is any other way?

|||

You could model like below:

case_id n1 n2 n3 output

1 0.1 0.1 0.6 1

2 ...

Alternatively, you could use the nested table feature and create a model like below. This would work if you do not know the number of inputs before modeling

(
Case_ID long key,
Output long continuous predict,
inputs Table
(
neuron text key,
weight double continuous
)
)

Assuming that you have a data source view containing the table above, you can train the mining structure/model self-joining the table with itself (using Case_ID as Key and then as Foreign Key).

In the wizard, you can specify the same table as both Case and Nested as long as you define a one-to-many reflexive relationship from case_id to case_id.

|||

Thank you for help again!

All works except for a Neural Network Viewer.

The NN Viewer gives out a message:

"Execution of the managed stored procedure GetAttributeScores failed with the following error: Exception has been thrown by the target of an invocation.Input string was not in a correct format..."

But it is not important. Prediction query works correct.

Once again thanks!

|||

Glad to hear it works

> All works except for a Neural Network Viewer

Some viewers problems were fixed in SP2 of SQL Server 2005, which should be available publicly soon

question about inserting varchar info (lenght limits?)

question about inserting varchar info (lenght limits?)
Assume I have a table column that accepts a user_id (the column is say varch
ar 15)
What happens if I send in a variable of length 16?
Is the entered string truncated and accepted by the database? (Assuming no c
onstraints or anything were programmed)Depends on you setting for ANSI_WARNINGS. If on, you get an error message.
This is easy to test:
CREATE TABLE #a(c1 varchar(5))
SET ANSI_WARNINGS OFF
INSERT INTO #a(c1) VALUES('fit')
INSERT INTO #a(c1) VALUES('Doesn''t fit')
SELECT * FROM #a
TRUNCATE TABLE #a
SET ANSI_WARNINGS ON
INSERT INTO #a(c1) VALUES('fit')
INSERT INTO #a(c1) VALUES('Doesn''t fit')
SELECT * FROM #a
Tibor Karaszi, SQL Server MVP
Archive at:
http://groups.google.com/groups?oi=...ublic.sqlserver
"david_in_Groton" <anonymous@.discussions.microsoft.com> wrote in message
news:416C3CA9-64CC-4C55-823B-B4A758139F2E@.microsoft.com...

quote:

---

> question about inserting varchar info (lenght limits?)
> Assume I have a table column that accepts a user_id (the column is say

---

varchar 15)

quote:

---

> What happens if I send in a variable of length 16?
> Is the entered string truncated and accepted by the database? (Assuming no

---

constraints or anything were programmed)

question about inserting varchar info (lenght limits?)

question about inserting varchar info (lenght limits?)
Assume I have a table column that accepts a user_id (the column is say varchar 15)
What happens if I send in a variable of length 16?
Is the entered string truncated and accepted by the database? (Assuming no constraints or anything were programmed)Depends on you setting for ANSI_WARNINGS. If on, you get an error message.
This is easy to test:
CREATE TABLE #a(c1 varchar(5))
SET ANSI_WARNINGS OFF
INSERT INTO #a(c1) VALUES('fit')
INSERT INTO #a(c1) VALUES('Doesn''t fit')
SELECT * FROM #a
TRUNCATE TABLE #a
SET ANSI_WARNINGS ON
INSERT INTO #a(c1) VALUES('fit')
INSERT INTO #a(c1) VALUES('Doesn''t fit')
SELECT * FROM #a
Tibor Karaszi, SQL Server MVP
Archive at:
http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
"david_in_Groton" <anonymous@.discussions.microsoft.com> wrote in message
news:416C3CA9-64CC-4C55-823B-B4A758139F2E@.microsoft.com...
> question about inserting varchar info (lenght limits?)
> Assume I have a table column that accepts a user_id (the column is say
varchar 15)
> What happens if I send in a variable of length 16?
> Is the entered string truncated and accepted by the database? (Assuming no
constraints or anything were programmed)sql

Question about formatting a date

I have a date string that is an integer 20040119 and I converted it to a varchar and then to date format-CONVERT(datetime, CAST(my_date AS varchar(8))) AS my_date
My result is 2003-12-31 00:00:00.000. How can I now format this string so I don't get the time string at the end?
ThanksYou already did.|||Maybe I didn't explain good enough. I want to drop all the zeros at the end and just show the date and not the time.
Thanks|||Something like this:

select RIGHT(CONVERT(char(10), CONVERT(datetime, CAST(my_date AS varchar(8))),103),10) AS my_date|||select convert(char(10), convert(datetime, '20040119'), 101)|||Thanks alot Avatar. You left off a right parenthesis, but that was the least of my worries. It works just like I wanted. Thanks again.|||Avatar,
I don't suppose you could explain real quick how that works?
Thanks.|||Im glad its work for you..

Here is my explanation:

1) CAST(my_date AS varchar(8)):
Convert my_date from DATETIME to VARCHAR(8) because I'm asuming that the COLUMN is DATETIME.
2) CONVERT(datetime, CAST(my_date AS varchar(8))),103):
Transform the VARCHAR(8) from Step 1 to a DATETIME with a format 'dd/mm/yyyy' (103), to convert a datetime column.
3) CONVERT(char(10), CONVERT(datetime, CAST(my_date AS varchar8))),103) :
Now transform the DATETIME from Step 2 to a VARCHAR (once again) because the function RIGHT only works with VARCHARS.
4)RIGHT(CONVERT(char(10), CONVERT(datetime, CAST(my_date AS varchar8))),103),10):
Finally the RIGHT functions takes the first 10 characters of the string.

I hope I explain well. ;)|||I got everything up to the right function. I thought the right function returns the specified number of characters from the right end of the character string?
Thanks again for your help and patience!!!|||Originally posted by exdter
I got everything up to the right function. I thought the right function returns the specified number of characters from the right end of the character string?
Thanks again for your help and patience!!!

Oppss..!
Now I'm looking better.. the RIGHT function is not necessary.
The finaly version of the QUERY is like this:

SELECT (CONVERT(char(10),CONVERT(datetime, CAST(getdate() as varchar(12)) ),103))

Because the last CONVERT function transform the datetime to a char(10)... without the use of RIGHT truncate the '2002-01-19 00:00:00.000' date to a '19/01/2004'.

Ahh.. by the way:
The LEFT or RIGHT function cuts a string by the LEFT or RIGHT respectabely... In this case RIGHT('19/01/2004',10) is no use :P

What a mess.. :D|||Thanks a whole lot!!!|||...In other words, this should have worked to begin with:

select convert(char(10), convert(datetime, '20040119'), 101)

The only difference is that your data is coming (as you said) in INTEGER data type, the the function above should have looked like this:

select convert(char(10), convert(datetime, cast(20040119 as char(8))), 101)

101 - MM/DD/YYYY
103 - DD/MM/YYYY

Take your pick!|||Yeah, gotta wonder why you don't just use rdjabarov's simple conversion. Shorter is better in programming.

Question about CASE

My proc:
CREATE PROCEDURE ListOrderDetails
DECLARE @.idoc INT
DECLARE @.inn AS VARCHAR(1000)
SET @.inn = '
<ROOT>
<OrderDetail num="261"/>
<OrderDetail num="263"/>
<OrderDetail num="264"/>
</ROOT>'
--Create an internal representation of the XML document.
EXEC sp_xml_preparedocument @.idoc OUTPUT, @.inn
select * from orders where id_order in <-- here goes the error
(
case
when len(@.inn) = 0 then (select id_order from orders)
else (select num from openxml(@.idoc,'/ROOT/OrderDetail', 1) WITH (num
int))
end
)
EXEC sp_xml_removedocument @.idoc
The Errorr (pointed out above):
Server: Msg 512, Level 16, State 1, Line 16
Subquery returned more than 1 value. This is not permitted when the
subquery follows =, !=, <, <= , >, >= or when the subquery is used as
an expression.
Can anyone explain me what's going on?
Thanks in advanceTry this (untested)
if len(@.inn) = 0
select * from orders where id_order in (select id_order from
rders) --I'm about what you do here
else
select * from orders where id_order in ( select num from
openxml(@.idoc,'/ROOT/OrderDetail', 1) WITH (num
> int)))
<vcinquini@.gmail.com> wrote in message
news:1145537892.798149.124240@.g10g2000cwb.googlegroups.com...
> My proc:
> CREATE PROCEDURE ListOrderDetails
> DECLARE @.idoc INT
> DECLARE @.inn AS VARCHAR(1000)
> SET @.inn = '
> <ROOT>
> <OrderDetail num="261"/>
> <OrderDetail num="263"/>
> <OrderDetail num="264"/>
> </ROOT>'
> --Create an internal representation of the XML document.
> EXEC sp_xml_preparedocument @.idoc OUTPUT, @.inn
> select * from orders where id_order in <-- here goes the error
> (
> case
> when len(@.inn) = 0 then (select id_order from orders)
> else (select num from openxml(@.idoc,'/ROOT/OrderDetail', 1) WITH (num
> int))
> end
> )
> EXEC sp_xml_removedocument @.idoc
> The Errorr (pointed out above):
> Server: Msg 512, Level 16, State 1, Line 16
> Subquery returned more than 1 value. This is not permitted when the
> subquery follows =, !=, <, <= , >, >= or when the subquery is used as
> an expression.
> Can anyone explain me what's going on?
> Thanks in advance
>|||I' could use your suggestion, but how to use an IF inside a WHERE
clause?

>if len(@.inn) = 0
>select * from orders where id_order in (select id_order from
>rders) --I'm about what you do here
>else
That's because I'm plannig to implement a filter in the future on order
details. When there's no filter passed as parameter, I'd like to
recover all rows (all order details). Where there is, I'd filter using
OPENXML stuff. Did you undertand? Excuse me for my bad English|||There is a difference between a CASE statement and an IF. I think other
people on this group would be better qualified to explain the details,
but if you want your code to just work as though it were an IF in a
WHERE clause, I suppose this could do it (though I've not tested this):
select * from orders where id_order in (
select id_order from orders WHERE len(@.inn)=0
UNION ALL
select num from openxml(@.idoc,'/ROOT/OrderDetail', 1) WITH (num int))
WHERE leb(@.Inn) != 0
)
Cheers
Will

question

Can anyone tell me why this gives me the following error:
declare @.hstname varchar (50)
set @.hstname = 'XPTO'
Select * from openquery (@.hstname,'select * from
master..sysdatabases')
Error:
Incorrect syntax near "@.hstname"
From Books Online:
"OPENQUERY does not accept variables for its arguments."
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
"" <anonymous@.discussions.microsoft.com> wrote in message news:33cc01c4291b$82c179b0$a301280a@.phx.gbl...
> Can anyone tell me why this gives me the following error:
> declare @.hstname varchar (50)
> set @.hstname = 'XPTO'
> Select * from openquery (@.hstname,'select * from
> master..sysdatabases')
> Error:
> Incorrect syntax near "@.hstname"

Question

Why does this view work with a compatibility level of 65 but not when 80 is
selected?
SELECT AccessionNumber, Info = CONVERT(varchar, AccessionNumber) + ' - '
+ tblLibraryBooks.Title + ' (Copy ' + CONVERT(varchar,
tblLibraryBookCopies.Copy) + CASE WHEN
tblLibraryBookCopies.Volume IS NOT NULL THEN ', Vol. ' +
tblLibraryBookCopies.Volume ELSE NULL
END + CASE WHEN tblLibraryBookStatusTypes.BookStatus
IS NOT NULL THEN ', Status: ' + CONVERT(varchar,
tblLibraryBookStatusTypes.BookStatus) ELSE NULL END +
')'
FROM tblLibraryBooks INNER JOIN
tblLibraryBookCopies ON tblLibraryBooks.BookID =
tblLibraryBookCopies.BookID LEFT OUTER JOIN
tblLibraryBookStatusTypes ON
tblLibraryBookCopies.BookStatusID = tblLibraryBookStatusTypes.BookStatusID
Thanks,
Tony
What is the error you are getting?
|||Your logic depends on certain connection settings (whose defaults have
apparently changed between versions) - a string + null = NULL. Therefore,
the answer is to concatenate an empty string, not NULL.
"Tony Schlak" <tony@.optics.arizona.edu> wrote in message
news:%23FC5uPzhFHA.2156@.TK2MSFTNGP14.phx.gbl...
> Why does this view work with a compatibility level of 65 but not when 80
is
> selected?
> SELECT AccessionNumber, Info = CONVERT(varchar, AccessionNumber) + ' -
'
> + tblLibraryBooks.Title + ' (Copy ' + CONVERT(varchar,
> tblLibraryBookCopies.Copy) + CASE WHEN
> tblLibraryBookCopies.Volume IS NOT NULL THEN ', Vol. ' +
> tblLibraryBookCopies.Volume ELSE NULL
> END + CASE WHEN tblLibraryBookStatusTypes.BookStatus
> IS NOT NULL THEN ', Status: ' + CONVERT(varchar,
> tblLibraryBookStatusTypes.BookStatus) ELSE NULL END
+
> ')'
> FROM tblLibraryBooks INNER JOIN
> tblLibraryBookCopies ON tblLibraryBooks.BookID =
> tblLibraryBookCopies.BookID LEFT OUTER JOIN
> tblLibraryBookStatusTypes ON
> tblLibraryBookCopies.BookStatusID = tblLibraryBookStatusTypes.BookStatusID
>
> Thanks,
> Tony
>
|||Unfortunately, this has already been answered in .programming. Please do
not independently post the same message to multiple newsgroups. You only
increase the work for you and everyone who donates their time to help you.
"Scott Morris" <bogus@.bogus.com> wrote in message
news:uzXZFv6hFHA.1052@.TK2MSFTNGP10.phx.gbl...[vbcol=seagreen]
> Your logic depends on certain connection settings (whose defaults have
> apparently changed between versions) - a string + null = NULL. Therefore,
> the answer is to concatenate an empty string, not NULL.
> "Tony Schlak" <tony@.optics.arizona.edu> wrote in message
> news:%23FC5uPzhFHA.2156@.TK2MSFTNGP14.phx.gbl...
> is
' -[vbcol=seagreen]
> '
tblLibraryBookStatusTypes.BookStatus[vbcol=seagreen]
END[vbcol=seagreen]
> +
tblLibraryBookStatusTypes.BookStatusID
>

question

Can anyone tell me why this gives me the following error:
declare @.hstname varchar (50)
set @.hstname = 'XPTO'
Select * from openquery (@.hstname,'select * from
master..sysdatabases')
Error:
Incorrect syntax near "@.hstname"From Books Online:
"OPENQUERY does not accept variables for its arguments."
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
":)" <anonymous@.discussions.microsoft.com> wrote in message news:33cc01c4291b$82c179b0$a301280a@.phx.gbl...
> Can anyone tell me why this gives me the following error:
> declare @.hstname varchar (50)
> set @.hstname = 'XPTO'
> Select * from openquery (@.hstname,'select * from
> master..sysdatabases')
> Error:
> Incorrect syntax near "@.hstname"

Question

Why does this view work with a compatibility level of 65 but not when 80 is
selected?
SELECT AccessionNumber, Info = CONVERT(varchar, AccessionNumber) + ' - '
+ tblLibraryBooks.Title + ' (Copy ' + CONVERT(varchar,
tblLibraryBookCopies.Copy) + CASE WHEN
tblLibraryBookCopies.Volume IS NOT NULL THEN ', Vol. ' +
tblLibraryBookCopies.Volume ELSE NULL
END + CASE WHEN tblLibraryBookStatusTypes.BookStatus
IS NOT NULL THEN ', Status: ' + CONVERT(varchar,
tblLibraryBookStatusTypes.BookStatus) ELSE NULL END +
')'
FROM tblLibraryBooks INNER JOIN
tblLibraryBookCopies ON tblLibraryBooks.BookID = tblLibraryBookCopies.BookID LEFT OUTER JOIN
tblLibraryBookStatusTypes ON
tblLibraryBookCopies.BookStatusID = tblLibraryBookStatusTypes.BookStatusID
Thanks,
TonyWhat is the error you are getting?|||Your logic depends on certain connection settings (whose defaults have
apparently changed between versions) - a string + null = NULL. Therefore,
the answer is to concatenate an empty string, not NULL.
"Tony Schlak" <tony@.optics.arizona.edu> wrote in message
news:%23FC5uPzhFHA.2156@.TK2MSFTNGP14.phx.gbl...
> Why does this view work with a compatibility level of 65 but not when 80
is
> selected?
> SELECT AccessionNumber, Info = CONVERT(varchar, AccessionNumber) + ' -
'
> + tblLibraryBooks.Title + ' (Copy ' + CONVERT(varchar,
> tblLibraryBookCopies.Copy) + CASE WHEN
> tblLibraryBookCopies.Volume IS NOT NULL THEN ', Vol. ' +
> tblLibraryBookCopies.Volume ELSE NULL
> END + CASE WHEN tblLibraryBookStatusTypes.BookStatus
> IS NOT NULL THEN ', Status: ' + CONVERT(varchar,
> tblLibraryBookStatusTypes.BookStatus) ELSE NULL END
+
> ')'
> FROM tblLibraryBooks INNER JOIN
> tblLibraryBookCopies ON tblLibraryBooks.BookID => tblLibraryBookCopies.BookID LEFT OUTER JOIN
> tblLibraryBookStatusTypes ON
> tblLibraryBookCopies.BookStatusID = tblLibraryBookStatusTypes.BookStatusID
>
> Thanks,
> Tony
>|||Unfortunately, this has already been answered in .programming. Please do
not independently post the same message to multiple newsgroups. You only
increase the work for you and everyone who donates their time to help you.
"Scott Morris" <bogus@.bogus.com> wrote in message
news:uzXZFv6hFHA.1052@.TK2MSFTNGP10.phx.gbl...
> Your logic depends on certain connection settings (whose defaults have
> apparently changed between versions) - a string + null = NULL. Therefore,
> the answer is to concatenate an empty string, not NULL.
> "Tony Schlak" <tony@.optics.arizona.edu> wrote in message
> news:%23FC5uPzhFHA.2156@.TK2MSFTNGP14.phx.gbl...
> > Why does this view work with a compatibility level of 65 but not when 80
> is
> > selected?
> >
> > SELECT AccessionNumber, Info = CONVERT(varchar, AccessionNumber) +
' -
> '
> > + tblLibraryBooks.Title + ' (Copy ' + CONVERT(varchar,
> > tblLibraryBookCopies.Copy) + CASE WHEN
> > tblLibraryBookCopies.Volume IS NOT NULL THEN ', Vol. ' +
> > tblLibraryBookCopies.Volume ELSE NULL
> > END + CASE WHEN
tblLibraryBookStatusTypes.BookStatus
> > IS NOT NULL THEN ', Status: ' + CONVERT(varchar,
> > tblLibraryBookStatusTypes.BookStatus) ELSE NULL
END
> +
> > ')'
> > FROM tblLibraryBooks INNER JOIN
> > tblLibraryBookCopies ON tblLibraryBooks.BookID => > tblLibraryBookCopies.BookID LEFT OUTER JOIN
> > tblLibraryBookStatusTypes ON
> > tblLibraryBookCopies.BookStatusID =tblLibraryBookStatusTypes.BookStatusID
> >
> >
> > Thanks,
> > Tony
> >
> >
>

Question

Why does this view work with a compatibility level of 65 but not when 80 is
selected?
SELECT AccessionNumber, Info = CONVERT(varchar, AccessionNumber) + ' - '
+ tblLibraryBooks.Title + ' (Copy ' + CONVERT(varchar,
tblLibraryBookCopies.Copy) + CASE WHEN
tblLibraryBookCopies.Volume IS NOT NULL THEN ', Vol. ' +
tblLibraryBookCopies.Volume ELSE NULL
END + CASE WHEN tblLibraryBookStatusTypes.BookStatus
IS NOT NULL THEN ', Status: ' + CONVERT(varchar,
tblLibraryBookStatusTypes.BookStatus) ELSE NULL END +
')'
FROM tblLibraryBooks INNER JOIN
tblLibraryBookCopies ON tblLibraryBooks.BookID =
tblLibraryBookCopies.BookID LEFT OUTER JOIN
tblLibraryBookStatusTypes ON
tblLibraryBookCopies.BookStatusID = tblLibraryBookStatusTypes.BookStatusID
Thanks,
TonyWhat is the error you are getting?|||Your logic depends on certain connection settings (whose defaults have
apparently changed between versions) - a string + null = NULL. Therefore,
the answer is to concatenate an empty string, not NULL.
"Tony Schlak" <tony@.optics.arizona.edu> wrote in message
news:%23FC5uPzhFHA.2156@.TK2MSFTNGP14.phx.gbl...
> Why does this view work with a compatibility level of 65 but not when 80
is
> selected?
> SELECT AccessionNumber, Info = CONVERT(varchar, AccessionNumber) + ' -
'
> + tblLibraryBooks.Title + ' (Copy ' + CONVERT(varchar,
> tblLibraryBookCopies.Copy) + CASE WHEN
> tblLibraryBookCopies.Volume IS NOT NULL THEN ', Vol. ' +
> tblLibraryBookCopies.Volume ELSE NULL
> END + CASE WHEN tblLibraryBookStatusTypes.BookStatus
> IS NOT NULL THEN ', Status: ' + CONVERT(varchar,
> tblLibraryBookStatusTypes.BookStatus) ELSE NULL END
+
> ')'
> FROM tblLibraryBooks INNER JOIN
> tblLibraryBookCopies ON tblLibraryBooks.BookID =
> tblLibraryBookCopies.BookID LEFT OUTER JOIN
> tblLibraryBookStatusTypes ON
> tblLibraryBookCopies.BookStatusID = tblLibraryBookStatusTypes.BookStatusID
>
> Thanks,
> Tony
>|||Unfortunately, this has already been answered in .programming. Please do
not independently post the same message to multiple newsgroups. You only
increase the work for you and everyone who donates their time to help you.
"Scott Morris" <bogus@.bogus.com> wrote in message
news:uzXZFv6hFHA.1052@.TK2MSFTNGP10.phx.gbl...
> Your logic depends on certain connection settings (whose defaults have
> apparently changed between versions) - a string + null = NULL. Therefore,
> the answer is to concatenate an empty string, not NULL.
> "Tony Schlak" <tony@.optics.arizona.edu> wrote in message
> news:%23FC5uPzhFHA.2156@.TK2MSFTNGP14.phx.gbl...
> is
' -[vbcol=seagreen]
> '
tblLibraryBookStatusTypes.BookStatus[vbcol=seagreen]
END[vbcol=seagreen]
> +
tblLibraryBookStatusTypes.BookStatusID[vbcol=seagreen]
>