Home » SQL & PL/SQL » SQL & PL/SQL » Different Chassis no against same registration no (SQL,8.0.6.0.0,XP)
Different Chassis no against same registration no [message #345348] Wed, 03 September 2008 02:58 Go to next message
gozuhair
Messages: 206
Registered: January 2008
Senior Member
Dear All


My table contains different chassis no’s or same chassis no’s against same registration no, how can I make that type of query through which I can view only those type of chassis no’s that are different from each other but there registration no are same

For example

Create table ro_mst
(ro_chassis_no  varchar2(17),
Ro_reg_no         varchar2(10));

Insert into ro_mst
(Ro_chassis_no,ro_reg_no)
Values
(500901,aak786);

Insert into ro_mst
(Ro_chassis_no,ro_reg_no)
Values
(500902,aak786);

Insert into ro_mst
(Ro_chassis_no,ro_reg_no)
Values
(500902,aak787);

Insert into ro_mst
(Ro_chassis_no,ro_reg_no)
Values
(500902,aak787);



Required output is
Ro_chassis_no	ro_reg_no

500901		aak786
500902		aak786
Re: Different Chassis no against same registration no [message #345355 is a reply to message #345348] Wed, 03 September 2008 03:29 Go to previous messageGo to next message
ThomasG
Messages: 3189
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
Thanks for the test case.

You could use a sub-select with analytical counts with different partitions clauses and then a where-clause on that counts:

SQL> SELECT * FROM ro_mst;

RO_CHASSIS_NO     RO_REG_NO
----------------- ----------
500901            aak786
500902            aak786
500902            aak787
500902            aak787

SQL>
SQL> SELECT RO_REG_NO,
  2         RO_CHASSIS_NO,
  3         Count(*) over (PARTITION BY RO_REG_NO,RO_CHASSIS_NO ) cnt_dis,
  4         Count(*) over (PARTITION BY RO_REG_NO ) cnt_reg
  5    FROM ro_mst;

RO_REG_NO  RO_CHASSIS_NO        CNT_DIS    CNT_REG
---------- ----------------- ---------- ----------
aak786     500901                     1          2
aak786     500902                     1          2
aak787     500902                     2          2
aak787     500902                     2          2

SQL>
SQL>
SQL> SELECT RO_CHASSIS_NO, RO_REG_NO FROM (
  2      SELECT RO_REG_NO,
  3             RO_CHASSIS_NO,
  4             Count(*) over (PARTITION BY RO_REG_NO,RO_CHASSIS_NO ) cnt_dis,
  5             Count(*) over (PARTITION BY RO_REG_NO ) cnt_reg
  6        FROM ro_mst)
  7  WHERE cnt_dis =1 AND cnt_reg > 1;

RO_CHASSIS_NO     RO_REG_NO
----------------- ----------
500901            aak786
500902            aak786

SQL>
SQL>


You might have to tweak the partition by and where clause a little depending on the entire data.
Re: Different Chassis no against same registration no [message #345362 is a reply to message #345355] Wed, 03 September 2008 03:45 Go to previous messageGo to next message
Michel Cadot
Messages: 64153
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Analytical functions don't exist in 8.0.

Regards
Michel
Re: Different Chassis no against same registration no [message #345369 is a reply to message #345362] Wed, 03 September 2008 04:32 Go to previous messageGo to next message
ThomasG
Messages: 3189
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
What? How can people live without analytical functions? How can they stand it? Wink

Anyway, one possible workaround could be to put the count(*)s into further sub selects.

SQL> SELECT reg.RO_REG_NO,
  2         dis.RO_CHASSIS_NO
  3    FROM
  4     (SELECT RO_REG_NO,
  5             Count(*) cnt_reg
  6        FROM ro_mst
  7       GROUP BY RO_REG_NO
  8     ) reg,
  9     (SELECT RO_REG_NO,
 10             RO_CHASSIS_NO,
 11             Count(*) cnt_dis
 12        FROM ro_mst
 13       GROUP BY RO_REG_NO, RO_CHASSIS_NO
 14     ) dis
 15  WHERE dis.RO_REG_NO =  reg.RO_REG_NO
 16    AND cnt_dis = 1 AND cnt_reg > 1;

RO_REG_NO  RO_CHASSIS_NO
---------- -----------------
aak786     500901
aak786     500902

SQL>



Re: Different Chassis no against same registration no [message #345371 is a reply to message #345362] Wed, 03 September 2008 04:36 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
For a non-analytic solution, try this:
with src as (select 500901 chassis_no,  'aak786' reg_no from dual union all 
             select 500902, 'aak786' from dual union all
             select 500902, 'aak787' from dual union all
             select 500902, 'aak787' from dual)
select * from src
where  reg_no in (select reg_no
                  from   src
                  having count(distinct chassis_no) > 1
                  group by reg_no);


10/10 for providing a test case by the way. Makes things a lot easier
Re: Different Chassis no against same registration no [message #345513 is a reply to message #345348] Wed, 03 September 2008 22:26 Go to previous messageGo to next message
gozuhair
Messages: 206
Registered: January 2008
Senior Member
thanks you people for providing assistance,I will check it out and let you know the result.
Re: Different Chassis no against same registration no [message #345850 is a reply to message #345371] Fri, 05 September 2008 01:50 Go to previous messageGo to next message
gozuhair
Messages: 206
Registered: January 2008
Senior Member
Thank you for all,I have solved my problem by installing oracle 9i database and using provided Analytical function example.
Re: Different Chassis no against same registration no [message #345871 is a reply to message #345850] Fri, 05 September 2008 03:09 Go to previous messageGo to next message
harshadsp
Messages: 100
Registered: August 2008
Senior Member

1 select
2 b.ro_chassis_no,
3 a.ro_reg_no
4 from
5 ro_mst a,
6 ro_mst b
7 where
8 a.ro_reg_no = b.ro_reg_no
9* and b.ro_chassis_no <> a.ro_chassis_no
SQL> /

RO_CHASSIS_NO RO_REG_NO
----------------- ----------
500901 aak786
500902 aak786
Re: Different Chassis no against same registration no [message #345877 is a reply to message #345871] Fri, 05 September 2008 03:30 Go to previous message
Michel Cadot
Messages: 64153
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
@harshadsp,

Don't you see the difference in readability between your post and other ones?
please read OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code (See SQL Formatter) and align the columns in result.
Use the "Preview Message" button to verify.

Regards
Michel
Previous Topic: sum multiple column with different criteria
Next Topic: Writing a Log File
Goto Forum:
  


Current Time: Sun Dec 11 08:34:39 CST 2016

Total time taken to generate the page: 0.08547 seconds