1

I was trying to convert the connect by level function of oracle to teradata. I have seen many examples over the net but this particular one is different, 

 (SELECT 
     CASE LEVEL 
        WHEN 1 THEN 'MB'
        WHEN 2 THEN 'SB'
        ELSE 'TOTAL'
     END AS DRUG_SOURCE
 FROM
    DUAL 
 CONNECT BY LEVEL <= 3) RW

Please let me know if you guys have any idea.

user708477
  • 147
  • 2
  • 4
  • 14
  • You can use recursive SQL to produce a hierarchy in Teradata. For example, you can build out the user or database hierarchy from the data dictionary in an indented list. Is there more to your query, like a table or tables, with a relationship that is known? – Rob Paller Nov 17 '11 at 19:26
  • Yes this is just a small part of the whole query – user708477 Nov 17 '11 at 20:07
  • Can you share anymore? Otherwise I can just provide you an example of some recursive SQL that may or may not accomplish what you want. – Rob Paller Nov 17 '11 at 20:10
  • Thanks for your interest. The out put of this query is inner joined with another sub query and these two queries come under the from block of the main query. Eg: Select..from (recursive query) inner join(query 2). I could not understand how to work on the recursive part of the above mentioned query. I know we have 'recursive with' in TD but as I have case-when block in my query I am not getting the correct form of 'recursive with' syntax more over there is no table name as dual is used here which cannot be used in TD. – user708477 Nov 17 '11 at 20:35
  • Take a look at this recursive example ( http://stackoverflow.com/questions/7807130/teradata-string-manipulation-second-space) While not exactly what is needed here it shows you how the CTE is referenced in the outer part of the query. I'm afraid your pseudo-code / description isn't enough for me to base a suggested solution. – Rob Paller Nov 17 '11 at 21:31

2 Answers2

1

Hey I found the solution for this. Sorry I couldn't explain my question clearly.

The below code would work exactly the way i wanted in teradata

select 'MB' as DRUG_SOURCE from dual  
union  
select 'SB' as DRUG_SOURCE from dual  
Union  
select 'TOTAL' as DRUG_SOURCE from dual
animuson
  • 53,861
  • 28
  • 137
  • 147
user708477
  • 147
  • 2
  • 4
  • 14
0

You can try this code:

select DRUG_SOURCE from (select 'MB' as DRUG_SOURCE,1 as id) a
union all
select DRUG_SOURCE from (select 'SB' as DRUG_SOURCE,2 as id) b
union all
select DRUG_SOURCE from (select 'TOTAL' as DRUG_SOURCE,3 as id) c
Alessandro Minoccheri
  • 35,521
  • 22
  • 122
  • 171
EugenyK
  • 1