nzsql - Converting a subquery into columns for another select

Question

Goal: Use a given subquery's results (a single column with many rows of names) to act as the outer select's selection field.

Currently, my subquery is the following:

SELECT column_name
FROM information_schema.columns
WHERE table_name = 'test_table' AND column_name not in ('colRemove');

What I am doing in this subquery is grabbing all the column names from a table (i.e. test_table) and outputting all except for the column name specified (i.e. colRemove). As stated in the "goal", I want to use this subquery as such:

SELECT (*enter subquery from above here*)
FROM actual_table
WHERE (*enter specific conditions*)

I am working on a Netezza SQL server that is version 7.0.4.4. Ideally, I would like to make the entire query executable in one line, but for now, a working solution would be much appreciated. Thanks!

Note: I do not believe that the SQL extensions has been installed (i.e. arrays), but I will need to double check this.

Unless Netezza has some magic in it, you're going to need to use dynamic SQL to put this together. Do some research on that topic — Twelfth, Jul 31 '14 at 18:38
Okay, thanks Twelfth. I will try to figure out the Dynamic SQL answer to this and post it up once I do. — Zhouster, Jul 31 '14 at 22:13
If you need help, there are a huge number of examples on Stack Overflow for you to take from (there's usually one or 2 dynamic SQL questions asked daily). If you are struggling with any concept, post a comment with @twelfth in it to nofity me tag and I'll try to help. — Twelfth, Jul 31 '14 at 22:19
@Twelfth Thanks for the advice and offering of help. I have navigated a number of sources, but it seems that Netezza is a little different from most SQLs. I'm aware it's a Postgresql variation, but I seem to have trouble declaring a variable. I've read that it is only allowed in a stored procedure, and I was wondering if you could confirm this/shed any light on this. — Zhouster, Jul 31 '14 at 22:54
Unfortunately no...I've evaluated Netezza but have yet to have a chance to actually use one. One of the advantages to the netezza box is it comes with one heck of a support agreement...have you considered sending this their way? — Twelfth, Jul 31 '14 at 22:59
@Twelfth I haven't actually. I may consider that if I can't find a reasonable workaround. — Zhouster, Aug 01 '14 at 00:11
Some of their support guys wrote the code behind the FPGA's and can write their sql statements to optimize their use (the fact that they wouldn't share that information on their FPGA's is actually why their bid to supply us was declined). One of the few cases where I can say their support team (atleast in Canada) definately knows their stuff. — Twelfth, Aug 01 '14 at 16:43

score 0 · Answer 1 · answered Jul 27 '15 at 10:29

A year too late, here's the best I can come up with but, as you already noticed, it requires a stored procedure to do the dynamic SQL. The stored proc creates a view with the all the columns from the source table minus the one you want to exclude.

-- Create test data.
CREATE TABLE test (firstcol INTEGER, secondcol INTEGER, thirdcol INTEGER);   
INSERT INTO test (firstcol, secondcol, thirdcol) VALUES (1, 2, 3);
INSERT INTO test (firstcol, secondcol, thirdcol) VALUES (4, 5, 6);

-- Install stored procedure.
CREATE OR REPLACE PROCEDURE CreateLimitedView (varchar(ANY), varchar(ANY)) RETURNS BOOLEAN
LANGUAGE NZPLSQL AS
BEGIN_PROC
  DECLARE
    tableName ALIAS FOR $1;
    columnToExclude ALIAS FOR $2;

    colRec RECORD;
    cols VARCHAR(2000); -- Adjust as needed.
    isfirstcol BOOLEAN;
  BEGIN
    isfirstcol := true;
    FOR colRec IN EXECUTE
        'SELECT ATTNAME AS NAME FROM _V_RELATION_COLUMN
        WHERE 
            NAME=UPPER('||quote_literal(tableName)||')
            AND ATTNAME <> UPPER('||quote_literal(columnToExclude)||')
        ORDER BY ATTNUM'
    LOOP
        IF isfirstcol THEN
            cols := colRec.NAME;
        ELSE
            cols := cols || ', ' || colRec.NAME;
        END IF;
        isfirstcol := false;
    END LOOP;

    -- Should really check if 'LimitedView' already exists as a view, table or synonym.
    EXECUTE IMMEDIATE 'CREATE OR REPLACE VIEW LimitedView AS SELECT ' || cols || ' FROM ' || quote_ident(tableName);
    RETURN true;
  END; 
END_PROC 
; 

-- Run the stored proc to create the view.
CALL CreateLimitedView('test', 'secondcol');

-- Select results from the view.
SELECT * FROM limitedView WHERE firstcol = 4;

FIRSTCOL  |  THIRDCOL
----------+----------
4         |  6

You could have the stored proc return a resultset directly but then you wouldn't be able to filter results with a WHERE clause.

nzsql - Converting a subquery into columns for another select

1 Answers1