Home » Developer & Programmer » JDeveloper, Java & XML » How to use left outer joins ,right outer joins and order by clause for below query (merged)
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 #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;
|
|
|
|
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.
|
|
|
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 #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>
|
|
|
|
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
|
|
|
Re: help needed in writing query [message #360203 is a reply to message #360200] |
Thu, 20 November 2008 01:14 |
|
Michel Cadot
Messages: 68722 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 #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 #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
|
|
|
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
|
|
|
Goto Forum:
Current Time: Mon Dec 09 19:51:30 CST 2024
|