OraFAQ Forum: JDeveloper, Java & XML » How to use left outer joins ,right outer joins and order by clause for below query (merged)

Home » Developer & Programmer » JDeveloper, Java & XML » How to use left outer joins ,right outer joins and order by clause for below query (merged)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

How to use left outer joins ,right outer joins and order by clause for below query (merged) [message #359802]

Tue, 18 November 2008 04:19

vikram1780
Messages: 222
Registered: January 2007
Location: Bangalore

Senior Member

Hi,

How to use left outer joins ,right outer joins and order by clause for below XML query.

The query which is red colour returns null then its not displaying any values for columns in that tables. Tried decode, nvl function hasn't worked.

SELECT XMLAGG ( XMLELEMENT( "P", XMLFOREST( P.process_id AS Ppid,  
		(SELECT XMLAGG( XMLELEMENT( "PI", XMLFOREST( PI.question_id AS PIqid, 
							    PI.process_id AS PIpid, 
                                                            PI.innertext AS
 							    PItext, PI.itemtype AS PItype,
							    PI.linkfrom AS PIfrom, 
							    PI.linkto AS PIto, 
                                                            PI.associated AS PIas, 
							    PI.content_id AS PIc, 
							    PI.exitpoint1_id AS PIe1, 
							    PI.exitpoint2_id AS PIe2, 
							    PI.exitpoint3_id AS PIe3, 
							    PI.followoncall AS PIfoc,
                                                            PI.userinput AS PIui, 
							    PI.resolveidentifier AS PIri, 
                                                            PI.libquestion_idfk AS PIlqid, 
							    PI.isLocked AS PIstls, 
							    PI.PreviousAnswer AS PIPAns, 
							    PI.VisibleToAgent AS PIVAgent, 
							    PI.RetryAttempt AS PIRetry, 
							    PI.Tags AS PITag,
						   SELECT XMLAGG( XMLELEMENT( "PO", XMLFOREST( PO.option_id AS POoid, 
							                                       PO.question_id AS POqid, 
											       PO.process_id AS popid,
											       PO.opt_innertext AS POtext, 
											       PO.opt_linkfrom AS POfrom, 
											       PO.opt_linkto AS POto,
											       PO.libquestion_idfk AS POlqid,
											       PO.liboption_idfk AS POloid ) ) )
      			
						   FROM vw_liveProcessOption_Sim_v6 PO
							WHERE PI.question_id = PO.question_id (+)
						        AND PI.process_id      = PO.process_id (+)
						    ) "A" ) ) ) AS "PO"
	     FROM vw_liveProcessItem_Sim_v6 PI
    		WHERE P.process_id = PI.process_id
  		) "A" ) ) ) AS "PI"
   FROM liveProcess_ec P
  WHERE (P.process_id = 450)

Any help really appreciated.

Thanks

[Updated on: Tue, 18 November 2008 04:34]

Report message to a moderator

