You must Sign In to post a response.
  • Category: ASP.NET

    Replace zero with null

    Here is my query.@ScoreCount may contain zero or not.If it contain zero,should consider zero,as well as null and empty character.How to do that in where clause?

    declare @ScoreCount varchar(50)
    set @ScoreCount = '2,5,0'
    select e.name,e.id,e.degisnation from tblEmployee e inner join Department d on d.id = e.did where e.degisnation =@designation and ScoreCount in (@ScoreCount)

    So i done like this.
    select e.name,e.id,e.degisnation from tblEmployee e inner join Department d on d.id = e.did where e.degisnation =@designation and
    case when ScoreCount is null then 0 when ScoreCount ='' then 0 else ScoreCount end in ( 2,5,0)
    It is right approach?

    Here I made database value to null and '' first and later apply in clause.Again I have one query.How execution take place.According to me

    step 1 from --- > Result1
    step2 apply join on Result1 -->Result2
    step3 apply where clause first part on Result2 -->Result3
    step4 apply case part on Result3 then msidn in part check -->Result4
    setp 5 select Result4

    Here first it will change all record of result3 to appropriate msidn then do cheking of msidn in clause or one by one change record of result3 to appropriate msidn then do cheking of msidn in clause
  • #759099
    Hello Pinky,

    Refer the below SQL query :

    SELECT ISNULL(myColumn, 0 ) FROM myTable

    This will put a 0 in myColumn if it is null in the first place.

    Hope this will help you.
    Mark the answer if it helped you.

    Regards,
    Nirav Lalan
    DNS Gold Member
    "Failure is the path of least persistence"

  • #759106

    Hi Pinky.

    This query you can try.

    select e.name,e.id,e.degisnation
    from tblEmployee e
    inner join Department d
    on d.id = e.did
    where e.degisnation =@designation and
    case when ScoreCount is null then 0 end
    or
    when ScoreCount ='' then 0 else ScoreCount end in ( 2,5,0)


    Regards

    Sridhar.
    DNS Member.
    "Hope for the best.. Prepare for the worst.."


    Sridhar Thota.
    Editor: DNS Forum.

  • #759165
    u r telling me to use or.May I know why?When I run below query I am able to get proper output.

    select e.name,e.id,e.degisnation from tblEmployee e inner join Department d on d.id = e.did where e.degisnation =@designation and
    case when ScoreCount is null then 0 when ScoreCount ='' then 0 else ScoreCount end in ( 2,5,0)

  • #759166
    Hello Pinky,

    There may be multiple queries to get single output. Sridhar suggested you the best query as per his knowledge and giving you an extra parameter so you can get the result by running that query also.

    Enjoy coding.

    Regards,
    Nirav Lalan
    DNS Gold Member
    "Failure is the path of least persistence"

  • #759167
    Hi Pinky.

    If you have more statements in case to check,then you can use OR operator.

    How ever your query got executed successfully thats fine.

    Regards

    Sridhar.
    DNS Member.
    "Hope for the best.. Prepare for the worst.."

    Sridhar Thota.
    Editor: DNS Forum.


  • Sign In to post your comments