asked on
Explicit Cursor To .csv Not Returning Data
When I run the below sql script I am not getting any data.
set echo off
set wrap off
set verify off
set linesize 500
set heading off
set feedback off
set pagesize 0
set serveroutput on size unlimited
/* Write data to a file, if an error, the error message ends up in the file */
spool &2
declare
C_HEADER CONSTANT VARCHAR(149) DEFAULT 'PONUM~POLINENUM~DESCRIPTION~ORDERDATE~STATUS~VENDOR~'||
'CONTRACTREFNUM~ORDERQTY~UNITCOST~LINECOST~RECEIVEDQTY~'||
'GLDEBITACCT~PROJECTID~PROJECT_TASK~SITEID';
C_DELIMITER CONSTANT VARCHAR(01) DEFAULT '~';
/* Variables */
V_DATA VARCHAR(205);
/* Flat File Variables */
V_PONUM VARCHAR(07);
V_POLINENUM VARCHAR(03);
V_DESCRIPTION VARCHAR(50);
V_ORDERDATE VARCHAR(08);
V_STATUS VARCHAR(25);
V_VENDOR VARCHAR(11);
V_CONTRACTREF VARCHAR(04);
V_ORDERQTY VARCHAR(11);
V_UNITCOST VARCHAR(16);
V_LINECOST VARCHAR(16);
V_RECEIVEDQTY VARCHAR(11);
V_GLDEBITACCT VARCHAR(18);
V_POLALN1 VARCHAR(10);
V_POLALN2 VARCHAR(10);
V_SITEID VARCHAR(05);
/* PO DATA */
CURSOR POLINE_CUR IS
select distinct(poline.ponum), poline.polinenum, poline.description, po.orderdate, po.status, companies.name as vendor_name, poline.contractrefnum,
poline.orderqty, poline.unitcost, poline.linecost, poline.receivedqty, poline.gldebitacct,
poline.polaln1, poline.polaln2, poline.siteid
from maximo.poline poline
inner join maximo.po po
on poline.ponum = po.ponum
and poline.SITEID = po.siteid
and poline.orgid = po.orgid
and poline.revisionnum = po.revisionnum
left outer join maximo.companies companies
on po.vendor = companies.company
and po.orgid = companies.orgid
WHERE PO.CHANGEDATE > (select nvl(max(EXPORTEDDATE), sysdate) FROM miftab.GA_PODATA_SQL_LOG where EXPORTEDDATE is not null)
and (poline.gldebitacct like '%654000' or poline.gldebitacct like '%654100' or poline.gldebitacct like '%654200')
order by ponum, polinenum;
POLINE_REC POLINE_CUR%ROWTYPE;
BEGIN
DBMS_OUTPUT.ENABLE(500000000);
/* Open File and Populate Flat File */
OPEN POLINE_CUR;
LOOP
BEGIN
FETCH POLINE_CUR INTO POLINE_REC;
IF POLINE_CUR%NOTFOUND
THEN
CLOSE POLINE_CUR;
EXIT;
END IF;
V_DATA := C_HEADER; -- 001 - 205
DBMS_OUTPUT.PUT_LINE (V_DATA);
V_DATA := ' ';
-- PO Number
V_PONUM := LPAD(NVL(POLINE_REC.PONUM, ' '),7,'0');
-- PO Line Number
V_POLINENUM := LPAD(NVL(POLINE_REC.POLINENUM, 0), 3, '0');
-- Poline Description
V_DESCRIPTION := NVL(POLINE_REC.DESCRIPTION, ' ');
-- Order Date
V_ORDERDATE := NVL(TO_CHAR(POLINE_REC.ORDERDATE,'YYYYMMDD'),' ');
-- Status
V_STATUS := NVL(POLINE_REC.STATUS,' ');
-- Vendor Name
V_VENDOR := NVL(POLINE_REC.VENDOR_NAME,' ');
-- Contract Reference Number
V_CONTRACTREF := NVL(POLINE_REC.CONTRACTREFNUM, ' ');
-- Quantity Ordered
V_ORDERQTY := LPAD(NVL(TRIM(TO_CHAR(POLINE_REC.ORDERQTY,'S0000000.00')),' '),11, '0');
-- Unit Cost
V_UNITCOST := LPAD(NVL(TRIM(TO_CHAR(POLINE_REC.UNITCOST,'S00000000.000000')),' '),16, '0');
-- Line Cost
V_LINECOST := LPAD(NVL(TRIM(TO_CHAR(POLINE_REC.LINECOST,'S00000000.000000')),' '),16, '0');
-- Received Qty
V_RECEIVEDQTY := LPAD(NVL(TRIM(TO_CHAR(POLINE_REC.RECEIVEDQTY,'S0000000.00')),' '),11, '0');
-- GL Debit Account
V_GLDEBITACCT := NVL(SUBSTR(POLINE_REC.GLDEBITACCT,1,4) ||
SUBSTR(POLINE_REC.GLDEBITACCT,6,4) ||
SUBSTR(POLINE_REC.GLDEBITACCT,11,4) ||
SUBSTR(POLINE_REC.GLDEBITACCT,16,6), ' ');
-- Project ID
V_POLALN1 := LPAD(NVL(POLINE_REC.POLALN1, ' '), 10);
-- Task ID
V_POLALN2 := LPAD(NVL(POLINE_REC.POLALN2, ' '), 10);
-- Site ID
V_SITEID := NVL(POLINE_REC.SITEID, ' ');
V_DATA := V_PONUM ||
C_DELIMITER ||
V_POLINENUM ||
C_DELIMITER ||
V_DESCRIPTION ||
C_DELIMITER ||
V_ORDERDATE ||
C_DELIMITER ||
V_STATUS ||
C_DELIMITER ||
V_VENDOR ||
C_DELIMITER ||
V_CONTRACTREF ||
C_DELIMITER ||
V_ORDERQTY ||
C_DELIMITER ||
V_UNITCOST ||
C_DELIMITER ||
V_LINECOST ||
C_DELIMITER ||
V_RECEIVEDQTY ||
C_DELIMITER ||
V_GLDEBITACCT ||
C_DELIMITER ||
V_POLALN1 ||
C_DELIMITER ||
V_POLALN2 ||
C_DELIMITER ||
V_SITEID;
/* Write Record */
DBMS_OUTPUT.PUT_LINE (V_DATA);
END LOOP;
CLOSE POLINE_CUR;
END;
END;
spool off
DB2SQL
Last Comment
slightwv (䄆 Netminder)