SQL Query sintaxe

Question

Post reply

SQL Query sintaxe

Viewing 8 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply

AJ Ahrens SSC-Insane Points: 20676 More actions · Answer 1

Try

CASE Code_Field WHEN 1 THEN Value + 100 ELSE Value - 100 END AS Calculated_Value

Good Hunting!

AJ Ahrens

webmaster@kritter.net

Pedro Neves Grasshopper Points: 23 More actions · Answer 2

Hi AJ,

Thanks for your reply, but the code you posted is VBscript (I think) not SQL (language) and so I cannot include it in my query string.

I tried anyway but It gave me a sintaxe error again...

I don't even know If I can do this directly on the query string

Ray M SSC-Insane Points: 21093 More actions · Answer 3

No the code she showed is not VB,

Look up Case in books online.

as in your original post

Select code_field, CASE Code_Field WHEN 1 THEN Value + 100 ELSE Value - 100 END AS Calculated_Value

from Database WHERE condition

From Books Online

Using CASE

The CASE function is a special Transact-SQL expression that allows an alternative value to be displayed depending on the value of a column. This change in data is temporary; therefore, there are no permanent changes to the data. For example, the CASE function can display California in a query result set for rows that have the value CA in the state column.

The CASE function consists of:

The CASE keyword.
The column name to transform.
WHEN clauses specifying the expressions to search for and THEN clauses specifying the expressions to replace them with.
The END keyword.
An optional AS clause defining an alias for the CASE function.

This example displays, in the query result set, the full name of the state each author lives in:

SELECT au_fname, au_lname,    
   CASE state      
      WHEN 'CA' THEN 'California'      
      WHEN 'KS' THEN 'Kansas'
      WHEN 'TN' THEN 'Tennessee'
      WHEN 'OR' THEN 'Oregon'
      WHEN 'MI' THEN 'Michigan'
      WHEN 'IN' THEN 'Indiana'
      WHEN 'MD' THEN 'Maryland'
      WHEN 'UT' THEN 'Utah'
   END AS StateName
FROM pubs.dbo.authorsORDER BY au_lname

Pedro Neves Grasshopper Points: 23 More actions · Answer 4

Thanks Ray!

I think I may have not explianed myself completely... I am NOT using this SQL string inside an SQL Server procedure or anything like it... I am trying to build a query inside a proprietary software called EventsPro to create a custom report (the program supports that feature).

The software, as best as I know, is built in FoxPro but direct access to it's database is made by common SQL Language (Not Transact-SQL), though they are very very similar.

Anyway, the program gives me a sintax error and I'm having their support guys to help me out!

Anyway I do appreciate your time on this matter, as well as AJ's.

Cheers

Pedro

noeld SSC Guru Points: 96590 More actions · Answer 5

Ah FoxPro... I have some fun memories and some not so fun from Developers that I used to work with. mind you FoxPro SQL SUCKS!!!!!!!!!

* Noel

Ray M SSC-Insane Points: 21093 More actions · Answer 6

Not trying to push you to any other site, and I'm not very familiar w/ foxpro. but perheps you can use this discussion forum to post your question.

I'm sure there's a case statement but I don't know the syntax.

Microsoft Community:

http://msdn.microsoft.com/newsgroups/default.aspx?dg=microsoft.public.fox.vfp.queries-sql&lang=en&cr=US

jlmahistrado Newbie Points: 9 More actions · Answer 7

try this one

iif(code_field=1, value+100, value-100) as calc_field

yuan(",)

skeane SSC Eights! Points: 887 More actions · Answer 8

or, build a calculation that doesn't use a conditional at all:

value = value + (code_field - 1)*(-200) + 100