August 11, 2006 at 4:22 am
hi
I have a view with the next information :
Is a join between the head of the orders and the orderlines :
select clientcode, articlecode, language from table_head
join table_lines on table_lines.ordernumber = table_head.ordernumber
So this is the view. Now I want the articledescription to be in the language of the customer. So the french description is within a different table : tbl_language. So if customer is french the description shoudl come from this table. If dutch (standard) the description should come from the table_orderline
Anyone got a clue 🙂 ?
Thx
Jef
JV
August 11, 2006 at 4:48 am
Jef
Please will you post the DDL and some sample data for the four tables (or three if, as I suspect, table_lines and table_orderline are actually the same table).
Thanks
John
August 11, 2006 at 4:49 am
Unfortunately you didn't post the structure of tables and how they are connected, so I can only guess. Under certain conditions, you could do without CASE:
SELECT head.clientcode,
line.articlecode,
head.language,
COALESCE(lang.article_description,line.article_description)
FROM table_head head
JOIN table_lines line ON line.ordernumber = head.ordernumber
LEFT JOIN tbl_language lang ON lang.articlecode = line.articlecode AND lang.language = head.language
This ensures, that if language is entered, and at the same time description for the relevant product exists (=is not NULL) in this language, it is displayed. When these conditions are not met, description from order line is displayed.
I think that DB and table design is not really the best and could be made better (and probably should), but maybe you just have to live with what you have...
August 11, 2006 at 5:30 am
Hi
Thanx. It's not my table design 🙂 It's from an ERP system.
I will give it a shot
JV
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply