Re: most sold pc's query?

invalid.drukqs_at_NOpandoraSPAM.belgie said...
> Daniel Morgan wrote in news:3EBBF2AA.519BDEBF_at_exxesolutions.com:
>
> > Lets say I think you are a student and I have given you hints
> > sufficient for you to do your homework.
> >
> > And unless you have an alternative explanation for asking such a basic
> > question and not being able to ask a more senior co-worker I encourage
> > others to not do your homework for you.
>
> Yes I am a student. I am not asking here to do all my homework for me. I'm
> learning this. I just ask small things, nothing wrong with that. Who wants
> to help, ok, who doesn', also ok.
> This is how I become the number of most sold pc's in march:
>
> select max(count(pcnr))
> into :testblock.number_of_pcs
> from computerssold
> where date between '01-mar-03' and '31-mar-03'
> group by pcnr;
>
> But I want to be able to select the month. So when I push the button that
> has the trigger I like to see the most sold pc's of the month o which field
> I selected. So I click in order number 1 which has 1 pc sold in april. Next
> I click the button and then I have to see most pc's sold in april. Get it?
> I don't need a complete sollution here, any hints are good. Thx
>

There's one thing Oracle provides a lot of and that's date functions, including the LAST_DAY function. Pass it a date representing any day in a given month (the first is a good choice) and it will return the last day of that month.

For example,

  last_day(to_date('01-mar-03','dd-mon-rr'))

returns 31-Mar-2003 as a date.

Which reminds me, whenever you work with dates, it's a good idea to always supply a format mask instead of relying on the "default" format set in the database. Therefore, your WHERE clause becomes:

  where sold_date between to_date('01-mar-03','dd-mon-rr')...

In your form trigger, you build the date string for the first day of the month and pass that to the LAST_DAY function. Use those two dates in your WHERE clause.

-- 
/Karsten
DBA > retired > DBA