Home » SQL & PL/SQL » SQL & PL/SQL » Display first value only for repeated values in a column
Display first value only for repeated values in a column [message #248640] Fri, 29 June 2007 16:41 Go to next message
yerics
Messages: 89
Registered: August 2006
Member
-- Table with Insert Scripts.

CREATE TABLE elabel
(
form_number number,
elabel_number number,
system_id varchar2(30),
userid_type_format varchar2(30),
resource_type_number number,
resource_type varchar2(30),
resource_entitlement varchar2(40)
);


INSERT INTO elabel values (4008,042,'MFTSBC','IDSX**',1,'USERNAME');
INSERT INTO elabel values (4008,042,'MFTSBC','IDSX**',2,'PASSWORD');
INSERT INTO elabel values (4008,042,'MFTSBC','IDSX**',3,'GROUP');
INSERT INTO elabel values (4008,042,'MFTSBC','IDSX**',4,'DFLTGRP');
INSERT INTO elabel values (4008,042,'MFTSBC','IDSX**',5,'PROFILE');
INSERT INTO elabel values (4008,042,'MFTSBC','SYNCC*',1,'USERNAME');
INSERT INTO elabel values (4008,042,'MFTSBC','SYNCC*',2,'PASSWORD');
INSERT INTO elabel values (4008,042,'MFTSBC','SYNCC*',3,'PROF');
INSERT INTO elabel values (4008,042,'MFTSBC','SYNCC*',4,'INSTDATA');

-- Data Info - For a given form number and elabel number (4008 and 042), a user(USERNAME) may be created on multiple systems (MFTSBC, MFTSTC) with different resource_types like (PASSWORD, DFLT GRP etc). The resource_type_number provides the order in which the various resource for the users will be installed.

I have the code which accepts the form number and elabel number as paramaters and generates a xml. Basically

1--> Get the form number and elabel number
2-- Get distinct value of system_id and loop to get the grouping of resource_type and resource_entitlement to generate the xml.

--Problem
I need to generate the xml in the order of resource_type_number
1--> Get the form number and elabel number
2--> Get distinct value of system id
3--> get the userid type format for this system id and in the correct order ( Not sure how to be done.)
--> Use the userid_type_format to get the resource_type and resource_number in the correct order for the xml.

I dont need the xml, but only the code in SQL to get the data in the correct order.

Re: Display first value only for repeated values in a column [message #248641 is a reply to message #248640] Fri, 29 June 2007 16:43 Go to previous messageGo to next message
yerics
Messages: 89
Registered: August 2006
Member
--Sorry the insert script is as below.
INSERT INTO elabel values (4008,042,'MFTSBC','IDSX**',1,'USERNAME','abc');
INSERT INTO elabel values (4008,042,'MFTSBC','IDSX**',2,'PASSWORD','pass12345');
INSERT INTO elabel values (4008,042,'MFTSBC','IDSX**',3,'GROUP','dxg');
INSERT INTO elabel values (4008,042,'MFTSBC','IDSX**',4,'DFLTGRP','dxg');
INSERT INTO elabel values (4008,042,'MFTSBC','IDSX**',5,'PROFILE','prof1');
INSERT INTO elabel values (4008,042,'MFTSBC','SYNCC*',6,'USERNAME','abc');
INSERT INTO elabel values (4008,042,'MFTSBC','SYNCC*',7,'PASSWORD','passxy123');
INSERT INTO elabel values (4008,042,'MFTSBC','SYNCC*',8,'PROF','prof1');
INSERT INTO elabel values (4008,042,'MFTSBC','SYNCC*',9,'INSTDATA','wel');
Re: Display first value only for repeated values in a column [message #248663 is a reply to message #248640] Sat, 30 June 2007 01:46 Go to previous messageGo to next message
Michel Cadot
Messages: 64111
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
First:
Please read and follow How to format your posts and How to get a quick answer to your question: TIPS AND TRICKS
Break your lines to max 80-100 characters when you format.
Please always post your Oracle version (4 decimals).

Then, is this not just:
SQL> select form_number, elabel_number, system_id, userid_type_format, 
  2         resource_type, resource_entitlement
  3  from elabel
  4  order by form_number, elabel_number, system_id, userid_type_format, 
  5           resource_type_number 
  6  /
FORM_NUMBER ELABEL_NUMBER SYSTEM_ID USERID_TYPE_FORMAT RESOURCE_TYPE RESOURCE_ENTITLEMENT
----------- ------------- --------- ------------------ ------------- --------------------
       4008            42 MFTSBC    IDSX**             USERNAME      abc
       4008            42 MFTSBC    IDSX**             PASSWORD      pass12345
       4008            42 MFTSBC    IDSX**             GROUP         dxg
       4008            42 MFTSBC    IDSX**             DFLTGRP       dxg
       4008            42 MFTSBC    IDSX**             PROFILE       prof1
       4008            42 MFTSBC    SYNCC*             USERNAME      abc
       4008            42 MFTSBC    SYNCC*             PASSWORD      passxy123
       4008            42 MFTSBC    SYNCC*             PROF          prof1
       4008            42 MFTSBC    SYNCC*             INSTDATA      wel

9 rows selected.

If not please post the result (in tabular format) you should have.

Regards
Michel
Re: Display first value only for repeated values in a column [message #248876 is a reply to message #248640] Mon, 02 July 2007 09:31 Go to previous messageGo to next message
yerics
Messages: 89
Registered: August 2006
Member
Let me explain it better.
I have to generate a xml from the table above in the following format

<Form_Number>
<Elabel_Number>
<System_Id>
<Userid_Format>
<Resource_Type>
<Resource_Type>
..
..
</Userid_Format>
<Userid_Format>
<Resource_Type>
<Resource_Type>
..
</Userid_Format>
<System_Id>
<Userid_Format>
<Resource_Type>
<Resource_Type>
..
..
</Userid_Format>
<Userid_Format>
<Resource_Type>
<Resource_Type>
..
</Userid_Format>
</System_Id>
</Elabel_Number>
</Form_Number>

So The step I follow in my proc is

a) Get the form_number and elabel_number (which is IN variable to the Proc)
and use UTL_FILE to form the Top element of the XML.
b) I get the distinct values of System_id (Since for a form and elabel there can be many system_IDs.
c) Use the distinct values in a loop and get the userid_format for the system_Id. (The problem is within a System_ID,
the userid_format must be in a specific order using resource_type_number. I am not sure how to do this.
For eg. If the system_id is 'MFTSBC' the value got out of distinct.. then within 'MFTSBC' the userid_format must be in
order specified by resource_type_number. The snippet of the code I use to generate xml as part of the loop is

SELECT
xmlelement("Entitlements",
xmlagg(xmlforest(
resource_type as "ResourceType",
resource_entity as "ResourceEntity",
resource_entitlement as "ResourceEntitlement"
)
))
INTO vx_xmlrows
FROM (
SELECT resource_type,
resource_entity,
resource_entitlement
FROM elabel_entitlements
WHERE form_number = p_form_number
AND elabel_number = p_elabel_number
AND nvl(system_id,'!!!') = nvl(elabelrows.system_id,'!!!')
ORDER BY resource_type_number
);
Re: Display first value only for repeated values in a column [message #248886 is a reply to message #248640] Mon, 02 July 2007 10:21 Go to previous messageGo to next message
MarcL
Messages: 455
Registered: November 2006
Location: Connecticut, USA
Senior Member
How about showing an example with your data of what you are looking to do ?
Re: Display first value only for repeated values in a column [message #248887 is a reply to message #248876] Mon, 02 July 2007 10:26 Go to previous messageGo to next message
Michel Cadot
Messages: 64111
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
As I said, we provided us data for an exemple, now provide us the result IN TABULAR FORMAT NOT XML of what you want.

Regards
Michel
Re: Display first value only for repeated values in a column [message #248900 is a reply to message #248887] Mon, 02 July 2007 11:02 Go to previous messageGo to next message
yerics
Messages: 89
Registered: August 2006
Member
Well. In xml format

<FormNumber>
4008
<ElabelNumber>
42
<SystemId>
MFTSBC
<UseridFormat>
IDSX**
<Entitlements>
<ResourceType>USERNAME</ResourceType>
<ResourceEntitlement>abc</ResourceEntitlement>
<ResourceType>PASSWORD</ResourceType>
<ResourceEntitlement>pass12345</ResourceEntitlement>
<ResourceType>GROUP</ResourceType>
<ResourceEntitlement>dxg</ResourceEntitlement>
<ResourceType>DFLTGRP</ResourceType>
<ResourceEntitlement>dxg</ResourceEntitlement>
<ResourceType>PROFILE</ResourceType>
<ResourceEntitlement>prof1</ResourceEntitlement>
</Entitlements>
</UseridFormat>
</SystemId>

<SystemId>
MFTSBC
<UseridFormat>
SYNCC*
<Entitlements>
<ResourceType>USERNAME</ResourceType>
<ResourceEntitlement>abc</ResourceEntitlement>
<ResourceType>PASSWORD</ResourceType>
<ResourceEntitlement>passxy123</ResourceEntitlement>
<ResourceType>PROF</ResourceType>
<ResourceEntitlement>prof1</ResourceEntitlement>
<ResourceType>INSTADATA</ResourceType>
<ResourceEntitlement>wel</ResourceEntitlement>
</Entitlements>
</UseridFormat>
</SystemId>
</ElabelNumber>
</FormNumber>

In tabular format
Form_Number Elabel_Number System_ID Userid_Type_ Resource_Type Resource_type resource_
Format _number entitlement
-------- ---------- --------- ----------- ----------- ----------- -----------
4008 042 MFTSBC IDSX** 1 USERNAME abc
2 PASSWORD pass12345
3 GROUP dxg
4 DFLTGRP dxg
5 PROFILE prof1
SYNCC* 6 USERNAME abc
7 PASSWORD passxy123
8 PROF prof1
9 INSTDATA wel
Re: Display first value only for repeated values in a column [message #248903 is a reply to message #248900] Mon, 02 July 2007 11:11 Go to previous messageGo to next message
Michel Cadot
Messages: 64111
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator


FORMAT YOUR POST

Read the link I posted.

Regards
Michel
Re: Display first value only for repeated values in a column [message #248906 is a reply to message #248640] Mon, 02 July 2007 11:38 Go to previous messageGo to next message
yerics
Messages: 89
Registered: August 2006
Member
I apologize for the formatting thing. I am attaching a text file containing the examplle of XML and table (to match the XML style).

Regards,
  • Attachment: TestData.txt
    (Size: 2.31KB, Downloaded 193 times)
Re: Display first value only for repeated values in a column [message #248911 is a reply to message #248640] Mon, 02 July 2007 12:18 Go to previous messageGo to next message
MarcL
Messages: 455
Registered: November 2006
Location: Connecticut, USA
Senior Member
Except most folks here will not download an attachment
Re: Display first value only for repeated values in a column [message #248927 is a reply to message #248640] Mon, 02 July 2007 14:09 Go to previous messageGo to next message
yerics
Messages: 89
Registered: August 2006
Member
I formatted the data to fit here.Maybe it will help.

FN EN SID UIDFORMAT RNO RESTYPE RESENTITL
-- -- ---- -------- --- ------ ------
4008 042 MFTSBC IDSX** 1 USERNAME abc
2 PASSWORD pass12345
3 GROUP dxg
4 DFLTGRP dxg
5 PROFILE prof1
SYNCC* 6 USERNAME abc
7 PASSWORD passxy123
8 PROF prof1
9 INSTDATA wel
Re: Display first value only for repeated values in a column [message #248929 is a reply to message #248927] Mon, 02 July 2007 14:15 Go to previous messageGo to next message
Michel Cadot
Messages: 64111
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
I don't see any format.
What's your problem with the link I posted?

Regards
Michel
Re: Display first value only for repeated values in a column [message #250031 is a reply to message #248927] Sat, 07 July 2007 02:52 Go to previous message
Volder
Messages: 38
Registered: April 2007
Location: Russia
Member
@yerics

The needed o/p can be got by the following query:

select XMLElement("form_number",
                  form_number,
                  XMLElement("elabel_number", elabel_number, XMLAgg(XML))) Col
  from (select form_number,
               elabel_number,
               XMLElement("system_id",
                          system_id,
                          XMLElement("useridformat",
                                     userid_type_format,
                                     XMLElement("Entitlements",
                                                XMLagg(XMLForest(resource_type,
                                                                 resource_entitlement))))) XML
          from (select * from elabel order by resource_type_number)
         group by form_number, elabel_number, system_id, userid_type_format)
 group by form_number, elabel_number


comments:
1) I'm not an expert in XML, everything I used here I've read here before answering you.
2) I'm sure this query can be rewritten in a simpler way, e.g. without using 2 "group by's"
3) BTW your tabular result and XML result are different (column "system_id" accorfing to tabular op should be displayed in the XML only once).
4) read the forum rules, and format your messages in the proper way. I'm sure more people will try to help you and respond.
regards.
VB.
Previous Topic: Generate triggers on multiple tables (Merged)
Next Topic: Function Help
Goto Forum:
  


Current Time: Mon Dec 05 19:07:42 CST 2016

Total time taken to generate the page: 0.09147 seconds