Re: SQR Not finding Rows -Reply

[Mark Kraft <kraftm@GUNET.GEORGETOWN.EDU>]

You can check this out if you would like, but for some reason, it just all of
a suddent stopped liking the comments at the end.  I took them out and it
started working fine again.  I have no idea why, but here you go.

Mark Kraft

!************************************************************************
begin-procedure get_journal_header
!************************************************************************
   let $journal_found = 'N'

begin-select
JH.BUSINESS_UNIT
JH.JOURNAL_ID
JH.BBF_DATE
JH.FISCAL_YEAR
JH.UNPOST_SEQ
JH.JOURNAL_DATE
JH.POSTED_DATE

  let $journal_found = 'Y'
  do process-main

FROM ps_jrnl_header JH
where JH.bbf_date is null
AND JH.JRNL_HDR_STATUS = 'P'                    !Added 10/21/98
AND JH.SOURCE != 'SQR'                          !Added 10/21/98
AND JH.TRANS_REF_NUM != 'FJ'                    !Added 10/21/98
AND JH.JOURNAL_ID = '0000003427'                !For Testing Purposes
ONLY
order by JH.business_unit, JH.journal_id
end-select
end-procedure get_journal_header

SQL Version

select
JH.BUSINESS_UNIT,
JH.JOURNAL_ID,
JH.BBF_DATE,
JH.FISCAL_YEAR,
JH.UNPOST_SEQ,
JH.JOURNAL_DATE,
JH.POSTED_DATE,
FROM ps_jrnl_header JH
where JH.bbf_date is null
AND JH.JRNL_HDR_STATUS = 'P'
AND JH.SOURCE != 'SQR'
AND JH.TRANS_REF_NUM != 'FJ'
AND JH.JOURNAL_ID = '0000003427'
order by JH.business_unit, JH.journal_id


>>> Tony DeLia <tdelia@EROLS.COM> 01/06/99 08:17pm >>>
Hi Mark,
   I like interesting problems... Can you forward the SQR begin-select
statement as well as the SQL*Plus version... Without actually seeing the
two I can only guess a bind variable (if there's any used at all) isn't
set to what you think it is... A mispelled bind variable could wreak
havoc... Just a thought...
                                      -Tony DeLia

Mark Kraft wrote:
>
> Ok, I have a rather interesting problem,
> I have an SQR that has worked in the past, but all of a sudden, it is not
> retrieving any rows from the database.  I know this sounds like an
easy
> question, but here is the kicker.  I Cut & Paste the SQL from the SQR
into
> SQL*Plus in the same database instance, and the SQL brings back the
> correct rows.  I have set a flag in the SQR to see if any rows are being
> fetched and the flag shows that there are no rows fetched.  Does
> Anyone have any ideas why this could be happening.  Thanks ahead
of
> time.
>
> Mark Kraft
> mkraft@gunet.georgetown.edu

--
Tony DeLia
AnswerThink Consulting Group
PeopleSoft Solutions Practice - Delphi Partners
tdelia@erols.com