By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,987 Members | 997 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,987 IT Pros & Developers. It's quick & easy.

Problem in Select Query

P: 12
Hi all
This is my Query

Expand|Select|Wrap|Line Numbers
  1. select m1.msgid as readstatus,Message.msgid,msgrecdate as sentdate,msgsubject as subject,message,
  2. userdetails.name as sender
  3. from message 
  4. inner join userdetails on userdetails.id = Message.msgfromusrid 
  5. left outer join (select distinct messagelog.msgid from messagelog 
  6. inner join message on
  7. messagelog.msgid=message.msgid) m1 on
  8. m1.msgid=message.msgid
  9. where msgtousrid = 1 and msginboxdel='0' 
  10. order by Message.msgid desc
  11.  
the output of the above query is as follows

Expand|Select|Wrap|Line Numbers
  1.    ;55;"2007-10-14 14:16:05.14";"rgryeyruruu6";"Translucent"
  2. 47;47;"2007-10-13 19:19:3844";"dgdfgd";"Translucent"
  3.  
here I need to display
when the raedstatus is Empty, I have to display 'NotRead'
else 'Read'

Please suggest me how to re write my code to work as per my requirement.

thanks in advance
Oct 15 '07 #1
Share this Question
Share on Google+
1 Reply


Expert 100+
P: 700
Hi all
This is my Query

Expand|Select|Wrap|Line Numbers
  1. select m1.msgid as readstatus,Message.msgid,msgrecdate as sentdate,msgsubject as subject,message,
  2. userdetails.name as sender
  3. from message 
  4. inner join userdetails on userdetails.id = Message.msgfromusrid 
  5. left outer join (select distinct messagelog.msgid from messagelog 
  6. inner join message on
  7. messagelog.msgid=message.msgid) m1 on
  8. m1.msgid=message.msgid
  9. where msgtousrid = 1 and msginboxdel='0' 
  10. order by Message.msgid desc
  11.  
the output of the above query is as follows

Expand|Select|Wrap|Line Numbers
  1.    ;55;"2007-10-14 14:16:05.14";"rgryeyruruu6";"Translucent"
  2. 47;47;"2007-10-13 19:19:3844";"dgdfgd";"Translucent"
  3.  
here I need to display
when the raedstatus is Empty, I have to display 'NotRead'
else 'Read'

Please suggest me how to re write my code to work as per my requirement.

thanks in advance
use case statement

select
case
when m1.msgid is NULL then 'NotRead'
else 'Read'
end

as readstatus,Message.msgid,msgrecdate as ....
Oct 15 '07 #2

Post your reply

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