Hi Monks, I have a query that works when executed from a client such as Toad, but when i run it within a perl script using Spreadsheet::WriteExcel No results are returned.

I replaced the "problem" query with a simple one and it returned results. I did this to rule out that something else within the perl script was causing the issue.

Also, i'm definitely pointing to the same database.

select * from (
select
to_number(trim(substr(regexp_substr(accnt_info,':93B::ELIG//FAMT/.*',1
+,1),length(':93B::ELIG//FAMT/')+1)),'999999999999999D999999999999999'
+,' NLS_NUMERIC_CHARACTERS = '',.'' ') as ELIG_AMT,
CASE WHEN to_number(trim(substr(regexp_substr(accnt_info,':93B::AFFB//
+FAMT/.*',1,1),length(':93B::AFFB//FAMT/')+1)),'999999999999999D999999
+999999999',' NLS_NUMERIC_CHARACTERS = '',.'' ') IS NOT NULL THEN to_n
+umber(trim(substr(regexp_substr(accnt_info,':93B::AFFB//FAMT/.*',1,1)
+,length(':93B::AFFB//FAMT/')+1)),'999999999999999D999999999999999',' 
+NLS_NUMERIC_CHARACTERS = '',.'' ')
ELSE to_number(trim(substr(regexp_substr(accnt_info,':93C::AFFB//FAMT/
+ELIG/.*',1,1),length(':93C::AFFB//FAMT/ELIG/')+1)),'999999999999999D9
+99999999999999',' NLS_NUMERIC_CHARACTERS = '',.'' ') END as AFFB_AMT,
 ACT_N,
case substr(sec_info,1,4)
    when '/US/' then 'CUSIP'
    when '/GB/' then 'SEDOL'
    when 'ISIN' then 'ISIN'
else
    null
end as "SECURITY TYPE",
case substr(sec_info,1,4)
    when  '/US/' then SUBSTR(sec_info,5)
    when '/GB/' then SUBSTR(sec_info,5)
    when 'ISIN' then SUBSTR(sec_info,6)
else
    null
end as "SECURITY NAME",
to_date(substr(payd_info,length(':98A::PAYD//')+1,14),'YYYYMMDDHH24MIS
+S') as "CALL DATE",
CA_ID,
CUSTODIAN_NAME,
CORP,SEME_N,
ISO,
msg_function_c "MSG STATUS CODE",
LOTO_D as "LOTTERY DATE",
CRET_D "MSG DATE",
PUBL_D as "RESULTS DATE",
to_number(trim(substr(regexp_substr(ssb_info,':90B::[[:alnum:]]*//[[:a
+lnum:]]*/USD.*',1,1),length(':93B::AFFB//FAMT/USD')+1)),'999999999999
+999D999999999999999',' NLS_NUMERIC_CHARACTERS = '',.'' ')*100 as SSB_
+AMT, CA_STATUS,
to_number(trim(substr(regexp_substr(accnt_info,':93B::AFFB//UNIT/.*',1
+,1),length(':93B::AFFB//UNIT/')+1)),'999999999999999D999999999999999'
+,' NLS_NUMERIC_CHARACTERS = '',.'' ') as AFFB_UNIT_AMT,
to_number(trim(substr(regexp_substr(accnt_info,':93C::AFFB//FAMT/ELIG/
+.*',1,1),length(':93C::AFFB//FAMT/ELIG/')+1)),'999999999999999D999999
+999999999',' NLS_NUMERIC_CHARACTERS = '',.'' ') as AFFB_FAMT_AMT_ELIG
+ from
  ( select sdsm1.CA_STATUS,
    sdsm1.CA_ID,
    sdsm1.SRC_IP_ID,
    sdsm1.corp,
    sdsm1.iso,
    sdsm1.act_n as ACT_N,
    sdsm2.seme_n,
    sdsm2.CUSTODIAN_NAME,
    regexp_substr(SDSM2.MSG_BODY,'(:16R:ACCTINFO)\W+(:95[[:alnum:]]*)*
+\W+:97A::SAFE//'||SDSM1.ACT_N||'(.+?):16S:ACCTINFO',1,1,'n') as accnt
+_info,
    regexp_substr(regexp_substr(SDSM2.MSG_BODY,':16R:USECU\W+:35B:((/U
+S/)|(/GB/)|(ISIN ))([[:alnum:]]+)',1,1,'n'),'((/US/)|(/GB/)|(ISIN ))(
+[[:alnum:]]+)') as     sec_info,
    decode(sdsm2.src_ip_id,203863,regexp_substr(SDSM2.MSG_BODY,':16R:C
+ASHMOVE.*:90B::[[:alnum:]]+//[[:alnum:]]+/USD[[:alnum:],]+',1,1,'n'),
+null) as ssb_info,
    regexp_substr(SDSM2.MSG_BODY,':98[AC-Z]::PAYD//([[:digit:]]+)',1,1
+,'n') as payd_info,
    sdsm2.msg_body,
    sdsm2.msg_function_c,
    sdsm2.cret_d,
    sdsm1.PMT_D,
    sdsm1.LOTO_D,
    sdsm1.PUBL_D from (
                      SELECT /*+ ORDERED */ distinct FICA.ROW_STAT_C A
+S "CA_STATUS",
                      FICA.CA_ID AS "CA_ID",
                      SDSM.SRC_IP_ID,
                      SDSM.CORP_ID AS CORP,
                      SDSM.CAEV_TY_C AS ISO,
                      SDSA.ACT_N,
                      FIRST_VALUE(SDSM.SEME_N) over (partition by SDSM
+.SRC_IP_ID, SDSM.CORP_ID, SDSM.ACT_N ORDER BY SDSM.CRET_D DESC) AS LA
+ST_SEME,
                      FICD.PMT_D,
                      FICD.LOTO_D,
                      FICD.PUBL_D
                      FROM DATA FICA INNER JOIN TRANSACTION FICD ON FI
+CD.CA_ID = FICA.CA_ID
                      INNER JOIN MESSAGE SDSM ON FICD.SRC_IP_ID = SDSM
+.SRC_IP_ID
                      AND FICD.VEND_CA_ID = SDSM.CORP_ID AND SDSM.REC_
+T = '564'
                      AND SDSM.CAEV_TY_C IN ('DRAW','PCAL')
                      and SDSM.CRET_D between (case when trim(to_char(
+sysdate,'Day')) ='Monday' then trunc(SYSDATE -3) else trunc(SYSDATE -
+1) end)
                      and SYSDATE +1
                      AND SDSM.msg_function_c <> 'WITH'
                      INNER JOIN ACCOUNTS SDSA ON
                      FICD.SRC_IP_ID = SDSA.SRC_IP_ID
                      AND FICD.VEND_CA_ID = SDSA.CORP_ID
                      WHERE FICA.REQ_C = 'H'
                      ) sdsm1,
                        MESSAGE sdsm2
                        where sdsm2.corp_id = sdsm1.corp
                        and sdsm2.src_ip_id = sdsm1.src_ip_id
                        and sdsm2.seme_n = sdsm1.LAST_SEME
                        and regexp_substr(SDSM2.MSG_BODY,'(:16R:ACCTIN
+FO)\W+(:95[[:alnum:]]*)*\W+:97A::SAFE//'||SDSM1.ACT_N||'(.+?):16S:ACC
+TINFO',1,1,'n') is not null
    )
) where nvl(affb_amt,0) >= 0 OR nvl(AFFB_UNIT_AMT,0) >= 0
[download]

As you can see there are some analytic funtions and seems a little complex, but it doesn't fail. It just doesn't return any rows within the perl script. I thought perhaps there is some limitation with Spreadsheet::WriteExcel as to how complex a query can be or something.

Any help or tip would be very much appreciated.

Thanks