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

In reply to Query Does not work in Spreadsheet::WriteExcel by dirtdog

Title:
Use:  <p> text here (a paragraph) </p>
and:  <code> code here </code>
to format your post, it's "PerlMonks-approved HTML":


  • Posts are HTML formatted. Put <p> </p> tags around your paragraphs. Put <code> </code> tags around your code and data!
  • Titles consisting of a single word are discouraged, and in most cases are disallowed outright.
  • Read Where should I post X? if you're not absolutely sure you're posting in the right place.
  • Please read these before you post! —
  • Posts may use any of the Perl Monks Approved HTML tags:
    a, abbr, b, big, blockquote, br, caption, center, col, colgroup, dd, del, details, div, dl, dt, em, font, h1, h2, h3, h4, h5, h6, hr, i, ins, li, ol, p, pre, readmore, small, span, spoiler, strike, strong, sub, summary, sup, table, tbody, td, tfoot, th, thead, tr, tt, u, ul, wbr
  • You may need to use entities for some characters, as follows. (Exception: Within code tags, you can put the characters literally.)
            For:     Use:
    & &amp;
    < &lt;
    > &gt;
    [ &#91;
    ] &#93;
  • Link using PerlMonks shortcuts! What shortcuts can I use for linking?
  • See Writeup Formatting Tips and other pages linked from there for more info.