Home » Developer & Programmer » Forms » LOV Query Display Problem (Oracle 9i, Forms 6i)
LOV Query Display Problem [message #356583] Fri, 31 October 2008 05:27 Go to next message
sweetkhaliq
Messages: 189
Registered: April 2006
Senior Member

Dear Group Members
I have the data as below

Table Name Emp_rec
empno     empname    join_date
11100     Astle      31/10/1978
11101     Adom       31/10/1978


when i run this query
select join_date,emp_no from emp_rec where join_date = '31-oct-1978';
then it shows data like this
join_date       emp_no
31/10/1978      11100
31/10/1978      11101


But i require output as like this
join_date       emp_no
31/10/1978      11100,11101


Tell me the query to show the require results.

Thanks and Regards

[Updated on: Fri, 31 October 2008 05:39]

Report message to a moderator

Re: LOV Query Display Problem [message #356590 is a reply to message #356583] Fri, 31 October 2008 06:11 Go to previous messageGo to next message
Littlefoot
Messages: 20897
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
What you want to do is to convert rows to a string. STRAGG function is one way to do that (search either OraFAQ Forum or use Google). However, as I've never used it in Forms, I can't confirm that you'll be able to implement it into Forms environment.
Re: LOV Query Display Problem [message #356722 is a reply to message #356590] Sat, 01 November 2008 06:59 Go to previous messageGo to next message
sweetkhaliq
Messages: 189
Registered: April 2006
Senior Member

Thanks for reply
the function STRAGG did not work. It may be due to database version but i have used differt technique to solve this problem.
SELECT trunc(x.atten_date,'DD')atten_date, MAX(SUBSTR(SYS_CONNECT_BY_PATH(TO_CHAR(x.emp_code),','),2))
KEEP(DENSE_RANK LAST ORDER BY x.curr)as_string

FROM (SELECT trunc(t.atten_date,'DD')atten_date,
t.emp_code,ROW_NUMBER()OVER (PARTITION BY trunc(t.atten_date,'DD') ORDER BY t.emp_code)curr,
ROW_NUMBER()OVER (PARTITION BY trunc(t.atten_date,'DD') ORDER BY t.emp_code) - 1 prev
FROM attendance t) x
where trunc(x.atten_date,'DD') = '01-nov-2008'
GROUP BY trunc(x.atten_date,'DD')
START WITH x.curr = 1
CONNECT BY x.prev = PRIOR x.curr
AND trunc(x.atten_date,'DD') = PRIOR trunc(x.atten_date,'DD');

Query Results
Atten_date     Employees
01-nov-2008    002222,002223,002251,002225
Re: LOV Query Display Problem [message #356869 is a reply to message #356722] Sun, 02 November 2008 23:00 Go to previous message
djmartin
Messages: 10180
Registered: March 2005
Location: Canberra ACT Australia
Senior Member
Account Moderator
If you have the answer then why are you asking us?

David
Previous Topic: unknow publisher error
Next Topic: FRM 40102 Records must be inserted or deleted first
Goto Forum:
  


Current Time: Wed Dec 07 07:02:38 CST 2016

Total time taken to generate the page: 0.21561 seconds