Page 1 of 1
Netezza: select all columns with name like %X from Y% tables
Posted: Thu Feb 01, 2018 3:30 pm
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?
Posted: Thu Feb 01, 2018 4:14 pm
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
Posted: Mon Feb 05, 2018 10:30 am
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]
Posted: Mon Feb 05, 2018 11:49 am
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)
Posted: Mon Feb 05, 2018 11:58 am
by jackson.eyton
awesome! Thanks a ton, doing that now
Posted: Mon Feb 05, 2018 12:27 pm
by Raftsman
Use the "After" subroutine and run an Unix command to remove last UNION.