I am trying to use the decode function on oracle within a but get an error any ideas?
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 4 of 4

Thread: I am trying to use the decode function on oracle within a but get an error any ideas?

  1. #1
    Join Date
    Dec 2011
    Posts
    8

    I am trying to use the decode function on oracle within a but get an error any ideas?

    i have two tables one called table_customer and one called tbl_membership_type i have to use the decode function within a view to create to create a list showing customer_id, first_name, last_name, country, description. the view should include a column that lists the membership type Gold as AU, Silvr as Ag, and bronze as CuCn but the solution must not update or add any data in the original table.
    i was thing it would be something along the lines of

    CREATE view attempt1 as

    SELECT tbl_customer.customer_id, first_name, last_name,country_of_residence, description_of_membership_type

    DECODE ( description_of_membership_type, gold, 'Au'
    silver, 'Ag'
    bronze, 'CuSn')
    FROM tbl_customer, tbl_membership_type

    where tbl_membership_type.membership_type_code = tbl_customer.mebership_type_code

    i am not sure if i have written the decode part correct and the join part im not sure if it should be some sort of left join.

    the tables are as follows:

    Table 1 - tbl_customer
    customer id ----------------------------------------… PRIMARY KEY
    last name
    first name
    date of birth
    mebership type code ----------------------------------------… FOREIGN KEY
    country of residence

    table 2 - tbl membership type
    membership type code ----------------------------------------… PRIMARY KEY
    description of membership type
    monthly fee uk pounds
    download limit

    I have also tried
    CREATE view "attempt1" as

    SELECT tbl_customer.customer_id, first_name, last_name,country_of_residence, tbl_membership_type.description_of_membe…

    FROM tbl_customer RIGHT OUTER JOIN tbl_memership_type
    on tbl_membership_type.membership_type_code = tbl_customer.membership_type_code

    DECODE ( description_of_membership_type, gold, 'Au'
    silver, 'Ag'
    bronze, 'CuSn')

    but get this error: ORA-00933: SQL command not properly ended

    any help would b great have been struggling with this for days!

  2. #2
    Join Date
    May 2002
    Posts
    2,645
    Add a ; at the end of your create table statement.

  3. #3
    Join Date
    Jul 2002
    Location
    Lake Worth, FL
    Posts
    1,459
    Quote Originally Posted by elmo_helper View Post
    -- Etc --
    i am not sure if i have written the decode part correct and the join part im not sure if it should be some sort of left join.
    -- Etc --
    None of your views seems to have a valid syntax -- try again.
    "The person who says it cannot be done should not interrupt the person doing it." --Chinese Proverb

  4. #4
    Join Date
    Nov 2000
    Location
    Pittsburgh, PA
    Posts
    3,997
    You can also use the case statement if you have Oracle 10g or higher. You should definitely use the inner join clause to join the tables. In my example NAM means not a member, you should always have an else to your case unless you verify that the value will always fall within the WHEN clauses.

    Code:
    CREATE view attempt1 as
       SELECT tbl_customer.customer_id, first_name, last_name,
              country_of_residence, description_of_membership_type,
              CASE description_of_membership_type
                  WHEN 'gold'   THEN 'Au'
                  WHEN 'silver' THEN 'Ag'
                  WHEN 'bronze' THEN 'CuSn'
                  ELSE 'NAM' description_of_membership_type
         FROM tbl_customer
        INNER JOIN tbl_membership_type
           ON tbl_membership_type.membership_type_code = 
              tbl_customer.mebership_type_code;
    this space intentionally left blank

Tags for this Thread

Posting Permissions

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



Click Here to Expand Forum to Full Width