Results 1 to 4 of 4
  1. #1
    Join Date
    Jan 2004
    Location
    Perth WA
    Posts
    2

    Unanswered: Formatting SQL output

    hi

    I have a table that looks like this

    DESC ONFIL:

    PPNAME VARCHAR2(8)
    DESCRIPTN VARCHAR2(40)
    PRIORITY NUMBER(2)
    OPER VARCHAR2(3)
    STARTIME VARCHAR2(6)
    ENDTIME VARCHAR2(6)
    ONFIL_DATA NUMBER(8,2)

    The STARTIME and ENDTIME columns have values such as 161548 which is 16:15:48 in 24 hour time. I wish to display it in SQL as 16:15:48 but can't figure out how.

    I have tried the column STARTIME FORM 00:00:00 But is invalid, also tried using TO_CHAR and TO_DATE.

    Appreciate any help with this one

  2. #2
    Join Date
    Apr 2003
    Location
    Greenville, SC (USA)
    Posts
    1,155
    Try

    sql> select substr(STARTIME,1,2)||':'||substr(STARTIME,3,2)||' :'||substr(STARTIME,5,2) from ...

    Since your column definition if VARCHAR, you can't use date functions
    or to_char funcations on the column ... You have to treat it like a "REGULAR" character datatype...

    HTH
    Gregg

  3. #3
    Join Date
    Jan 2004
    Location
    Perth WA
    Posts
    2
    my entire SQL is:

    SET LINESIZE 200
    CLEAR COLUMNS
    SELECT PPNAME, DESCRIPTN, PRIORITY, OPER, STARTIME, ENDTIME, ENDTIME-STARTIME "TIME TAKEN", ONFIL_DATA
    FROM ONFIL WHERE ONFIL_DATA = &ONFIL_DATA;

    running "Select substr(STARTIME,1,2)||':'||substr(STARTIME,3,2)||'
    :'||substr(STARTIME,5,2) from onfil" errors with "single row subquery returns more than one row"

    Works if I run by itself

    cheers

  4. #4
    Join Date
    Jan 2004
    Location
    India
    Posts
    62
    Single row sub query returns more than one row.
    Are you using the out put of this query for another query? If so, you will get this error due to multiple rows selection.
    Check the where condition and your data.
    If you still get this problem, please write complete code here.
    Regards,

    Rushi

Posting Permissions

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