SQL Query sintaxe

Question

Post reply

SQL Query sintaxe

Pedro Neves

Grasshopper

Points: 23
More actions
September 20, 2005 at 5:51 am

#102806

Hi everyone,
I'm having some trouble that probably most of you experienced guys will be able to help me...

I'm creating a query that selects record from a table and has a value field and a code field... what i need is to have the value add by 100 or by -100 depending on the code field.
If code field = 1 then value = value +100
If code field =2 then value = value -100
This is what i was tryng but I get a sintaxe error:
Select code_field, (IF(code_field=1, value=value+100,value)) As calculated_value from Database WHERE condition
Any help in the right direction, even with sites where to learn more about SQL sintaxe would me much appreciated!
tia
Pedro

Viewing 9 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