DBAsupport.com Forums - Powered by vBulletin
Results 1 to 3 of 3

Thread: how to concatenate in PL/SQL

  1. #1
    Join Date
    Nov 2005
    Posts
    5

    how to concatenate in PL/SQL

    Hi,

    I need to create a function to concatenate first name, blank, last name from employee table...
    It should return the concatenated value...
    and also i have to check for exceptions...

    have to then call it using sleect command

  2. #2
    Join Date
    May 2002
    Posts
    2,645
    select name||address|city||whatever
    from your_table.

    Use the double pipe ("||") to concatenate (as one way). Another is to append strings to strings, but since you're using a select, the "||" method will suit your needs.

  3. #3
    Join Date
    Nov 2005
    Posts
    5
    hey,
    thanks..I kind of got that working..

    i have another Q for u..
    I have to write a PROCEDURE called DISCOUNT_ORDERS which gives X percent discount for all Y orders whose shipping date is more than Y days from the order date. The procedure takes X% and Ydays as input paramters...I am asked to display only orders who get a discount....It should display order id, order date, ship date, and discount amount and update total of all orders...
    I tried to create a function first and then thought of calling the function frlom preocdure...doesnt look like it works....can u help here?

    ---------------------------------------------------------------
    > SQL> Create or replace function DISCOUNT(p_diff IN number)
    >
    > 2 RETURN number IS
    > 3 p_disamt number(2,2);
    > 4 p_newamt number(5,2);
    > 5 max_day :=15;
    > 6 min_day :=5;
    > 7 max_dis :=10;
    > 8 min_dis :=5;
    > 9 BEGIN
    > 10 SELECT total INTO p_total from sales_order;
    > 11 If(p_diff=min_day)
    > 12 THEN
    > 13 p_disamt :=(p_total * (min_dis/100));
    > 14 p_newamt :=(p_total - p_disamt);
    > 15 ELSIF(p_diff>=max_day)
    > 16 THEN p_disamt :=(p_total *(max_dis/100));
    > 17 p_newamt :=(p_total - p_disamt);
    > 18 ELS p_disamt :=0;
    > 19 END IF;
    > 20 /
    >


    TABLE USED:
    CREATE TABLE sales_order (
    order_id NUMBER(4) NOT NULL,
    order_date DATE,
    customer_id NUMBER(6),
    ship_date DATE,
    total NUMBER(8,2));

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