On Oct 24, 12:23 pm, Tim Slattery <Slatter...@bls.gov> wrote:

Perhaps the index_date not being in a group by?

Maybe you need a subquery that has the select for the max date in it.

jg
--
@home.com is bogus.
EXT4 Data Corruption Bug Hits Stable Linux Kernels
http://www.phoronix.com/scan.php?page=news_item&px=MTIxNDQ

Slatter...@bls.gov says...

If you only want the row for MAX(index_date) then you should SELECT
that, not use it in a HAVING clause.  Then you will need to GROUP BY
every other column that has not had an aggregation/function applied to
it.

OPEN cv_1 FOR
    SELECT
        item_code,
        MAX(index_date) as index_date,
        price,
        currency_code,
        price_basis,
        not_traded,
        price_estimated,
        no_change,
        is_valid
    FROM ipp_price
    WHERE item_code = v_code
    GROUP BY
        item_code
        price,
        currency_code,
        price_basis,
        not_traded,
        price_estimated,
        no_change,
        is_valid;

GM

Thanks, looks like that's it.

I'm converting an application from Sybase to Oracle. The SQL statement
I posted ran perfectly fine in Sybase. Different SQL dialects, I
suppose.

--
Tim Slattery
Slatter...@bls.gov

On Oct 25, 9:54 am, Tim Slattery <Slatter...@bls.gov> wrote:

Hmmm, yes, this thing says it's a t-sql extension to the standard (and
implies the Oracle one is also a different extension to the standard,
though I haven't thought it through):
http://infocenter.sybase.com/help/index.jsp?topic=/com.sybase.help.as...
v. http://docs.oracle.com/cd/E11882_01/server.112/e26088/statements_1000...

jg
--
@home.com is bogus.
http://flickrhivemind.net/

On Oct 25, 8:24 am, Geoff Muldoon <geoff.muld...@trap.gmail.com>
wrote:

Your misunderstanding of the group by concept, the purpose of which is
to aggragate over combinations of values from one or more columns (in
general.) By selecting a column without aggragation, you're saying you
want every row combination including values from that column.

So your query is saying give me a row for every combination of
item_code,
index_date, price, currency_code, price_basis, not_traded,
price_estimated,
no_change and is_valid, BUT only return one row for each item_code.

                   ^^^^^^^
You're assuming there is only one such row.

That will still return multiple rows for a given item_code if any of
the
other columns differ for different index_date entries.

It is similar to...

    select p1.item_code,
           p1.index_date,
           p1.price,
           p1.currency_code,
           p1.price_basis,
           p1.not_traded,
           p1.price_estimated,
           p1.no_change,
           p1.is_valid
      from ipp_price p1
     where p1.item_code = v_code
       and p1.index_date =
             (select max(p2.index_date)
                from ipp_price p2
               where p2.item_code       = p1.item_code
                 and p2.price           = p1.price
                 and p2.currency_code   = p1.currency_code
                 and p2.price_basis     = p1.price_basis
                 and p2.not_traded      = p1.not_traded
                 and p2.price_estimated = p1.price_estimated
                 and p2.no_change       = p1.no_change
                 and p2.is_valid        = p1.is_valid);

Whereas I suspect the OP wants something more akin to...

    select p1.item_code,
           p1.index_date,
           p1.price,
           p1.currency_cde,
           p1.price_basis,
           p1.not_traded,
           p1.price_estimated,
           p1.no_change,
           p1.is_valid
      from ipp_price p1
     where p1.item_code = v_code
       and p1.index_date =
             (select max(p2.index_date)
                from ipp_price p2
               where p2.item_code = p1.item_code);

This too assumes that item_code and index_date form a unique key on
ipp_price,
in which case, the following does the same but is generally more
efficient...

    select item_code,
           max(index_date)      keep (dense_rank last order by
index_date) as index_date,
           max(price)           keep (dense_rank last order by
index_date) as price,
           max(currency_code)   keep (dense_rank last order by
index_date) as currency_cde,
           max(price_basis)     keep (dense_rank last order by
index_date) as price_basis,
           max(not_traded)      keep (dense_rank last order by
index_date) as not_traded,
           max(price_estimated) keep (dense_rank last order by
index_date) as price_estimated,
           max(no_change)       keep (dense_rank last order by
index_date) as no_change,
           max(is_valid)        keep (dense_rank last order by
index_date) as is_valid
      from ipp_price
     where item_code = v_code
     group by item_code;

--
Peter