Re: Fine points on analytic functions

From: Roger Redford <dba_222_at_yahoo.com>
Date: 9 Nov 2004 08:09:52 -0800
Message-ID: <a8c29269.0411090809.28b6845@posting.google.com>

Thanks for responding.

I should point out, I'm not looking for someone to do my work for me.

What I'm hoping to find, is a response such as:

- use this function:
- with rank, this, that, the other thing
- in this kind of subquery
- etc.

from someone who speaks from experience, and has done something similar before.

Since I first posted this, I did come up with a way to do it all in one big query:

select some_field

	sum(  type1),
	count( type1 ),
	sum( some_code1 ),
	count( some_code1 )
	etc.

from 
	(	select
	CASE
		WHEN	LENGTH(field1) = 3
		THEN  1
		ELSE	0
	END	AS  type1,
	- same for length = 1


	CASE 
		when field1 = 'some_code1'
		THEN 		1
	      	Else       0
	end 	as   some_code1,

	- same for others.

	)

group by some_field

The problem is, I could not get the case statements to work in a stored procedure in version 817.

Am I incorrect on this? Is there a way to use the case statements in 817?

Will case statements work in stored procedures in 9?

As for the length of the codes meaning something, this is not something that anyone thought of. It means that the code comes from different sources. It was a lucky accident, and I can use this now. Each code has an exact meaning. And we can also use the length for summary information.

jkstill_at_gmail.com (Jared Still) wrote in message news:<bf4638.0411021120.5ef1652_at_posting.google.com>...
> You would get a lot further here if you provided DDL
> to create test data that is representative of your
> real data.
>
> This looks like an interesting challenge, but not many
> folks are inclined to invent test data for someone
> else's problem. :)
>
> Also, is this a custom app? If so, you need to do
> something about encoding information in the length
> of the data.
>
> Jared
>
> dba_222_at_yahoo.com (Roger Redford) wrote in message news:<a8c29269.0410270826.4b1df9af_at_posting.google.com>...
> > Dear experts,
> >
> > I have a complex report to create, and I'm wondering if
> > analytic functions can help me. I'm a newbie with them,
> > and I've been having some success, but I'm not sure if they
> > can help me here or not.
> >
> > There are two tables in a standard 1:M relationship.
> > Let's call the main table the master table. The
> > many table is let's say, master_history. In master_history,
> > we keep a log of all activity with the master table.
> >
> ...
Received on Tue Nov 09 2004 - 10:09:52 CST