qualify statement with 'case' in teradata

Database

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

Tags (1)
1 REPLY
Enthusiast

Re: qualify statement with 'case' in teradata

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.