Home » SQL & PL/SQL » SQL & PL/SQL » need the query to hide the column values (toad 9.1 ,oracle 10g)
need the query to hide the column values [message #308359] Mon, 24 March 2008 02:22 Go to next message
ram anand
Messages: 244
Registered: February 2008
Location: india
Senior Member
Hi guys,
I need a query regarding a scenerio which shown clearly below

create table xxx (name varchar2(20),dept varchar2(20),aera varchar2(7));

Here now i created the table named XXX and inserted the values
and the table could view as shown below

NAME DEPT AREA
A D1 A1
A D1 A2
A D2 A1
A D2 A2
A D2 A3

In the table Above the NAME A has different DEPT and different AREA ,now whats the scenerio is that i need to show the table in the below format

NAME DEPT AREA
A D1 A1
- - A2
A D2 A1
- - A2
- - A3
(ie) i need the AREA to be displayed for the single NamE and Dept

So pls any one help regarding this issue

Thanks,
Ram.




[Updated on: Mon, 24 March 2008 02:26]

Report message to a moderator

Re: need the query to hide the column values [message #308360 is a reply to message #308359] Mon, 24 March 2008 02:26 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
You should read OraFAQ Forum Guide, especially "How to format your post?" section.
Indent the code (See SQL Formatter) and align the columns in result.
Use the "Preview Message" button to verify.

Have a look at:
SQL*PlusŪ User's Guide and Reference
Chapter 6 Formatting SQL*Plus Reports
Section Clarifying Your Report with Spacing and Summary Lines
Subsection Suppressing Duplicate Values in Break Columns

Regards
Michel
Re: need the query to hide the column values [message #308394 is a reply to message #308360] Mon, 24 March 2008 03:45 Go to previous messageGo to next message
ram anand
Messages: 244
Registered: February 2008
Location: india
Senior Member
Hi,
Thanks a lot MR.Michel

Thanks&regards
Re: need the query to hide the column values [message #308417 is a reply to message #308360] Mon, 24 March 2008 04:44 Go to previous messageGo to next message
ram anand
Messages: 244
Registered: February 2008
Location: india
Senior Member
Hi,
I need the O/P in the form of select Query because when i run the Script pls see the below file to verify the error, if we run as Script exceution it runs but using ordinary exceution it throws the error,so pls give me the correct answer

Thanks,
  • Attachment: select.doc
    (Size: 138.00KB, Downloaded 495 times)
Re: need the query to hide the column values [message #308422 is a reply to message #308417] Mon, 24 March 2008 04:56 Go to previous messageGo to next message
Littlefoot
Messages: 20891
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
OraFAQ Forum Guide
Do not describe your problem and/or solution in MS Office Word, Excel or similar files and attach them to your message. Some people can not, some do not want to download them. Plain TXT files are acceptable.
Re: need the query to hide the column values [message #308428 is a reply to message #308360] Mon, 24 March 2008 05:15 Go to previous messageGo to next message
ram anand
Messages: 244
Registered: February 2008
Location: india
Senior Member
Hi,
Without break command can we do the above mentioned scenerio through the select qurey ,if means pls send the query

Thanks&regards
Ram.
Re: need the query to hide the column values [message #308432 is a reply to message #308428] Mon, 24 March 2008 05:28 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Use LAG function along with CASE/DECODE.

Regards
Michel
Re: need the query to hide the column values [message #308437 is a reply to message #308432] Mon, 24 March 2008 05:42 Go to previous messageGo to next message
ram anand
Messages: 244
Registered: February 2008
Location: india
Senior Member
Hi,
Can u pls note on this
SQL> break on department_id
SQL> SELECT DEPARTMENT_ID, LAST_NAME, SALARY
FROM EMP_DETAILS_VIEW
WHERE SALARY > 12000
ORDER BY DEPARTMENT_ID

DEPARTMENT_ID LAST_NAME SALARY
------------- ------------------------- ----------
20 Hartstein 13000
80 Russell 14000
Partners 13500
90 King 24000
Kochhar 17000
De Haan 17000


6 rows selected.
i need this same output without using BREAK command,ie it should as select query only,so pls help me reg this issue

Thanks,

[Updated on: Mon, 24 March 2008 05:43]

Report message to a moderator

Re: need the query to hide the column values [message #308444 is a reply to message #308437] Mon, 24 March 2008 05:57 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Read my first answer and follow the guidelines.
Read my previous answer and folow the link.

Regards
Michel
Re: need the query to hide the column values [message #308445 is a reply to message #308444] Mon, 24 March 2008 06:08 Go to previous messageGo to next message
ram anand
Messages: 244
Registered: February 2008
Location: india
Senior Member
Hi,
can pls show the solution reg the above said message,pls give me the select query which match the exact output

Thanks,
Ram
Re: need the query to hide the column values [message #308448 is a reply to message #308444] Mon, 24 March 2008 06:17 Go to previous messageGo to next message
ram anand
Messages: 244
Registered: February 2008
Location: india
Senior Member
Hi,
 SQL> SELECT DEPARTMENT_ID, LAST_NAME, SALARY,
LAG(department_id) OVER (order by department_id) as id
FROM EMP_DETAILS_VIEW
WHERE SALARY > 10000


DEPARTMENT_ID LAST_NAME                     SALARY         ID
------------- ------------------------- ---------- ----------
           20 Hartstein                      13000           
           30 Raphaely                       11000         20
           80 Russell                        14000         30
           80 Cambrault                      11000         80
           80 Errazuriz                      12000         80
           80 Partners                       13500         80
           80 Zlotkey                        10500         80
           80 Ozer                           11500         80
           80 Abel                           11000         80
           80 Vishney                        10500         80
           90 King                           24000         80
           90 Kochhar                        17000         90
           90 De Haan                        17000         90
          100 Greenberg                      12000         90
          110 Higgins                        12000        100
15 rows selected.

I need the o/p as

DEPARTMENT_ID LAST_NAME                     SALARY
------------- ------------------------- ----------
           20 Hartstein                      13000
           30 Raphaely                       11000
           80 Russell                        14000
              Cambrault                      11000
              Errazuriz                      12000
              Partners                       13500
              Zlotkey                        10500
              Ozer                           11500
              Abel                           11000
              Vishney                        10500
           90 King                           24000
              Kochhar                        17000
              De Haan                        17000
          100 Greenberg                      12000
          110 Higgins                        12000


15 rows selected.

[Updated on: Mon, 24 March 2008 06:28]

Report message to a moderator

Re: need the query to hide the column values [message #308453 is a reply to message #308448] Mon, 24 March 2008 06:58 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
In your query, you know the current department id and the previous, what you want is that when they are equal you displau NULL instead of department id. This is there you need DECODE or CASE.

Regards
Michel
Re: need the query to hide the column values [message #308457 is a reply to message #308453] Mon, 24 March 2008 07:15 Go to previous messageGo to next message
ram anand
Messages: 244
Registered: February 2008
Location: india
Senior Member
Hi,
Can u pls show the select query for this issue

Thanks,
Re: need the query to hide the column values [message #308458 is a reply to message #308457] Mon, 24 March 2008 07:25 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
case when value1=value2 then null else value1 end
or
decode(value1,value2,null,value1)

Try it and post what you tried.

Regards
Michel
Re: need the query to hide the column values [message #308465 is a reply to message #308458] Mon, 24 March 2008 07:40 Go to previous messageGo to next message
ram anand
Messages: 244
Registered: February 2008
Location: india
Senior Member
Hi,

SELECT DEPARTMENT_ID, LAST_NAME, SALARY,
LAG(department_id,1,0) OVER (order by department_id) as prev_id,
decode(department_id,LAG(department_id,1,0) OVER (order by department_id),NULL,department_id)
FROM EMP_DETAILS_VIEW
WHERE SALARY > 10000



DEPARTMENT_ID LAST_NAME                     SALARY    PREV_ID
------------- ------------------------- ---------- ----------
DECODE(DEPARTMENT_ID,LAG(DEPARTMENT_ID,1
----------------------------------------
           20 Hartstein                      13000          0
20                                      
                                                                                
           30 Raphaely                       11000         20
30                                      
                                                                                
           80 Russell                        14000         30
80                                      
                                                                                
           80 Cambrault                      11000         80
                                        
                                                                                
           80 Errazuriz                      12000         80
                                        
                                                                                
           80 Partners                       13500         80
                                        
                                                                                
           80 Zlotkey                        10500         80

DEPARTMENT_ID LAST_NAME                     SALARY    PREV_ID
------------- ------------------------- ---------- ----------
DECODE(DEPARTMENT_ID,LAG(DEPARTMENT_ID,1
----------------------------------------
                                        
                                                                                
           80 Ozer                           11500         80
                                        
                                                                                
           80 Abel                           11000         80
                                        
                                                                                
           80 Vishney                        10500         80
                                        
                                                                                
           90 King                           24000         80
90                                      
                                                                                
           90 Kochhar                        17000         90
                                        
                                                                                
           90 De Haan                        17000         90
                                        

DEPARTMENT_ID LAST_NAME                     SALARY    PREV_ID
------------- ------------------------- ---------- ----------
DECODE(DEPARTMENT_ID,LAG(DEPARTMENT_ID,1
----------------------------------------
                                                                                
          100 Greenberg                      12000         90
100                                     
                                                                                
          110 Higgins                        12000        100
110                                     
                                                                                


15 rows selected.


Thanks,
Re: need the query to hide the column values [message #308467 is a reply to message #308465] Mon, 24 March 2008 07:45 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Yes, now replace your first department_id column by the last expression (that you can alias to department_id) and remove the prev_id expression in select list and you're done.

Regards
Michel
Re: need the query to hide the column values [message #308473 is a reply to message #308458] Mon, 24 March 2008 07:57 Go to previous messageGo to next message
ram anand
Messages: 244
Registered: February 2008
Location: india
Senior Member
Hi,
Can we give different columns in LAG command
(ie) LAG(col1,col2,col3) over(col1,col2,col3)

Thanks,
Ram.
Re: need the query to hide the column values [message #308477 is a reply to message #308467] Mon, 24 March 2008 08:12 Go to previous messageGo to next message
ram anand
Messages: 244
Registered: February 2008
Location: india
Senior Member
Hi,
Thanks a lot for your Kind reply's and answers

Regards,
Ram
Re: need the query to hide the column values [message #308575 is a reply to message #308467] Mon, 24 March 2008 23:25 Go to previous messageGo to next message
ram anand
Messages: 244
Registered: February 2008
Location: india
Senior Member
Hi,
Mr.Michel i have tried the concept of suppressing the duplicates using select query

I have created the table test3 and inserted the values
CREATE TABLE TEST3
(
  F1  VARCHAR2(10 BYTE),
  F2  VARCHAR2(15 BYTE),
  F3  VARCHAR2(10 BYTE)
)


Insert into TEST3 (F1, F2, F3) Values ('A', 'D1', 'A1');
Insert into TEST3 (F1, F2, F3) Values ('A', 'D1', 'A2');
Insert into TEST3 (F1, F2, F3) Values ('A', 'D2', 'A3');
Insert into TEST3 (F1, F2, F3) Values ('A', 'D2', 'A4');
Insert into TEST3 (F1, F2, F3) Values ('A', 'D2', 'A5');
COMMIT;



SQL> select decode(f1,LAG(f1) OVER(order by f1),null,f1)as F1,
            decode(f2,LAG(f2) OVER(order by f2),null,f2) as F2,
            F3
from test3


F1         F2              F3        
---------- --------------- ----------
A          D1              A1        
                           A2        
           D2              A3        
                           A4        
                           A5        


5 rows selected.


Here the o/p is correct but i need the following O/P ,can u pls help me with correct solution

F1         F2              F3        
---------- --------------- ----------
A          D1              A1        
                           A2        
A          D2              A3        
                           A4        
                           A5 

[Updated on: Tue, 25 March 2008 00:01] by Moderator

Report message to a moderator

Re: need the query to hide the column values [message #308579 is a reply to message #308575] Mon, 24 March 2008 23:59 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
If you want to display the value only when second column changes then only test on the second column. The result values from DECODE does not need to be the same as the ones that are tested.
That is, the first 2 parameters of DECODE in your query are the same.

Please keep your lines in 80 characters.

Regards
Michel
Re: need the query to hide the column values [message #308588 is a reply to message #308579] Tue, 25 March 2008 00:16 Go to previous messageGo to next message
ram anand
Messages: 244
Registered: February 2008
Location: india
Senior Member
Hi,
I cannot able to understand your reply PLS can u describe me briefly or can u give me the select query reg this issue

Thanks,
Ram.
Re: need the query to hide the column values [message #308603 is a reply to message #308588] Tue, 25 March 2008 00:55 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
select decode(val1,val2,null,col1) myfirstcol, decode(val1,val2,null,col2) mysecondcol, ...

Regards
Michel
Re: need the query to hide the column values [message #308646 is a reply to message #308603] Tue, 25 March 2008 02:25 Go to previous messageGo to next message
ram anand
Messages: 244
Registered: February 2008
Location: india
Senior Member
Hi,
I tried the concept but i could not get the exact answer

SQL> select decode(LAG(f1) OVER(order by f1),LAG(f2) OVER(order by f2),null,f1) as F1,
decode(LAG(f2) OVER(order by f2),LAG(f1) OVER(order by f1),null,f2) as F2,
F3
from test3

F1         F2              F3        
---------- --------------- ----------
                           A1        
A          D1              A2        
A          D2              A3        
A          D2              A4        
A          D2              A5        


5 rows selected.


can pls note on this and resolve it

Thanks,
Ram
Re: need the query to hide the column values [message #308656 is a reply to message #308646] Tue, 25 March 2008 02:39 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Your previous query was correct, you just have to put the same conditions in decode for F1 as for F2.

Regards
Michel
Re: need the query to hide the column values [message #308683 is a reply to message #308656] Tue, 25 March 2008 03:41 Go to previous messageGo to next message
ram anand
Messages: 244
Registered: February 2008
Location: india
Senior Member
Hi,
Can u pls show the query

Thanks,
Ram.
Re: need the query to hide the column values [message #308684 is a reply to message #308683] Tue, 25 March 2008 03:42 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Just replace f1 by f2 in the first 2 fields of the first decode.
Can you do that?

Regards
Michel
Re: need the query to hide the column values [message #308690 is a reply to message #308684] Tue, 25 March 2008 04:00 Go to previous messageGo to next message
ram anand
Messages: 244
Registered: February 2008
Location: india
Senior Member
Hi,
I am very sorry i cannot able proceed from your message,can u pls show the correct query ,below is that what i tried

SQL> select decode(LAG(f2) OVER(order by f1),LAG(f2) OVER(order by f2),null,f1) as F1,
decode(LAG(f1) OVER(order by f1),LAG(f2) OVER(order by f2),null,f2) as F2,F3
from test3

F1         F2              F3        
---------- --------------- ----------
                           A1        
           D1              A2        
           D2              A3        
           D2              A4        
           D2              A5        


5 rows selected.
Re: need the query to hide the column values [message #308692 is a reply to message #308690] Tue, 25 March 2008 04:03 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Use the correct query which was in message 308575.

Regards
Michel
Re: need the query to hide the column values [message #308696 is a reply to message #308692] Tue, 25 March 2008 04:09 Go to previous messageGo to next message
ram anand
Messages: 244
Registered: February 2008
Location: india
Senior Member
hi,
Thanks a lot Michel I got the exact answer u are great ,thank u very much ,below query which i tried and got the answer


SQL> select decode(f2,LAG(f2) OVER(order by f2),null,f1)as F1,
      decode(f2,LAG(f2) OVER(order by f2),null,f2) as F2,F3
from test3

F1         F2              F3        
---------- --------------- ----------
A          D1              A1        
                           A2        
A          D2              A3        
                           A4        
                           A5        


5 rows selected.
Re: need the query to hide the column values [message #308704 is a reply to message #308696] Tue, 25 March 2008 04:27 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Glad you finally got it. Smile

Regards
Michel
Re: suppressing duplicate values using the select query without break and distinct [message #308737 is a reply to message #308704] Tue, 25 March 2008 05:43 Go to previous messageGo to next message
ram anand
Messages: 244
Registered: February 2008
Location: india
Senior Member
Hi,
Here comes another concept ,till then we have tried for individual now pls have a note below

SQL> select * from test3

F1         F2              F3        
---------- --------------- ----------
A          D1              A1        
B          D1              A2        
A          D2              A3        
B          D2              A4        
B          D2              A5        
C          D3              A6        
D          D3              A7        
D          D3              A8        


8 rows selected.


this shows the table structure and the values of test3 table, now i need the following o/p
F1         F2              F3        
---------- --------------- ----------
A          D1              A1        
B          D1              A2        
A          D2              A3        
B          D2              A4        
           D2              A5        
C          D3              A6        
D          D3              A7        
           D3              A8  
           D3		   A9

but i tried with the below query,it shows

SQL> select  decode(f1,LAG(f2) OVER(order by f2),null,f1)as F1,
      decode(f2,LAG(f2) OVER(order by f2),null,f2) as F2,F3
from test3

F1         F2              F3        
---------- --------------- ----------
A          D1              A1        
B                          A2        
A          D2              A3        
B                          A4        
B                          A5        
C          D3              A6        
D                          A7        
D                          A8        


8 rows selected.


Thanks,
Ram.
Re: need the query to hide the column values [message #308750 is a reply to message #308704] Tue, 25 March 2008 06:23 Go to previous messageGo to next message
ram anand
Messages: 244
Registered: February 2008
Location: india
Senior Member
Hi,
i think so the above concept is not possible by LAG whether by any other possiblities,if means pls reply me

Thanks,
Ram.
Re: suppressing duplicate values using the select query without break and distinct [message #308751 is a reply to message #308737] Tue, 25 March 2008 06:25 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Continue to work with the previous good query. Understand why it works. You will finally get this one by yourself, it is just a slight modification. Just think about what is the modification between the 2 requirements.

Regards
Michel
Re: suppressing duplicate values using the select query without break and distinct [message #308777 is a reply to message #308751] Tue, 25 March 2008 07:23 Go to previous messageGo to next message
ram anand
Messages: 244
Registered: February 2008
Location: india
Senior Member
Hi,
I have tried using this query pls can u check it,but i could not get exact answer

SQL> select  decode(f2,LAG(f2) OVER(order by f1),null,f1)as F1,
      decode(f2,LAG(f2) OVER(order by f2),f2,f2) as F2,F3
from test3

F1         F2              F3        
---------- --------------- ----------
A          D1              A1        
B          D1              A2        
A          D2              A3        
B          D2              A4        
           D2              A5        
C          D3              A6        
           D3              A7        
D          D4              A8        
           D4              A9        
           D4              A10       


10 rows selected.


I need the O/P as

F1         F2              F3        
---------- --------------- ----------
A          D1              A1        
B          D1              A2        
A          D2              A3        
B          D2              A4        
                           A5        
C          D3              A6        
                           A7        
D          D4              A8        
                           A9        
                           A10     


Thanks,
Ram.
Re: suppressing duplicate values using the select query without break and distinct [message #308788 is a reply to message #308777] Tue, 25 March 2008 08:16 Go to previous message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Try your query in message 308575.

Regards
Michel
Previous Topic: ORA-12801: error signaled in parallel query server
Next Topic: Derived table access
Goto Forum:
  


Current Time: Sat Dec 03 20:14:53 CST 2016

Total time taken to generate the page: 0.10011 seconds