Database

Mike Ellison5-May-04 7:40

Mike Ellison

5-May-04 7:40

Hi Keith. Oracle does have its own set of tools for connecting as well, including its own Enterprise Manager[^] and sql query client.

Chris Meech5-May-04 7:44

Chris Meech

5-May-04 7:44

When you install Oracle Client software, you usually have the opportunity to specify one of several flavours of installation. These range from a simple user installation, to a full blown administrator installation. One of the products installed during the admin installation is called, 'Enterprise Manager Console'. While it's not the best tool in the world, it does suffice for a lot of stuff, like password changes, schema changes, etc.

Chris Meech

We're more like a hobbiest in a Home Depot drooling at all the shiny power tools, rather than a craftsman that makes the chair to an exacting level of comfort by measuring the customer's butt. Marc Clifton

VB is like a toolbox, in the hands of a craftsman, you can end up with some amazing stuff, but without the skills to use it right you end up with Homer Simpson's attempt at building a barbeque or his attempt at a Spice rack. Michael P. Butler

Keith Vitali5-May-04 8:59

Keith Vitali

5-May-04 8:59

Hi Chris,

Thanks for the reply.

I must have installed the InstantClient package from Oracle. However, I do not know which is the regular Oracle client package that I need to install. Can you point me a link to where I can download and find more about this client. I miss SQL Server... I hate corporate policies.

Thanks,

Keith

Chris Meech5-May-04 9:30

Chris Meech

5-May-04 9:30

InstantClient is a separate package from the 10G database package. If you follow the links to download 10G, you eventually will get to a page from where you can download the Oracle Database 10g Client Release 1 (10.1.0.2) for Microsoft Windows (32-bit)[^]. I hope this is what you are looking for. You might have to signon to OTN first in order to access that link, though.

Chris Meech

We're more like a hobbiest in a Home Depot drooling at all the shiny power tools, rather than a craftsman that makes the chair to an exacting level of comfort by measuring the customer's butt. Marc Clifton

VB is like a toolbox, in the hands of a craftsman, you can end up with some amazing stuff, but without the skills to use it right you end up with Homer Simpson's attempt at building a barbeque or his attempt at a Spice rack. Michael P. Butler

Keith Vitali5-May-04 10:11

Keith Vitali

5-May-04 10:11

Hi there,

Thanks Chris. I hope this is what I need. I will find out tomorrow morning Big Grin | :-D

Thanks again,

Keith

Max and Where

OmegaSupreme5-May-04 1:28

5-May-04 1:28

I'm using max and where in my select statement but the problem is I want the max of all the dates for a given id but only want to return those rows that fall between certain dates.

A simplified version of the query is something like this :

<br />
SELECT MAX(BookingDate.bookingDate) AS MaxDate, MIN(BookingDate.bookingDate) AS MinDate<br />
<br />
FROM Booking <br />
<br />
WHERE (Booking.fkChannelId = ? ) AND (BookingDate.bookingDate > ?)  AND (BookingDate.bookingDate < ?)<br />
<br />

But I'd like the max and min dates for all the bookings for a given channel id but only want to return rows that fall within the dates.

Life is hard Confused | :confused:

Try not! Do or do not, there is no try. - Master Yoda

Mike Dimmick5-May-04 1:54

Mike Dimmick

5-May-04 1:54

