All Forums Database
anoopjohn 2 posts Joined 09/14
24 Sep 2014
qualify statement with 'case' in teradata

Hi all,
I am trying to execute below query which has got a lot of case statements in qulify clause.
insert into tab2 b
select 
      a.id,a.name
    from tab1 a
where a.country='US'
qualify(
       row_number() over(partition by a.university_id
       order by
       case when a.student_in_computerscience_fl='Y' then 1 else 0 end,
       case when a.student_in_chemistry_fl='Y' then 1 else 0 end,
       case when a.student_in_electronics_fl='Y' or a.student_in_physics_fl='Y' then 1 else 0 end                 )=1)
Will the presence of a qualify with lot of case statements generate performance issues?
or do i have to use a temp table where i will store the rank(rk) as an additional field?
like,
insert into tmptab
select 
      a.id,a.name,
     row_number() over(partition by a.university_id
       order by
       case when a.student_in_computerscience_fl='Y' then 1 else 0 end,
       case when a.student_in_chemistry_fl='Y' then 1 else 0 end,
       case when a.student_in_electronics_fl='Y' or a.student_in_physics_fl='Y' then 1 else 0 end                 ) as rk
    from tab1 a
where a.country='US'
and then to insert the final target table from above mentioned temporary table.
issert into tab2
(name,id)
select name,id from tmptab where rk=1
kindly share your opinion.
Regards,
Anoop
 
 

24 Sep 2014

Hi Anoop,
Performance tuning or issues relating to performance are better understood when applied. Run the query as u ve suggested above check the explain plain,DBQLogTbl which would give u a better idea as to how u can proceed.
But one thing i understand from the query given above. If u insert into a temp table u would reduce the no.of.steps involved to do a operation.
But better understood when applied.
 
Thanks
Jugal.

You must sign in to leave a comment.