[Date Prev][Date Next][Thread Prev][Thread Next]
[Author Index]
[Date Index]
[Thread Index]
[SQR-USERS Info]
[SQRUG Home Page]
Re: SQR Error
Charu,
Please modify your SQL statement as below to get the desired result.
The subquery MUST have "company = a.company" to get the most effective date
for selected company. Now if you are matching the correct date picture, you
will get the desired result.
SELECT A.COMPANY, A.DESCRSHORT FROM PS_COMPANY_TBL A
WHERE A.COMPANY = $Company
AND A.EFFDT = (SELECT MAX(EFFDT) FROM PS_COMPANY_TBL
WHERE comapny = a.company
and EFFDT < =
to_date($AsOfDate,'DD-MON-YYYY')
)
Hope it helps you !
-Alok Kulshrestha
(202)623-9083
On Thursday, December 03, 1998 12:52 PM, Charu Srinivas
[SMTP:C._Srinivas/dsisys/US@DSISYS.COM] wrote:
: I encountered the same problem with one of my clients. The NLS_DATE_FORMAT
: set by the SETENV.SQC is "lost". The workaround that I used and the one
: that seems to work is to add an ALTER SESSION SET NLS_DATE_FORMAT within a
: BEGIN-SQL END-SQL block at the beginning of the SQR.
:
: Another strange thing that I noticed was when I used a to_char in a
: where clause of a select statement, for e.g.
: SELECT A.COMPANY, A.DESCRSHORT FROM PS_COMPANY_TBL
: WHERE A.COMPANY = $Company
: AND A.EFFDT = (SELECT MAX(EFFDT) FROM PS_COMPANY_TBL
: WHERE TO_CHAR(EFFDT,'DD-MON-YYYY') < =
: $AsOfDate
:
: This gave me rows only for two companies and not for all the three
: companies that the client has. I ran the same SQL statement in SQLPLUS( I
: put in a todya's date in the DD-MON-YYYY format instead of $AsOfDate) and
: still got only two rows. There is definitely data for the third company
: because when I removed the to_char from the where clause and hardcoded a
: two-digit year date in place of $AsOfDate, I got three distinct rows.
:
: So I removed the to_char from the SQR also and added the ALTER SESSION at
: the beginning and it works.
: I have found no particular solution to it other than having workarounds.
: If anyone has faced a similar problem and found a solution, please post it
: on the SQR-USERS group.
:
: Charu
:
:
:
:
: tdelia@EROLS.COM on 11/30/98 03:31:26 PM
:
: Please respond to SQR-USERS@USA.NET
:
: To: SQR-USERS@list.iex.net
: cc: (bcc: C. Srinivas/dsisys/US)
: Subject: Re: SQR Error
:
:
:
:
: Hello,
:
: It's quite a coincidence this 'ORA-01830 Date Picture Format' topic has
: come up... I've recently encountered this under some strange
: circumstances... Hopefully a "seasoned" Oracle DBA will read this and
: provide some insight on what's happening... Here's the details...
:
: - SQR and COBOL processes (in Production) "randomly" generate ORA-01830
: Errors (I'll focus on the SQR programs).
: - The SQR programs use SETENV.SQC and alters the NLS_DATE_FORMAT using
: 'DD-MON-YYYY'...
: - $AsOfDate is set at the beginning of the program in 'DD-MON-YYYY'
: format and does not change throughout the life of the program...
: - $AsOfDate is used in a number of SQL statements (SELECT, INSERT, etc)
: without a problem (because it is indeed a valid date)...
: - Thousands of rows are processed using $AsOfDate without fail...
: - Without warning the NLS_DATE_FORMAT set by SETENV.SQC is "lost"...
: Oracle reverts back to it's 'DD-MON-YY' format...
: - Since $AsOfDate is set in the beginning of the program as
: 'DD-MON-YYYY' it is "suddenly" invalid due to the mysterious date format
: change...
: - The client informed me they experienced the exact problem a year
: earlier without resolution... The problem has returned and I feel it
: should be dealt with... to change each and every SQR (and COBOL) process
: to accomodate this anomaly would be insane!!!
: - In the past they've added workarounds that weren't very effective and
: altered the original coding... Specifically they added a counter that
: re-executes the ALTER SESSION command every 100 rows/records... If the
: unpredicatable date format change occurs within 100 the SQL statement
: fails... they would run their process(es) over and over until it
: completed successfully (ignoring the real problem)... that's NUTS!!!!
:
: - As a "temporary" work-around to remain productive during this time I
: added TO_DATE functions against any date variables whenever possible...
: The SELECT statement changes were obvious... Here's a workaround for
: INSERTS that use the VALUE clause...
:
: Replace - INSERT INTO table
: VALUES ($AsOfDate,
: $PayEndDt);
:
: With - INSERT INTO table
: SELECT TO_DATE($AsOfDate,'{Native-Date}'),
: TO_DATE($PayEndDt,'{Native-Date}')
: FROM DUAL;
:
: Replacing the VALUES clause with a SELECT clause against the DUAL table
: allows you to use the TO_DATE function.
:
: Keep in mind... for this particular problem (where the NLS_DATE_FORMAT
: is lost before the end of the session)... the solution IS NOT coding
: workarounds - rather finding why the NLS_DATE_FORMAT is lost...
:
: Has anyone experienced this same problem??? and more importantly
: resolved it?!!!
: -Tony DeLia
:
:
:
:
: Ray Ontko wrote:
: >
: > Bill,
: >
: > I'm guessing that the problem is in your where clause.
: > If you want to compare a date variable to a string variable,
: > do an explicit conversion of the string to a date.
: > For example:
: >
: > > AND Effdt <= $ThruDate)
: >
: > should perhaps be
: >
: > AND effdt <= to_date($thrudate,'YYYYMMDD')
: >
: > The error you're getting is Oracle complaining that the default
: > date format (probably 'dd-mon-yy' on your system) doesn't match
: > the value you're passing.
: >
: > Hope this helps.
: >
: > Ray
: > > I am executing the following select and receiving the following
: sqr
: > > error. Anybody have any ideas?
: > >
: > > Bill
: > >
: > > (Bowersb@psph.providence.org)
: > >
: > >
: > >
: > > (SQR 5528) ORACLE OFETCH error -1830 in cursor 2:
: > > ORA-01830: date format picture ends before converting entire
: input
: > > string
: > >
: > > Error on line 240:
: > > (SQR 3725) Bad return fetching row from database.
: > >
: > > SQRW: Program Aborting.
: > >
: > >
: > > I am using Oracle and SQR version 3.
: > >
: > > Note: Line 240 is the begin-select statement
: > >
: > > begin-SELECT loops=50
: > > show '100 **************************************'
: > > !Personal Data
: > > PD.Name
: > > show '100 &pd.name: ' &pd.name
: > > PD.SSN
: > > PD.Address1
: > > PD.Address2
: > > PD.City
: > > PD.State
: > > PD.ZIP
: > > PD.Sex
: > > PD.Birthdate
: > > !to_char(PD.Birthdate, 'YYYYMMDD') &Birthdate
: > > PD.Emplid
: > > !Employment
: > > E.Hire_Dt
: > > !to_char(E.Hire_Dt, 'YYYYMMDD') &Hire_dt
: > > E.Termination_dt
: > > !to_char(E.Termination_Dt, 'YYYYMMDD') &Termination_dt
: > >
: > > !Job
: > > J.Emplid
: > > J.Empl_Rcd#
: > > J.Company
: > > J.Empl_Status
: > > J.Action_Dt
: > > J.Effdt
: > > J.Action
: > > !show '100 &j.action: ' &J.action
: > > J.Action_Reason
: > > !show '100 &j.action_reason: ' &J.action_reason
: > >
: > > !Health_benefit
: > > HB.Plan_Type
: > > do 1200-Write-Emp !Builds detail record
: > >
: > > HB.Benefit#
: > > hb.coverage_elect_dt
: > > hb.coverage_begin_dt
: > > hb.coverage_elect
: > > HB.Benefit_Plan
: > > HB.Covrg_Cd
: > >
: > > !Benefit Plan
: > > bp.Plan_type
: > >
: > >
: > > HB.Effdt
: > > !to_char(HB.Effdt, 'YYYYMMDD') &Effdt
: > > !to_char(HB.Coverage_Begin_Dt, 'YYYYMMDD') &covrg_begin_dt
: > > !to_char(hb.Coverage_Begin_Dt, 'MM/DD/YYYY')
&Print_Coverage_Date
: > >
: > > BP.Provider
: > >
: > >
: > > FROM PS_Personal_Data PD,
: > > PS_Employment E,
: > > PS_Job J,
: > > PS_Health_Benefit HB,
: > > PS_Benef_Plan_Tbl BP
: > >
: > > WHERE J.Effdt = (SELECT MAX(Effdt) FROM PS_Job
: > > WHERE Emplid = J.Emplid
: > > AND Empl_Rcd# = J.Empl_Rcd#
: > > AND Effdt <= $ThruDate)
: > > !AND Effdt <= $AsOfDate
: > > !and Action_dt between $FromDate and
$ThruDate)
: > > !and Action_dt >= $FromDate)
: > >
: > > AND J.Effseq = (Select MAX(Effseq) FROM PS_Job
: > > WHERE Emplid = J.Emplid
: > > AND Empl_Rcd# = J.Empl_Rcd#
: > > AND Effdt = J.Effdt)
: > > AND J.Empl_Rcd# = 0
: > > AND J.Company in [$Company_Reqstd]
: > > AND E.Emplid = J.Emplid
: > > AND E.Empl_Rcd# = 0
: > > AND PD.EmplID = J.EmplID
: > > AND HB.EmplID = J.EmplID
: > > AND HB.Empl_Rcd# = J.Empl_Rcd#
: > > AND HB.Benefit# = 0
: > > AND HB.Effdt = (SELECT Max(Effdt)
: > > FROM PS_Health_Benefit
: > > WHERE Emplid = HB.Emplid
: > > AND Empl_Rcd# = HB.Empl_Rcd#
: > > AND Plan_Type = HB.Plan_Type
: > > AND Benefit# = HB.Benefit#
: > > !and coverage_begin_dt is not null
: > > and coverage_begin_dt < = $ThruDate)
: > > !and effdt < = $ThruDate)
: > >
: > > AND HB.Coverage_Elect = 'E'
: > > AND (HB.Coverage_End_Dt IS NULL OR
: > > HB.Coverage_End_Dt > $ThruDate)
: > > AND BP.Plan_Type = HB.Plan_Type
: > > AND BP.Benefit_Plan = HB.Benefit_Plan
: > > AND BP.EffDt = (SELECT Max(EffDt)
: > > FROM PS_Benef_Plan_Tbl
: > > WHERE Plan_Type = BP.Plan_Type
: > > AND Benefit_Plan = BP.Benefit_Plan
: > > and effdt < = $ThruDate)
: > >
: > > AND BP.Provider = 'FRSTHE'
: > >
: > > ORDER BY PD.Name, HB.Plan_Type,HB.Benefit_Plan
: > >
: > > end-SELECT
: > >
: > > end-procedure
: > >
: >
: > ----------------------------------------------------------------------
: > Ray Ontko | Ray Ontko & Co | "Time for a new signature line."
: > rayo@ontko.com | Richmond, In | See us at http://www.ontko.com/
:
: --
: Tony DeLia
: AnswerThink Consulting Group
: PeopleSoft Solutions Practice - Delphi Partners
: tdelia@erols.com