need help- case...when not working

I am getting the error
Incorrect syntax near 'fValue1_precision'.
from the stored procedure below
Can someone see what is wrong?
Thanks
...
@.MyField3 float(8)=NULL,
@.MyField3_precision int=NULL,
...
SELECT
MyTable.[ID],
MyTable.MyField1,
MyTable.MyField2,
(CASE
WHEN MyField3_precision IS NULL THEN MyField3
ELSE CONVERT(decimal(10, MyField3_precision), MyField3)
END) AS MyField3,
MyTable.MyField4,
MyTable.MyField5,Mad Scientist Jr wrote:
> I am getting the error
> Incorrect syntax near 'fValue1_precision'.
> from the stored procedure below
> Can someone see what is wrong?
> Thanks
> ...
> @.MyField3 float(8)=NULL,
> @.MyField3_precision int=NULL,
> ...
> SELECT
> MyTable.[ID],
> MyTable.MyField1,
> MyTable.MyField2,
> (CASE
> WHEN MyField3_precision IS NULL THEN MyField3
> ELSE CONVERT(decimal(10, MyField3_precision), MyField3)
> END) AS MyField3,
> MyTable.MyField4,
> MyTable.MyField5,
Given that the code fragment you posted doesn't include the name
referred to in the error message it's kind of hard to be sure...
However, this bit isn't valid syntax:
CONVERT(decimal(10, MyField3_precision)
You can't use a column value for the precision. The precision must be a
literal value.
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--|||the actual error was
Incorrect syntax near 'MyField3_precision'.
but that sounds like it could be the problem
thanks|||so the correct way to do it would be something like this?
(CASE
WHEN MyField3_precision IS NULL THEN MyField3
WHEN MyField3_precision=0 THEN CONVERT(decimal(10, 0),
MyField3)
--WHEN MyField3_precision=0 THEN CONVERT(int, MyField3)
WHEN MyField3_precision=1 THEN CONVERT(decimal(10, 1),
MyField3)
WHEN MyField3_precision=2 THEN CONVERT(decimal(10, 2),
MyField3)
WHEN MyField3_precision=3 THEN CONVERT(decimal(10, 3),
MyField3)
WHEN MyField3_precision=4 THEN CONVERT(decimal(10, 4),
MyField3)
WHEN MyField3_precision=5 THEN CONVERT(decimal(10, 5),
MyField3)
WHEN MyField3_precision=6 THEN CONVERT(decimal(10, 6),
MyField3)
WHEN MyField3_precision=7 THEN CONVERT(decimal(10, 7),
MyField3)
WHEN MyField3_precision=8 THEN CONVERT(decimal(10, 8),
MyField3)
WHEN MyField3_precision=9 THEN CONVERT(decimal(10, 9),
MyField3)
WHEN MyField3_precision=10 THEN CONVERT(decimal(10, 10),
MyField3)
ELSE MyField3
END) AS MyField3,|||Mad Scientist Jr wrote:
> so the correct way to do it would be something like this?
> (CASE
> WHEN MyField3_precision IS NULL THEN MyField3
> WHEN MyField3_precision=0 THEN CONVERT(decimal(10, 0),
> MyField3)
> --WHEN MyField3_precision=0 THEN CONVERT(int, MyField3)
> WHEN MyField3_precision=1 THEN CONVERT(decimal(10, 1),
> MyField3)
> WHEN MyField3_precision=2 THEN CONVERT(decimal(10, 2),
> MyField3)
> WHEN MyField3_precision=3 THEN CONVERT(decimal(10, 3),
> MyField3)
> WHEN MyField3_precision=4 THEN CONVERT(decimal(10, 4),
> MyField3)
> WHEN MyField3_precision=5 THEN CONVERT(decimal(10, 5),
> MyField3)
> WHEN MyField3_precision=6 THEN CONVERT(decimal(10, 6),
> MyField3)
> WHEN MyField3_precision=7 THEN CONVERT(decimal(10, 7),
> MyField3)
> WHEN MyField3_precision=8 THEN CONVERT(decimal(10, 8),
> MyField3)
> WHEN MyField3_precision=9 THEN CONVERT(decimal(10, 9),
> MyField3)
> WHEN MyField3_precision=10 THEN CONVERT(decimal(10, 10),
> MyField3)
> ELSE MyField3
> END) AS MyField3,
No. A column can only have one type, scale and precision and that's
determined at compile-time, so the multiple versions of CONVERT aren't
doing much for you. If you want each row formatted differently in SQL
then you'll have to convert the values to strings. Probably makes more
sense to return numbers at full precision and then format them in the
client app or presentation tier.
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--|||Strange, the above code is working. Using case-when I am getting back
the precision I want in the select (the result is being displayed in a
datagrid). It appears to be working, so what is wrong with the code?
Thanks|||Mad Scientist Jr wrote:
> Strange, the above code is working. Using case-when I am getting back
> the precision I want in the select (the result is being displayed in a
> datagrid). It appears to be working, so what is wrong with the code?
> Thanks
Depends what you mean by "appears to be working". What is it supposed
to do? Formatting of numbers is controlled by your client application,
not by SQL Server. Possibly the implict rounding performed by CONVERT
is influencing the way your app displays the numbers - but you don't
need CONVERT to do rounding. You can use ROUND or FLOOR instead.
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--|||Back to basics. CASE is an expression. An expression returns a scalar
result of one and only one data type. Why? Because a column can have
one and only one data type!! You missed the very definition of 1NF.
You might also way to learn HUGE differences between columns and
fields. Fields can change types and structure because in a file
system, the host program gives meanng to the data as it reads it from
left to right. In a RDBMS, the meaning is in the schema, not the
applications.

Need advice on IF...THEN...ELSE type T-SQL syntax

I don't write SQL that often, so...

What is the most "efficient" way to write an SQL statement that accomplishes:

If a record for this key exists in this table, just update it with the following information, but if a record does not exist, create one with the information.

Thanks in advance.

Look into If Exists.

|||

try this

ifexists(SELECT key_fieldfrom yourtablewhere key_field='key')BEGINUPDATE yourtableset [thefield]='your new value'WHERE key_field='key'END

|||

My understanding is that with the 2008 release of sql server, it will add the MERGE command, which is designed precisely for this purpose.

Otherwise, you'll have to use the prior example (but remember to add an "else" branch to do the insert.

Alternatively, there is a way to do it as two statements (one update, one insert) that will handle all the rows you need updated/inserted.

It just depends upon the nature of the update. If all the records to be updated need to be updated with the same info (i.e., add 5% to the salary), one update statement will update all of them.

Then, do an insert statement in the form of:

insert into destination_table (col1,col2) select colA, colB from source_table where source_table.key_column not in (select key_column from destination_table)

That will insert all the missing records.

|||

Thanks for the push, all. I also came upon the slightly different approach of

Do the Update, if the @.@.ROWCOUNT equals 0, do the insert.

|||

DisturbedBuddha:

Thanks for the push, all. I also came upon the slightly different approach of

Do the Update, if the @.@.ROWCOUNT equals 0, do the insert.

Duh! Much easier and probably more efficient than the exists check.

|||

http://disturbedbuddha.wordpress.com/2007/11/29/easy-sql-if-record-exists-update-it-if-not-insert-it/

NEAR syntax in Full-Text Search

In tuning our search engine which is running against SQL 2005, I'm trying to understand the "near" operator in my CONTAINSTABLE query. I'm doing a query like the following:

select * FROM CONTAINSTABLE(Catalog, *,
'FORMSOF(INFLECTIONAL,"class") OR
FORMSOF(INFLECTIONAL,"calendar") OR
("class" near "calendar")', 1000)

Entries that have class and calendar directly next to eachother are being ranked higher, as would be expected. But entries that have Class <word> calendar, are being ranked the exact same as they would be with just the two Inflectional ORs and no near syntax.

I thought the near syntax was supposed to be useful up to 50 words?

The near syntax is useful up to 50 words means if the two words are more than 50 words apart, the ranking score (of that OR clause) will be 0.

Ranking is rather complicated. In your example, the ranking will be affected by all 3 OR clauses. The ranking also depends on other factors, like document length, average document length of the corpus, frequency of the terms in the document and across corpus. So, word distance in the near clause may or may not be visible in your final ranking score.

NEAR syntax in Full-Text Search

In tuning our search engine which is running against SQL 2005, I'm trying to understand the "near" operator in my CONTAINSTABLE query. I'm doing a query like the following:

select * FROM CONTAINSTABLE(Catalog, *,
'FORMSOF(INFLECTIONAL,"class") OR
FORMSOF(INFLECTIONAL,"calendar") OR
("class" near "calendar")', 1000)

Entries that have class and calendar directly next to eachother are being ranked higher, as would be expected. But entries that have Class <word> calendar, are being ranked the exact same as they would be with just the two Inflectional ORs and no near syntax.

I thought the near syntax was supposed to be useful up to 50 words?

The near syntax is useful up to 50 words means if the two words are more than 50 words apart, the ranking score (of that OR clause) will be 0.

Ranking is rather complicated. In your example, the ranking will be affected by all 3 OR clauses. The ranking also depends on other factors, like document length, average document length of the corpus, frequency of the terms in the document and across corpus. So, word distance in the near clause may or may not be visible in your final ranking score.