sql help needed

MahendraSetty · #1 (**permalink**) 04-29-04, 15:09

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

dmmac · #2 (**permalink**) 04-29-04, 16:07

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)

MahendraSetty · #3 (**permalink**) 04-29-04, 16:20

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

MahendraSetty · #4 (**permalink**) 05-26-04, 12:03

Any fresh ideas ?

Thanks

JDionne · #5 (**permalink**) 05-26-04, 13:09

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

ARWinner · #6 (**permalink**) 05-26-04, 13:10

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

MahendraSetty · #7 (**permalink**) 05-26-04, 13:40

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

Damian Ibbotson · #8 (**permalink**) 05-27-04, 11:09

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}'

Internet Services

Web Development

Digital Marketing

Consumer Tech