Netezza: select all columns with name like %X from Y% tables

A forum for discussing DataStage<sup>®</sup> basics. If you're not sure where your question goes, start here.

Moderators: chulett, rschirm, roy

Post Reply
jackson.eyton
Premium Member
Premium Member
Posts: 145
Joined: Thu Oct 26, 2017 10:43 am

Netezza: select all columns with name like %X from Y% tables

Post by jackson.eyton »

First off, forgive me if this is not the right place for this, I'm not sure where else to ask, and please feel free to flag and close if this is inappropriate for this forum.

I am looking to have a query that can pull the values of certain columns from certain tables. I would like this to be as flexible as possible however so as we add more of these tables the query wouldn't need to be changed. So essentially something like this:

Code: Select all

Select (SELECT COLUMN_NAME
FROM _V_SYS_COLUMNS 
WHERE 
TABLE_NAME like 'XFMD%' 
AND SCHEMA = 'ADMIN' 
AND COLUMN_NAME like '%BK') as Column_Name
FROM (SELECT DISTINCT TABLE_NAME
FROM _V_SYS_COLUMNS 
WHERE 
TABLE_NAME like 'XFMD%'
AND DATABASE = 'VALHALLA'
AND SCHEMA = 'ADMIN') as Source_Table
obviously this does not work and my SQL coding experience is a bit limited unfortunately. I am sure this is a logic approach problem but I can't seem to get my own head past my preconceived concept here. Could anyone point me in the right direction perchance?
-Me
jackson.eyton
Premium Member
Premium Member
Posts: 145
Joined: Thu Oct 26, 2017 10:43 am

Post by jackson.eyton »

Ok, so this might be a better approach, basically looking to self generate the SQL code to do the selection. The following gets me fairly close, I would like to consolidate all the rows returned from this into a single string and concat 'UNION' to string them all together.
Select ('Select '||TI.COLUMN_NAME||' as BANK FROM '||TI.TABLE_NAME) as SQL_QUERY
FROM (SELECT * FROM _V_SYS_COLUMNS WHERE TABLE_NAME like 'XFMD%' AND SCHEMA = 'ADMIN' AND COLUMN_NAME like '%BK') as TI
-Me
jackson.eyton
Premium Member
Premium Member
Posts: 145
Joined: Thu Oct 26, 2017 10:43 am

Post by jackson.eyton »

Code: Select all

Select ('Select '||'cast('||TI.COLUMN_NAME||' as varchar(256))'||' as BANK, '||''''||TI.COLUMN_NAME||''''||' as COLUMNNAME, '||''''||TI.TABLE_NAME||''''||' as TABLENAME FROM '||TI.TABLE_NAME||' UNION') as SQL_QUERY
FROM (SELECT * FROM _V_SYS_COLUMNS WHERE TABLE_NAME like 'XFMD%' AND SCHEMA = 'ADMIN' AND COLUMN_NAME like '%BK%' AND TYPE_NAME NOT IN ('DATE','TIME','TIMESTAMP')) as TI
So this more or less works for what I wanted, I do have to manually copy the results and remove the last UNION. I'm sure there is a way to run this, get a count, then loop through the results based on the count to add UNION to all but the final result. In powershell, C#, or python it'd be pretty easy, I'm just not familiar enough with netezza's somewhat different sql.[/quote]
-Me
gsbrown
Premium Member
Premium Member
Posts: 148
Joined: Mon Sep 23, 2002 1:00 pm
Location: USA

Post by gsbrown »

Hey! Another Netezza User :D
You might try posting this to the "Enzee" community. It's active and should be able to get quick help with this there.

https://www.ibm.com/developerworks/comm ... 73767a71a5 - Overview - Data Warehouse Community (includes Enzee | PureData Community)
jackson.eyton
Premium Member
Premium Member
Posts: 145
Joined: Thu Oct 26, 2017 10:43 am

Post by jackson.eyton »

awesome! Thanks a ton, doing that now :-)
-Me
Raftsman
Premium Member
Premium Member
Posts: 335
Joined: Thu May 26, 2005 8:56 am
Location: Ottawa, Canada

Post by Raftsman »

Use the "After" subroutine and run an Unix command to remove last UNION.
Jim Stewart
Post Reply