Re: Fetching 2 records in 1 record

[Arun Kumar <arunkumar13@YAHOO.COM>]

I think the best way to handle is to use on-break on
the emplid and move the benefit plan to variables if
they have two plans.


--- Larry Roux <LRoux@SYR.EDU> wrote:
> In  Sybase you can do something like this:
>
> select
> A.FIELD1,
> A.FIELD2,
> (select B.FIELD1 from TABLE2 B where B.KEY = A.KEY)
> from TABLE1 A
> where A.FIELD1 = 'somevalue'
>
> the use-a-select-as-a-field portion, I believe, can
> only return 1 value.
>
> Not sure if this works in other RDBMS systems
>
> -Larry Roux
>  Syracuse University
>
>
> ******************************
> Larry Roux
> Syracuse University
> lroux@syr.edu
> *******************************
>
> >>> jtran@CALSTATE.EDU 02/15/02 01:21PM >>>
> If you decide to use creating view and join it with
> the original table to get
> the result, be careful because you will get multiple
> rows for each employee
> depending on the number of available benefit plans
> that he/she is enrolled in
> (i.e. providing that SSN is the group field and each
> SSN is enrolled in N
> available benefit plans, the total number of rows
> you will get is N*(N-1) rows
>
> ) There is another way is using array to store the
> number of benefits plan
> extracted from the tables. The array can be defined
> either in SQL or SQR
> depending on the data structure/way of doing things
> at your shop. This array
> allows you to display multiple benefit plans for the
> same person/SSN on the
> same line.
>
> Jennifer
>
>
>
> "Johnson, Dan" wrote:
>
> >         If I am reading this correctly you could
> do the following:
> >
> > Create a view with all of the records that you
> need. Then join the view to
> > itself based on key fields like so:
> >
> > select [columns from a],[columns from b]
> > from view_1 a,view_1 b
> > where a.key_field = b.key_field (it looks like
> your key field is EMPLID)
> > and a.benefit_plan < b.benifit_plan
> > and (maybe some other predicates)
> >
> > This may not be to efficient, even with tuning,
> but it should work if I am
> > understanding you correctly.
> >
> > Hope this helps,
> > Dan
> >
> > -----Original Message-----
> > From: M G [mailto:email_to_madan@YAHOO.COM]
> > Sent: Thursday, February 14, 2002 7:06 PM
> > To: SQR-USERS@list.iex.net
> > Subject: Fetching 2 records in 1 record
> >
> > Hi All,
> > I have a problem with my SQR, thought I should
> send it
> > to you, before spending lots of time on it.
> > Please try to help me in this problem.
> >
> > I have a SQR which fetches 2 records for every
> > employee for 2 different plans. Is it possible to
> pull
> > out both the records in one record?
> >
> > Please have a look into the SQL.
> >
> > SELECT A.SSN, A.EMPLID, B.DED_CUR, B.BENEFIT_PLAN
> >   FROM PS_PAY_CHECK A, PS_PAY_DEDUCTION B,
> > PS_PAY_CALENDAR C, PS_EMPLOYMENT D,
> PS_SAVINGS_PLAN E
> >   WHERE A.COMPANY = 'ABC'
> >      AND A.PAY_END_DT = '2002-01-06'
> >      AND A.COMPANY = B.COMPANY
> >      AND A.PAYGROUP = B.PAYGROUP
> >      AND A.PAY_END_DT = B.PAY_END_DT
> >      AND A.OFF_CYCLE = B.OFF_CYCLE
> >      AND A.PAGE_NUM = B.PAGE_NUM
> >      AND A.LINE_NUM = B.LINE_NUM
> >      AND A.SEPCHK = B.SEPCHK
> >      AND B.BENEFIT_PLAN IN ('EIP1','EIP2','SIP')
> >      AND B.DED_CUR > 0
> >      AND B.COMPANY = C.COMPANY
> >      AND B.PAYGROUP = C.PAYGROUP
> >      AND B.PAY_END_DT = C.PAY_END_DT
> >      AND D.EMPLID = A.EMPLID
> >      AND D.EMPL_RCD = A.EMPL_RCD
> >      AND D.EMPLID = E.EMPLID
> >      AND D.EMPL_RCD = E.EMPL_RCD
> >      AND E.PCT_GROSS_ATAX BETWEEN 1 AND 10
> >      AND E.EFFDT =
> >         (SELECT MAX(E_ED.EFFDT) FROM
> PS_SAVINGS_PLAN
> > E_ED
> >         WHERE E.EMPLID = E_ED.EMPLID
> >           AND E.EMPL_RCD = E_ED.EMPL_RCD
> >           AND E.PLAN_TYPE = E_ED.PLAN_TYPE
> >           AND E.BENEFIT_NBR = E_ED.BENEFIT_NBR
> >           AND E_ED.EFFDT <= CURRENT DATE)
> >   ORDER BY 1
> >
> > SSN       EMPLID      DED_CUR      BENEFIT_PLAN
> > --------- ----------- ------------ ------------
> > 154742869 027492             50.00 SIP
> > 154742869 027492             61.73 EIP1
> > 199753852 038518              0.45 EIP1
> > 199753852 038518             12.00 SIP
> >
> > If you have noticed the SSN, you could see 2
> entries,
> > which means one for each Benefit Plan.
> > But I want the SQL in such a way that I should get
> > only one record.
> >
> > The result should be:
> >
> > SSN       EMPLID
> > --------- ----------- ------------ ------------
> > 154742869 027492             50.00 SIP
> 61.73
> > EIP1
> > 199753852 038518              0.45 EIP1
> 12.00
> > SIP
> >
> > Is it possible to do it either via SQL or via SQR?
> >
> > Please let me know at the earliest.
> > Thanks,
> > Madan.
> >
> > (Note: I have used some other table in order to
> get
> > some other values from those tables, so please
> ignore
> > those tables)
> >
> > __________________________________________________
> > Do You Yahoo!?
> > Send FREE Valentine eCards with Yahoo! Greetings!
> > http://greetings.yahoo.com


__________________________________________________
Do You Yahoo!?
Got something to say? Say it better with Yahoo! Video Mail
http://mail.yahoo.com