Problem with a Tera Data Query

Database
skt
Fan

Problem with a Tera Data Query

Hi guys,

I am pretty new to this forum and Tera Data. I got a problem while writing a query.Please help me to sort this out.

I have three tables SUB_RAT,SUB_AGE,SUB_GEN. In three tables i have a common columns RAT_EN.And SUB_COUNT .
I have to Select the Distinct RAT_EN from Each table(As three tables have same values in RAT_EN) and its corrosponding MAX VALUE from Sub_COUNT1,Sub_COUNT2,Sub_COUNT3.

For eg;

max(SUB_COUNT)

RAT_EN SUB_COUNT1 SUB_COUNT2 SUB_COUNT3
car 1000 1500 1487
jeep 650 800 478
bus 4210 414 4514

Hope you understand my question... If any one want more clarification please ask.

I tried With the below Query. But it was showing some error "Syntax error: expected something between '(' and the 'SELECT' keyword." I am using a Tera DBMS

SELECT A.RAT_EN,

(SELECT max(sub_count) FROM UP_AALZEER.PS_V_SUB_RAT WHERE RAT_EN=A.RAT_EN) AS SUB_COUNT1,

(SELECT max(sub_count) FROM UP_AALZEER.PS_V_SUB_AGE WHERE RAT_EN=A.RAT_EN) AS SUB_COUNT2,

(SELECT max(sub_count) FROM UP_AALZEER.PS_V_SUB_GEN WHERE RAT_EN=A.RAT_EN) AS SUB_COUNT3

FROM UP_AALZEER.PS_V_SUB_AGE AS A

GROUP BY A.RAT_EN;

Please help me to sort this out.........
6 REPLIES
Enthusiast

Re: Problem with a Tera Data Query



Hi,

I think the below query solves your problem
SELECT A.RAT_EN,MAX(A.sub_count),MAX(B.sub_count),MAX(C.sub_count)
FROM
UP_AALZEER.PS_V_SUB_AGE A,
UP_AALZEER.PS_V_SUB_AGE B,
UP_AALZEER.PS_V_SUB_AGE C
WHERE A.RAT_EN = B.RAT_EN
AND A.RAT_EN = C.RAT_EN
GROUP BY A.RAT_EN;

cheers
skt
Fan

Re: Problem with a Tera Data Query



No,This Wont work. Becoz All the three columns MAX(A.sub_count),MAX(B.sub_count),MAX(C.sub_count) will get the same values.

As i tried it is getting the saome values...

Any other suggestions.
Enthusiast

Re: Problem with a Tera Data Query

Hi skt,

Here is what i understand of the problem. You have three tables each having one common column(rat_en) and there are count in another column i.e. sub_count. Now we need to show the max(sub_count) for each of rat_en and from the three tables.
here is queries that i ran...

create volatile table t1
(
rate_en char(20),
sub_count integer)
on commit preserve rows

create volatile table t2
(
rate_en char(20),
sub_count integer)
on commit preserve rows

create volatile table t3
(
rate_en char(20),
sub_count integer)
on commit preserve rows

the above three tables represent your tables and next step is to insert some data in it..
insert into t1 values('Car',100);
insert into t1 values('Car',200);
insert into t2 values('Car',200);
insert into t2 values('Car',300);
insert into t3 values('Car',300);
insert into t1 values('Car',400);

Now we need to show the max of sub_count for 'Car' so writing three queries will do
sel rate_en , max(sub_count) from t1 group by rate_en;
sel rate_en , max(sub_count) from t1 group by rate_en;
sel rate_en , max(sub_count) from t1 group by rate_en;

But since you need the output in a single row hence the below query should do

SELECT A.RATe_EN,MAX(A.sub_count),MAX(B.sub_count),MAX(C.sub_count)
FROM
t1 A,
t2 B,
t3 C
WHERE A.RATe_EN = B.RATe_EN
AND A.RATe_EN = C.RATe_EN
GROUP BY A.RATe_EN;

rate_en MAX(A.sub_count) MAX(b.sub_count) MAX(c.sub_count)
Car 200 300 400

going by your response earlier i think i am not understanding your requirements. Could you give us table structure and few records of your table and the answerset as you want?

Cheers,
Novice
Fan

Re: Problem with a Tera Data Query

I completely agree witch novice with just replacement of the last line in the insertion paeragraph

insert into t1 values('Car',400);
by
insert into t3 values('Car',400);

Regards ,
Walied
skt
Fan

Re: Problem with a Tera Data Query

Hi All

Thanks novice for your reply,

Hope the above query will work.
Any way here is my table structure so that if you have any better idea you can share it.

This is my table structure

Rat_en Sub_count

Car 1000

Jeep 1500

Bus 500

Car 400

Jeep 600

Bus 2500

Motor 100

Motor 2000

bicycle 300

limousine 700

Rat_en Sub_count

Car 700

Jeep 300

Bus 2000

Motor 1000

bicycle 1500

limousine 800

Jeep 600

Bus 2500

Motor 100

Rat_en Sub_count

Car 400

Jeep 600

Bus 2500

Motor 100

Jeep 300

Bus 2000

Motor 1000

bicycle 1400

limousine 200

The Result Should be in this Format

Rat_en Sub_count1 Sub_count2 Sub_Count3

Car 1000 700 400

Jeep 1500 600 600

Bus 2500 2500 2500

Motor 2000 1000 1000

bicycle 300 1500 1400

limousine 700 800 200

Here Sub_count1 is the alias name for sub_count from table sub_rat,Similarly for all

Sub_count1 contains the maximum value of rat_en from table sub_rat

Similarly for three tables.

Hope you understand my question....
Enthusiast

Re: Problem with a Tera Data Query

Hi skt,

i had created 3 tables t1,t2,t3 and inserted data given by you
The following query gives you the desired result:

sel a.re,sc1,sc2,sc3 from
(sel rat_en,max(sub_count) from t1 group by 1)a(re,sc1)
inner join
(sel rat_en,max(sub_count) from t2 group by 1) b (re,sc2)
on a.re=b.re
inner join
(sel rat_en,max(sub_count) from t3 group by 1) c (re,sc3)
on a.re=c.re

cheers:-)