Home » SQL & PL/SQL » SQL & PL/SQL » Tricky sql query help needed
Tricky sql query help needed [message #201971] Tue, 07 November 2006 11:25 Go to next message
ferrethouse
Messages: 43
Registered: August 2006
Member
We are implementing multilingual content and have created several "text" tables to hold the textual information. Here is the definition of one of the tables...

Describing tblpackagetext....
NAME                            Null?     Type
------------------------------- --------- -----
PACKAGEID                       NOT NULL  NUMBER(11,0)
LANGUAGEID                      NOT NULL  NUMBER(2,0)
NAME                                      VARCHAR2(80)
DESCRIPTION                               VARCHAR2(4000)
COMMENTS                                  VARCHAR2(4000)
QACOMMENTS                                VARCHAR2(4000)
GUIDELINE                                 VARCHAR2(20)
EDITED                          NOT NULL  NUMBER(1,0)
TIMESTAMP                       NOT NULL  DATE


I need a SQL query that returns a recordset like this...

packageid, Name, NameFrench, Description, DescriptionFrench, Comments, CommentsFrench, etc..

For any given PackageID there may be up to two records (one for french and one for english). I need to merge the french and english records and position the french version of each field beside its english equivalent.

Note: There will often be no french record corresponding to the english record in which case I need blank fields in the resulting recordset.

This query will be used to populate a gridview control in .NET.

Thanks for your help. I have no idea how to even get started with this.
Re: Tricky sql query help needed [message #201974 is a reply to message #201971] Tue, 07 November 2006 11:46 Go to previous messageGo to next message
Todd Barry
Messages: 4819
Registered: August 2001
Senior Member
This is just a standard pivot query (collapsing multiple rows into a single row). Search here on the forum for many examples, but basically you will group by packageid and then use a max and a decode on the languageid column to populate your two name columns in the result.

Give it a try and we'll be glad to help you along.
Re: Tricky sql query help needed [message #201979 is a reply to message #201974] Tue, 07 November 2006 12:24 Go to previous messageGo to next message
ferrethouse
Messages: 43
Registered: August 2006
Member
Thanks Todd. I think I'm making progress. It seems to return the proper columns (I don't have data yet so I'm not sure if the values are correct). I didn't make use of decode. And I've never used max or decode so please verify that this is correct...

select packageid, Name, NameFrench
from (
select max(packageid) AS packageid, 
max(case when languageid = 1 then name end) Name,
max(case when languageid = 2 then name end) NameFrench
from tblpackagetext group by packageid)


Do I need decode on name in order to handle nulls?

[Updated on: Tue, 07 November 2006 12:24]

Report message to a moderator

Re: Tricky sql query help needed [message #201980 is a reply to message #201979] Tue, 07 November 2006 12:31 Go to previous messageGo to next message
Todd Barry
Messages: 4819
Registered: August 2001
Senior Member
sql>select * from tblpackagetext;

PACKAGEID LANGUAGEID NAME                 DESCRIPTION
--------- ---------- -------------------- --------------------
        1          1 English Name         English Description
        1          2 French Name          French Description
        2          1 Another English      More English

3 rows selected.

sql>select packageid,
  2         max(decode(languageid, 1, name, null)) name,
  3         max(decode(languageid, 2, name, null)) nameFrench,
  4         max(decode(languageid, 1, description, null)) description,
  5         max(decode(languageid, 2, description, null)) descriptionFrench
  6    from tblpackagetext
  7   group by packageid;

PACKAGEID NAME                 NAMEFRENCH
--------- -------------------- ------------------------------
DESCRIPTION          DESCRIPTIONFRENCH
-------------------- ------------------------------
        1 English Name         French Name
English Description  French Description

        2 Another English
More English

2 rows selected.
Re: Tricky sql query help needed [message #201981 is a reply to message #201980] Tue, 07 November 2006 12:34 Go to previous messageGo to next message
ferrethouse
Messages: 43
Registered: August 2006
Member
Yes. It does work. I just put data in and it does work.

Thank you so much Todd for guiding me to the answer instead of simply giving me the answer.

Edit: My solution seems to produce the same result as yours.

[Updated on: Tue, 07 November 2006 12:36]

Report message to a moderator

Re: Tricky sql query help needed [message #201984 is a reply to message #201981] Tue, 07 November 2006 13:50 Go to previous message
Todd Barry
Messages: 4819
Registered: August 2001
Senior Member
In your version, you do not need the max(projectid) since you are grouping by that column, and you do not need the outer select. You can certainly use CASE instead of DECODE though.
Previous Topic: what should i do?? - hints?
Next Topic: How to fix ORA-01632 error?
Goto Forum:
  


Current Time: Fri Dec 02 18:52:54 CST 2016

Total time taken to generate the page: 0.27597 seconds