Hi David,
Still doesnt work...!!
Let me give some more details...
Your query first takes the record which has max(effv_date) date and if
max(effv_date) > SYSDATE then it omits the product. This shouldn't happen.
Example:
* * *Product_id * * effv_date* * zone* 868735 1-Jul-09 2 868735
4-Aug-09 2 868735 15-Jul-09 2 868735 26-Aug-09 2
For the above given product: 868735 in a zone 2 has max(effv_date) =
26-Aug-09. Which is more than SYSDATE so your query drops the product..
*Desired out:*
*Product_ID effv_date zone*
868735 1-Jul-09 2
*In short*: First, we have to select the record which has effv_date <
SYSDATE group by product_id, zone. Among the resultant records we need to
select max(effv_date) records.
On Fri, Jul 10, 2009 at 7:00 PM, ddf <orat...@msn.com> wrote:
>
>
>
> On Jul 10, 6:03 am, Mayank <mayank.del...@gmail.com> wrote:
> > hi,
> >
> > hope u'll come out from ur problem..
> >
> > SELECT PRODUCT_ID,ZONE,MAX(EFFV_DATE)
> > FROM PL_GRP
> > WHERE EFFV_DATE < TO_DATE('13-JUL-09','DD-MON-YY') -- 13-JUL-09 TAKEN
> > AS SYSDATE
> > GROUP BY PRODUCT_ID,ZONE
> >
> > Mayank
> >
> > On Jul 10, 3:43 pm, Roopesh S <sro...@gmail.com> wrote:
> >
> >
> >
> > > Hi There,
> >
> > > I want to select a product_id for a zone which has a max effv_date
> which is
> > > less then sysdate. Please consider that sysdate is '13-July-2009'
> >
> > > PS: There can be many products which in the same zone. As given in the
> > > example below.
> >
> > > *Example data:*
> >
> > > * * *product_id* * zone* * effv_date * 123
> 1
> > > 10-Jul-09 123 1 11-Jul-09 123 1 12-Jul-09 234 1 9-Jul-09 234 1 8-Jul-09
> 456
> > > 2 5-Jul-09 456 2 6-Jul-09 * *
> > > *Output expected*
> > > * *
> > > *product_id* *zone* *effv_date* 123 1 12-Jul-09 234 1 9-Jul-09 456 2
> > > 6-Jul-09
> >
> > > Please let me know your inputs..
> >
> > > Thanx'N'Regards
> > > Roopesh- Hide quoted text -
> >
> > - Show quoted text -
>
> SQL> --
> SQL> -- Create table
> SQL> --
> SQL>
> SQL> create table proddt(
> 2 product_id number,
> 3 zone number,
> 4 eff_date date
> 5 );
>
> Table created.
>
> SQL>
> SQL> --
> SQL> -- Populate table
> SQL> --
> SQL>
> SQL> insert all
> 2 into proddt
> 3 values(123, 1, to_date('10-Jul-09','dd-mon-rr'))
> 4 into proddt
> 5 values(123, 1, to_date('11-Jul-09','dd-mon-rr'))
> 6 into proddt
> 7 values(123, 1, to_date('12-Jul-09','dd-mon-rr'))
> 8 into proddt
> 9 values(234, 1, to_date('9-Jul-09','dd-mon-rr'))
> 10 into proddt
> 11 values(234, 1, to_date('8-Jul-09','dd-mon-rr'))
> 12 into proddt
> 13 values(456, 2, to_date('5-Jul-09','dd-mon-rr'))
> 14 into proddt
> 15 values(456, 2, to_date('6-Jul-09','dd-mon-rr'))
> 16 select * from dual;
>
> 7 rows created.
>
> SQL>
> SQL> commit;
>
> Commit complete.
>
> SQL>
> SQL> --
> SQL> -- Doesn't give correct results
> SQL> -- but does provide desired results for
> SQL> -- sample data provided
> SQL> --
> SQL> -- Query ignores all data with an EFF_DATE
> SQL> -- greater than SYSDATE
> SQL> --
> SQL>
> SQL> SELECT PRODUCT_ID,ZONE,MAX(EFF_DATE)
> 2 FROM PRODDT
> 3 WHERE EFF_DATE < TO_DATE('13-JUL-09','DD-MON-YY') -- 13-JUL-09
> TAKEN AS SYSDATE
> 4 GROUP BY PRODUCT_ID,ZONE ;
>
> PRODUCT_ID ZONE MAX(EFF_DATE)
> ---------- ---------- --------------------
> 123 1 12-JUL-2009 00:00:00
> 234 1 09-JUL-2009 00:00:00
> 456 2 06-JUL-2009 00:00:00
>
> SQL>
> SQL> --
> SQL> -- Provides desired data
> SQL> -- and provides correct results regardless
> SQL> -- of value for SYSDATE
> SQL> --
> SQL>
> SQL> select product_id, zone, max(eff_date) eff_date
> 2 from proddt
> 3 group by product_id, zone
> 4 having max(eff_date) < to_date('13-jul-09','dd-mon-rr')
> 5 /
>
> PRODUCT_ID ZONE EFF_DATE
> ---------- ---------- --------------------
> 123 1 12-JUL-2009 00:00:00
> 234 1 09-JUL-2009 00:00:00
> 456 2 06-JUL-2009 00:00:00
>
> SQL>
> SQL> --
> SQL> -- Insert more data
> SQL> --
> SQL>
> SQL> insert all
> 2 into proddt
> 3 values(123, 1, to_date('15-Jul-09','dd-mon-rr'))
> 4 into proddt
> 5 values(123, 1, to_date('14-Jul-09','dd-mon-rr'))
> 6 into proddt
> 7 values(123, 1, to_date('16-Jul-09','dd-mon-rr'))
> 8 into proddt
> 9 values(234, 1, to_date('19-Jul-09','dd-mon-rr'))
> 10 into proddt
> 11 values(234, 1, to_date('18-Jul-09','dd-mon-rr'))
> 12 into proddt
> 13 values(456, 2, to_date('15-Jul-09','dd-mon-rr'))
> 14 into proddt
> 15 values(456, 2, to_date('16-Jul-09','dd-mon-rr'))
> 16 select * from dual;
>
> 7 rows created.
>
> SQL>
> SQL> commit;
>
> Commit complete.
>
> SQL>
> SQL> --
> SQL> -- Run queries again
> SQL> --
> SQL> -- No records should be returned according to
> SQL> -- specification by OP that max(eff_date) must be
> SQL> -- less than SYSDATE
> SQL> --
> SQL>
> SQL> --
> SQL> -- Gives same results as before
> SQL> -- even though max(eff_date) is greater than
> SQL> -- SYSDATE
> SQL> --
> SQL>
> SQL> SELECT PRODUCT_ID,ZONE,MAX(EFF_DATE)
> 2 FROM PRODDT
> 3 WHERE EFF_DATE < TO_DATE('13-JUL-09','DD-MON-YY') -- 13-JUL-09
> TAKEN AS SYSDATE
> 4 GROUP BY PRODUCT_ID,ZONE ;
>
> PRODUCT_ID ZONE MAX(EFF_DATE)
> ---------- ---------- --------------------
> 123 1 12-JUL-2009 00:00:00
> 234 1 09-JUL-2009 00:00:00
> 456 2 06-JUL-2009 00:00:00
>
> SQL>
> SQL> --
> SQL> -- Gives correct results
> SQL> --
> SQL>
> SQL> select product_id, zone, max(eff_date) eff_date
> 2 from proddt
> 3 group by product_id, zone
> 4 having max(eff_date) < to_date('13-jul-09','dd-mon-rr')
> 5 /
>
> no rows selected
>
> SQL>
>
>
>
> David Fitzjarrell
> >
>
--
Thanx'N'Regards
Roopesh
--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google
Groups "Oracle PL/SQL" group.
To post to this group, send email to Oracle-PLSQL@googlegroups.com
To unsubscribe from this group, send email to
oracle-plsql-unsubscr...@googlegroups.com
For more options, visit this group at
http://groups.google.com/group/Oracle-PLSQL?hl=en
-~----------~----~----~----~------~----~------~--~---
