Help

Toad for Oracle
1

Hi.

I m new in Databases please help i am using Toad

Query:-

Select DISTINCT A.EMPLOYEEID , PRESONNAME , DESIGNATION , ABBRIV , JOININGDATE , RATING 1, RATING 2 RATING 3, FINAL TO_CHAR(FROMDATE ,‘YYYY’), ACRTYPE, STATUS, SCALE

FROM
A_EMPLOYEEMASTER A, TCR B , DESIGNATION C
WHERE
A.EMPLOYEEID = B.EMPLOYEEID(+)
AND DESIGNATION = DESIGNATION(+)
AND STATUS = ‘ACTIVE’
AND REVEIVEDDATE = ‘02-AUG-2010’

RESULT (OUTPUT)
EMPLOYEEID DESIGNATION ABBRIV JOININGDATE ACRYEAR
13056 ACCOUNTANT ACC 10/05/2000 2004
13056 ACCOUNTANT ACC 10/05/2000 2006
13056 ACCOUNTANT ACC 10/05/2000 2009

its okay , but i want output like this

13056 ACCOUNTANT ACC 10/05/2000 2004,2006,2009

i hope u people got my point … help me please thanks

DexTer
Pakistan

2

Hi DexTer,

have you tried the following

AND REVEIVEDDATE IN ( ‘10/05/2000’,'10/05/2005,‘10/05/2006’,‘10/05/2009’)

Is that what your looking for?

Kind Reagrds

Bob
— On Tue, 10/8/10, Liaqat Naveed wrote:

3

First thing I noticed is need to alias all the columns in where section, and
I’d do same in select section too

Deborah M Flad, DBA
Office: 302.633.2694 Emergencies: 302.528.3652
“Please consider your environmental responsibility before printing this e-mail”
image001.gif

4

DexTer,

The only real way is to call a function to get the acryear based on a key value.

create or replace function get_acryear (i_key in NUMBER)

return VARCHAR2

is

o_results VARCHAR2 (40);

cursor temp

is

select year

from year_list a

where key = i_key;

begin

for tr in temp

loop

o_results := o_results || year || ', ';

end loop;

– trim off the trailing ', ’

o_results := substr ( o_results, 1, length (o_results) - 2);

return o_results;

end;

select distinct a.employee_id, get_acryear(a.employee_id) from a_employeemaster
a;

HTH

chris
image001.jpeg

5

DexTer,

The only real way is to call a function to get the acryear based on a key value.

create or replace function get_acryear (i_key in NUMBER)

return VARCHAR2

is

o_results VARCHAR2 (40);

cursor temp

is

select year

from year_list a

where key = i_key;

begin

for tr in temp

loop

o_results := o_results || year || ', ';

end loop;

– trim off the trailing ', ’

o_results := substr ( o_results, 1, length (o_results) - 2);

return o_results;

end;

select distinct a.employee_id, get_acryear(a.employee_id) from a_employeemaster
a;

HTH

chris
image001.jpeg

6

Liaqat,

I’ll just reference a similar email I answered recently about how to
concatenate a column.

Basically the easiest way not knowing the number of rows to concatenate
you can simply create a function that returns the concatenated data like
below.

So for your query replace the date/year column with the function call
passing in the EMPLOYEEID to retrieve the years.

If you need more help let us know.

Ed
[TeamT]

-------- Original Message --------
Subject: Re: [toad] concatenation in toad challenge.
Date: Tue, 13 Jul 2010 12:55:04 -0400

7

[ Attachment(s) from Erwin Rollauer included below]

I have attached a file that uses CONNECT BY to do this without requiring a
function.

What you are trying to do is NOT NORMAL SQL so you have to use major ingenuity
such as the Function that TRYON suggested, the CONNECT BY that I am suggesting.

8

The only real way is to call a function

Not quite the only real way. The last time this question came through (just a
couple weeks ago if I remember correctly) there was a very nice solution
provided. It can be seen in the following example.

Notes:

  1. The credit for the solution lies with the original author, I simply kept a
    sample of it in case I ever needed it.

  2. Put the code in the formatter first, knowing Yahoo it’ll wipe out all
    the extra white space.

  3. The “with” portion is strictly there to create a temporary
    “table like set” of the target data

WITH order_details AS (SELECT 1 order_no,

'ABC' item_code

FROM DUAL

UNION ALL

SELECT 1 order_no,

'DEF' item_code

FROM DUAL

UNION ALL

SELECT 1 order_no,

'GHI' item_code

FROM DUAL

UNION ALL

SELECT 2 order_no,

'JKL' item_code

FROM DUAL

UNION ALL

SELECT 2 order_no,

'MNO' item_code

FROM DUAL

UNION ALL

SELECT 3 order_no,

'PQR' item_code

FROM DUAL)

SELECT order_no,

SUBSTR( SYS_CONNECT_BY_PATH( item_code,

','

),

2

)

item_codes

FROM ( SELECT order_no,

item_code,

ROW_NUMBER( ) OVER (PARTITION BY order_no ORDER BY item_code) rn

FROM order_details )

WHERE CONNECT_BY_ISLEAF = 1

CONNECT BY ( rn = PRIOR rn + 1 ) AND

( order_no = PRIOR order_no )

START WITH rn = 1

ORDER BY order_no;

Produces:

Row# ORDER_NO ITEM_CODES

1 1 ABC,DEF,GHI

2 2 JKL,MNO

3 3 PQR

Roger S.