Hi,

I've been trying to get this SQL, but I just can't get it to work. It
should a be a relatively simple SELECT on a single table, with 2
criteria - a date range for "lastupdate". Basically it's like "give me
all the records from this table where the lastupdate is between
startdate and enddate." There's one catch, though. There are three
types of "transaction_types" in this table: "Add", "Update", "Delete".
If the type is "Update", then there will be always be 2 records, with
possibly different lastupdate values. The catch is that this SQL has to
return the second "Update" row, even if it has a lastupdate outside of
the specified date range. What's the best way to accomplish this?

Thanks in advance for any help. My SQL is not up to par...

Kobee wrote:
Where is your SQL?

We can't help you with it if you don't post it.

And your Oracle version number?
--
Daniel A. Morgan
University of Washington
damorgan@x.washington.edu
(replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org

Oracle 9i

SELECT
PROGRAM_ID, PROGRAM_TYPE, ANNUALBUDGETTYPE, DESCRIPTION, NAME,
NVL(SPLIT_FEDERAL, 0) AS "SPLIT_FEDERAL", NVL(SPLIT_MUNICIPAL, 0) AS
"SPLIT_MUNICIPAL", NVL(SPLIT_PROVINCIAL, 0) AS "SPLIT_PROVINCIAL",
AFS_FLAG, CAPITALPLAN_FLAG, CER_FLAG, HPEMS_FLAG, START_DATE,
RENTSUP_FLAG,
BENCHMARK_FLAG, STATUS, NVL(SUBSIDY_CC, 0) AS "SUBSIDY_CC",
LASTUPDATE, NVL(SUBSIDY_CE, 0) AS "SUBSIDY_CE",
DATE_CREATED, TRANSACTION_TYPE, MODIFIED_BY, TRANSACTION_ID,
TRANSACTION_ORDER
FROM
PROGRAM_LOG
WHERE
TO_DATE(TO_CHAR(LASTUPDATE, 'yyyy-Mon-dd'), 'yyyy-Mon-dd') BETWEEN
TO_DATE(dtstartdate, 'yyyy-Mon-dd') AND TO_DATE(dtenddate,
'yyyy-Mon-dd')
OR
TRANSACTION_TYPE = 'U'
ORDER BY
TRANSACTION_ID,
TRANSACTION_ORDER,
LASTUPDATE DESC;


I' ve tried several approaches, but so far the "OR" statement is coming
closest to what I need. The only problem is that the OR clause will
return records whether or not there is a corresponding "Update" row
within the date range.

Thanks


DA Morgan wrote:

Kobee wrote:

I would split your query into two subqueries. One with the where
clause of a date range, the other with the transaction_type. Then
either join two subqueries or do a union. Hope it helps a bit.
NK

Kobee wrote:
1. Please do not top post.
2. OR clauses should always be inside parentheses so that Oracle knows
how you want them interpreted. What you've written here looks like
you need AND.
--
Daniel A. Morgan
University of Washington
damorgan@x.washington.edu
(replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org

"Kobee" <dbmdata (AT) gmail (DOT) com> wrote

TO_DATE(TO_CHAR(LASTUPDATE, 'yyyy-Mon-dd'), 'yyyy-Mon-dd')
just do
lastupdate between ...

Your conversion of a date to a char to a date might be causing a full table
scan where you don't need it.
Jim

Kobee,

I think the best approach here might be to use pl/sql create a function
which returns the value of the type first and pass this to a package
procedure which then gives the required output depending output from
the function.

Sql will this type of query very difficult to manage.
Kobee wrote:

I'm sorry, I don't think I understand at all what you are saying. Can
you please explain?

Thanks.


wannabe RAC wrote:

Kobee wrote:
It all boils down to problem solving skills.

Since teh rules for the Update types are different, put that in a
different query than the Add and Delete types. When you have both
subqueries working, just union the results together.

HTH,
ed