1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

How to write query for following logic? help

Discussion in 'SQL PL/SQL' started by johnchennai, Dec 26, 2013.

  1. johnchennai

    johnchennai Active Member

    Messages:
    17
    Likes Received:
    0
    Trophy Points:
    100
    to concatenate(join) the employee_number to a comma separated string

    example:'101,102,103,104.,,,,'
     
  2. johnchennai

    johnchennai Active Member

    Messages:
    17
    Likes Received:
    0
    Trophy Points:
    100
    Help to resolve the following query?

    Write a anonymous block to print numbers from 1 to 100, if the number is divisible by 3 then
    print as 'FOO', if the number is divisible by 5 then print as 'BAR', if the number is divisible by both (3&5)
    then print 'FOOBAR'
    else print the actual number.

    Example:
    1
    2
    FOO
    4
    BAR
    7
    .
    .
    14
    FOOBAR
    16
    .
    .
    98
    BAR
    FOO
     
  3. Bharat

    Bharat Community Moderator Forum Guru

    Messages:
    1,747
    Likes Received:
    147
    Trophy Points:
    1,805
    Location:
    Vijayawada, India
    Re: Help to resolve the following query?

    Hi John,

    Please go through the following PL/SQL code and let us know if it works as expected..
    Code (Text):


    begin

    for i in 1..100 loop

    if mod(i,3) = 0 then
    dbms_output.put_line('FOO');
    end if;
    if mod(i,5) = 0 then
    dbms_output.put_line('BAR');
    end if;
    if mod(i,5) = 0 and mod(i,3) = 0 then
    dbms_output.put_line('FOOBAR');
    end if;
    if mod(i,5) <> 0 and mod(i,3) <> 0 then
    dbms_output.put_line(i);
    end if;

    end loop;

    end;

     
     
  4. johnchennai

    johnchennai Active Member

    Messages:
    17
    Likes Received:
    0
    Trophy Points:
    100
    Re: Help to resolve the following query?

    Thanks bharath
     
  5. jagadekara

    jagadekara Forum Guru

    Messages:
    1,126
    Likes Received:
    145
    Trophy Points:
    1,805
    Location:
    Pulivendula
    Re: Help to resolve the following query?

    Hi,


    Try this..

    begin
    for i in 1..100 loop
    if mod(i,5) = 0 and mod(i,3) = 0 then
    dbms_output.put_line('FOOBAR');
    elsif mod(i,3) = 0 then
    dbms_output.put_line('FOO');
    elsif mod(i,5) = 0 then
    dbms_output.put_line('BAR');
    elsif mod(i,5) <> 0 and mod(i,3) <> 0 then
    dbms_output.put_line(i);
    end if;
    end loop;
    end;


    My Query getting only 100 records.
     
    johnchennai likes this.
  6. krasnoslobodtsev_si

    krasnoslobodtsev_si Forum Genius

    Messages:
    626
    Likes Received:
    129
    Trophy Points:
    810
    Location:
    Russian Federation
    there are some candidate solutions :

    1)
    Code (SQL):

    WITH
    comma_to_tab AS
    (
    SELECT  
          regexp_substr('123,122,133,1234','[^,]+',1,level) AS id
    FROM dual
    CONNECT BY regexp_substr('123,122,133,1234','[^,]+',1,level) IS NOT NULL
    )
    ,
    simple_tab AS
    (SELECT 123   AS id ,'A'   AS txt   FROM dual UNION  ALL
     SELECT 133         ,'B'            FROM dual UNION  ALL
     SELECT 1335        ,'C'            FROM dual UNION  ALL
     SELECT 1234        ,'D'            FROM dual
     )
    SELECT  
       st.id ,c.id id_ctb, st.txt
    FROM simple_tab st , comma_to_tab c
    WHERE st.id = c.id;

    SQL>
     
            ID ID_CTB                                                           TXT
    ---------- ---------------------------------------------------------------- ---
           123 123                                                              A
           133 133                                                              B
          1234 1234                                                            D

     
    2)
    Code (SQL):

    define str = '123,122,133,1234'
    WITH
    simple_tab AS
    (SELECT 123   AS id ,'A'   AS txt   FROM dual UNION  ALL
     SELECT 133         ,'B'            FROM dual UNION  ALL
     SELECT 1335        ,'C'            FROM dual UNION  ALL
     SELECT 1234        ,'D'            FROM dual
     )
    SELECT  
       st.id ,c.column_value id_ctb, st.txt
    FROM simple_tab st , TABLE(sys.odcinumberlist(&&str)) c
    WHERE st.id = c.column_value;


    SQL>
     
            ID     ID_CTB TXT
    ---------- ---------- ---
           123        123 A
           133        133 B
          1234       1234 D


     
     
    johnchennai likes this.
  7. jagadekara

    jagadekara Forum Guru

    Messages:
    1,126
    Likes Received:
    145
    Trophy Points:
    1,805
    Location:
    Pulivendula
    Hi do you want employee_numbers in row by seperating with comma?

    Then try this...

    select
    rtrim (xmlagg (xmlelement (e, empno || ',')).extract ('//text()'), ',') employee_numbers
    from
    emp
     
    johnchennai likes this.
  8. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,351
    Likes Received:
    348
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    Without any proof the original poster did any work to solve this 'solutions' really should not be offered; it is not customary in this forum to do homework for those who ask us to do it. The goal is to assist, not spoon-feed those who ask as spoon-feeding does nothing to teach problem solving or coding skills.