Re: How to use left outer joins ,right outer joins and order by clause for below query [message #359812 is a reply to message #359802]

Tue, 18 November 2008 04:32

JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...

Senior Member

If you give us some CREATE TABLE and INSERT statements to create some test data that demonstrates the problem then you'll get a lot more people looking at it.
At the moment, all I see is a badly formatted query that I've not got the inclination to unpick.

Report message to a moderator

Re: How to use left outer joins ,right outer joins and order by clause for below query [message #359818 is a reply to message #359812]

Tue, 18 November 2008 04:38

vikram1780
Messages: 222
Registered: January 2007
Location: Bangalore

Senior Member

Hi Jrow,

I have edited my post and formated in a easy way. Could you pls let me know how to use the joins order by clause in above query.

Its the similar query written in a way to get XML o/p in desired format.

SQL Query

 SELECT     
	P.process_id AS Ppid, 
	PI.question_id AS PIqid, 
	PI.process_id AS PIpid, 
	PI.innertext AS PItext, 
	PI.itemtype AS PItype, 
	PI.linkfrom AS PIfrom, 
	PI.linkto AS PIto, 
	PI.associated AS PIas, 
	PI.content_id AS PIc, 
	PI.exitpoint1_id AS PIe1, 
	PI.exitpoint2_id AS PIe2, 
	PI.exitpoint3_id AS PIe3, 
	PI.followoncall AS PIfoc, 
	PI.userinput AS PIui, 
	PI.resolveidentifier AS PIri, 
	PI.libquestion_idfk AS PIlqid, 
	PI.isLocked AS PIstls, 
	PI.PreviousAnswer as PIPAns, 
	PI.VisibleToAgent as PIVAgent, 
	PI.RetryAttempt as PIRetry, 
	PI.Tags as PITag,
	PO.option_id AS POoid, 
	PO.question_id AS POqid, 
	PO.process_id AS popid, 
	PO.opt_innertext AS POtext, 
	PO.opt_linkfrom AS POfrom, 
	PO.opt_linkto AS POto, 
	PO.libquestion_idfk AS POlqid, 
	PO.liboption_idfk AS POloid
FROM        liveProcess_ec  P  INNER JOIN
          vw_liveProcessItem_Sim_v6  PI  ON P.process_id = PI.process_id LEFT OUTER JOIN
             vw_liveProcessOption_Sim_v6  PO  ON PI.question_id = PO.question_id AND PI.process_id = PO.process_id 
             where p.process_id=452            
ORDER BY Ppid, PIqid, POoid ASC;

Report message to a moderator

Re: How to use left outer joins ,right outer joins and order by clause for below query [message #359862 is a reply to message #359802]

Tue, 18 November 2008 06:24

vikram1780
Messages: 222
Registered: January 2007
Location: Bangalore

Senior Member

Hi,

Could anyone help on this.

Really i got stucked at this.

Any help really appreciated.

Thanks

Report message to a moderator

Re: How to use left outer joins ,right outer joins and order by clause for below query [message #359864 is a reply to message #359862]

Tue, 18 November 2008 06:28

JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...

Senior Member

All you've given us is a query that looks ok.

What's wrong with it? I don't know, because I don't have your tables, or your data.
That's why we like Create Table and Insert scripts - it lets us run your query and see what the problem is.

Either post a script, or describe in enought detail for someone who has no idea what your system does to understand, what the problem you're geting is, and what you need it to do.

Report message to a moderator

Re: How to use left outer joins ,right outer joins and order by clause for below query [message #359868 is a reply to message #359864]

Tue, 18 November 2008 06:45

vikram1780
Messages: 222
Registered: January 2007
Location: Bangalore

Senior Member

Hi,

Here below is the scripts of tables, insert statements and Required output.

CREATE TABLE VW_LIVEPROCESSOPTION_SIM_v6
( "OPTION_ID" NUMBER,
"QUESTION_ID" NUMBER(10,0),
"PROCESS_ID" NUMBER(10,0),
"OPT_INNERTEXT" VARCHAR2(200 CHAR),
"OPT_LINKFROM" VARCHAR2(20 CHAR),
"OPT_LINKTO" VARCHAR2(20 CHAR),
"LIBQUESTION_IDFK" NUMBER,
"LIBOPTION_IDFK" NUMBER
);

Insert into VW_LIVEPROCESSOPTION_SIM_v6(OPTION_ID,QUESTION_ID,PROCESS_ID,OPT_INNERTEXT,OPT_LINKFROM,OPT_LINKTO,LIBQUESTION_IDFK,LIBOPTION_IDFK) values (1,2,450,'Yes',null,'5',null,null);
Insert into VW_LIVEPROCESSOPTION_SIM_v6(OPTION_ID,QUESTION_ID,PROCESS_ID,OPT_INNERTEXT,OPT_LINKFROM,OPT_LINKTO,LIBQUESTION_IDFK,LIBOPTION_IDFK) values (1,3,450,'Yes',null,'5',null,null);
Insert into VW_LIVEPROCESSOPTION_SIM_v6(OPTION_ID,QUESTION_ID,PROCESS_ID,OPT_INNERTEXT,OPT_LINKFROM,OPT_LINKTO,LIBQUESTION_IDFK,LIBOPTION_IDFK) values (1,5,450,'Yes',null,'6',null,null);
Insert into VW_LIVEPROCESSOPTION_SIM_v6(OPTION_ID,QUESTION_ID,PROCESS_ID,OPT_INNERTEXT,OPT_LINKFROM,OPT_LINKTO,LIBQUESTION_IDFK,LIBOPTION_IDFK) values (1,6,450,'Yes',null,'7',null,null);
Insert into VW_LIVEPROCESSOPTION_SIM_v6(OPTION_ID,QUESTION_ID,PROCESS_ID,OPT_INNERTEXT,OPT_LINKFROM,OPT_LINKTO,LIBQUESTION_IDFK,LIBOPTION_IDFK) values (1,8,450,'Block All',null,'9',null,null);
Insert into VW_LIVEPROCESSOPTION_SIM_v6(OPTION_ID,QUESTION_ID,PROCESS_ID,OPT_INNERTEXT,OPT_LINKFROM,OPT_LINKTO,LIBQUESTION_IDFK,LIBOPTION_IDFK) values (1,9,450,'Yes',null,'10',null,null);
Insert into VW_LIVEPROCESSOPTION_SIM_v6(OPTION_ID,QUESTION_ID,PROCESS_ID,OPT_INNERTEXT,OPT_LINKFROM,OPT_LINKTO,LIBQUESTION_IDFK,LIBOPTION_IDFK) values (1,11,450,'Yes',null,'12',null,null);
Insert into VW_LIVEPROCESSOPTION_SIM_v6(OPTION_ID,QUESTION_ID,PROCESS_ID,OPT_INNERTEXT,OPT_LINKFROM,OPT_LINKTO,LIBQUESTION_IDFK,LIBOPTION_IDFK) values (1,12,450,'Yes',null,'13',null,null);
Insert into VW_LIVEPROCESSOPTION_SIM_v6(OPTION_ID,QUESTION_ID,PROCESS_ID,OPT_INNERTEXT,OPT_LINKFROM,OPT_LINKTO,LIBQUESTION_IDFK,LIBOPTION_IDFK) values (1,14,450,'Yes',null,'16',null,null);
Insert into VW_LIVEPROCESSOPTION_SIM_v6(OPTION_ID,QUESTION_ID,PROCESS_ID,OPT_INNERTEXT,OPT_LINKFROM,OPT_LINKTO,LIBQUESTION_IDFK,LIBOPTION_IDFK) values (2,2,450,'No',null,'3',null,null);
Insert into VW_LIVEPROCESSOPTION_SIM_v6(OPTION_ID,QUESTION_ID,PROCESS_ID,OPT_INNERTEXT,OPT_LINKFROM,OPT_LINKTO,LIBQUESTION_IDFK,LIBOPTION_IDFK) values (2,3,450,'No',null,'4',null,null);
Insert into VW_LIVEPROCESSOPTION_SIM_v6(OPTION_ID,QUESTION_ID,PROCESS_ID,OPT_INNERTEXT,OPT_LINKFROM,OPT_LINKTO,LIBQUESTION_IDFK,LIBOPTION_IDFK) values (2,5,450,'No',null,'8',null,null);
Insert into VW_LIVEPROCESSOPTION_SIM_v6(OPTION_ID,QUESTION_ID,PROCESS_ID,OPT_INNERTEXT,OPT_LINKFROM,OPT_LINKTO,LIBQUESTION_IDFK,LIBOPTION_IDFK) values (2,6,450,'No',null,'8',null,null);
Insert into VW_LIVEPROCESSOPTION_SIM_v6(OPTION_ID,QUESTION_ID,PROCESS_ID,OPT_INNERTEXT,OPT_LINKFROM,OPT_LINKTO,LIBQUESTION_IDFK,LIBOPTION_IDFK) values (2,8,450,'Standard',null,'11',null,null);
Insert into VW_LIVEPROCESSOPTION_SIM_v6(OPTION_ID,QUESTION_ID,PROCESS_ID,OPT_INNERTEXT,OPT_LINKFROM,OPT_LINKTO,LIBQUESTION_IDFK,LIBOPTION_IDFK) values (2,9,450,'No',null,'11',null,null);
Insert into VW_LIVEPROCESSOPTION_SIM_v6(OPTION_ID,QUESTION_ID,PROCESS_ID,OPT_INNERTEXT,OPT_LINKFROM,OPT_LINKTO,LIBQUESTION_IDFK,LIBOPTION_IDFK) values (2,11,450,'No',null,'14',null,null);
Insert into VW_LIVEPROCESSOPTION_SIM_v6(OPTION_ID,QUESTION_ID,PROCESS_ID,OPT_INNERTEXT,OPT_LINKFROM,OPT_LINKTO,LIBQUESTION_IDFK,LIBOPTION_IDFK) values (2,12,450,'No',null,'14',null,null);
Insert into VW_LIVEPROCESSOPTION_SIM_v6(OPTION_ID,QUESTION_ID,PROCESS_ID,OPT_INNERTEXT,OPT_LINKFROM,OPT_LINKTO,LIBQUESTION_IDFK,LIBOPTION_IDFK) values (2,14,450,'No',null,'15',null,null);
Insert into VW_LIVEPROCESSOPTION_SIM_v6(OPTION_ID,QUESTION_ID,PROCESS_ID,OPT_INNERTEXT,OPT_LINKFROM,OPT_LINKTO,LIBQUESTION_IDFK,LIBOPTION_IDFK) values (3,8,450,'Disabled',null,'12',null,null);
Insert into VW_LIVEPROCESSOPTION_SIM_v6(OPTION_ID,QUESTION_ID,PROCESS_ID,OPT_INNERTEXT,OPT_LINKFROM,OPT_LINKTO,LIBQUESTION_IDFK,LIBOPTION_IDFK) values (4,8,450,'User Defined',null,'12',null,null);

****************************************************************************************************************************

REATE TABLE "VW_LIVEPROCESSITEM_SIM_v6"
( "QUESTION_ID" NUMBER(10,0),
"PROCESS_ID" NUMBER(10,0),
"INNERTEXT" VARCHAR2(200 CHAR),
"ITEMTYPE" VARCHAR2(50 CHAR),
"LINKFROM" VARCHAR2(500 CHAR),
"LINKTO" VARCHAR2(500 CHAR),
"ASSOCIATED" VARCHAR2(200 CHAR),
"CONTENT_ID" NUMBER,
"EXITPOINT1_ID" NUMBER(10,0),
"EXITPOINT2_ID" NUMBER(10,0),
"EXITPOINT3_ID" NUMBER(10,0),
"RESOLVEIDENTIFIER" VARCHAR2(40 CHAR),
"LIBQUESTION_IDFK" NUMBER(10,0),
"FOLLOWONCALL" NUMBER(1,0),
"USERINPUT" VARCHAR2(200 CHAR),
"ISLOCKED" NUMBER(1,0),
"PREVIOUSANSWER" NUMBER(1,0),
"VISIBLETOAGENT" NUMBER(1,0),
"RETRYATTEMPT" NUMBER(10,0),
"TAGS" VARCHAR2(50 BYTE)
);

Insert into VW_LIVEPROCESSITEM_SIM_v6 (QUESTION_ID,PROCESS_ID,INNERTEXT,ITEMTYPE,LINKFROM,LINKTO,ASSOCIATED,CONTENT_ID,EXITPOINT1_ID,EXITPOINT2_ID,EXITPOINT3_ID,RESOLVEIDE NTIFIER,LIBQUESTION_IDFK,FOLLOWONCALL,USERINPUT,ISLOCKED,PREVIOUSANSWER,VISIBLETOAGENT,RETRYATTEMPT,TAGS) values (1,450,'CBB1015 - Router Firewall Settinngs Process','Title',null,'2',null,null,null,null,null,null,null,null,null,null,null,null,null,null);
Insert into VW_LIVEPROCESSITEM_SIM_v6 (QUESTION_ID,PROCESS_ID,INNERTEXT,ITEMTYPE,LINKFROM,LINKTO,ASSOCIATED,CONTENT_ID,EXITPOINT1_ID,EXITPOINT2_ID,EXITPOINT3_ID,RESOLVEIDE NTIFIER,LIBQUESTION_IDFK,FOLLOWONCALL,USERINPUT,ISLOCKED,PREVIOUSANSWER,VISIBLETOAGENT,RETRYATTEMPT,TAGS) values (2,450,'Is the customers PC Firewall turned off?','Question','1','2.2,2.1',null,null,null,null,null,null,null,null,null,null,null,null,null,null);
Insert into VW_LIVEPROCESSITEM_SIM_v6 (QUESTION_ID,PROCESS_ID,INNERTEXT,ITEMTYPE,LINKFROM,LINKTO,ASSOCIATED,CONTENT_ID,EXITPOINT1_ID,EXITPOINT2_ID,EXITPOINT3_ID,RESOLVEIDE NTIFIER,LIBQUESTION_IDFK,FOLLOWONCALL,USERINPUT,ISLOCKED,PREVIOUSANSWER,VISIBLETOAGENT,RETRYATTEMPT,TAGS) values (3,450,'Advise the customer to turn off the PC Firewall in order to continue. Has this been done?','Question','2.2','3.2,3.1',null,278,null,null,null,null,null,null,null,null,null,null,null,null);
Insert into VW_LIVEPROCESSITEM_SIM_v6 (QUESTION_ID,PROCESS_ID,INNERTEXT,ITEMTYPE,LINKFROM,LINKTO,ASSOCIATED,CONTENT_ID,EXITPOINT1_ID,EXITPOINT2_ID,EXITPOINT3_ID,RESOLVEIDE NTIFIER,LIBQUESTION_IDFK,FOLLOWONCALL,USERINPUT,ISLOCKED,PREVIOUSANSWER,VISIBLETOAGENT,RETRYATTEMPT,TAGS) values (4,450,'Advise the customer the PC Firewall must be switched off before this process????','ExitPoint','3.2',null,null,null,14,null,null,null,null,null,null,null,null,null,null,null);
Insert into VW_LIVEPROCESSITEM_SIM_v6 (QUESTION_ID,PROCESS_ID,INNERTEXT,ITEMTYPE,LINKFROM,LINKTO,ASSOCIATED,CONTENT_ID,EXITPOINT1_ID,EXITPOINT2_ID,EXITPOINT3_ID,RESOLVEIDE NTIFIER,LIBQUESTION_IDFK,FOLLOWONCALL,USERINPUT,ISLOCKED,PREVIOUSANSWER,VISIBLETOAGENT,RETRYATTEMPT,TAGS) values (5,450,'Is the customer able to access the internet now?','Question','3.1,2.1','5.2,5.1',null,null,null,null,null,null,null,null,null,null,null,null,null,null);
Insert into VW_LIVEPROCESSITEM_SIM_v6 (QUESTION_ID,PROCESS_ID,INNERTEXT,ITEMTYPE,LINKFROM,LINKTO,ASSOCIATED,CONTENT_ID,EXITPOINT1_ID,EXITPOINT2_ID,EXITPOINT3_ID,RESOLVEIDE NTIFIER,LIBQUESTION_IDFK,FOLLOWONCALL,USERINPUT,ISLOCKED,PREVIOUSANSWER,VISIBLETOAGENT,RETRYATTEMPT,TAGS) values (6,450,'Is the customer having a problem with a specific website?','Question','5.1','6.2,6.1',null,null,null,null,null,null,null,null,null,null,null,null,null,null);
Insert into VW_LIVEPROCESSITEM_SIM_v6 (QUESTION_ID,PROCESS_ID,INNERTEXT,ITEMTYPE,LINKFROM,LINKTO,ASSOCIATED,CONTENT_ID,EXITPOINT1_ID,EXITPOINT2_ID,EXITPOINT3_ID,RESOLVEIDE NTIFIER,LIBQUESTION_IDFK,FOLLOWONCALL,USERINPUT,ISLOCKED,PREVIOUSANSWER,VISIBLETOAGENT,RETRYATTEMPT,TAGS) values (7,450,'1536: CBB1008 - Browser Setup and Daignostics','SubProcess','6.1',null,'1536-1-0',null,null,null,null,null,null,null,null,null,null,null,null,null);
Insert into VW_LIVEPROCESSITEM_SIM_v6 (QUESTION_ID,PROCESS_ID,INNERTEXT,ITEMTYPE,LINKFROM,LINKTO,ASSOCIATED,CONTENT_ID,EXITPOINT1_ID,EXITPOINT2_ID,EXITPOINT3_ID,RESOLVEIDE NTIFIER,LIBQUESTION_IDFK,FOLLOWONCALL,USERINPUT,ISLOCKED,PREVIOUSANSWER,VISIBLETOAGENT,RETRYATTEMPT,TAGS) values (8,450,'What is the security level on the CPE Management page?','Question','6.2,5.2','8.4,8.3,8.2,8.1',null,279,null,null,null,null,null,null,null,null,null,null,null,null);
Insert into VW_LIVEPROCESSITEM_SIM_v6 (QUESTION_ID,PROCESS_ID,INNERTEXT,ITEMTYPE,LINKFROM,LINKTO,ASSOCIATED,CONTENT_ID,EXITPOINT1_ID,EXITPOINT2_ID,EXITPOINT3_ID,RESOLVEIDE NTIFIER,LIBQUESTION_IDFK,FOLLOWONCALL,USERINPUT,ISLOCKED,PREVIOUSANSWER,VISIBLETOAGENT,RETRYATTEMPT,TAGS) values (9,450,'Change the security level to Standard. Does this resolve the customers issue?','Question','8.1','9.2,9.1',null,280,null,null,null,null,null,null,null,null,null,null,null,null);
Insert into VW_LIVEPROCESSITEM_SIM_v6 (QUESTION_ID,PROCESS_ID,INNERTEXT,ITEMTYPE,LINKFROM,LINKTO,ASSOCIATED,CONTENT_ID,EXITPOINT1_ID,EXITPOINT2_ID,EXITPOINT3_ID,RESOLVEIDE NTIFIER,LIBQUESTION_IDFK,FOLLOWONCALL,USERINPUT,ISLOCKED,PREVIOUSANSWER,VISIBLETOAGENT,RETRYATTEMPT,TAGS) values (10,450,'Issue Resolved','ExitPoint','9.1',null,null,null,1,6,122,null,null,null,null,null,null,null,null,null);
Insert into VW_LIVEPROCESSITEM_SIM_v6 (QUESTION_ID,PROCESS_ID,INNERTEXT,ITEMTYPE,LINKFROM,LINKTO,ASSOCIATED,CONTENT_ID,EXITPOINT1_ID,EXITPOINT2_ID,EXITPOINT3_ID,RESOLVEIDE NTIFIER,LIBQUESTION_IDFK,FOLLOWONCALL,USERINPUT,ISLOCKED,PREVIOUSANSWER,VISIBLETOAGENT,RETRYATTEMPT,TAGS) values (11,450,'Change the security level to Disabled. Is the customer able to browse the internet?','Question','9.2,8.2','11.2,11.1',null,281,null,null,null,null,null,null,null,null,null,null,null,null);
Insert into VW_LIVEPROCESSITEM_SIM_v6 (QUESTION_ID,PROCESS_ID,INNERTEXT,ITEMTYPE,LINKFROM,LINKTO,ASSOCIATED,CONTENT_ID,EXITPOINT1_ID,EXITPOINT2_ID,EXITPOINT3_ID,RESOLVEIDE NTIFIER,LIBQUESTION_IDFK,FOLLOWONCALL,USERINPUT,ISLOCKED,PREVIOUSANSWER,VISIBLETOAGENT,RETRYATTEMPT,TAGS) values (12,450,'Change the security level to Standard. Is the customer able to browse the internet now?','Question','11.1,8.3,8.4','12.2,12.1',null,283,null,null,null,null,null,null,null,null,null,null,null,null);
Insert into VW_LIVEPROCESSITEM_SIM_v6 (QUESTION_ID,PROCESS_ID,INNERTEXT,ITEMTYPE,LINKFROM,LINKTO,ASSOCIATED,CONTENT_ID,EXITPOINT1_ID,EXITPOINT2_ID,EXITPOINT3_ID,RESOLVEIDE NTIFIER,LIBQUESTION_IDFK,FOLLOWONCALL,USERINPUT,ISLOCKED,PREVIOUSANSWER,VISIBLETOAGENT,RETRYATTEMPT,TAGS) values (13,450,'Issue Resolved','ExitPoint','12.1',null,null,null,1,6,123,null,null,null,null,null,null,null,null,null);
Insert into VW_LIVEPROCESSITEM_SIM_v6 (QUESTION_ID,PROCESS_ID,INNERTEXT,ITEMTYPE,LINKFROM,LINKTO,ASSOCIATED,CONTENT_ID,EXITPOINT1_ID,EXITPOINT2_ID,EXITPOINT3_ID,RESOLVEIDE NTIFIER,LIBQUESTION_IDFK,FOLLOWONCALL,USERINPUT,ISLOCKED,PREVIOUSANSWER,VISIBLETOAGENT,RETRYATTEMPT,TAGS) values (14,450,'Ask the customer to perform a master reset. Does this resolve their issue?','Question','12.2,11.2','14.2,14.1',null,282,null,null,null,null,null,null,null,null,null,null,null,null);
Insert into VW_LIVEPROCESSITEM_SIM_v6 (QUESTION_ID,PROCESS_ID,INNERTEXT,ITEMTYPE,LINKFROM,LINKTO,ASSOCIATED,CONTENT_ID,EXITPOINT1_ID,EXITPOINT2_ID,EXITPOINT3_ID,RESOLVEIDE NTIFIER,LIBQUESTION_IDFK,FOLLOWONCALL,USERINPUT,ISLOCKED,PREVIOUSANSWER,VISIBLETOAGENT,RETRYATTEMPT,TAGS) values (15,450,'Faulty CPE','ExitPoint','14.2',null,null,null,1,6,124,null,null,null,null,null,null,null,null,null);
Insert into VW_LIVEPROCESSITEM_SIM_v6 (QUESTION_ID,PROCESS_ID,INNERTEXT,ITEMTYPE,LINKFROM,LINKTO,ASSOCIATED,CONTENT_ID,EXITPOINT1_ID,EXITPOINT2_ID,EXITPOINT3_ID,RESOLVEIDE NTIFIER,LIBQUESTION_IDFK,FOLLOWONCALL,USERINPUT,ISLOCKED,PREVIOUSANSWER,VISIBLETOAGENT,RETRYATTEMPT,TAGS) values (16,450,'Issue Resolved','ExitPoint','14.1',null,null,null,1,6,123,null,null,null,null,null,null,null,null,null);

****************************************************************************************************************************

CREATE TABLE "LIVEPROCESS_EC_V"
( "PROCESS_ID" NUMBER(10,0),
"USER_ID" NUMBER(10,0),
"CREATED" TIMESTAMP (6)
);

Insert into LIVEPROCESS_EC (PROCESS_ID,USER_ID,CREATED) values (450,7460,to_timestamp('21-APR-08 09.34.41.000000000 AM','DD-MON-RR HH.MI.SS.FF AM'));

***************************************************************************************************************************
Required O/P in XML format


<P>
  <Ppid>450</Ppid>
  <PI>
    <PIqid>1</PIqid>
    <PIpid>450</PIpid>
    <PItext>CBB1015 - Router Firewall Settinngs Process</PItext>
    <PItype>Title</PItype>
    <PIto>2</PIto>
    <PO />
  </PI>
  <PI>
    <PIqid>2</PIqid>
    <PIpid>450</PIpid>
    <PItext>Is the customers PC Firewall turned off?</PItext>
    <PItype>Question</PItype>
    <PIfrom>1</PIfrom>
    <PIto>2.2,2.1</PIto>
    <PO>
      <POoid>1</POoid>
      <POqid>2</POqid>
      <popid>450</popid>
      <POtext>Yes</POtext>
      <POto>5</POto>
    </PO>
    <PO>
      <POoid>2</POoid>
      <POqid>2</POqid>
      <popid>450</popid>
      <POtext>No</POtext>
      <POto>3</POto>
    </PO>
  </PI>
  <PI>
    <PIqid>3</PIqid>
    <PIpid>450</PIpid>
    <PItext>Advise the customer to turn off the PC Firewall in order to continue. Has this been done?</PItext>
    <PItype>Question</PItype>
    <PIfrom>2.2</PIfrom>
    <PIto>3.2,3.1</PIto>
    <PIc>278</PIc>
    <PO>
      <POoid>1</POoid>
      <POqid>3</POqid>
      <popid>450</popid>
      <POtext>Yes</POtext>
      <POto>5</POto>
    </PO>
    <PO>
      <POoid>2</POoid>
      <POqid>3</POqid>
      <popid>450</popid>
      <POtext>No</POtext>
      <POto>4</POto>
    </PO>
  </PI>
  <PI>
    <PIqid>4</PIqid>
    <PIpid>450</PIpid>
    <PItext>Advise the customer the PC Firewall must be switched off before this process????</PItext>
    <PItype>ExitPoint</PItype>
    <PIfrom>3.2</PIfrom>
    <PIe1>14</PIe1>
    <PO />
  </PI>
  <PI>
    <PIqid>5</PIqid>
    <PIpid>450</PIpid>
    <PItext>Is the customer able to access the internet now?</PItext>
    <PItype>Question</PItype>
    <PIfrom>3.1,2.1</PIfrom>
    <PIto>5.2,5.1</PIto>
    <PO>
      <POoid>1</POoid>
      <POqid>5</POqid>
      <popid>450</popid>
      <POtext>Yes</POtext>
      <POto>6</POto>
    </PO>
    <PO>
      <POoid>2</POoid>
      <POqid>5</POqid>
      <popid>450</popid>
      <POtext>No</POtext>
      <POto>8</POto>
    </PO>
  </PI>
  <PI>
    <PIqid>6</PIqid>
    <PIpid>450</PIpid>
    <PItext>Is the customer having a problem with a specific website?</PItext>
    <PItype>Question</PItype>
    <PIfrom>5.1</PIfrom>
    <PIto>6.2,6.1</PIto>
    <PO>
      <POoid>1</POoid>
      <POqid>6</POqid>
      <popid>450</popid>
      <POtext>Yes</POtext>
      <POto>7</POto>
    </PO>
    <PO>
      <POoid>2</POoid>
      <POqid>6</POqid>
      <popid>450</popid>
      <POtext>No</POtext>
      <POto>8</POto>
    </PO>
  </PI>
  <PI>
    <PIqid>7</PIqid>
    <PIpid>450</PIpid>
    <PItext>1536: CBB1008 - Browser Setup and Daignostics</PItext>
    <PItype>SubProcess</PItype>
    <PIfrom>6.1</PIfrom>
    <PIas>1536-1-0</PIas>
    <PO />
  </PI>
  <PI>
    <PIqid>8</PIqid>
    <PIpid>450</PIpid>
    <PItext>What is the security level on the CPE Management page?</PItext>
    <PItype>Question</PItype>
    <PIfrom>6.2,5.2</PIfrom>
    <PIto>8.4,8.3,8.2,8.1</PIto>
    <PIc>279</PIc>
    <PO>
      <POoid>1</POoid>
      <POqid>8</POqid>
      <popid>450</popid>
      <POtext>Block All</POtext>
      <POto>9</POto>
    </PO>
    <PO>
      <POoid>2</POoid>
      <POqid>8</POqid>
      <popid>450</popid>
      <POtext>Standard</POtext>
      <POto>11</POto>
    </PO>
    <PO>
      <POoid>3</POoid>
      <POqid>8</POqid>
      <popid>450</popid>
      <POtext>Disabled</POtext>
      <POto>12</POto>
    </PO>
    <PO>
      <POoid>4</POoid>
      <POqid>8</POqid>
      <popid>450</popid>
      <POtext>User Defined</POtext>
      <POto>12</POto>
    </PO>
  </PI>
  <PI>
    <PIqid>9</PIqid>
    <PIpid>450</PIpid>
    <PItext>Change the security level to Standard. Does this resolve the customers issue?</PItext>
    <PItype>Question</PItype>
    <PIfrom>8.1</PIfrom>
    <PIto>9.2,9.1</PIto>
    <PIc>280</PIc>
    <PO>
      <POoid>1</POoid>
      <POqid>9</POqid>
      <popid>450</popid>
      <POtext>Yes</POtext>
      <POto>10</POto>
    </PO>
    <PO>
      <POoid>2</POoid>
      <POqid>9</POqid>
      <popid>450</popid>
      <POtext>No</POtext>
      <POto>11</POto>
    </PO>
  </PI>
  <PI>
    <PIqid>10</PIqid>
    <PIpid>450</PIpid>
    <PItext>Issue Resolved</PItext>
    <PItype>ExitPoint</PItype>
    <PIfrom>9.1</PIfrom>
    <PIe1>1</PIe1>
    <PIe2>6</PIe2>
    <PIe3>122</PIe3>
    <PO />
  </PI>
  <PI>
    <PIqid>11</PIqid>
    <PIpid>450</PIpid>
    <PItext>Change the security level to Disabled. Is the customer able to browse the internet?</PItext>
    <PItype>Question</PItype>
    <PIfrom>9.2,8.2</PIfrom>
    <PIto>11.2,11.1</PIto>
    <PIc>281</PIc>
    <PO>
      <POoid>1</POoid>
      <POqid>11</POqid>
      <popid>450</popid>
      <POtext>Yes</POtext>
      <POto>12</POto>
    </PO>
    <PO>
      <POoid>2</POoid>
      <POqid>11</POqid>
      <popid>450</popid>
      <POtext>No</POtext>
      <POto>14</POto>
    </PO>
  </PI>
  <PI>
    <PIqid>12</PIqid>
    <PIpid>450</PIpid>
    <PItext>Change the security level to Standard. Is the customer able to browse the internet now?</PItext>
    <PItype>Question</PItype>
    <PIfrom>11.1,8.3,8.4</PIfrom>
    <PIto>12.2,12.1</PIto>
    <PIc>283</PIc>
    <PO>
      <POoid>1</POoid>
      <POqid>12</POqid>
      <popid>450</popid>
      <POtext>Yes</POtext>
      <POto>13</POto>
    </PO>
    <PO>
      <POoid>2</POoid>
      <POqid>12</POqid>
      <popid>450</popid>
      <POtext>No</POtext>
      <POto>14</POto>
    </PO>
  </PI>
  <PI>
    <PIqid>13</PIqid>
    <PIpid>450</PIpid>
    <PItext>Issue Resolved</PItext>
    <PItype>ExitPoint</PItype>
    <PIfrom>12.1</PIfrom>
    <PIe1>1</PIe1>
    <PIe2>6</PIe2>
    <PIe3>123</PIe3>
    <PO />
  </PI>
  <PI>
    <PIqid>14</PIqid>
    <PIpid>450</PIpid>
    <PItext>Ask the customer to perform a master reset. Does this resolve their issue?</PItext>
    <PItype>Question</PItype>
    <PIfrom>12.2,11.2</PIfrom>
    <PIto>14.2,14.1</PIto>
    <PIc>282</PIc>
    <PO>
      <POoid>1</POoid>
      <POqid>14</POqid>
      <popid>450</popid>
      <POtext>Yes</POtext>
      <POto>16</POto>
    </PO>
    <PO>
      <POoid>2</POoid>
      <POqid>14</POqid>
      <popid>450</popid>
      <POtext>No</POtext>
      <POto>15</POto>
    </PO>
  </PI>
  <PI>
    <PIqid>15</PIqid>
    <PIpid>450</PIpid>
    <PItext>Faulty CPE</PItext>
    <PItype>ExitPoint</PItype>
    <PIfrom>14.2</PIfrom>
    <PIe1>1</PIe1>
    <PIe2>6</PIe2>
    <PIe3>124</PIe3>
    <PO />
  </PI>
  <PI>
    <PIqid>16</PIqid>
    <PIpid>450</PIpid>
    <PItext>Issue Resolved</PItext>
    <PItype>ExitPoint</PItype>
    <PIfrom>14.1</PIfrom>
    <PIe1>1</PIe1>
    <PIe2>6</PIe2>
    <PIe3>123</PIe3>
    <PO />
  </PI>
</P>

Thanks in advance

[Updated on: Tue, 18 November 2008 06:47]

Report message to a moderator

Re: How to use left outer joins ,right outer joins and order by clause for below query [message #359893 is a reply to message #359802]

Tue, 18 November 2008 09:28

scorpio_biker
Messages: 154
Registered: November 2005
Location: Kent, England

Senior Member

Hi,

I gave this a go and apart from the fact that you have syntax errors in the query you posted (a missing bracket and incorrect table name) it seems to work fine. I get the XML you gave as your output. Are you getting an error when you run your query, and if so what is it?

Report message to a moderator

Re: How to use left outer joins ,right outer joins and order by clause for below query [message #359897 is a reply to message #359893]

Tue, 18 November 2008 10:07

JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...

Senior Member

I get a slightly different output, but I had to mess around with the brackets in the query to get it to work.
I get some additional <A> tags.

Is this the expected result (Sorry about the lack of formating, but I've got no decent XML editor on this box)

- <P>
  <PPID>450</PPID> 
- <A>
- <PI>
  <PIQID>1</PIQID> 
  <PIPID>450</PIPID> 
  <PITEXT>CBB1015 - Router Firewall Settinngs Process</PITEXT> 
  <PITYPE>Title</PITYPE> 
  <PITO>2</PITO> 
  </PI>
- <PI>
  <PIQID>2</PIQID> 
  <PIPID>450</PIPID> 
  <PITEXT>Is the customers PC Firewall turned off?</PITEXT> 
  <PITYPE>Question</PITYPE> 
  <PIFROM>1</PIFROM> 
  <PITO>2.2,2.1</PITO> 
- <A>
- <PO>
  <POOID>1</POOID> 
  <POQID>2</POQID> 
  <POPID>450</POPID> 
  <POTEXT>Yes</POTEXT> 
  <POTO>5</POTO> 
  </PO>
- <PO>
  <POOID>2</POOID> 
  <POQID>2</POQID> 
  <POPID>450</POPID> 
  <POTEXT>No</POTEXT> 
  <POTO>3</POTO> 
  </PO>
  </A>
  </PI>
- <PI>
  <PIQID>3</PIQID> 
  <PIPID>450</PIPID> 
  <PITEXT>Advise the customer to turn off the PC Firewall in order to continue. Has this been done?</PITEXT> 
  <PITYPE>Question</PITYPE> 
  <PIFROM>2.2</PIFROM> 
  <PITO>3.2,3.1</PITO> 
  <PIC>278</PIC> 
- <A>
- <PO>
  <POOID>1</POOID> 
  <POQID>3</POQID> 
  <POPID>450</POPID> 
  <POTEXT>Yes</POTEXT> 
  <POTO>5</POTO> 
  </PO>
- <PO>
  <POOID>2</POOID> 
  <POQID>3</POQID> 
  <POPID>450</POPID> 
  <POTEXT>No</POTEXT> 
  <POTO>4</POTO> 
  </PO>
  </A>
  </PI>
- <PI>
  <PIQID>4</PIQID> 
  <PIPID>450</PIPID> 
  <PITEXT>Advise the customer the PC Firewall must be switched off before this process????</PITEXT> 
  <PITYPE>ExitPoint</PITYPE> 
  <PIFROM>3.2</PIFROM> 
  <PIE1>14</PIE1> 
  </PI>
- <PI>
  <PIQID>5</PIQID> 
  <PIPID>450</PIPID> 
  <PITEXT>Is the customer able to access the internet now?</PITEXT> 
  <PITYPE>Question</PITYPE> 
  <PIFROM>3.1,2.1</PIFROM> 
  <PITO>5.2,5.1</PITO> 
- <A>
- <PO>
  <POOID>1</POOID> 
  <POQID>5</POQID> 
  <POPID>450</POPID> 
  <POTEXT>Yes</POTEXT> 
  <POTO>6</POTO> 
  </PO>
- <PO>
  <POOID>2</POOID> 
  <POQID>5</POQID> 
  <POPID>450</POPID> 
  <POTEXT>No</POTEXT> 
  <POTO>8</POTO> 
  </PO>
  </A>
  </PI>
- <PI>
  <PIQID>6</PIQID> 
  <PIPID>450</PIPID> 
  <PITEXT>Is the customer having a problem with a specific website?</PITEXT> 
  <PITYPE>Question</PITYPE> 
  <PIFROM>5.1</PIFROM> 
  <PITO>6.2,6.1</PITO> 
- <A>
- <PO>
  <POOID>1</POOID> 
  <POQID>6</POQID> 
  <POPID>450</POPID> 
  <POTEXT>Yes</POTEXT> 
  <POTO>7</POTO> 
  </PO>
- <PO>
  <POOID>2</POOID> 
  <POQID>6</POQID> 
  <POPID>450</POPID> 
  <POTEXT>No</POTEXT> 
  <POTO>8</POTO> 
  </PO>
  </A>
  </PI>
- <PI>
  <PIQID>7</PIQID> 
  <PIPID>450</PIPID> 
  <PITEXT>1536: CBB1008 - Browser Setup and Daignostics</PITEXT> 
  <PITYPE>SubProcess</PITYPE> 
  <PIFROM>6.1</PIFROM> 
  <PIAS>1536-1-0</PIAS> 
  </PI>
- <PI>
  <PIQID>8</PIQID> 
  <PIPID>450</PIPID> 
  <PITEXT>What is the security level on the CPE Management page?</PITEXT> 
  <PITYPE>Question</PITYPE> 
  <PIFROM>6.2,5.2</PIFROM> 
  <PITO>8.4,8.3,8.2,8.1</PITO> 
  <PIC>279</PIC> 
- <A>
- <PO>
  <POOID>1</POOID> 
  <POQID>8</POQID> 
  <POPID>450</POPID> 
  <POTEXT>Block All</POTEXT> 
  <POTO>9</POTO> 
  </PO>
- <PO>
  <POOID>2</POOID> 
  <POQID>8</POQID> 
  <POPID>450</POPID> 
  <POTEXT>Standard</POTEXT> 
  <POTO>11</POTO> 
  </PO>
- <PO>
  <POOID>3</POOID> 
  <POQID>8</POQID> 
  <POPID>450</POPID> 
  <POTEXT>Disabled</POTEXT> 
  <POTO>12</POTO> 
  </PO>
- <PO>
  <POOID>4</POOID> 
  <POQID>8</POQID> 
  <POPID>450</POPID> 
  <POTEXT>User Defined</POTEXT> 
  <POTO>12</POTO> 
  </PO>
  </A>
  </PI>
- <PI>
  <PIQID>9</PIQID> 
  <PIPID>450</PIPID> 
  <PITEXT>Change the security level to Standard. Does this resolve the customers issue?</PITEXT> 
  <PITYPE>Question</PITYPE> 
  <PIFROM>8.1</PIFROM> 
  <PITO>9.2,9.1</PITO> 
  <PIC>280</PIC> 
- <A>
- <PO>
  <POOID>1</POOID> 
  <POQID>9</POQID> 
  <POPID>450</POPID> 
  <POTEXT>Yes</POTEXT> 
  <POTO>10</POTO> 
  </PO>
- <PO>
  <POOID>2</POOID> 
  <POQID>9</POQID> 
  <POPID>450</POPID> 
  <POTEXT>No</POTEXT> 
  <POTO>11</POTO> 
  </PO>
  </A>
  </PI>
- <PI>
  <PIQID>10</PIQID> 
  <PIPID>450</PIPID> 
  <PITEXT>Issue Resolved</PITEXT> 
  <PITYPE>ExitPoint</PITYPE> 
  <PIFROM>9.1</PIFROM> 
  <PIE1>1</PIE1> 
  <PIE2>6</PIE2> 
  <PIE3>122</PIE3> 
  </PI>
- <PI>
  <PIQID>11</PIQID> 
  <PIPID>450</PIPID> 
  <PITEXT>Change the security level to Disabled. Is the customer able to browse the internet?</PITEXT> 
  <PITYPE>Question</PITYPE> 
  <PIFROM>9.2,8.2</PIFROM> 
  <PITO>11.2,11.1</PITO> 
  <PIC>281</PIC> 
- <A>
- <PO>
  <POOID>1</POOID> 
  <POQID>11</POQID> 
  <POPID>450</POPID> 
  <POTEXT>Yes</POTEXT> 
  <POTO>12</POTO> 
  </PO>
- <PO>
  <POOID>2</POOID> 
  <POQID>11</POQID> 
  <POPID>450</POPID> 
  <POTEXT>No</POTEXT> 
  <POTO>14</POTO> 
  </PO>
  </A>
  </PI>
- <PI>
  <PIQID>12</PIQID> 
  <PIPID>450</PIPID> 
  <PITEXT>Change the security level to Standard. Is the customer able to browse the internet now?</PITEXT> 
  <PITYPE>Question</PITYPE> 
  <PIFROM>11.1,8.3,8.4</PIFROM> 
  <PITO>12.2,12.1</PITO> 
  <PIC>283</PIC> 
- <A>
- <PO>
  <POOID>1</POOID> 
  <POQID>12</POQID> 
  <POPID>450</POPID> 
  <POTEXT>Yes</POTEXT> 
  <POTO>13</POTO> 
  </PO>
- <PO>
  <POOID>2</POOID> 
  <POQID>12</POQID> 
  <POPID>450</POPID> 
  <POTEXT>No</POTEXT> 
  <POTO>14</POTO> 
  </PO>
  </A>
  </PI>
- <PI>
  <PIQID>13</PIQID> 
  <PIPID>450</PIPID> 
  <PITEXT>Issue Resolved</PITEXT> 
  <PITYPE>ExitPoint</PITYPE> 
  <PIFROM>12.1</PIFROM> 
  <PIE1>1</PIE1> 
  <PIE2>6</PIE2> 
  <PIE3>123</PIE3> 
  </PI>
- <PI>
  <PIQID>14</PIQID> 
  <PIPID>450</PIPID> 
  <PITEXT>Ask the customer to perform a master reset. Does this resolve their issue?</PITEXT> 
  <PITYPE>Question</PITYPE> 
  <PIFROM>12.2,11.2</PIFROM> 
  <PITO>14.2,14.1</PITO> 
  <PIC>282</PIC> 
- <A>
- <PO>
  <POOID>1</POOID> 
  <POQID>14</POQID> 
  <POPID>450</POPID> 
  <POTEXT>Yes</POTEXT> 
  <POTO>16</POTO> 
  </PO>
- <PO>
  <POOID>2</POOID> 
  <POQID>14</POQID> 
  <POPID>450</POPID> 
  <POTEXT>No</POTEXT> 
  <POTO>15</POTO> 
  </PO>
  </A>
  </PI>
- <PI>
  <PIQID>15</PIQID> 
  <PIPID>450</PIPID> 
  <PITEXT>Faulty CPE</PITEXT> 
  <PITYPE>ExitPoint</PITYPE> 
  <PIFROM>14.2</PIFROM> 
  <PIE1>1</PIE1> 
  <PIE2>6</PIE2> 
  <PIE3>124</PIE3> 
  </PI>
- <PI>
  <PIQID>16</PIQID> 
  <PIPID>450</PIPID> 
  <PITEXT>Issue Resolved</PITEXT> 
  <PITYPE>ExitPoint</PITYPE> 
  <PIFROM>14.1</PIFROM> 
  <PIE1>1</PIE1> 
  <PIE2>6</PIE2> 
  <PIE3>123</PIE3> 
  </PI>
  </A>
  </P>

Report message to a moderator

Re: How to use left outer joins ,right outer joins and order by clause for below query [message #359900 is a reply to message #359897]

Tue, 18 November 2008 11:21

vikram1780
Messages: 222
Registered: January 2007
Location: Bangalore

Senior Member

Hi ,

I am able to get this output with the query posted here by me. But I am missing some of the these tags <PO /> which are required for my desired output.

I replaced the additional tags of A with '' in the output.

The only thing missing in my query is <PO /> for nulls. When there is no po records.

Thanks in advance

[Updated on: Tue, 18 November 2008 11:24]

Report message to a moderator

help needed in writing query [message #360200 is a reply to message #359802]

Thu, 20 November 2008 01:03

vikram1780
Messages: 222
Registered: January 2007
Location: Bangalore

Senior Member

Hi,

Could anyone help me in writing below query without syntax errors.

I tried but no luck

select xmlelement("g", XMLATTRIBUTES(g.contentgroup_id as "id",g.groupname as "label",
(select xmlagg(xmlelement ("c",XMLATTRIBUTES(c.title as "title",c.content_id as "id")))) as "A"))
as "A" from
(SELECT g.contentgroup_id AS id, g.groupname AS label, c.title AS label, c.content_id AS id 
FROM content_ec  c FULL OUTER JOIN contentgroup_ec  g ON c.group_id = g.contentgroup_id
oRDER BY g.groupname ,c.title ASC );

Any help really appreciated.

Thanks

Report message to a moderator

Re: help needed in writing query [message #360203 is a reply to message #360200]

Thu, 20 November 2008 01:14

Michel Cadot
Messages: 68716
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

It is useless to repost the same question in a new topic. They will be merged.
More it is really bad to do it without correctly formatting and without the useful information provided in the previous topic.

Regards
Michel

[Updated on: Thu, 20 November 2008 01:16]

Report message to a moderator

Re: help needed in writing query [message #360205 is a reply to message #360203]

Thu, 20 November 2008 01:19

vikram1780
Messages: 222
Registered: January 2007
Location: Bangalore

Senior Member

Hi Michel,

Request you to recheck the post before commenting. Its not the same post and moreover. I havent got any answer for my previous post too.

Thanks

Report message to a moderator

Re: help needed in writing query [message #360211 is a reply to message #360205]

Thu, 20 November 2008 01:25

Michel Cadot
Messages: 68716
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

If you correctly formatted the query maybe I'd see this is not the same question.
In addition, we are not SQL syntax compiler so how could we know why the SQL is wrong without any information about your objects. The least you could do is to copy and paste your SQL*Plus session to show us what is the error and where it is.

Regards
Michel

Report message to a moderator

Re: help needed in writing query [message #360213 is a reply to message #360211]

Thu, 20 November 2008 01:34

vikram1780
Messages: 222
Registered: January 2007
Location: Bangalore

Senior Member

Hi,

Pls find formatted query.

I know that you are not an sql compiler. I asked here to verify the query. After me verify about the objects posted here.

SELECT xmlelement("g", XMLATTRIBUTES(g.contentgroup_id AS "id",g.groupname AS "label",
  (SELECT xmlagg(xmlelement ("c",XMLATTRIBUTES(c.title AS "title",c.content_id AS "id")))
     FROM A
  ) ))
   FROM A
  (SELECT g.contentgroup_id AS id   ,
    g.groupname             AS label,
    c.title                 AS label,
    c.content_id            AS id
     FROM content_ec c
  FULL OUTER JOIN contentgroup_ec g
       ON c.group_id = g.contentgroup_id
 ORDER BY g.groupname ,
    c.title ASC
  ) A

Thanks

[Updated on: Thu, 20 November 2008 01:35]

Report message to a moderator

Re: help needed in writing query [message #360221 is a reply to message #360213]

Thu, 20 November 2008 01:56

Michel Cadot
Messages: 68716
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

Once again, use SQL*plus and copy and paste your session like:

SQL> select *
  2  from dual
  3  where something
  4  order by 1
  5  /
order by 1
*
ERROR at line 4:
ORA-00920: invalid relational operator

There we know which error is and where it is.

Is it clear now?

Regards
Michel

Report message to a moderator

Re: help needed in writing query [message #360239 is a reply to message #360221]

Thu, 20 November 2008 02:54

vikram1780
Messages: 222
Registered: January 2007
Location: Bangalore

Senior Member

Hi,

Below is the session


SQL> SELECT xmlelement("g", XMLATTRIBUTES(g.contentgroup_id as "id",g.groupname
as "label",
  2      xmlagg(xmlelement ("c",XMLATTRIBUTES(c.title as "title", c.content_id a
s "id") as "A")))) AS "A"
  3     FROM (SELECT g.contentgroup_id AS id,
  4                     g.groupname AS label,
  5                     c.title AS label,
  6                     c.content_id AS id
  7        FROM content_ec c
  8        FULL OUTER JOIN contentgroup_ec g
  9          ON c.group_id = g.contentgroup_id
 10       ORDER BY g.groupname ,c.title ASC );
         xmlagg(xmlelement ("c",XMLATTRIBUTES(c.title as "title", c.content_id a
s "id") as "A")))) AS "A"

        *
ERROR at line 2:
ORA-00917: missing comma


Thanks

Report message to a moderator

Re: help needed in writing query [message #360241 is a reply to message #360239]

Thu, 20 November 2008 03:05

vikram1780
Messages: 222
Registered: January 2007
Location: Bangalore

Senior Member

Hi,

Could you pls let me know . How to get below XML output. With above query.


<g id="23" label="abc">
  <c />
</g>
<g id="16" label="BBA">
  <c label="BA: How Do I Send A File Using Bluetooth" id="1998" />
  <c label="BBA Software Version" id="1908" />
</g>

Thanks

Report message to a moderator

Previous Topic:	CLOB missing values from XMLType
Next Topic:	XML Problem

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Sun Nov 24 01:02:30 CST 2024