Is there a smarter way to do this SQL so I do not have many subselects?

SELECT 
    emp1,
    emp3,
    mtrl,
    kat,
    (select f_desc from tbllangdesc where tbllangdesc.id = tblPart.id and tbllangdesc.rownr = 3 and tbllangdesc.langcode = 'DK') LangDescDK3,
    (select f_desc from tbllangdesc where tbllangdesc.id = tblPart.id and tbllangdesc.rownr = 4 and tbllangdesc.langcode = 'DK') LangDescDK4,
    (select f_desc from tbllangdesc where tbllangdesc.id = tblPart.id and tbllangdesc.rownr = 3 and tbllangdesc.langcode = 'EN') LangDescEN3,
    (select f_desc from tbllangdesc where tbllangdesc.id = tblPart.id and tbllangdesc.rownr = 3 and tbllangdesc.langcode = 'DE') LangDescDE3,
    (select f_desc from tbllangdesc where tbllangdesc.id = tblPart.id and tbllangdesc.rownr = 3 and tbllangdesc.langcode = 'FR') LangDescFR3,
    (select f_desc from tbllangdesc where tbllangdesc.id = tblPart.id and tbllangdesc.rownr = 3 and tbllangdesc.langcode = 'FI') LangDescFI3,
    (select f_desc from tbllangdesc where tbllangdesc.id = tblPart.id and tbllangdesc.rownr = 3 and tbllangdesc.langcode = 'ZH') LangDescZH3,
    (select f_desc from tbllangdesc where tbllangdesc.id = tblPart.id and tbllangdesc.rownr = 3 and tbllangdesc.langcode = 'LI') LangDescLI3,
    (select f_desc from tbllangdesc where tbllangdesc.id = tblPart.id and tbllangdesc.rownr = 3 and tbllangdesc.langcode = 'NO') LangDescNO3,
    (select f_desc from tbllangdesc where tbllangdesc.id = tblPart.id and tbllangdesc.rownr = 3 and tbllangdesc.langcode = 'PL') LangDescPL3,
    (select f_desc from tbllangdesc where tbllangdesc.id = tblPart.id and tbllangdesc.rownr = 3 and tbllangdesc.langcode = 'PT') LangDescPT3,
    (select f_desc from tbllangdesc where tbllangdesc.id = tblPart.id and tbllangdesc.rownr = 3 and tbllangdesc.langcode = 'RU') LangDescRU3

FROM
    tblEmp,
    tblPart
WHERE
    tblEmp.id = tblPart.id

asked 06 Mar '16, 09:37

Rolle's gravatar image

Rolle
379273342
accept rate: 0%

Just to understand:

