tag:blogger.com,1999:blog-2627158641075173556.post747885384616481716..comments2023-11-27T08:52:31.824-08:00Comments on SOA: What, How & Why: How to Use SQL Query having IN Clause With DB AdapterDharmendrahttp://www.blogger.com/profile/10820121550721912427noreply@blogger.comBlogger9125tag:blogger.com,1999:blog-2627158641075173556.post-16137677439057794752012-03-05T18:59:04.022-08:002012-03-05T18:59:04.022-08:00My Bad. I was running this against MS SQL Server d...My Bad. I was running this against MS SQL Server database. This work perfectly fine with Oracle DB. Can anyone let us know the equivalent query in MS SQL?<br /><br />Appreciate your help.<br /><br />Thanks,<br />SamSamhttps://www.blogger.com/profile/09993827742415535891noreply@blogger.comtag:blogger.com,1999:blog-2627158641075173556.post-85585919860118084882012-03-04T13:34:24.722-08:002012-03-04T13:34:24.722-08:00The query doesn't compile even in sqldeveloper...The query doesn't compile even in sqldeveloper. <br /><br />Appreciate if you could help in solving this issue.<br /><br />My Query:<br /> SELECT bod.hpod_delivery,<br /> bod.hpod_deliverydetailid<br />from DBO.BOLORDERDETAIL BOD<br />where BOD.HPOD_DELIVERYDETAILID in (with VALUE_LIST as (<br />SELECT ? val<br />FROM dual)<br />SELECT SUBSTR(val, (decode(LEVEL, 1, 0, instr(val, ':', 1, LEVEL -1)) +<br />1), (decode(instr(val, ':', 1, LEVEL) -1, -1, LENGTH(val), instr(val,<br />':', 1, LEVEL) -1)) -(decode(LEVEL, 1, 0, instr(val, ':', 1, LEVEL -1))<br />+ 1) + 1) a<br />FROM VALUE_LIST<br />CONNECT BY LEVEL <= (<br />select(length(VAL) -length(replace(VAL, ':', null)))<br />FROM VALUE_LIST) + 1)<br /><br />Error Message: Incorrect syntax near the keyword 'with'.<br /><br />Thanks,<br />SantoshSamhttps://www.blogger.com/profile/09993827742415535891noreply@blogger.comtag:blogger.com,1999:blog-2627158641075173556.post-32222164082448130812010-12-01T18:08:05.764-08:002010-12-01T18:08:05.764-08:00Great Man.....keep up the good work...was hard to ...Great Man.....keep up the good work...was hard to get this thing going......Samirhttps://www.blogger.com/profile/00096120859757629978noreply@blogger.comtag:blogger.com,1999:blog-2627158641075173556.post-18908135747646606792009-11-18T14:08:49.758-08:002009-11-18T14:08:49.758-08:00Hello Dharmendra,
I tried to use this query but r...Hello Dharmendra,<br /><br />I tried to use this query but returns the "ORA-01473: cannot have subqueries in CONNECT BY clause" because I have to use GROUP BY.<br /><br />My query is:<br /><br />SELECT ANO, PERIODO, SUM(PAGO_ANO) as PAGO_ANO, SUM(PROCESSADO_ANO) as<br /> PROCESSADO_ANO, PERIODO_NUM, SUM(EXECUTADO_ANO) as EXECUTADO_ANO<br /> FROM C__ACTIVIDADE<br /> WHERE ACTIVIDADE IN (WITH VALUE_LIST AS (<br /> SELECT ? val<br /> FROM dual)<br /> SELECT SUBSTR(val, (decode(LEVEL, 1, 0, instr(val, ':', 1, LEVEL -1)) +<br /> 1), (decode(instr(val, ':', 1, LEVEL) -1, -1, LENGTH(val), instr(val,<br /> ':', 1, LEVEL) -1)) -(decode(LEVEL, 1, 0, instr(val, ':', 1, LEVEL -1))<br /> + 1) + 1) a<br /> FROM VALUE_LIST<br /> CONNECT BY LEVEL <= (<br /> SELECT(LENGTH(val) -LENGTH(REPLACE(val, ':', NULL)))<br /> FROM VALUE_LIST) + 1) GROUP BY ANO, PERIODO, PERIODO_NUM<br /><br />Maybe in this case, is better to create a PL/SQL stored procedure or is there any other way?<br /><br />Thank you,<br /><br />Lara Fernandes.llarafernandeshttps://www.blogger.com/profile/04499639332640894064noreply@blogger.comtag:blogger.com,1999:blog-2627158641075173556.post-46616544978221856572009-10-06T02:59:22.363-07:002009-10-06T02:59:22.363-07:00Select NVL(to_char(ow_org_id),'Not Available&#...Select NVL(to_char(ow_org_id),'Not Available') organizationId, count(rep_num)<br />From xxx_table<br />Where to_date(create_date) > to_date(?)<br />Group by own_org_id, rep_num<br /><br />i have created bpel process and trying to get output based on the creation date. the process is based on Httpbinding where parameters are given from WSDL url as<br /><br />http://hostname:port/httpbinding/default/processname?&repDate=date<br /><br />i am creating the service with DB adapter(custom sql) output is empty xml<br />i can get the output if i run the query in sqldeveloper can anyone help me in this regard<br /><br />created the httpbindings based on this link<br /><br />http://blogs.oracle.com/reynolds/2005/09/invoking_bpel_from_an_html_for.htmlnaveenhttps://www.blogger.com/profile/04876733997700147929noreply@blogger.comtag:blogger.com,1999:blog-2627158641075173556.post-22920816776150293892009-10-06T02:58:55.187-07:002009-10-06T02:58:55.187-07:00Select NVL(to_char(ow_org_id),'Not Available&#...Select NVL(to_char(ow_org_id),'Not Available') organizationId, count(rep_num)<br />From xxx_table<br />Where to_date(create_date) > to_date(?)<br />Group by own_org_id, rep_num<br /><br />i have created bpel process and trying to get output based on the creation date. the process is based on Httpbinding where parameters are given from WSDL url as<br /><br />http://hostname:port/httpbinding/default/processname?&repDate=date<br /><br />i am creating the service with DB adapter(custom sql) output is empty xml<br />i can get the output if i run the query in sqldeveloper can anyone help me in this regard<br /><br />created the httpbindings based on this link<br /><br />http://blogs.oracle.com/reynolds/2005/09/invoking_bpel_from_an_html_for.htmlnaveenhttps://www.blogger.com/profile/04876733997700147929noreply@blogger.comtag:blogger.com,1999:blog-2627158641075173556.post-18246594977716161142009-06-09T20:00:21.622-07:002009-06-09T20:00:21.622-07:00It works beautifully. Thanks for the post. Made my...It works beautifully. Thanks for the post. Made my life lot easierSusan Philiphttps://www.blogger.com/profile/09397894559001174362noreply@blogger.comtag:blogger.com,1999:blog-2627158641075173556.post-58577479098819407342009-05-29T11:27:18.562-07:002009-05-29T11:27:18.562-07:00Hi,
Nice article about the "Execute Custom SQL"...Hi,<br /> Nice article about the "Execute Custom SQL". <br /><br /> I have another question, is there a way to execute the DDL comnads using the "Execute Custom SQL"?<br /><br />Regards,<br />MadhavMadshttps://www.blogger.com/profile/00855145157323012739noreply@blogger.comtag:blogger.com,1999:blog-2627158641075173556.post-49091770220359350522009-05-07T22:34:00.000-07:002009-05-07T22:34:00.000-07:00Excellent !! works wonders :)) thnx buddy............Excellent !! works wonders :)) thnx buddy.........Vaibhav Pandeyhttps://www.blogger.com/profile/05870950481287844288noreply@blogger.com