troubleshooting Question

Explicit Cursor To .csv Not Returning Data

Avatar of Denise Fisher
Denise Fisher asked on
DB2SQL
10 Comments1 Solution87 ViewsLast Modified:
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
Join the community to see this answer!
Join our exclusive community to see this answer & millions of others.
Unlock 1 Answer and 10 Comments.
Join the Community
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 10 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros