Please Help! How to get Case with aggreagte 1

[dba2adm]

Dear Group,

Teradata Version: V2R5

I have been trying to solve this problem for more than a day and everytime I get differenty error.

I want to update a certain table with count(*) of another table if certain condition is true, else update with 0.

This is the portion I have problem with,

SELECT col1, col2, col3,
CASE WHEN t1.col1 > t1.col2 then (select count(*) from T2
where t1.key = t2.key and t2.dt between
<startdate> and <enddate>)
ELSE 0
END new_val from T1 where ...

My update would be something like,

update table set coln = <above case>

Thanks,
Da

 

[dnoeth]

Hi Da,
the bad news: Teradata doesn't support scalar subqueries.
the good news: Those scalar subqueries usually may be rewritten using outer joins.

Your select is probably:

SELECT T1.col1, T1.col2, T1.col3,
coalesce(T2.cnt, 0) as new_val
from T1 left join
(
select key, count(*) as cnt
from T2
group by key
where t2.dt between <startdate> and <enddate>
) T2 on t1.key = t2.key and t1.col1 > t1.col2
where ...

This Select may be used in your Update:

update table from (above select) dt
set coln = dt.new_val
where table.pk_col = dt.pk_col

Dieter

 

[dba2adm]

Thanks Dieter,

I am from the Oracle/DB2 bacground and thought of trying scalar sub queries before trying left outer join. Since, scalar doesn't work in this case, I should go with left outer join as you suggested.

I even tried, select col1, col2, (select count*) ..)

Again thanks for the respone.

-Dave

 

[dba2adm]

I have another case where ,

when t1.col1 > t1.col2 then count( * ) from t2
else 0

it should be something like this
Select t1.col1 , t1.col2,
case when t1.col1 > t1.col2
then (select count(*) from t2 where t2.col1 between t1.col1 and t1.col2 and t2.col2 = 0)
else 0 end (BYTEINT) temp_var_name
from t1 where condition;

NB: Basicaly for certain condition I need count (*) when we get a range of t2.col1 values between t1.col1 and t1.col2.
Also there is no real relation between 2 tables t1 and t2.

Thanks

 

[dnoeth]

Sometimes it's much easier to use a Scalar Subquery ;-)

"when t1.col1 > t1.col2" is a typo, should be
"t1.col1 < t1.col2" or Between will return nothing...

Select t1.col1 , t1.col2,
count(t2.col2) temp_var_name
from t1 left join t2
on t2.col1 between t1.col1 and t1.col2 and t2.col2 = 0
where condition
group by 1,2;

Using this query there's no need to check for "t1.col1 < t1.col2" and Coalesce.

But be aware, you'll have a product join between t1 and t2, so hopefully one of those tables is rather small...

Btw, looks like you're trying to calculate the number of working days between start and end date using a calendar table.

Dieter

 

[dba2adm]

Thanks for the response.

You are absolutely right, I am trying to get the working days based on certain conditions.

This is my requirement. how do I extend your query to take care of all the three CASEs.


select mst.*,
case when mst.dt1 = dat.dt and dat.ind =1 then 1
case when dt1 < dt2
then (select count(*) from dat where ind = 1 and dt between mst.dt1
and mst.dt2)
else 0
end as ct from mst
--where ..

I kind of, worked out the last part using the following query. I still have to do some validation.

select mst.*, sum(case when dt1 < dt2 and dt between mst.dt1 and mst.dt2 and ind = 1 then 1 else 0 end) as dt
from mst, dat group by mst.dt1, mst.dt2

What I need here is,

Table DAT
-----------
Dt Date --has all dates for five years
ind int --says whether its a holiday or not etc
--ex ind = 1 working day, ind = 2 fed holiday ..
Table MST
-------------
dt1 date
dt2 date
fld1 int ..

*if mst.dt1 is in dat.dt and ind = 1 then return 1
*if mst.dt1 < mst.dt2 then return the number of working days between mst.dt1 and mst.dt2
*else return 0

FYI: I have the posting in google also, at

http://groups.google.com/groups?dq=...=&ie=UTF-8&group=comp.databases.oracle.server

Thanks.

 

[dnoeth]

I only read comp.databases ;-)

But i don't understand the examples you posted (here and in cdos), are there two seperate cases or is it just one?

"case when mst.dt1 = dat.dt and dat.ind =1 then 1"
will only work if you join to dat, but this join is missing.

I just rewrote your DDL & Inserts in Teradata SQL and tried to solve it using a single case.
IMHO i succeeded, but when i added the last condition, it crashed my Teradata (Luckily it's only running on my notebook)

It's too late here now, so i'll try it tomorrow again...

Dieter

 

[dba2adm]

Thanks Dieter,

I couln't come up with the correct SQL. Thats the reason the first join was incomplete.

These is just only one case,

The value from the case should be

a, 1 if there an exact matching record where mst.dt1 = dat.dt and dat.ind = 1
b, if the above is not tru, then number of working days between mst.dt1 and mst.dt2
c, else just give 0

Case
When mst.dt1 is in dat and dat.ind = 1 then 1
when mst.dt1 < mst.dt2 then number_of_holidays between dt1 and dt2
else 0
end

"dat" table is the holiday table with all the days of the year.

Thanks so much!

 

[dnoeth]

Sometimes it's good to have a break and rethink your approach, it's much easier than i thought...

select mst.dt1, mst.dt2,
case
-- mst.dt1 = dat.dt not necessary
WHEN dat.ind = 1 THEN 1
when dt1 < dt2
then (select count(*) from dat
where ind = 1
and dt between mst.dt1 and mst.dt2)
else 0
end as ct
from mst left join dat on mst.dt1 = dat.dt

translates to

select mst.dt1, mst.dt2,
case
when min(dat.dt) = mst.dt1 then 1
when mst.dt1 < mst.dt2 then count(dat.dt)
else 0
end as ct
from mst left join dat
on dat.dt between mst.dt1 and mst.dt2 and dat.ind=1
group by mst.dt1, mst.dt2

Loaded some data on Teradata, Insert/Selected those tables to MS SQL Server, which supports Scalar Subqueries and compared the result sets.

I'd be interested in some info about efficieny of both versions on DB2, could you post which one runs faster?
How many rows? Percentage of rows with count step?

Btw, there's another Teradata forum (a list server):

http://www.teradataforum.com,

lots of Teradata experts...
I usually recommend it in comp.databases

Dieter

 

[dba2adm]

Thanks Dieter,

I will try your SQL. I tried subscribing to teradataforum, but it didn't go thru. I will try again.