Hi

I have a query that is spooling results to a text file via an automated process Monday to Friday. Below are the variables that are used in the query. The queries returns data in Oracle SQL Developer, but returns only the headers with no data when I use Toad for Oracle 17.0 Patch 1. When I check the variables and they do have the same data in Oracle SQL and Toad. The query is very long as it pulls data from many schema (I inherited this), so I just took 4 schema to show you what the query does. The automated process is using TOAD. Why am I getting no data in Toad ?


DEFINE nextDate = TO_CHAR(sysDate,'yyyymmdd') ;
DEFINE prevDateS = 'TO_CHAR(SYSDATE - ( CASE RTRIM(TO_CHAR(SYSDATE,''day'')) when ''monday'' THEN 3 ELSE 1 END ), ''yyyymmdd'')';

------QUERY BELOW

set verify off
set heading off
set pagesize 0
set linesize 1200
set echo off
set termout off
set trimspool on
set blanklines on
set feedback off
set trimout off
set embedded off
SPOOL C:users/Charges.txt

DEFINE nextDate = TO_CHAR(sysDate,'yyyymmdd') ;
DEFINE prevDateS = 'TO_CHAR(SYSDATE-(CASE RTRIM(TO_CHAR(SYSDATE,''day'')) when ''monday''THEN 3 ELSE 1 END ), ''yyyymmdd'')' ;

PROMPT TRAN_ID,SYSTEM_REF,BEARER,FEE_TYPE,CURRENCY,FEE,TERRITORY

SELECT TRAN_ID||';'||SYSTEM_REF||';'||BEARER||';'||FEE_TYPE||';'||CURRENCY||';'||FEE||';'||TERRITORY
FROM
(
(
SELECT ----------1------History-----
T.GPTRANID as TRAN_ID, R.REFERENCETEXT as SYSTEM_REF, C.BEARER,
C.PROMPT AS FEE_TYPE, C.CURRENCY, SUM(to_char(C.AMOUNT/100, 999999999999999.99)) as FEE,
'FCIBBBBB' as TERRITORY
FROM FCIBBBBB_HIST.GPTRAN T, FCIBBBBB_HIST.GPTRAN_CHARGE C, FCIBBBBB_HIST.GPTRAN_REFERENCE R, FCIBBBBB_HIST.GPTRAN_AMOUNT A, FCIBBBBB_HIST.gptran_parsedmessage pmsg
WHERE T.GPTRANID = C.GPTRANID AND T.GPTRANID = R.GPTRANID AND T.GPTRANID = A.GPTRANID AND T.gptranid = pmsg.gptranid
AND T.status IN ('Completed','Canceled')
AND R.referenceTag = 'SYSREF'
AND T.messageType in ('103','202' )
and A.Linkelmtags like '%TRANAMT%'
and pmsg.creater is null
and pmsg.tag = 'HISTMOVEDATE'
and pmsg.tagValue >= &prevDateS
GROUP BY T.GPTRANID, R.REFERENCETEXT, C.BEARER, C.PROMPT, C.CURRENCY
)
UNION ALL
(
SELECT -------1---------SendTo Swift-----
T.GPTRANID as TRAN_ID, R.REFERENCETEXT as SYSTEM_REF, C.BEARER,
C.PROMPT AS FEE_TYPE, C.CURRENCY, SUM(to_char(C.AMOUNT/100, 999999999999999.99)) as FEE,
'FCIBBBBB' as TERRITORY
FROM FCIBBBBB.GPTRAN T, FCIBBBBB.GPTRAN_CHARGE C, FCIBBBBB.GPTRAN_REFERENCE R, FCIBBBBB.GPTRAN_AMOUNT A
WHERE T.GPTRANID = C.GPTRANID AND T.GPTRANID = R.GPTRANID AND T.GPTRANID = A.GPTRANID
AND T.status IN ('SentTo SWIFT')
AND A.amountid = (select am.amountid from FCIBBBBB.gptran_amount am, FCIBBBBB.gptran_parsedmessage pmsg , FCIBBBBB.gptran g
where g.gptranid = pmsg.gptranid and pmsg.tag ='TRANAMT' and am.amountid = pmsg.amountid
and g.gptranid = T.gptranid
and pmsg.creater is null
)
AND R.referenceTag = 'SYSREF'
AND T.messageType in ('103','202' )
AND A.valueDate >= &prevDateS
AND A.valueDate < &nextDate
GROUP BY T.GPTRANID, R.REFERENCETEXT, C.BEARER, C.PROMPT, C.CURRENCY
)
UNION ALL
(
SELECT ----------2------History-----
T.GPTRANID as TRAN_ID, R.REFERENCETEXT as SYSTEM_REF, C.BEARER,
C.PROMPT AS FEE_TYPE, C.CURRENCY, SUM(to_char(C.AMOUNT/100, 999999999999999.99)) as FEE,
'FCIBJMKN' as TERRITORY
FROM FCIBJMKN_HIST.GPTRAN T, FCIBJMKN_HIST.GPTRAN_CHARGE C, FCIBJMKN_HIST.GPTRAN_REFERENCE R, FCIBJMKN_HIST.GPTRAN_AMOUNT A, FCIBJMKN_HIST.gptran_parsedmessage pmsg
WHERE T.GPTRANID = C.GPTRANID AND T.GPTRANID = R.GPTRANID AND T.GPTRANID = A.GPTRANID AND T.gptranid = pmsg.gptranid
AND T.status IN ('Completed','Canceled')
AND R.referenceTag = 'SYSREF'
AND T.messageType in ('103','202' )
and A.Linkelmtags like '%TRANAMT%'
and pmsg.creater is null
and pmsg.tag = 'HISTMOVEDATE'
and pmsg.tagValue >= &prevDateS
GROUP BY T.GPTRANID, R.REFERENCETEXT, C.BEARER, C.PROMPT, C.CURRENCY
)
UNION ALL
(
SELECT ----------2------SendTo Swift-----
T.GPTRANID as TRAN_ID, R.REFERENCETEXT as SYSTEM_REF, C.BEARER,
C.PROMPT AS FEE_TYPE, C.CURRENCY, SUM(to_char(C.AMOUNT/100, 999999999999999.99)) as FEE,
'FCIBJMKN' as TERRITORY
FROM FCIBJMKN.GPTRAN T, FCIBJMKN.GPTRAN_CHARGE C, FCIBJMKN.GPTRAN_REFERENCE R, FCIBJMKN.GPTRAN_AMOUNT A
WHERE T.GPTRANID = C.GPTRANID AND T.GPTRANID = R.GPTRANID AND T.GPTRANID = A.GPTRANID
AND T.status IN ('SentTo SWIFT')
AND A.amountid = (select am.amountid from FCIBJMKN.gptran_amount am, FCIBJMKN.gptran_parsedmessage pmsg , FCIBJMKN.gptran g
where g.gptranid = pmsg.gptranid and pmsg.tag ='TRANAMT' and am.amountid = pmsg.amountid
and g.gptranid = T.gptranid
and pmsg.creater is null
)
AND R.referenceTag = 'SYSREF'
AND T.messageType in ('103','202' )
AND A.valueDate >= &prevDateS
AND A.valueDate < &nextDate
GROUP BY T.GPTRANID, R.REFERENCETEXT, C.BEARER, C.PROMPT, C.CURRENCY
)
);
/
SPOOL OFF;