Without knowing your data schema (you refer to two tables Booking and BookingDate in your example, but you don't join them) it's a bit difficult to know how to help.

GROUP BY and HAVING clauses may be helpful. GROUP BY allows data to be aggregated across groups, and HAVING allows you to select from the aggregated data. If WHERE applies to the data in the table, HAVING applies to the aggregated data.

Stability. What an interesting concept. -- Chris Maunder

WoutL5-May-04 1:57

WoutL

5-May-04 1:57

Just try:

SELECT MAX(BookingDate.bookingDate) AS MaxDate, MIN(BookingDate.bookingDate) AS MinDate

<br />
FROM Booking <br />
HAVING (BookingDate.bookingDate > ?) AND (BookingDate.bookingDate < ?)<br />
WHERE (Booking.fkChannelId = ? ) <br />

Maybe the HAVING part belongs after WHERE...

Wout Louwers

OmegaSupreme5-May-04 3:28

5-May-04 3:28

Thank you both for your help.

I tried adding the HAVING clause instead of WHERE and got the following errors:

Column 'BookingDate.bookingDate' is invalid in the HAVING clause because it is not contained in either an aggregate function or the GROUP BY clause.
Column 'Booking.fkChannelId' is invalid in the HAVING clause because it is not contained in either an aggregate function or the GROUP BY clause.

The tables I have are as follows each with a one to many relationship with the following table:

Ad
	pkAdId
Booking
	pkBookingId
	fkAdId
BookingDate
	pkBookingDateId
	fkBookingId
BookingHour
	pkBookingHourId
	fkBookingDateId

and heres the query as it stands :

SELECT DISTINCT 
		                      Ad.pkAdId, Ad.fileName, Ad.name,
 Ad.length, Ad.lastUpdated, MAX(BookingDate.bookingDate) AS MaxDate, MIN(BookingDate.bookingDate) AS MinDate, 
		                      AVG(BookingHour.bookedSlots + BookingHour.bonusSlots) AS AverageSlots
		FROM         Ad INNER JOIN
		                      Booking ON Ad.pkAdId = Booking.fkAdId INNER JOIN
		                      BookingDate ON Booking.pkBookingId = BookingDate.fkBookingId INNER JOIN
		                      BookingHour ON BookingDate.pkBookingDateId = BookingHour.fkBookingDateId
		
		WHERE  (BookingDate.bookingDate >= @startDate) 
		AND (BookingDate.bookingDate <= @endDate) 
		AND (Booking.fkChannelId = @channelId) AND
		GROUP BY Ad.pkAdId, Ad.fileName, Ad.name, Ad.length, Ad.lastUpdated
		ORDER BY Ad.fileName

Try not! Do or do not, there is no try. - Master Yoda

WoutL5-May-04 3:50

WoutL

5-May-04 3:50

Try:

<br />
SELECT DISTINCT <br />
Ad.pkAdId, Ad.fileName, Ad.name, Ad.length, Ad.lastUpdated,<br />
MAX(BookingDate.bookingDate) AS MaxDate,<br />
MIN(BookingDate.bookingDate) AS MinDate,<br />
AVG(BookingHour.bookedSlots + BookingHour.bonusSlots) AS AverageSlots<br />
FROM Ad <br />
INNER JOIN Booking ON Ad.pkAdId = Booking.fkAdId <br />
INNER JOIN BookingDate ON Booking.pkBookingId = BookingDate.fkBookingId<br />
INNER JOIN BookingHour ON BookingDate.pkBookingDateId = BookingHour.fkBookingDateId<br />
WHERE  (Booking.fkChannelId = @channelId)<br />
GROUP BY Ad.pkAdId, Ad.fileName, Ad.name, Ad.length, Ad.lastUpdated<br />
Having MinDate >= @startDate)<br />
AND MaxDate <= @endDate)<br />
ORDER BY Ad.fileName<br />

Wout Louwers

OmegaSupreme5-May-04 4:00

5-May-04 4:00

Hi Wout,

Thanks for your reply, unfortunatley Query Analyser complains :

Invalid column name 'MinDate'.
Invalid column name 'MaxDate'.

Also I want those bookings between the dates not whose max and min booking dates fall between the dates.

Sorry to be a pain and thanks again.

Try not! Do or do not, there is no try. - Master Yoda

WoutL5-May-04 4:06

WoutL

5-May-04 4:06

<br />
SELECT DISTINCT <br />
Ad.pkAdId, Ad.fileName, Ad.name, Ad.length, Ad.lastUpdated,<br />
MAX(BookingDate.bookingDate) AS MaxDate,<br />
MIN(BookingDate.bookingDate) AS MinDate,<br />
AVG(BookingHour.bookedSlots + BookingHour.bonusSlots) AS AverageSlots<br />
FROM Ad <br />
INNER JOIN Booking ON Ad.pkAdId = Booking.fkAdId <br />
INNER JOIN BookingDate ON Booking.pkBookingId = BookingDate.fkBookingId<br />
INNER JOIN BookingHour ON BookingDate.pkBookingDateId = BookingHour.fkBookingDateId<br />
WHERE (Booking.fkChannelId = @channelId)<br />
GROUP BY Ad.pkAdId, Ad.fileName, Ad.name, Ad.length, Ad.lastUpdated<br />
Having MAX(BookingDate.bookingDate) >= @startDate<br />
AND MIN(BookingDate.bookingDate) <= @endDate)<br />
ORDER BY Ad.fileName<br />

??

Wout Louwers

OmegaSupreme5-May-04 4:39