Trouble linking data from another table
-
Hi
I have a Item table and next to that I have a table with extended lines. This is like extra descriprions to the items.
The data in this table looks like this :
No_ Language Code Line No_ Text
DIAU202 10000 text1
DIAU202 20000 text2
DIAU202 NLB 10000 text1
DIAU202 NLB 20000 text2
DIAU202 FRB 10000 not important
Now if there is no language code I need the line no_ = 10000. If there is no blank language code in need the first line of the NLB language code.
My query looks like this :
SELECT
distinct Item.No_ as artikelcode,
(
select [Text] from [NAV_SEPIA].[dbo].[OPS Sepia$Extended Text Line] as ETL
where
ETL.[No_] = Item.No_ and [Language Code] = 'NLB'
and
ETL.[Line No_] = '10000'
union
select
[Text] from [NAV_SEPIA].[dbo].[OPS Sepia$Extended Text Line] as ETL
where
ETL.[No_] = Item.No_ and [Language Code] = ''
and
ETL.[Line No_] = '10000'
and
Item.[No_] not in
(
select ETL.No_ from [NAV_SEPIA].[dbo].[OPS Sepia$Extended Text Line] as ETL where [Language Code] = 'NLB'))
as
Extraomschrijving,
FROM
[OPS Sepia$Item] AS Item
I always have problems because my subquery passes too much values. When I use the subquery itself with Item.No_ = DIAU202 I only get one result.
What can possibly be the problem ?
thx guyz & girlz (of course
JV -
I think that your problem is in the fact that you don't have clearly defined requirements. Maybe if you try to describe in words what precisely should be returned in which situation, you'll find the problem even without our help.
Anyway, if you want us to help you, we will need the precise description of requirements and also some example, to make sure that we understand each other. I can see that the query is unnecessary complicated and could be written much simpler, but how, that's something I don't know yet - it depends on requirements.
-
I tried to generate the enviroment from what you posted and this is the result... can not guarantee that it works well in all situations.
/*environment for testing*/
create table [OPS Sepia$Extended Text Line] ([No_] varchar(20), [Line No_] varchar(20), [Language Code] varchar(10), [text] varchar(20))
insert into [OPS Sepia$Extended Text Line] ([No_], [Line No_], [Language Code], [text])
values ('DIAU202','10000','', 'text1')
insert into [OPS Sepia$Extended Text Line] ([No_], [Line No_], [Language Code], [text])
values ('DIAU202','20000','', 'text2')
insert into [OPS Sepia$Extended Text Line] ([No_], [Line No_], [Language Code], [text])
values ('DIAU202','10000','NLB', 'text1NLB')
insert into [OPS Sepia$Extended Text Line] ([No_], [Line No_], [Language Code], [text])
values ('DIAU202','20000','NLB', 'text2NLB')
insert into [OPS Sepia$Extended Text Line] ([No_], [Line No_], [Language Code], [text])
values ('DIAU202','10000','FRB', 'not important')
create table [OPS Sepia$Item] ([No_] varchar(20))
insert into [OPS Sepia$Item]([No_]) values ('DIAU202')
/*query to find description*/
SELECT Q.artikelcode, ISNULL(MAX(Q.NLB_text), MAX(Q.blank_text)) as Extraomschrijving
FROM
(SELECT Item.No_ as artikelcode,
CASE WHEN ETL.[Language Code] = 'NLB' THEN [Text] ELSE NULL END as NLB_text,
CASE WHEN ETL.[Language Code] = '' THEN [Text] ELSE NULL END as blank_text
FROM [OPS Sepia$Item] AS Item
LEFT JOIN [OPS Sepia$Extended Text Line] as ETL ON ETL.[No_] = Item.No_ and ETL.[Line No_] = '10000') as Q
GROUP BY Q.artikelcode
-
Vladan
It should be simple.
I have to make an inventory list but with teh extended description of the item added to this list. So the items are in one table, all extended texts are in the other table.
Problem 1 : If the language code is not filled in, this text is available for all languages. So if there is a text within language code blank it should return this value. When there is no blank language code, it should take a look at the texts where there is a NLB language code. If there is no NLB too, then a null should be returned.
The extended text can also contain multiple lines. Only the one first line (Line No_ = '10000'- should be returned.
Hope this makes things clear.
Kind regards
Jeffrey
JV -
OK, thanks, seems I understood it correctly. I did some more testing now and the query I posted works for all possibilities mentioned in your post - you just have to switch order in the ISNULL.
If you're not happy with it, please tell me what's wrong.
So, the query would be
SELECT Q.artikelcode, ISNULL(MAX(Q.blank_text), MAX(Q.NLB_text)) as Extraomschrijving
FROM
(SELECT Item.No_ as artikelcode,
CASE WHEN ETL.[Language Code] = 'NLB' THEN [Text] ELSE NULL END as NLB_text,
CASE WHEN ETL.[Language Code] = '' THEN [Text] ELSE NULL END as blank_text
FROM [OPS Sepia$Item] AS Item
LEFT JOIN [OPS Sepia$Extended Text Line] as ETL ON ETL.[No_] = Item.No_ and ETL.[Line No_] = '10000') as Q
GROUP BY Q.artikelcode
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply