Results 1 to 10 of 10
  1. #1
    Join Date
    Jan 2008
    Posts
    23

    Unanswered: query syntax plz

    am looking to make a select query that has some condition where it should look like:
    select if(SomeField is null) then 'Not Set' else SomeField
    from SomeTable

    how it can be done ?????

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    select coalesce(SomeField,'Not Set') from SomeTable
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Jan 2008
    Posts
    23
    ok i tried it but when ever the value is null it doesnt give the value 'Not Set' instead it ignore the whole record from the selection ????

  4. #4
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Post the full SQL
    George
    Home | Blog

  5. #5
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    please show entire query
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  6. #6
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    argh, sniped again

    nice to see we think alike, though, eh

    right down to the minimal four words, although my post includes "please" and yours an unnecessary definite article

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  7. #7
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Quote Originally Posted by akkad
    ok i tried it but when ever the value is null it doesnt give the value 'Not Set' instead it ignore the whole record from the selection ????
    Is the column in the WHERE Clause?
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  8. #8
    Join Date
    Jan 2008
    Posts
    23
    thnx for ur responses, here is the sql :

    select Requests.ID, Owners.Name As Owner, Requests.Title, Products.Name, Requests.CreateDate,
    coalesce(Requests.CompletedTime, 'Not Completed') as CompletedTime,
    coalesce(datediff(day, Requests.CreateDate, Requests.CompletedTime), datediff(day, Requests.CreateDate, GetDate())) as Age,
    Status.Description, Customers.Name as Customer, Requests.Contact
    from Requests inner join products on Requests.product = products.id
    inner join Status on Requests.status = status.id
    inner join Owners on Requests.Owner = Owners.id
    inner join Customers on Requests.Customer = Customers.id
    where Requests.Owner = @OwnerId or @OwnerId = -1


    the goal of the sql is when ever the completed date is null i want to set to "Not Completed" otherwise select it, and for calculating the request age there will be two equations depending on the completed date if it is null or not as follows:
    age = create date - completed date // whenever completed date is not null
    age = create date - today date // whenever completed date is null

    "coalesce" it ignores selecting the records which its completed date is null, why is that i dont know ?????
    Last edited by akkad; 02-07-08 at 03:58.

  9. #9
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    I am going to assume that the completed date is of the datetime datatype.

    By coalescing (sp?) to "not completed" you are attempting to convert the datetime value to a textual value; t-sql doesn't like it when you do that.

    You may have to convert and then coalesce.
    George
    Home | Blog

  10. #10
    Join Date
    Jan 2008
    Posts
    23
    and u r right, it is working now after conversion, thnx.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •