Results 1 to 12 of 12

Thread: select if.....

  1. #1
    Join Date
    Dec 2003
    Posts
    14

    Unanswered: select if.....

    I have some problem with this code below,

    SELECT IF(isNULL("person"."PersonID"),
    "Sold") as "Person",
    Car.SerialNumber "Serial Number"
    FROM Car left join PERSON
    ON "PERSON"."PersonID" = Car.PersonID
    ORDER BY Car.SerialNumber
    ;

    can someone here kindly tell me what's wrong with it?

    thanks

  2. #2
    Join Date
    Nov 2003
    Location
    Bangalore, INDIA
    Posts
    333

    Thumbs up Re: select if.....

    Hi,

    Try this

    SELECT NVL(person.PersonID,'Sold') as Person,
    Car.SerialNumber Serial Number
    FROM Car left join PERSON
    ON PERSON.PersonID = Car.PersonID
    ORDER BY Car.SerialNumber
    ;



    Originally posted by aurel1412
    I have some problem with this code below,

    SELECT IF(isNULL("person"."PersonID"),
    "Sold") as "Person",
    Car.SerialNumber "Serial Number"
    FROM Car left join PERSON
    ON "PERSON"."PersonID" = Car.PersonID
    ORDER BY Car.SerialNumber
    ;

    can someone here kindly tell me what's wrong with it?

    thanks
    SATHISH .

  3. #3
    Join Date
    Dec 2003
    Posts
    14
    SELECT NVL(person.PersonID,'Sold') wont work because i defined my person.PersonID as a NUMBER type so i can't replace if with a string "sold" .... NVL can only be use if i wanna replace it with a number

    is there any other build in function that can be use like NVL though?

  4. #4
    Join Date
    Nov 2003
    Location
    Bangalore, INDIA
    Posts
    333

    Thumbs up

    Hi,

    I can't underdstand your Logic. If the PersonID is a NUMBER then it can hold a value like 12345. why you are trying to replace the ID(Numeric Value) With a String Value (SOLD).


    Originally posted by aurel1412
    SELECT NVL(person.PersonID,'Sold') wont work because i defined my person.PersonID as a NUMBER type so i can't replace if with a string "sold" .... NVL can only be use if i wanna replace it with a number

    is there any other build in function that can be use like NVL though?
    SATHISH .

  5. #5
    Join Date
    Dec 2003
    Posts
    14
    ok....this is what i wanna do actually:

    if (Person.personID is null)
    then replace Person.name with "SOLD"
    else
    just print the Person.name

    how can i implement this in sql?

    thx

  6. #6
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Code:
    select case when person.id is null
                then 'sold'
                else person.name
             end
         , car.serialnumber as "serial number"
      from car 
    left outer
      join person
        on car.id = person.id
    order 
        by car.serialnumber
    rudy
    http://r937.com/

  7. #7
    Join Date
    Dec 2003
    Posts
    14
    select CAR.SerialNumber as "serial number",
    case "PERSON"."PersonID"
    when null
    then "SOLD"
    else "PERSON"."LastName"
    end as "Name",
    from CAR left outer join PERSON
    on CAR.PersonID = "PERSON"."PersonID"
    order by CAR.SerialNumber
    ;


    i did this .... and it gives me missing expression error....

  8. #8
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    "measure twice, cut once"

    you have a comma in front of FROM


  9. #9
    Join Date
    Dec 2003
    Posts
    14
    hahaha thanks..yeah it was the comma
    but now i have another problem
    it doesnt wanna print "SOLD" ... its just leave a blank space there in replace of "SOLD"....
    please help....

  10. #10
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    you really must slow down and take your time

    just a guess, but i think it's your quotes that are causing the problem

    "sold" is a column name, whereas 'sold' is a character string


  11. #11
    Join Date
    Dec 2003
    Posts
    14
    nope...actually i did change that before because it wont run at all if its "SOLD" .... it still give me this blank space.....
    ok i guess i really need to get some sleep....its 4am now....my brain is not working

  12. #12
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Originally posted by aurel1412
    it wont run at all if its "SOLD"
    that makes sense, since you don't have a column by that name

    try the other form of the CASE expression, the one i gave

    ... and double-check to make sure the lastname is not null at the same time that the id is null!


    rudy

Posting Permissions

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