Creating a column in a view

[richand1]

Hi all

Hopefully a quick one for you: I've used SQL Server 2000 before and the equivelent of what I need to do in Teradata's SQL Assistant would have gone like this in SQL Server:

select COLUMN_A, "Help me" as Column_B

So this would have inserted a new column called COLUMN_B and put the value "Help me" into every row in column b, right?

How do I do this in SQL Assistant? I've tried using single quotation marks, double quotation marks, brackets (and a combination of all three), case statements but with an error every time.

Please help me?!

Many thanks
Richard

 

[BillDHS]

This works fine for me:
select 'Help me' as Column_B;
RESULTS:
Column_B
Help me

Where is your FROM clause for the source of COULMN_A?
What is the error?

 

[richand1]

I've created a crosstab query using this statement:

select a.depot_number, b.product_sub_group_code,
sum(case when a.year_week_number = 200601 then a.total_stock_cases else 0 end) as "200601"

(this carries on to year_week_number 200652)

from VWI0WDP_WEEKLY_SD_DEPOT_STOCK a
inner join
bf50.Sub_Group_Hierarchy b
on a.base_product_number = b.base_product_number
group by depot_number, product_sub_group_code

How would I go about integrating the script you have provided?

Thank you for this.

 

[richand1]

Ok. I've got it:

SELECT 'Stock' as Subject, a.depot_number, b.product_sub_group_code,
sum(case when a.year_week_number = 200601 then a.total_stock_cases else 0 end) as "200601"

FROM VWI0WDP_WEEKLY_SD_DEPOT_STOCK a

INNER JOIN

bf50.Sub_Group_Hierarchy b

ON a.base_product_number = b.base_product_number

GROUP BY depot_number, product_sub_group_code

RESULTS: Subject Stock
___________________________________________________

Thank you very much for your help - it's really appreciated.

Richard

 

[richand1]

Actually, I can't now replace the view with this one. I get the error "Something expected between the word 'product_sub_group_code' and the word 'results'".

it will run without the replace view command, but not otherwise...

 

[manmaria]

What are you doing? Results is not part of the sql. Bill is showing you the results after he ran the query.

 

[richand1]

I realised that afterwards. I've managed to do it though.

Thanks.