Home » SQL & PL/SQL » SQL & PL/SQL » Working with elements in a concatenated field
Working with elements in a concatenated field [message #195383] Thu, 28 September 2006 06:01 Go to next message
rbrickne
Messages: 3
Registered: August 2006
Junior Member
I am trying to write a query which will return a count of EMP_INFO.Employee_id's that can be found in PROMOTIONS.Emp_Array with a PROMOTIONS.Status of 'Y' so that I know how many employees have been promoted.

I have:

Oracle 9i (and not allowed to upgrade)

Table: EMP_INFO
Field: Emp_ID NUMBER which is a unique key. Emp_ID contains a unique number for each employee such as 11 or 2.

TABLE: PROMOTIONS
FIELD: Status CHAR(1)
FIELD: Emp_Array VarChar2(250)which is a concatenated series of Emp_ID's with pipe signs used for separation so that one record would appear as '|11|5|27'. This means that the record pertains to employee_id's 11, 5, and 27.

Here's what I have figured out so far:

I am homogenizing the emp_array data with:

SELECT ('|' || TRIM(Emp_Array) || '|') as Emp_id
FROM PROMOTIONS
where Status = 'Y'

This returns a record set:

||
||6|
||11|5|
||12|24|31|

Now each employee ID in the array has a leading and trailing pipe sign. This way, I can query for |31| even if it is the last ID in the array.

I am trying to incorporate this into the master query:

SELECT count(Emp_ID) as nbr_emp_promitions
FROM EMP_INFO
Where (SELECT ('|' || TRIM(Emp_Array) || '|') as Emp_id
FROM PROMOTIONS
where Status = 'Y') LIKE '%|' || Emp_id || '&|';

Oracle does not like the multiple returns from my encapsulated PROMOTIONS query at the beginning of the LIKE statement. At this point I am stumped. Is there a better approach? I am not allowed to use temporary tables or alter the table structures in any way.

Thanks!
Re: Working with elements in a concatenated field [message #195391 is a reply to message #195383] Thu, 28 September 2006 06:41 Go to previous messageGo to next message
gojko
Messages: 18
Registered: September 2006
Location: London
Junior Member
try to join those two tables and use instr to match part of the string. I suppose that the promotions table is much, much smaller then employees, so it would make sense to process that table first as a subquery and then join it with emp_info.

The query would look something like this (I did not check the syntax in sqlplus, but it should give you an idea):

SELECT 
  count(Emp_ID) as nbr_emp_promitions
FROM 
  EMP_INFO, 
  ( 
    SELECT ('|' || TRIM(Emp_Array) || '|') as Emp_ids FROM     
    PROMOTIONS where Status = 'Y') 
  ) x
where 
  instr(x.emp_ids, '|' || EMP_INFO.EmpId || '|' )>1


also, since a single employee may be promoted several times, you might need to select count of distinct employee ids. check your requirements about that.

Gojko Adzic
http://www.gojko.com

[Updated on: Thu, 28 September 2006 06:41]

Report message to a moderator

Re: Working with elements in a concatenated field [message #195392 is a reply to message #195383] Thu, 28 September 2006 06:43 Go to previous messageGo to next message
amul
Messages: 252
Registered: April 2001
Location: Chennai
Senior Member
oops mistake corrected...if only you had posted the scripts it would have helped us better..

[Updated on: Thu, 28 September 2006 07:04]

Report message to a moderator

Re: Working with elements in a concatenated field [message #195398 is a reply to message #195392] Thu, 28 September 2006 06:57 Go to previous message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
But there's no GROUP BY statement.
How can HAVING conceivably help?
Previous Topic: second case issue
Next Topic: insert into two tables from one query
Goto Forum:
  


Current Time: Wed Dec 07 07:10:22 CST 2016

Total time taken to generate the page: 0.13835 seconds