Home » SQL & PL/SQL » SQL & PL/SQL » Picking one language
Picking one language [message #201703] Mon, 06 November 2006 07:44 Go to next message
Safeeq.S
Messages: 100
Registered: October 2005
Location: Bangalore
Senior Member

Hi,

I have query which picks the 'Source name' and language.
If a source name has been configured with more than one language, then it should display source name only once corresponindg to US language.

SELECT distinct
s.je_source_name JE_SOURCE_NAME
,S.LANGUAGE,s.user_je_source_name
FROM gl_interface b,
gl_je_sources_tl S
WHERE b.set_of_books_id = &v_set_of_books_id
AND b.user_je_source_name = s.user_je_source_name
AND (s.language,s.user_je_source_name) in
(SELECT distinct
s.language
,s.user_je_source_name
FROM gl_interface b,
gl_je_sources_tl S
WHERE b.set_of_books_id = &v_set_of_books_id
AND b.user_je_source_name = s.user_je_source_name
)
GROUP BY s.je_source_name
,S.LANGUAGE
,s.user_je_source_name

For ex,

the query returns 7 rows

Source name Language

Bad Debt D
Bad Debt E
Bad Debt US
Bad Bedt I
Bad Debt F
Bad Debt NL
Payables US

The output has repeated rows returned for the source 'Bad Debt' which needs to be restricted to one row esp to US ie if the source is configured to more than one lang, then it needs to be defaulted to US row.

The expecting output should look like ,

Bad Debt US
Payables US
Receivables FR

Can any one help me on this,please?


Thanks
Safeeq
Re: Picking one language [message #201707 is a reply to message #201703] Mon, 06 November 2006 07:56 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Table structure and sample data to reproduce the problem?
Re: Picking one language [message #201712 is a reply to message #201703] Mon, 06 November 2006 08:18 Go to previous messageGo to next message
skooman
Messages: 912
Registered: March 2005
Location: Netherlands
Senior Member
Safeeq,

Do I understand correctly:
- if more then 1 language exists for a source_name and one of them is US, then show US
- if only 1 language exists for a source_name, then show that language
- if more then 1 language exists for a source_name and none of those is US, what then?

Some details in your statement:
- why the distincts? in what cases could you get (unwanted) duplicate rows?
- what are you trying to accomplish with the subquery?
- why the group by? you don't have an aggregate function in your statement right? than why a group by?

Anyway, you might be looking for something like:

SELECT sub1.translated_source_name
      ,sub1.original_source_name
      ,sub1.LANGUAGE
FROM   (SELECT g.user_je_source_name translated_source_name
              ,g.je_source_name original_source_name
              ,g.LANGUAGE LANGUAGE
              ,COUNT(*) over(PARTITION BY g.je_source_name) counts
        FROM   gl_je_sources_tl g) sub1
WHERE  sub1.LANGUAGE = CASE WHEN counts > 1 THEN 'US' ELSE sub1.LANGUAGE END


Regards,
Sabine

JRowbottom, I recognized the Apps tables, but of course you're right, to enable the rest of the world (poor Apps-less people) to help... Wink

[Updated on: Mon, 06 November 2006 08:20]

Report message to a moderator

Re: Picking one language [message #201716 is a reply to message #201712] Mon, 06 November 2006 08:32 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
I did wonder if it was Apps, but as I don't know Apps, the knowledge wasn't going to be a great deal of help. Razz
Re: Picking one language [message #201727 is a reply to message #201712] Mon, 06 November 2006 09:18 Go to previous messageGo to next message
Safeeq.S
Messages: 100
Registered: October 2005
Location: Bangalore
Senior Member

Hi,

Your understanding is right and if the source_name exists for one more one language and none of those is US,
then also it is defaulted to US for some business purposes.

- The Subquery is used to retrieve distinct the language & source_name for the Set of books.
- Agreed, the group by is not required. I was doing some workaround with regards this query
and have posted the same here. sorry for that.

The query you'd given is more or less similar to the one which i was looking but we'd need to include gl_sets_of_books table as we'll be trying to retrieve informations for one
set of books at a time.

Thanks
Safeeq



Re: Picking one language [message #201750 is a reply to message #201727] Mon, 06 November 2006 10:14 Go to previous messageGo to next message
skooman
Messages: 912
Registered: March 2005
Location: Netherlands
Senior Member
Hi Safeeq,

You stated that you use the subquery to retrieve the set of book id's. But that subquery just selects from the same sources and interface table again, so nothing new there that isn't already in the main query. Furthermore, it seems a bit strange to me, to get the set of books id's from the interface table. That would mean that if (for any reason) there are no records in the interface table for a certain set of books, than that set of books won't show from your query. And since you query on setup data (languages is obviously setup data), I don't think that is what you want.

I don't have enough knowledge of GL to be sure. But it seems to me that there is no direct relationship between set of books and sources. Maybe in your setup, there is a logical relationship between the two, but purely based on the datamodel, there isn't.

Maybe someone else knows??

Regards,
Sabine
Re: Picking one language [message #201759 is a reply to message #201750] Mon, 06 November 2006 10:44 Go to previous messageGo to next message
Safeeq.S
Messages: 100
Registered: October 2005
Location: Bangalore
Senior Member

Hi Sabine

Thanks for your replies.

I hope i have'd confused a bit. We could forget about the subquery for now. I have removed the sub-query to make it bit clear as i am finding it difficult to explain the whole requirement.

The query is ,

SELECT distinct s.language
,s.user_je_source_name SOURCE_NAME
FROM gl_interface b,
gl_je_sources_tl S
WHERE b.set_of_books_id = &v_set_of_books_id
AND b.user_je_source_name = s.user_je_source_name;

If i execute the query for the set_of_books_id=23, it is returning me 8 records.

The sample output would looks like -

Language | SOURCE_NAME
-----------------------------------
D | Bad Debts
E | Bad Debts
F | Bad Debts
I | Bad Debts
NL | Bad Debts
US | Bad Debts
US | Receivables
FR | Payables

In this case, 'Bad Debts' is returned for multi-languages which needs to be restricted for one lang e.g needs to be defaulted 'US'(eg1-6 records) if it is returned for multiple times otherwise use the relevant language set up for the set of books (eg 7&8 records)

Please note 'Distinct' is needed as the gl_interface has many transactions for the various different sources.

Hope i am clear this time Smile

Many Thanks
Safeeq
Re: Picking one language [message #201764 is a reply to message #201703] Mon, 06 November 2006 11:01 Go to previous messageGo to next message
Cthulhu
Messages: 381
Registered: September 2006
Location: UK
Senior Member
SELECT source_name, language
from
(SELECT s.user_je_source_name SOURCE_NAME,
       s.language,
       row_number() over (partition by s.user_je_source_name order by decode(s.language, 'US', 1, 2)) as rn
FROM gl_interface b,
gl_je_sources_tl S
WHERE b.set_of_books_id = &v_set_of_books_id
AND b.user_je_source_name = s.user_je_source_name)
where rn=1;
Re: Picking one language [message #201898 is a reply to message #201764] Tue, 07 November 2006 04:41 Go to previous message
Safeeq.S
Messages: 100
Registered: October 2005
Location: Bangalore
Senior Member

Hi,

The query works fine.

I have one more doubt in this, if the 'Bad Debts' source is configured in French (F) for the set of books -23 and the output has both F & US for the source 'Bad Debt' then, we'd need to consider the language configured to the set of books which is french in this case.

the output would like ,

Language | SOURCE_NAME
-----------------------------------
D | Bad Debts
E | Bad Debts
F | Bad Debts
I | Bad Debts
NL | Bad Debts
US | Bad Debts
US | Receivables
FR | Payables
US | Assets
D | Assests
I | Assets

But i want the output to be displayed like,

Language | SOURCE_NAME
-----------------------------------
F | Bad Debts
US | Receivables
FR | Payables
US | Assets

To set the appropriate language, i am adding a new attribute for the Set of books(attribute2) , people who dont have oracle apps knowledge , please excuse me.

SELECT JE_SOURCE_NAME
,'S' STATUS
,GROUP_ID,language
FROM
(SELECT distinct s.je_source_name JE_SOURCE_NAME,
s.language,
b.group_id group_id,
row_number() over (partition by s.je_source_name order by decode(s.language, 'US', 1, 2)) as rn
FROM gl_interface b,
gl_je_sources_tl S
,gl_sets_of_books g
WHERE b.set_of_books_id = &v_set_of_books_id
AND b.user_je_source_name = s.user_je_source_name
and b.set_of_books_id = g.set_of_books_id
and g.attribute2=s.language--its picking only the lang set to SOB
)
WHERE rn=1;

[Updated on: Tue, 07 November 2006 04:51]

Report message to a moderator

Previous Topic: row data in a column Format with Table_name and Column Name.
Next Topic: Triggers / Cursors with outer join (merged)
Goto Forum:
  


Current Time: Fri Dec 09 15:32:03 CST 2016

Total time taken to generate the page: 0.25037 seconds