niXforums Forum Index
 FAQFAQ   SearchSearch   MemberlistMemberlist   UsergroupsUsergroups   RegisterRegister 
 ProfileProfile   PreferencesPreferences   Log in to check your private messagesLog in to check your private messages   Log inLog in 
·  nixdoc.net ·  man pages ·  Linux HOWTOs ·  FreeBSD Tips ·  Forums
navigation Forum index » Databases » Oracle
SQL Statement Help
Post new topic   Reply to topic Page 1 of 1 [9 Posts] View previous topic :: View next topic
Author Message
Kobee
*nix forums beginner


Joined: 06 Jul 2006
Posts: 3

PostPosted: Thu Jul 06, 2006 2:23 pm    Post subject: SQL Statement Help Reply with quote
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...
Back to top
psoug
*nix forums Guru


Joined: 15 May 2005
Posts: 3492

PostPosted: Thu Jul 06, 2006 3:52 pm    Post subject: Re: SQL Statement Help Reply with quote
Kobee wrote:
Quote:
Hi,

I've been trying to get this SQL, but I just can't get it to work.

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
Back to top
Kobee
*nix forums beginner


Joined: 06 Jul 2006
Posts: 3

PostPosted: Thu Jul 06, 2006 4:22 pm    Post subject: Re: SQL Statement Help Reply with quote
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:
Quote:
Kobee wrote:
Hi,

I've been trying to get this SQL, but I just can't get it to work.

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
Back to top
nkunkov@escholar.com
*nix forums beginner


Joined: 14 Jun 2006
Posts: 14

PostPosted: Thu Jul 06, 2006 6:13 pm    Post subject: Re: SQL Statement Help Reply with quote
Kobee wrote:
Quote:
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:
Hi,

I've been trying to get this SQL, but I just can't get it to work.

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


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
Back to top
psoug
*nix forums Guru


Joined: 15 May 2005
Posts: 3492

PostPosted: Thu Jul 06, 2006 7:26 pm    Post subject: Re: SQL Statement Help Reply with quote
Kobee wrote:
Quote:
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:
Hi,

I've been trying to get this SQL, but I just can't get it to work.
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

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
Back to top
Jim Kennedy
*nix forums Guru


Joined: 30 Apr 2005
Posts: 367

PostPosted: Fri Jul 07, 2006 3:58 am    Post subject: Re: SQL Statement Help Reply with quote
"Kobee" <dbmdata@gmail.com> wrote in message
news:1152202945.932701.169190@a14g2000cwb.googlegroups.com...
Quote:
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:
Hi,

I've been trying to get this SQL, but I just can't get it to work.

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

Why are you doing:

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
Back to top
wannabe DBA
*nix forums beginner


Joined: 30 May 2006
Posts: 11

PostPosted: Sat Jul 08, 2006 8:43 am    Post subject: Re: SQL Statement Help Reply with quote
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:

Quote:
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...
Back to top
Kobee
*nix forums beginner


Joined: 06 Jul 2006
Posts: 3

PostPosted: Tue Jul 18, 2006 1:46 pm    Post subject: Re: SQL Statement Help Reply with quote
I'm sorry, I don't think I understand at all what you are saying. Can
you please explain?

Thanks.


wannabe RAC wrote:
Quote:
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:

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...
Back to top
Ed Prochak
*nix forums Guru Wannabe


Joined: 17 Mar 2005
Posts: 271

PostPosted: Tue Jul 18, 2006 8:26 pm    Post subject: Re: SQL Statement Help Reply with quote
Kobee wrote:
Quote:
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...

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
Back to top
Google
Back to top
Display posts from previous:   
Post new topic   Reply to topic Page 1 of 1 [9 Posts] View previous topic :: View next topic
The time now is Mon Dec 01, 2008 10:20 pm | All times are GMT
navigation Forum index » Databases » Oracle
Jump to:  

Similar Topics
Topic Author Forum Replies Last Post
No new posts Select statement Shamna Sybase 0 Mon Sep 17, 2007 6:03 am
No new posts ECPG (usage of simple select statement) Jasbinder Bali PostgreSQL 0 Fri Jul 21, 2006 3:28 am
No new posts tremendous upturn on pink sheets, overwhelmingly importan... Larry Hurd devel 0 Mon Jul 17, 2006 4:50 pm
No new posts Help for a SQL Query statement: group by roberto IBM DB2 3 Mon Jul 17, 2006 12:43 pm
No new posts what's wrong with my code? about the #if statement. Python.LeoJay@gmail.com C 7 Mon Jul 17, 2006 8:14 am

Debt Help | Free Ringtones | Credit Card Consolidation | Masini second hand | Loans
Copyright © 2004-2005 DeniX Solutions SRL
 
Other DeniX Solutions sites: Unix/Linux blog |  electronics forum |  medicine forum |  science forum | 
Privacy Policy


Powered by phpBB © 2001, 2005 phpBB Group

[ Time: 0.2421s ][ Queries: 16 (0.1338s) ][ GZIP on - Debug on ]