Re: Fetching 2 records in 1 record

[Larry Roux <LRoux@SYR.EDU>]

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