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

Open in new window

ASKER CERTIFIED SOLUTION
Denise FisherIT Technical Lead

Our community of experts have been thoroughly vetted for their expertise and industry experience.

Join our community to see this answer!
Unlock 1 Answer and 10 Comments.
Start Free Trial
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