Results 1 to 8 of 8
  1. #1
    Join Date
    Dec 2005
    Posts
    59
    Provided Answers: 1

    Unanswered: PL/SQL query to display series of data in VB.NET!

    Hello all,

    I have a column data named SERIES that contain some similar characters that group by the first two characters as following:

    AA 1, AA 2, AA 3, BB 2, BB 15, BB 24, AB 3, AB 4

    I would like to display those data to the front end of ASP.NET page dynamically as following:

    1. AA series: 1,2,3

    2. BB series: 2, 15, 24

    3. AB series: 3, 4

    Any helping in PL/SQL query and VB.NET codes are much appreciated. Thanks in advance.

  2. #2
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    How about this?
    (Not tested. Just an idea.)
    Code:
    SELECT LPAD(series , 2) || ' series: ' ||
           LISTAGG( SUBSTR(series , 4) , ', ' )
              WITHIN GROUP(ORDER BY rnum) AS display_list
     FROM  (SELECT series
                 , ROW_NUMBER() OVER() AS rnum
             FROM  t
           )
     GROUP BY
           LPAD(series , 2)
     ORDER BY
           MIN(rnum)
    ;

  3. #3
    Join Date
    Dec 2005
    Posts
    59
    Provided Answers: 1
    SELECT LPAD(series , 2) || ' series: ' ||
    LISTAGG( SUBSTR(series , 4) , ', ' )
    WITHIN GROUP(ORDER BY rnum) AS display_list
    FROM (SELECT series
    , ROW_NUMBER() OVER() AS rnum
    FROM tblSeries
    )
    GROUP BY
    LPAD(series , 2)
    ORDER BY
    MIN(rnum)

    Your PL/SQL syntax error: "ORA-30485: missing ORDER BY expression in the window specification" when the query script executed. I got working code in SQL Server, but I am newbie to PL/SQL. Therefore, test it before posting. Thanks anyway.
    Last edited by avt2k6; 01-24-12 at 21:22.

  4. #4
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    ..., test it before posting.
    Please don't expect too much and perfect answer from me.
    Because, I joined this forum voluntary and not paied anything.(I think almost every members were same.)

    If I should test all sample codes before posting, it would force me too much work and I might forgive to publish the sample code.
    Then you might not get any progress for your issue, even now(one day passed).

    Anyhow, you can try to debug my sample code by yourself.
    Or, try another example, like...
    Code:
    SELECT LPAD(series , 2) || ' series: ' ||
           LISTAGG( SUBSTR(series , 4) , ', ' )
              WITHIN GROUP( ORDER BY TO_NUMBER(SUBSTR(series , 4)) ) AS display_list
     FROM  t
     GROUP BY
           LPAD(series , 2)
     ORDER BY
           MIN( TO_NUMBER(SUBSTR(series , 4)) )
    ;

  5. #5
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    >Therefore, test it before posting.
    You could make testing possible, by posting CREATE TABLE statement & INSERT statements
    so we can run code against your tables & your data.
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  6. #6
    Join Date
    Dec 2005
    Posts
    59
    Provided Answers: 1
    Hello,

    I understand what you mean. I used to help another members from other forum in ASP.NET, silverlight, crystal report, etc...if I knew the solution. Almost forums that share our programmer experiences. Your code is small, so I don't think that it takes long to run it. You posted with syntax error, so I just responded my feedback. Please be positive.

    Quote Originally Posted by tonkuma View Post
    Please don't expect too much and perfect answer from me.
    Because, I joined this forum voluntary and not paied anything.(I think almost every members were same.)

    If I should test all sample codes before posting, it would force me too much work and I might forgive to publish the sample code.
    Then you might not get any progress for your issue, even now(one day passed).

    Anyhow, you can try to debug my sample code by yourself.
    Or, try another example, like...
    Code:
    SELECT LPAD(series , 2) || ' series: ' ||
           LISTAGG( SUBSTR(series , 4) , ', ' )
              WITHIN GROUP( ORDER BY TO_NUMBER(SUBSTR(series , 4)) ) AS display_list
     FROM  t
     GROUP BY
           LPAD(series , 2)
     ORDER BY
           MIN( TO_NUMBER(SUBSTR(series , 4)) )
    ;

  7. #7
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    Even for syntax checking, it will take some work.

    For example:
    (1) Start Server, if it was not up.
    (2) Start client and connect to Server.
    (3) Construct create table statements for tables(sometimes guessing datatypes of columns) used in the query,
    then execute/debug them.
    It may be not necessary to populate the tables for syntax checking only.
    (4) Finally, I can try to execute the query.
    If there were syntax error(s), need to see wheather the problem(s) in my create table statements or in the query.


    I thought that a questionar should reduce as much as possible those extra work of persons who want to help you,
    if you want more help from more peoples.
    One of basic information which you could supply would be what anacedent described.
    Quote Originally Posted by anacedent View Post
    >Therefore, test it before posting.
    You could make testing possible, by posting CREATE TABLE statement & INSERT statements
    so we can run code against your tables & your data.

  8. #8
    Join Date
    Dec 2005
    Posts
    59
    Provided Answers: 1
    Thank you for your new code after my feedback. I tested it and worked in Oracle 11g perfectly, but LISTAGG function was not working in Oracle 10g.
    Last edited by avt2k6; 02-02-12 at 12:21.

Posting Permissions

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