Results 1 to 5 of 5
  1. #1
    Join Date
    Dec 2003
    Posts
    14

    Unanswered: problem writting a report using CASE

    Hi

    I'm writting a report now but i encountered a problem. can someone check out my code and find out what's wrong with this code?

    TTITLE 'Name Contact List'
    SET LINESIZE 120
    SET PAGESIZE 30
    SET NEWPAGE 1
    COLUMN PERSON FORMAT A10;
    COLUMN CONTACT FORMAT A10;

    SELECT "PERSON"."LastName" "Last Name",
    "PERSON"."FirstName" "First Name",
    "PERSON"."Street" "Address",
    CONTACT.ContactData "Contact Data",
    CASE ContactType
    WHEN 'H' THEN 'Home Phone Number'
    WHEN 'W' THEN 'Work Phone Number'
    WHEN 'P' THEN 'Pager numbers'
    WHEN 'E' THEN 'E-mail Addresses'
    WHEN 'C' THEN 'Cellular '
    END
    FROM PERSON left join CONTACT
    ON "PERSON"."PersonID" = CONTACT.PersonID
    WHERE CONTACT.ContactData IS NOT NULL
    ORDER BY "PERSON"."LastName"
    ;

    It runs correctly but somehow the Contact Data column's title is
    "CASECONTACTTYPEWH" instead of just "Contact Data"


    Name Contact List

    Last Name First Name Address Contact Data CASECONTACTTYPEWH
    ---------- ---------- ---------------------------------------- ---------------------

  2. #2
    Join Date
    Oct 2003
    Location
    Germany - Stuttgart
    Posts
    14
    try

    WHEN 'C' THEN 'Cellular '
    END as contact_type

    instead of

    WHEN 'C' THEN 'Cellular '
    END

  3. #3
    Join Date
    Dec 2003
    Posts
    14
    thanks a lot it works like a charm..... but i wanna change the title from ContactType to "Contact Type"
    I tried :

    CASE CONTACT.ContactType "Contact Type"
    WHEN 'H' THEN 'Home Phone Number'
    WHEN 'W' THEN 'Work Phone Number'
    WHEN 'P' THEN 'Pager numbers'
    WHEN 'E' THEN 'E-mail Addresses'
    WHEN 'C' THEN 'Cellular '
    END as ContactType
    but its not working...any idea?

  4. #4
    Join Date
    Oct 2003
    Posts
    37
    Try

    END as "Contact Type"

    instead of

    END as ContactType

    Though it may conflict with the first line of your case statement - due to their being two items called the same thing.


    Incidently what version of oracle are you running, I tried writing my case statement like yours and sql didn't like it at all (I'm using 8i).

    Regards

    Keith

  5. #5
    Join Date
    Dec 2003
    Posts
    14

    Thank you so much

    wow your my saviour....
    it works it works.... yippie
    Thanks a lot man
    and btw im using oracle 9.2 or whatever is the new one..
    just got it installed a couple months ago...

Posting Permissions

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