473,321 Members | 1,916 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,321 software developers and data experts.

if else statement inside a where clause

Good day!!

I was wondering if it is possible to put an if else statement inside a where clause of an sql statement... you see i would like my update statement to do this:


update trial_clear set num = @count2 /* @count2 is a integer passed*/
where
if (select top 1 def from trial_clear where num is NULL) is NULL
def is NULL
else
def = @def /*given value*/

please do help me... i would appreciate it much!!
Oct 19 '07 #1
5 8157
iburyak
1,017 Expert 512MB
Try this:

Expand|Select|Wrap|Line Numbers
  1. update trial_clear set num = @count2 /* @count2 is a integer passed*/
  2. where def =
  3. case when (select top 1 def from trial_clear where num is NULL) is NULL Then null
  4. else @def  end
Good Luck.
Oct 19 '07 #2
Try this:

Expand|Select|Wrap|Line Numbers
  1. update trial_clear set num = @count2 /* @count2 is a integer passed*/
  2. where def =
  3. case when (select top 1 def from trial_clear where num is NULL) is NULL Then null
  4. else @def  end
Good Luck.



thanks for the code but still it doesn't work, i should have made my message clear... you see i've got this table..
--table---
trial_clear(num,def,def2,def3)
--these are the existing values--
1st row (NULL,q,NULL,NULL)
2nd row (NULL,w,w,NULL)
3rd row (NULL,e,e,r)
4th row (t,NULL,y)
5th row (q,w,NULL)

you see what i was supposed to do is update this table, assigning the num value with int.. starting form 1 to so on...the table i presented is just a prototype of the real table

while making my procedure i noticed that it will only work if i would say 'def is NULL' not def = NULL and that was why i was wondering if there was and if else statement inside the where clause
Oct 19 '07 #3
iburyak
1,017 Expert 512MB
Try this:

Expand|Select|Wrap|Line Numbers
  1. set rowcount 1
  2.  
  3. update trial_clear set num = @count2 /* @count2 is a integer passed*/
  4. where isnull(def, '') = ''
  5.  
  6.  
Oct 19 '07 #4
Try this:

Expand|Select|Wrap|Line Numbers
  1. set rowcount 1
  2.  
  3. update trial_clear set num = @count2 /* @count2 is a integer passed*/
  4. where isnull(def, '') = ''
  5.  
  6.  

thanks it really helped!!

But i too got another solution. I simply put the resulting rows into a cursor and it works even better than my previous code. Thanks for reminding me that there is a better way... THANK YOU!!!
Oct 20 '07 #5
ck9663
2,878 Expert 2GB
thanks it really helped!!

But i too got another solution. I simply put the resulting rows into a cursor and it works even better than my previous code. Thanks for reminding me that there is a better way... THANK YOU!!!

cursor should be your last resort...
Oct 20 '07 #6

Sign in to post your reply or Sign up for a free account.

Similar topics

2
by: Largo SQL Tools | last post by:
Can anyone tell me if it's possible to use a Case statement in a Where clause, and if so, the proper syntax? J.R. Largo SQL Tools The Finest Collection of SQL Tools Available...
9
by: paulmac106 | last post by:
If you could help me with my syntax i would really appreciate it. I'm trying to change the where clause on the fly, but it's giving me a syntax error. Hopefully from what I have below you can...
8
by: frenzic | last post by:
Hi I am a DBA - just been in the job two weeks and I would be grateful for some help. I am trying to update a table with values from another table. I have spent two days on this and I think...
2
by: knmadhhu | last post by:
Hi Everybody I am writing this statement,but is it hanging! Can anybody correct me? Any help will be appreciated! select (cba.billing_name1||' '||cba.billing_name2) as cust_bill_name from...
2
by: edwinjs | last post by:
Hi I am not able to execute the following query. select emp_code from employees where emp_code in (case when emp_type = 1 then select emp_code from...
1
by: ebo2006 | last post by:
There are two combo boxes in my form: CORP and CLASS. The WHERE clause of the SELECT statement in the Row Source Property of the CLASS combo box works only every time I start the form. It does not...
15
by: shlo | last post by:
I was wondering if someone would be able to tell me what is wrong with my code here: SELECT COUNT(*) FROM CALL WHERE CALL.DESCRIPTION = CASE WHEN @Param1 = 'In' THEN 'In' ...
1
by: djpaul | last post by:
Hello, I'm trying to select a dayvalue in the sql statement depending on the openingstime. So if the shop opens on 8:00 and the time is before that i want to show the times that it's open today....
0
by: yshali | last post by:
I am trying to handle the NULL value passed to a parameter. if param1 = 1,2,3 query returns where param in (1,2,3) if param = NULL query returns all values. I have a split function to...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.