[sqr-users] Re: PS Query

["jiju vengal" <jijucvengal@gmail.com>]

Hi,
As remarked by Stphen, you are not joing the EMPLID in OA_PAY_CHECK
which is requried and you may have to take max(PAY_END_DT) in
OA_PAY_CHECK.


On 4/8/06, Edwin Hommes <edwin.hommes@bluewin.ch> wrote:
> Depending on your version, probably 8.4 or above, the table PS_PERSONAL_DATA
> is a view or a table that should be updated by a nightly batchprogram, so be
> carefull about using it. It may not reflect the current data.
>
> ----- Original Message -----
> From: "Alexander, Steven" <Steven.Alexander@sanjoseca.gov>
> To: "'This list is for discussion about the SQR database
> reportinglanguagefrom Hyperion Solutions.'" <sqr-users@sqrug.org>
> Sent: Friday, 07 April, 2006 23:19
> Subject: RE: [sqr-users] PS Query
>
>
> > You didn't specify D.EMPLID = C.EMPLID in the "not exists" subquery.  You
> > are including terminated employees if Payroll doesn't terminate their
> direct
> > deposit (our Payroll department doesn't).  Also, if PS_PERSON_NAME gives
> you
> > performance problems, you could consider using PS_PERSONAL_DATA.
> >
> > -----Original Message-----
> > From: veena kolanu [mailto:veena_kolanu@hotmail.com]
> > Sent: Friday, April 07, 2006 1:35 PM
> > To: sqr-users@sqrug.org
> > Subject: [sqr-users] PS Query
> >
> > Hi All,
> >   I am new to this group and wondering someone can help me with the query
> I
> > am working on.
> > I need to develop a query that shows any people off assignment for 90 days
> > or more that have an direct deposit.
> >  This is what my sql looks like and let me  know if this is correct or
> not.
> >
> > Thanks
> >
> > SELECT C.NAME, C.EMPLID  FROM PS_PERSON_NAME C, PS_PERS_SRCH_QRY C1,
> > PS_DIRECT_DEPOSIT B  WHERE C.EMPLID = C1.EMPLID    AND C1.ROWSECCLASS =
> > 'DPALL'    AND ( C.EMPLID = B.EMPLID     AND B.EFFDT =        (SELECT
> > MAX(B_ED.EFFDT) FROM PS_DIRECT_DEPOSIT B_ED        WHERE B.EMPLID =
> > B_ED.EMPLID          AND B_ED.EFFDT <= SYSDATE)     AND B.EFF_STATUS = 'A'
> > AND NOT EXISTS (SELECT 'X'  FROM PS_OA_PAY_CHECK D  where D.PAY_END_DT >
> > SYSDATE-90))  order by 1;
> > _________________________________________________________________
> > Because e-mail on your cell phone should be easy:  Try Windows Live Mail
> for
> > Mobile beta
> > http://www2.imagine-msn.com/minisites/mail/Default.aspx?locale=en-us
> >
> > _______________________________________________
> > sqr-users mailing list
> > sqr-users@sqrug.org
> > http://www.sqrug.org/mailman/listinfo/sqr-users
> >
> >
> >
>
>
> _______________________________________________
> sqr-users mailing list
> sqr-users@sqrug.org
> http://www.sqrug.org/mailman/listinfo/sqr-users
>

_______________________________________________
sqr-users mailing list
sqr-users@sqrug.org
http://www.sqrug.org/mailman/listinfo/sqr-users