Home » SQL & PL/SQL » SQL & PL/SQL » showing the values just once for matching records
showing the values just once for matching records [message #261083] Tue, 21 August 2007 14:52 Go to next message
mohapatra
Messages: 24
Registered: August 2006
Location: New Jersey
Junior Member




Please note last 2 columns . The query requires that only one of the rows for that primary key shows the values for those columns and others as 0 or null.that is they dont want to report the last 2 columns twice for the same primary key records.

I tried a lot , but could not figure out .Can somebody help me on this.any help will be greatly appreciated.

[Updated on: Wed, 22 August 2007 13:52]

Report message to a moderator

Re: showing the values just once for matching records [message #261085 is a reply to message #261083] Tue, 21 August 2007 14:58 Go to previous messageGo to next message
joy_division
Messages: 4640
Registered: February 2005
Location: East Coast USA
Senior Member
Please learn how to post your code formatted so it is readable. If someone asked you to solve this, you would be unable to make heads or tails of the data. Please read the sticky.

answer:
break on primary_key nodup
Re: showing the values just once for matching records [message #261117 is a reply to message #261085] Tue, 21 August 2007 21:22 Go to previous messageGo to next message
mohapatra
Messages: 24
Registered: August 2006
Location: New Jersey
Junior Member
Thanks.. apolozize for not formatting the post.
I think Break ON may not work, as using a SQL*plus command , would be used in crystal report's query. I need something to be modified in SQL itself.
Re: showing the values just once for matching records [message #261120 is a reply to message #261083] Tue, 21 August 2007 21:42 Go to previous messageGo to next message
BlackSwan
Messages: 25036
Registered: January 2009
Location: SoCal
Senior Member
>would be used in crystal report's query.
Typical scope creep & requirement redefinition.

>I need something to be modified in SQL itself.
So please modify the SQL itself.

Perhaps you should post this question in a Crystal Reports (CR) forum to learn how CR can do the same as SQL*Plus.

[Updated on: Tue, 21 August 2007 21:43] by Moderator

Report message to a moderator

Re: showing the values just once for matching records [message #261152 is a reply to message #261083] Wed, 22 August 2007 01:05 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
The query requires that only one of the rows for that primary key shows the values for those columns and others as 0 or null

Which one?

Regards
Michel
Re: showing the values just once for matching records [message #261234 is a reply to message #261152] Wed, 22 August 2007 03:33 Go to previous messageGo to next message
darshanmeel
Messages: 44
Registered: June 2007
Location: India
Member
Hi

Please use the lead function for the key.Then comapre the value of lead and key if both are not same then only display the values otherwise not.

Here is an example

create table tst(id,number(4,0),name varchar2(30),jndt date,bts number(4,0))

insert into tst values(1,'dsarshan',sysdate,12);
insert into tst values(1,'singh',sysdate,12);

insert into tst values(1,'meel',sysdate,12);

insert into tst values(2,'vikram',sysdate,12);

insert into tst values(2,'saran',sysdate,12);

insert into tst values(3,'meel singh',sysdate,12);
insert into tst values(4,'meel darshan singh',sysdate,12);

Then please try following query

select id,name,case
when id<> lead_id then jndt
else null
end jndt,
case
when id<>lead_id then bts
else null
end bts
from (
select id,name,jnts,bts,lead(id) over(order by id) lead_id
from tst
);

To handle null values for lead_id use lead(id,0).

Hope this might help.I am sorry if i havent put anything in format.

Darshan Singh Meel

Re: showing the values just once for matching records [message #261242 is a reply to message #261234] Wed, 22 August 2007 03:47 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
1/
Please read and follow How to format your posts

2/ lead needs an order, so you must have the answer to the question:
Quote:
Which one is the first one?

Regards
Michel
Re: showing the values just once for matching records [message #261252 is a reply to message #261242] Wed, 22 August 2007 03:56 Go to previous messageGo to next message
darshanmeel
Messages: 44
Registered: June 2007
Location: India
Member
Thanks Michal

1. I do not have the server on the same machine on which i am working.So i need to use the virtual machine so i couldnt copy all steps simpley i have written what i have done.

2. Which one is first in this case It depends on the input of the inner query.The row which will contain the balues will be the last for that particular rowid in the inner query.

If you need a particular order then you need to specify it by sorting on one more column say name in inner query.

Hope now it is fine.
Re: showing the values just once for matching records [message #261268 is a reply to message #261252] Wed, 22 August 2007 04:24 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
1. I do not have the server on the same machine on which i am working.So i need to use the virtual machine so i couldnt copy all steps simpley i have written what i have done.

What this has to do with what I said? And with the ability to format?
Quote:
2. Which one is first in this case It depends on the input of the inner query

It depends on the requirements that OP have and that he didn't share with us as he didn't answer to this question.
Till now there is no order, so you can't use lead (logically speaking).

Regards
Michel
Re: showing the values just once for matching records [message #261271 is a reply to message #261268] Wed, 22 August 2007 04:28 Go to previous messageGo to next message
darshanmeel
Messages: 44
Registered: June 2007
Location: India
Member
Quote:
1. I do not have the server on the same machine on which i am working.So i need to use the virtual machine so i couldnt copy all steps simpley i have written what i have done.


What this has to do with what I said? And with the ability to format? I was not able to copy everything form sql plus.
Quote:
2. Which one is first in this case It depends on the input of the inner query


It depends on the requirements that OP have and that he didn't share with us as he didn't answer to this question.
Till now there is no order, so you can't use lead (logically speaking).This is general query.Now OP has to decide in what order he wants the data.Once that is clear query could be refined.
Re: showing the values just once for matching records [message #261279 is a reply to message #261268] Wed, 22 August 2007 04:48 Go to previous messageGo to next message
darshanmeel
Messages: 44
Registered: June 2007
Location: India
Member
select id,name,case
when id<> lead_id then jndt
else null
end jndt,
case
when id<>lead_id then bts
else null
end bts
from (
select id,name,jnts,bts,lead(id) over(order by id,name) lead_id
from tst
);

This is query if user wants that the order should be by name.The first value contains all the jnts and bts values but other values for same id containg null or 0 whatever it maybe.
Similiarly if user want the sorting on other columns he can include the same in the over() clause's order by.

Now it would be more clear.
Re: showing the values just once for matching records [message #261283 is a reply to message #261279] Wed, 22 August 2007 04:52 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Why don't you want to read and follow How to format your posts?

I understand your query, I just say you can't give an answer BEFORE OP gives all requirements.
Any answer with LEAD is (logically) wrong UNTIL the requirements are complete.

Is this clear now?

Regards
Michel
Re: showing the values just once for matching records [message #261292 is a reply to message #261283] Wed, 22 August 2007 05:01 Go to previous messageGo to next message
darshanmeel
Messages: 44
Registered: June 2007
Location: India
Member
Michel

What i was trying to tell here is that query which i have posted is very general query and users could modify the query as per thier requirement.
Re: showing the values just once for matching records [message #261437 is a reply to message #261292] Wed, 22 August 2007 10:38 Go to previous messageGo to next message
mohapatra
Messages: 24
Registered: August 2006
Location: New Jersey
Junior Member
Darshan , thanks for your response,. I think Mike is trying to say that the query that you wrote , u need to write it with proper indentation and format .(i.e to follow the format standard)
Re: showing the values just once for matching records [message #261439 is a reply to message #261437] Wed, 22 August 2007 10:45 Go to previous messageGo to next message
mohapatra
Messages: 24
Registered: August 2006
Location: New Jersey
Junior Member

please check the desired output in attached notepad . The user doesnt want the values of the last 2 columns to be reported twice.just once is enough.

The query is attached in the original question. with BREAK ON.. NODUP , it worked perfectly. But this is a SQL*Plus command. this wont work . I need this in a single query. Thanks for all your help and sugestions.
  • Attachment: output.txt
    (Size: 0.31KB, Downloaded 117 times)
Re: showing the values just once for matching records [message #261442 is a reply to message #261439] Wed, 22 August 2007 11:03 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Yes but my question still still remains: which one is first one?
why:
C James 12001 ZUR 5/12/2000 15000  15000    0     0
A Tom   12001 ZUR 9/1/2005  551.78 15364.28 36768 7/1/2007

and not
C James 12001 ZUR 5/12/2000 15000  15000    36768 7/1/2007
A Tom   12001 ZUR 9/1/2005  551.78 15364.28 0     0

Why James and not Tom or the opposite?

Regards
Michel
Re: showing the values just once for matching records [message #261445 is a reply to message #261442] Wed, 22 August 2007 11:08 Go to previous messageGo to next message
mohapatra
Messages: 24
Registered: August 2006
Location: New Jersey
Junior Member
I dont care which one..those 2 last columns sud be just reported once.
Re: showing the values just once for matching records [message #261447 is a reply to message #261445] Wed, 22 August 2007 11:21 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
It just amazes me why don't simply not display it at application level.
Anyway:
SQL> select ename, dname from emp e, dept d where d.deptno=e.deptno order by 2,1;
ENAME      DNAME
---------- --------------
CLARK      ACCOUNTING
KING       ACCOUNTING
MILLER     ACCOUNTING
FORD       RESEARCH
JONES      RESEARCH
SMITH      RESEARCH
ALLEN      SALES
BLAKE      SALES
JAMES      SALES
MARTIN     SALES
TURNER     SALES
WARD       SALES

12 rows selected.

SQL> select ename, 
  2         decode(lag(dname) over(partition by dname order by null),null,dname) deptname
  3  from emp e, dept d 
  4  where d.deptno=e.deptno 
  5  order by dname, ename;
ENAME      DEPTNAME
---------- --------------
CLARK      ACCOUNTING
KING
MILLER
FORD       RESEARCH
JONES
SMITH
ALLEN      SALES
BLAKE
JAMES
MARTIN
TURNER
WARD

12 rows selected.

Regards
Michel
Re: showing the values just once for matching records [message #261537 is a reply to message #261447] Thu, 23 August 2007 00:08 Go to previous messageGo to next message
darshanmeel
Messages: 44
Registered: June 2007
Location: India
Member
Michel

I have suggested the same solution and you were not agreed.I used the lead function.You are using lag function.Anyway thanks for a new approach to look on things.
Re: showing the values just once for matching records [message #261558 is a reply to message #261537] Thu, 23 August 2007 01:03 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
I was not in disagrement with your solution (I didn't analyze it, I don't read non formatted SQL), I was not agree that you post a solution BEFORE OP explains precisely the requirements.
He did it in his last message, so I posted a solution.
Note that I reflect his requirement: "I dont care which one" with an "order by null".
You order by id or name, this is not in the requirements.

Prime directive: Thou shalt do nothing that is not absolutly indispensable

Regards
Michel
Re: showing the values just once for matching records [message #261560 is a reply to message #261558] Thu, 23 August 2007 01:06 Go to previous message
darshanmeel
Messages: 44
Registered: June 2007
Location: India
Member
Thanks.

Hope will get the inputs of OP and then reply.
Previous Topic: Stored Procedure to merge Two tables
Next Topic: Max statement
Goto Forum:
  


Current Time: Sun Dec 04 18:41:28 CST 2016

Total time taken to generate the page: 0.07692 seconds