title: How use CASE syntac in Stored Procedure on Netezza
body: <p>i just a newbie in netezza (Running version netezza Release 7.1.0.1-P1 [Build 37788]), i have created query to load data from another database, this is my query :</p>
  
  <pre><code>CASE INSTR(B.WIFI.WIFI_DETAIL.LOCATION, ';') WHEN 0 THEN B.WIFI.WIFI_DETAIL.LOCATION ELSE BTRIM(SUBSTR(B.WIFI.WIFI_DETAIL.LOCATION, 1, INSTR(B.WIFI.WIFI_DETAIL.LOCATION, ';')-1)) END AS POI_NAME
  </code></pre>
  
  <p>it's run perfect, but when i put in stored procedure , this is have modified using the solution from @ScottMcG :</p>
  
  <pre><code>create or replace procedure SP_CREATE_TMP_F_WIFIID_AP_UTILISASI()
  LANGUAGE NZPLSQL RETURNS INT4 AS
  BEGIN_PROC
  
  DECLARE
  
  BEGIN
      /* CREATE TABLE TMP AP UTILISASI WITHOUT CLIENT */
      EXECUTE IMMEDIATE '
          create TABLE TMP_F_AP_UTILISASI as select AVG(ANN.THROUGPUT_V) as AVG_THROUGHPUT
              , SUM(ANN.BYTES_SENT) AS SUM_SENT
              , SUM(ANN.BYTES_RECIEVE) AS SUM_RECIEVE
              , (SUM(ANN.BYTES_RECIEVE)+SUM(ANN.BYTES_SENT)) AS TOTAL_AMOUNT
              , COUNT(ANN.C_MAC_ADDRESS) AS ASSOCIATED_CLIENT_COUNT
              , COUNT(ANN.USER_NAME) AS AUTHENTICATED_CLIENT_COUNT
              , MAX(ANN.TOTAL_WAKTU) AS MAX_SESSION_DURATION
              , ANN.AP_NAME
              , ANN.ASSO_TIME_V
              , ANN.PAKET_NAME
              , ANN.SSID
              , CASE INSTR(AD.LOCATION, ' || quote_literal (';') || ') WHEN 0 THEN AD.LOCATION ELSE BTRIM(SUBSTR(AD.LOCATION, 1, INSTR(AD.LOCATION, ' || quote_literal (';') || ')-1)) END AS POI_NAME
          FROM
              A.USR3.V_ALL_NMS_DETAIL AS ANN
          JOIN A.USR3.V_NMS_AP_DETAIL AS AD
              ON ANN.AP_NAME = AD.AP_NAME
          GROUP BY ANN.AP_NAME
              , ANN.ASSO_TIME_V
              , ANN.PAKET_NAME
              , ANN.SSID
              , AD.LOCATION
      ';
  
  END;
  
  END_PROC;
  </code></pre>
  
  <p>when i call the procedure, it's still not work :</p>
  
  <pre><code>ERROR: Cross Database Access not supported for this type of command
  </code></pre>
  
  <p>i call from database A, and the procedure build in A</p>
  
  <p>maybe you have idea for solved my problem,,,</p>
  
  <p>or maybe any one have another query to equal for this query :</p>
  
  <pre><code>CASE INSTR(AD.LOCATION, ' || quote_literal (';') || ') WHEN 0 THEN AD.LOCATION ELSE BTRIM(SUBSTR(AD.LOCATION, 1, INSTR(AD.LOCATION, ' || quote_literal (';') || ')-1)) END AS POI_NAME
  </code></pre>
  
  <p>Thx</p>