DJ Dunk

iTrader: (5)

Okay, I'm stuggling again, I think I may be over simplifying this one . . . still an Oracle n00b I'm afraid.

My current script is kind of like this:

select b.field1,
a.field2,
a.field3,
a.field4,
a.field5,
sum(decode(a.field7, 'S', 1, 0) SINGLE,
sum(decode(a.field7, 'M', 1, 0) MULT,
sum(decode(a.field7, null, 1, 0) BLANK,
from a.asset, b.company
where a.field6 = b.field6
group by b.field1, a.field2, a.field3, a.field4, a.field5
order by b.field1, a.field2, a.field3, a.field4, a.field5

Basically, I'm trying to summarise the contents of the fields with counts of the S's, M's and nulls in field 7 at the end of each unique row. My sums are way off. Any idea what I'm doing wrong ?

GCollier

I'd start by taking a look at your join condition:

a.field6 = b.field6

If there is more than one row in table b which satisfies the join for the value of a.field6, then the sums you'll end up with will be a multiple of the values you're expecting.

Gary.

DJ Dunk

iTrader: (5)

Thanks for the advice.

Bearing in mind that there are over 10 million records in the asset table, could I get away with no 'where' clause ? I don't mind if it takes a couple of hours to run.

jpor

iTrader: (1)

Not sure if Oracle uses it but in Informix you can use the 'DISTINCT' clause in your SELECT to only bring back one of a kind if you have multiples.

J4CKO

iTrader: (1)

Perhaps if you posted the table descriptions and a sample of the data ?

IanJ

The above would help. But perhaps you could try

select distinct b.field1,
a.field2,
a.field3,
a.field4,
a.field5,
sum(decode(a.field7, 'S', 1, 0) SINGLE,
sum(decode(a.field7, 'M', 1, 0) MULT,
sum(decode(a.field7, null, 1, 0) BLANK,
from a.asset, b.company
where a.field6 = b.field6
group by b.field1, a.field2, a.field3, a.field4, a.field5
order by b.field1, a.field2, a.field3, a.field4, a.field5

I would suggest for performance you might want to look at those order by and group by's

HTH,
Ian

DJ Dunk

iTrader: (5)

Thanks, I'll give it a go. The actual tables contain sensitive data which is why I'm a little vague, sorry.

Basically, 'field7' will have just S's, M's or nulls. Table 'b.company' has many rows returned per company name, which looks like the problem, so your distinct solution should do the trick

jpor

iTrader: (1)

No worries.