0

I have some SQL statements stored in our Netezza DWH. I want to run some of these (or at least one) from a new query.

I have tried a lot of things using Declare and such, but it seems not to be working on the Netezza.

This is a query that has been stored: 

SELECT 
    "CUST-NO (CUNO)" AS KEY_REFERENCE, 
    'TESTDB' AS REQ_NBR, 
    CASE 
       WHEN TRIM("VAT-ID (VATID)") = '' 
          THEN 'InValid' 
          ELSE 'Valid' 
    END AS VALIDATION
FROM
    PE_NL."Customer Name and Address Physical File (CIPNAME0)"
WHERE 
    "COUNTRY (CCNTRY)" = 'NL'

This is how to retrieve the query: 

SELECT QUERY_STR 
FROM MDM.DQM_REFERENCE_TESTDB 
WHERE SOURCE_SYSTEM = 'LOTUSN'

I want to create a query which looks up the 1st query and execute it. I could not find a similar question yet on this site (for Netezza). Hope that somebody can help me. Thanks.

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
D Bakker
  • 1
  • Have you tried with EXECUTE clause ?, is your problem similar to this ? [How to execute a SQL String that references a table variable?](https://stackoverflow.com/questions/12059327/how-to-execute-a-sql-string-that-references-a-table-variable) – Arnaud Peralta Feb 01 '19 at 14:17

1 Answers1

0

You have to create a stored procedure. Inside that one a whole new set of statements are possible. In this case the ‘execute immediate’ will probably do the trick. Read more here https://www.ibm.com/support/knowledgecenter/en/SSULQD_7.2.1/com.ibm.nz.sproc.doc/c_sproc_executing_dynamic_queries.html

Lars G Olsen
  • 1,093
  • 8
  • 11