Do you need all these language-dependent columns in the same query, or do you later filter only those you need for the current connection/current language, what ever? (I'm asking since it is a common requirement to return a description in the "one and only appropriate language" for the current client but that seems not to be the case here...)

And do you require two columns for "DK" and only one for the other languages?

(07 Mar '16, 04:35) Volker Barth

The languages which is typed in all this subselects is all Possible languages that can be used. Used languages codes is in tables tblLang. This can I type with select distinct lang_lang from tblLang. This will return all used language codes like IT, DKK etc.. Wich would be the best. Instead of use many subselects I hope I can use it in a smarter way. DKK is on row 1,3 and 4. All other languages is on row 1 and 3.

(09 Mar '16, 05:15) Rolle
Replies hidden

I still do not really understand what you are trying to achieve - do you need a result set with all these languages, or just one with the chosen/used language? And if several languages, do you need these to be returned in the same row as in your sample, or could they be returned in different rows?

It would be probably easier for others to make suggestions if you could please explain what result set you would like to have, so please just show us what the resulting data should look like...

(09 Mar '16, 06:37) Volker Barth

DROP TABLE IF EXISTS tbllangdesc;

    CREATE TABLE tbllangdesc
        (
        ID integer,
        langcode nvarchar(3),
        f_desc nvarchar(40),
        rownr smallint
        );

        INSERT INTO tbllangdesc VALUES (1,'EN', 'Test row 1 EN', 1);
        INSERT INTO tbllangdesc VALUES (1,'EN', 'Test row 3 EN', 3);
        INSERT INTO tbllangdesc VALUES (1,'IT', 'Test row 1 IT', 1);
        INSERT INTO tbllangdesc VALUES (1,'IT', 'Test row 3 IT', 3);
        INSERT INTO tbllangdesc VALUES (1,'DKK', 'Test row 1 DKK', 1);
        INSERT INTO tbllangdesc VALUES (1,'DKK', 'Test row 3 DKK', 3);
        INSERT INTO tbllangdesc VALUES (1,'DKK', 'Test row 4 DKK', 4);
        INSERT INTO tbllangdesc VALUES (1,'RU', 'Test row 1 RU', 1);
        INSERT INTO tbllangdesc VALUES (1,'RU', 'Test row 3 RU', 3);
        INSERT INTO tbllangdesc VALUES (2,'EN', 'Test row 1 EN', 1);
        INSERT INTO tbllangdesc VALUES (2,'EN', 'Test row 3 EN', 3);
        INSERT INTO tbllangdesc VALUES (2,'IT', 'Test row 1 IT', 1);
        INSERT INTO tbllangdesc VALUES (2,'IT', 'Test row 3 IT', 3);
        INSERT INTO tbllangdesc VALUES (2,'DKK', 'Test row 1 DKK', 1);
        INSERT INTO tbllangdesc VALUES (2,'DKK', 'Test row 3 DKK', 3);
        INSERT INTO tbllangdesc VALUES (2,'DKK', 'Test row 4 DKK', 4);
        INSERT INTO tbllangdesc VALUES (2,'RU', 'Test row 1 RU', 1);
        INSERT INTO tbllangdesc VALUES (2,'RU', 'Test row 3 RU', 3);
        INSERT INTO tbllangdesc VALUES (2,'PL', 'Test row 1 PL', 1);
        INSERT INTO tbllangdesc VALUES (2,'PL', 'Test row 3 PL', 3);

        select ....... from tbllangdesc  //should return this (even column names)

        ID---LangDescEN1----LangDescEN3----LangDescIT1----LangDescIT3----LangDescDKK1----LangDescDKK3----LangDescDKK4----LangDescRU1----LangDescRU3----LangDescPL1----LangDescPL3
        --------------------------------------------------------------------------------------------------------------------------------------------------------------------------
        1---Test row 1 EN---Test row 3 EN--Test row 1 IT--Test row 3 IT--Test row 1 DKK--Test row 3 DKK--Test row 4 DKK--Test row 1 RU--Test row 3 RU--NULL()---------NULL()------
        2---Test row 1 EN---Test row 3 EN--Test row 1 IT--Test row 3 IT--Test row 1 DKK--Test row 3 DKK--Test row 4 DKK--Test row 1 RU--Test row 3 RU--Test row 1 PL--Test row 3 PL

Should be doing in a different way (smarter way with smaller code) than subselect I done now

(09 Mar '16, 07:58) Rolle
Replies hidden

While I still do not understand what exactly you are really trying to achieve (or what you will do with that particular result set), your sample points out that you need to turn values of rows of some table (like 'EN' and '1') into column names of a result set (such as "LangDescEN1").

If so, I agree with Breck that you could try to use the (quite complex) PIVOT clause if you are using v17, or could stay with your current solution, although that may mean you will have to adapt the query if there are more languages/rownrs to handle...

I'm still not sure whether you could also try to use a derived table for all those language IDs and join that with other data (and that way get a solution able to cope easily with more language entries) - but as stated, I do not really understand what it's all good for.

(09 Mar '16, 08:44) Volker Barth

What I am asking for is a kind of Pivot for this. I think there is a more dynamic solution with pivot and less code is needed. We use SA16. I wrote wrong in my previous post ... I get all used unique language codes by

select distinct langCode from tbllangdesc
(09 Mar '16, 14:34) Rolle
showing 4 of 6 show all flat view

Maybe you can figure out how PIVOT works :)

...or not, because your code is pretty straightforward. Verbose is not bad if it easy to understand and maintain (which is the polar opposite of PIVOT, alas)

permanent link

answered 06 Mar '16, 14:55

Breck%20Carter's gravatar image

Breck Carter
26.9k437609883
accept rate: 21%

I removed this...

(09 Mar '16, 07:56) Rolle
Your answer
toggle preview

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text](http://url.com/ "title")
  • image?![alt text](/path/img.jpg "title")
  • numbered list: 1. Foo 2. Bar
  • to add a line break simply add two spaces to where you would like the new line to be.
  • basic HTML tags are also supported

Question tags:

×15

question asked: 06 Mar '16, 09:37

question was seen: 318 times

last updated: 09 Mar '16, 14:36