Results 1 to 8 of 8

Thread: sql help needed

  1. #1
    Join Date
    Apr 2004
    Posts
    48

    Smile Unanswered: sql help needed

    Hi,

    I have a query which returns all the tables and their columns. I just need a blank line after each table. How to write the query to accomplish this ??

    The original query is like this :
    SELECT
    B.DBNAME,A.TBCREATOR, A.TBNAME , A.NAME

    FROM
    SYSIBM.SYSCOLUMNS A ,
    SYSIBM.SYSTABLES B

    WHERE A.TBNAME = B.NAME
    AND A.TBCREATOR = B.CREATOR
    AND DBNAME LIKE 'TOLT%'

    ORDER BY DBNAME , B.CREATOR , A.TBNAME , A.NAME

    Thanks

  2. #2
    Join Date
    Aug 2003
    Location
    Massachusetts, USA
    Posts
    106
    If I'm understanding your question, you may have to substitute your SELECT column list with this

    SELECT B.DBNAME, A.TBCREATOR, A.TBNAME , A.NAME ||CHR(10) || CHR(13)

  3. #3
    Join Date
    Apr 2004
    Posts
    48
    No I am afraid that may not help.

    Right now my query returns :
    TOLTACTC TDB2OLTA ADJ_TYP_AND_CD_DOM ABBR_NM
    TOLTACTC TDB2OLTA ADJ_TYP_AND_CD_DOM ADJ_TYP_AND_CD
    TOLTACTC TDB2OLTA ADJ_TYP_AND_CD_DOM NM
    TOLTACTC TDB2OLTA ADJ_TYP_AND_CD_DOM TXT
    TOLTACTC TDB2OLTA BLD_EFF_GRD_DOM ABBR_NM
    TOLTACTC TDB2OLTA BLD_EFF_GRD_DOM BLD_EFF_GRD_CD
    TOLTACTC TDB2OLTA BLD_EFF_GRD_DOM BLD_EFF_GRD_ID

    I want the output as :

    TOLTACTC TDB2OLTA ADJ_TYP_AND_CD_DOM ABBR_NM
    TOLTACTC TDB2OLTA ADJ_TYP_AND_CD_DOM ADJ_TYP_AND_CD
    TOLTACTC TDB2OLTA ADJ_TYP_AND_CD_DOM NM
    TOLTACTC TDB2OLTA ADJ_TYP_AND_CD_DOM TXT

    TOLTACTC TDB2OLTA BLD_EFF_GRD_DOM ABBR_NM
    TOLTACTC TDB2OLTA BLD_EFF_GRD_DOM BLD_EFF_GRD_CD
    TOLTACTC TDB2OLTA BLD_EFF_GRD_DOM BLD_EFF_GRD_ID


    Note the blank line after table ADJ_TYP_AND_CD_DOM .

    Thanks

  4. #4
    Join Date
    Apr 2004
    Posts
    48
    Any fresh ideas ?

    Thanks

  5. #5
    Join Date
    Aug 2002
    Location
    Charlotte NC
    Posts
    665
    Quote Originally Posted by MahendraSetty
    Any fresh ideas ?

    Thanks

    The only thing that I can think of is that if you know exactly at wich table you want the space then make two queries, something like

    select tablenames
    from systable
    where table_id between 1 and 30

    echo ''

    select tablenames
    from systable
    where table_id between 31 and 100

    But this will only work if you know exactly where you want the breaks and it could get time consuming if you have a lot of breaks to add.
    Jim
    ------------
    And back to SQL Server....I always find my way home
    View my Linkedin profile

  6. #6
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    I know this is not exactly what you want , but how about something like:

    SELECT
    B.DBNAME as DBNAME,A.TBCREATOR AS CREATOE, A.TBNAME , A.NAME

    FROM
    SYSIBM.SYSCOLUMNS A ,
    SYSIBM.SYSTABLES B

    WHERE A.TBNAME = B.NAME
    AND A.TBCREATOR = B.CREATOR
    AND DBNAME LIKE 'TOLT%'
    union
    SELECT
    DBNAME,CREATOR,NAME AS TBNAME, ' ' as NAME
    FROM
    SYSIBM.SYSTABLES
    WHERE DBNAME LIKE 'TOLT%'
    ORDER BY DBNAME , CREATOR , TBNAME , NAME

    Andy

  7. #7
    Join Date
    Apr 2004
    Posts
    48
    Thanks all,

    Andy's solution is quite close to what I want. I can edit the output created by Andy's sql and get what I want by using 1 or 2 edit commands.

    Again thanks to all.

    Have a good one
    Mahendra

  8. #8
    Join Date
    Jun 2002
    Location
    UK
    Posts
    525
    Here's one way to do it in SQL...
    Code:
    create table add_blanks_test
    ( col1 char(1), col2 char(2) )
    ;
    
    insert into add_blanks_test
    values ('A','A')
    ,      ('A','B')
    ,      ('A','C')
    ,      ('B','A')
    ,      ('B','B')
    ,      ('C','A')
    ;
    
    with numbered_rows ( col1, col2, rn ) as
    (
      select col1
      ,      col2
      ,      row_number() over(partition by col1 order by col1, col2 desc )
      from add_blanks_test
    )
    select case rn when 0 then '' else col1 end, col2
    from
    (
      select col1
      ,      col2
      ,      rn
      from   numbered_rows
      union all
      select col1
      ,      '' col2
      ,      rn
      from (
            select col1
            ,      0 rn
            from numbered_rows
            where rn = 1
           ) dummyrows
    ) temp2
    order by col1, rn desc
    ;
    But this is probably better done by editing the sql output...
    Code:
    db2 -x "select * from add_blanks_test" | awk '$1!=last{print "" ; last=$1}{print}'
    Last edited by Damian Ibbotson; 05-27-04 at 12:21. Reason: removed alias to 'case' value as col1 as wanted to order by the real col1

Posting Permissions

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