Advanced SQL like 'decode'

[JayHack]

What is the equivalent advanced Teradata SQL code for the PL/SQL SELECT below?

Suppose you have a select or subselect which filescans 1 billion records, and you need several elements from this set of records. How can you do this with one filescan? What is the Advanced Teradata SQL equivalent for the Oracle PL/SQL sum(decode(w,x,y,z)) construct like that below? UNION SELECT works, but would require five lengthy scans instead of just one to get the five elements. Is it a variant of CASE..WHEN..?? This would have to be beyond ANSI-standard SQL, an advanced Teradata SQL construct.

SELECT
sum(decode(a.txn_type,'SALE',a.INV_CHG_QTY,0)) sales_qty,
sum(decode(a.txn_type,'SALE',a.RETAIL_REV_AMT,0)) gross_sales,
sum(decode(a.txn_type,'CRED',a.INV_CHG_QTY,0)) net_credits,
sum(decode(a.txn_type,'CRED',a.INV_CHG_QTY,0)) discounts,
sum(decode(a.txn_type,'CO8',a.RETAIL_REV_AMT,0)) voids
FROM tablename a
WHERE ...

Thanks in advance,

Looking-4-Efficient-SQL

 

[BillDHS]

I am not real familiar with Oracle, but it looks like a case statement would give you your results in one table read.

Select
SUM(Case when a.txn_type = 'SALE'
THEN a.INV_CHG_QTY ELSE 0 END)as sales_qty,
sum(case when a.txn_type = 'SALE'
then a.RETAIL_REV_AMT ELSE 0 end) gross_sales ...


You may have to add qualifiers to handle NULLs