tag:blogger.com,1999:blog-15319370.comments2024-03-05T11:16:00.846+01:00Roland Bouman's blogrpboumanhttp://www.blogger.com/profile/13365137747952711328noreply@blogger.comBlogger2453125tag:blogger.com,1999:blog-15319370.post-89970857130792425812021-12-24T00:51:30.585+01:002021-12-24T00:51:30.585+01:00Thank you for the nested loop solution. I am star...Thank you for the nested loop solution. I am starting to learn procs and cursors. Your post helped me a lot :)blogerbashhttps://www.blogger.com/profile/14879280013775346958noreply@blogger.comtag:blogger.com,1999:blog-15319370.post-61595558149916063582021-07-22T19:00:51.369+02:002021-07-22T19:00:51.369+02:00Hi and thanks for ur best website and best informa...Hi and thanks for ur best website and best information. I enjoy alotresearchyarhttps://researchyar.irnoreply@blogger.comtag:blogger.com,1999:blog-15319370.post-37751545948928564142021-05-17T13:09:49.064+02:002021-05-17T13:09:49.064+02:00i will try this.i will try this.Triahttps://papberries99lezat100.blogspot.com/noreply@blogger.comtag:blogger.com,1999:blog-15319370.post-54994085928397060082021-05-13T13:13:26.780+02:002021-05-13T13:13:26.780+02:00Hi @Unknown, you asked
"wanna save the outpu...Hi @Unknown, you asked<br /><br />"wanna save the output of the SQL query into a transformation parameter value."<br /><br />yes you can - use the "Set Variables" step.<br /><br />https://wiki.pentaho.com/display/EAI/Set+Variables<br /><br />However, the new variable value will not be seen immediately in the transformation after setting it. That's because all the steps are already running. <br /><br />The new value will be visible from the next step in the job that started the transformation that changed the parameter value.<br /><br />I hope this helps!rpboumanhttps://www.blogger.com/profile/13365137747952711328noreply@blogger.comtag:blogger.com,1999:blog-15319370.post-23252785363718661042021-05-13T08:54:23.715+02:002021-05-13T08:54:23.715+02:00I wanna do it the other way around. I wanna save t...I wanna do it the other way around. I wanna save the output of the SQL query into a transformation parameter value. Can someone please tell me how to do it?Anonymoushttps://www.blogger.com/profile/05288203529319684372noreply@blogger.comtag:blogger.com,1999:blog-15319370.post-13377440781939592312021-02-17T01:36:33.114+01:002021-02-17T01:36:33.114+01:00Thank you - yes that was the issue!
Here is the s...Thank you - yes that was the issue!<br /><br />Here is the sample data - I cannot do this in one query because in latest_nav table i need to search for the nav of the date exact match and if that is not found then i need to get the prior date for the scheme_id where there is an entry (so i need to do get nav from latest_nav where scheme_id = tblResults.scheme_id and latest_nav_date <<br />= tmp_Dates.Dates order by latest_nav.latest_nav_date limit 1<br /><br />tblResults<br />scheme_id<br />365<br />4644<br />1418<br /><br /><br />tmp_Dates<br />id Dates<br />1 2016-02-23<br />2 2016-03-01<br />3 2016-03-08<br /><br /><br />Latest_nav<br />scheme_id nav latest_nav_date<br />365 123 2016-02-23<br />365 124 2016-02-29<br />365 123 2016-03-08<br /><br /><br />Anonymoushttps://www.blogger.com/profile/09695196104604349524noreply@blogger.comtag:blogger.com,1999:blog-15319370.post-37158072611196680682021-02-16T19:35:19.506+01:002021-02-16T19:35:19.506+01:00Hi @Unknown ,
thanks for the comment.
> what ...Hi @Unknown ,<br /><br />thanks for the comment.<br /><br />> what am i doing wrong here...i only get the first data set from the cursor and the loop<br /><br />I suppose you mean the outer loop only runs a single iteration?<br /><br />I think it's because of your loop condition:<br /><br />WHILE loop_ct < dt_ct DO<br /><br /><br />You initialize loop_ct and dt_ct both as 0, and then inside the loop, you increase loop_ct <br /><br />SET loop_ct = loop_ct + 1;<br /><br />So once that happens, loop_ct is 1 but dt_ct is still 0. So, the second time around, the loop condition becomes false.<br /><br />That said: I think you can probably rewrite all this logic to a single INSERT INTO ... SELECT statement. <br /><br />If you need some help with that, just post some sample data for all tables involved (like, 3 rows no more) and explain the situation before and expected situation after. <br /><br />Cheers, and best regards,<br />Roland.<br /><br />rpboumanhttps://www.blogger.com/profile/13365137747952711328noreply@blogger.comtag:blogger.com,1999:blog-15319370.post-91412499775211002752021-02-16T18:49:18.405+01:002021-02-16T18:49:18.405+01:00what am i doing wrong here...i only get the first ...what am i doing wrong here...i only get the first data set from the cursor and the loop<br /><br />DECLARE done INT DEFAULT FALSE;<br />DECLARE loopInitVal INT DEFAULT 0;<br />DECLARE dt_ct INT DEFAULT 0;<br />DECLARE loop_ct INT DEFAULT 0;<br /><br />DECLARE cur1 CURSOR FOR SELECT distinct scheme_id FROM tblResults; <br />DECLARE CONTINUE HANDLER FOR NOT FOUND SET done= TRUE; <br /><br /><br />OPEN cur1;<br />FETCH cur1 INTO loopInitVal;<br />read_loop: LOOP<br /> <br /> IF done THEN<br /> LEAVE read_loop;<br /> END IF; <br /> <br /> <br /> WHILE loop_ct < dt_ct DO<br /> INSERT INTO tmp_scheme_nav_Dates (Dates, scheme_id, latest_nav) SELECT latest_nav_date, loopInitVal, latest_nav FROM latest_nav, tmp_Dates<br /> WHERE scheme_id = loopInitVal AND latest_nav_date <= Dates AND tmp_Dates.id = loop_ct + 1 ORDER BY nav_id DESC LIMIT 1;<br /> SET done = FALSE; <br /> SET loop_ct = loop_ct + 1;<br /> <br /> END WHILE;<br /> <br /> FETCH NEXT FROM cur1 INTO loopInitVal; <br /> END LOOP read_loop; <br /> CLOSE cur1;<br /> <br /> <br /><br />ENDAnonymoushttps://www.blogger.com/profile/09695196104604349524noreply@blogger.comtag:blogger.com,1999:blog-15319370.post-91917770486656884732020-06-10T08:54:22.894+02:002020-06-10T08:54:22.894+02:00A nice article Roland this will be really helpful ...A nice article Roland this will be really helpful for newcomers learning PDI. People looking for job vacancy in the sector can also learn through this article.Bhavyahttps://jobvacancyresult.com/noreply@blogger.comtag:blogger.com,1999:blog-15319370.post-24493410786024378432020-04-27T18:02:57.772+02:002020-04-27T18:02:57.772+02:00> @Madhivanan how can I prevent deletion from a...> @Madhivanan how can I prevent deletion from a view? Do you have any idea?<br /><br />Why not simply use the GRANT/privileges system for that? No hacking needed. rpboumanhttps://www.blogger.com/profile/13365137747952711328noreply@blogger.comtag:blogger.com,1999:blog-15319370.post-45082099269908040482020-04-27T17:52:04.123+02:002020-04-27T17:52:04.123+02:00@Madhivanan how can I prevent deletion from a view...@Madhivanan how can I prevent deletion from a view? Do you have any idea?Anonymousnoreply@blogger.comtag:blogger.com,1999:blog-15319370.post-35136137287413002042020-04-22T09:58:39.875+02:002020-04-22T09:58:39.875+02:00hi
How to call stored procedure in pentaho report...hi <br />How to call stored procedure in pentaho report designer and i want to pass multiple iD through the URl.codesolutionshttps://www.blogger.com/profile/16883676674208992917noreply@blogger.comtag:blogger.com,1999:blog-15319370.post-55705580014291210182020-04-07T19:06:12.734+02:002020-04-07T19:06:12.734+02:00Thanks for sharing you @Bohan! Much appreciated :)...Thanks for sharing you @Bohan! Much appreciated :) rpboumanhttps://www.blogger.com/profile/13365137747952711328noreply@blogger.comtag:blogger.com,1999:blog-15319370.post-74889804987363210342020-04-07T18:24:59.462+02:002020-04-07T18:24:59.462+02:00A side effect of the "a join b using (x, y)&q...A side effect of the "a join b using (x, y)" syntax is that you can no longer refer to the qualified names of the columns that where part of the using clause, i.e. no more "a.x", "a.y", "b.x", "b.y". This makes sense since the origin of x or y doesn't matter anymore, it has to be the same, and stay the same as you add even more joins that made use of those in using clauses. Where this becomes annoying is when you want to put all columns of a table or subquery in the select clause: you are not allowed to do "a.*" nor "b.*" anymore, which i think is a bug in the standard.<br /><br />Anyway, I think the same as you about this syntax being only half-useful or not usable at all if you column naming convention is incompatible. I have no idea why the standardisation committee spent lost their time specifying this and the other natural join (which is an antipattern as you shown), instead of taking advantage of the 99% of tables that can only, unambiguously be joined with one foreign key constraint.<br /><br />Regards<br />bohanhttps://www.blogger.com/profile/02639742284683566272noreply@blogger.comtag:blogger.com,1999:blog-15319370.post-31152046074828869712020-03-12T22:41:58.361+01:002020-03-12T22:41:58.361+01:00It's 2020, and the AJP Proxy was still the ans...It's 2020, and the AJP Proxy was still the answer for most proxy problems i had with Pentaho, thank you so much!Nickolashttps://www.blogger.com/profile/09957760141867867982noreply@blogger.comtag:blogger.com,1999:blog-15319370.post-55514838490763325012020-02-24T22:52:44.954+01:002020-02-24T22:52:44.954+01:00@Almani74, please check @Artoo's comment, and ...@Almani74, please check @Artoo's comment, and try the same. Thank you.rpboumanhttps://www.blogger.com/profile/13365137747952711328noreply@blogger.comtag:blogger.com,1999:blog-15319370.post-10499849301818771292020-02-24T12:24:26.544+01:002020-02-24T12:24:26.544+01:00Hi Roland I am trying to connect it with mysql dat...Hi Roland I am trying to connect it with mysql database; but it is giving an error that is as below.<br />No suitable connection found.<br /><br />my data-source configuration is as below.<br /><br /> Provider=mondrian; JdbcUser=root; JdbcPassword=root; Jdbc=jdbc:mysql://localhost:3306/newDB;JdbcDrivers=com.mysql.jdbc.Driver<br /><br />I also have tried mysql connection information as we do spring boot and pure java JDBC.<br /><br />But No luck can you please provide a little info about connection with mysql or oracle<br />Thanks IN Advance<br />Almani74https://www.blogger.com/profile/06591749595252415166noreply@blogger.comtag:blogger.com,1999:blog-15319370.post-24044869065512686832020-02-04T17:32:36.711+01:002020-02-04T17:32:36.711+01:00hi, do i need to change the original xml file to h...hi, do i need to change the original xml file to have the "resultset" tag? thanks!simplymehttps://www.blogger.com/profile/09747798623170062360noreply@blogger.comtag:blogger.com,1999:blog-15319370.post-77342593493870221272020-01-29T08:58:06.795+01:002020-01-29T08:58:06.795+01:00Hi Graeme,
if I recall correctly the sample app ...Hi Graeme, <br /><br />if I recall correctly the sample app has the open from url feature as well as the file open feature both on the toolbar. The url indeed only works when you have either same domain or CORS, but open file should work regardless.rpboumanhttps://www.blogger.com/profile/13365137747952711328noreply@blogger.comtag:blogger.com,1999:blog-15319370.post-46359146863103168122020-01-28T04:49:07.339+01:002020-01-28T04:49:07.339+01:00Hi Roland
I have now resolved my issues and misund...Hi Roland<br />I have now resolved my issues and misunderstandings.<br />Using XMLHttpRequest (Open Folder icon) to open a file on a local file system runs into CORS policy issues and basically will not work. [There seems to be discussion around that it can be done by adding specific headers using the POST method but all too hard].<br />Anyway I have the project running on a website and local host and can load a project automatically at startup by adding a url to the browser command line:<br />http://localhost/msp-viewer/resources/html/indexX.html?url=http://localhost/msp-viewer/sample/3PointPlan-example.xml<br /><br />Graeme Wellingtonhttps://www.blogger.com/profile/11726673749828651591noreply@blogger.comtag:blogger.com,1999:blog-15319370.post-9666247317084603452020-01-22T07:21:22.535+01:002020-01-22T07:21:22.535+01:00Hi Roland
I have the base system running on my in-...Hi Roland<br />I have the base system running on my in-house web site and things looking good.<br />However, I am having trouble with app.js (modified to load sample).<br />With this modified version in place I get a continuous spinner and nothing loads.<br /><br />if I click the'Open Folder' icon at far left and load from the 'Prompt' box:<br />(Please enter a url to download a MS Project XML file:<br />[file:///C:/msp-viewer/sample/3PointPlan-example.xml]<br />This same behaviour occurs with the original or modified app.js.<br /><br />However, if I click the 'Chooes File' option then the Windows Open box appears and I can select and successfully load the project.<br /><br />Would you be able to assist or point me in the right direction.<br /><br />Thanks in advance.<br /><br />Graeme Wellington<br />graeme.l.wellington@hotmail.com<br /><br /><br /><br /><br />Graeme Wellingtonhttps://www.blogger.com/profile/11726673749828651591noreply@blogger.comtag:blogger.com,1999:blog-15319370.post-76020705858762183862020-01-21T01:41:58.127+01:002020-01-21T01:41:58.127+01:00Hi Unknown,
yes, you can open a project xml file ...Hi Unknown,<br /><br />yes, you can open a project xml file in the browser and render it as gantt chart.<br /><br />For this purpose please checkout the newer javascript based solution. It comes with a sample application that lets you open a project xml file for your local machine. You may modify that part of the code to suit your needs.<br /><br />see: https://github.com/rpbouman/open-msp-viewer<br /><br />rpboumanhttps://www.blogger.com/profile/13365137747952711328noreply@blogger.comtag:blogger.com,1999:blog-15319370.post-30152859118531475052020-01-21T01:18:45.049+01:002020-01-21T01:18:45.049+01:00Hi Roland
I think your project has renewed potenti...Hi Roland<br />I think your project has renewed potential with the move to Software as a Service (Saas) applications.<br /><br />I am doing some development which I will now see if I can adapt concepts from your project.<br /><br />Is it possible to call a nominated project.xml directly from the browser similar to below?<br /><br />Example: file:///C:/Projects/msp-viewer/resources/html/index.html?URL="file:///C:/Projects/Project1.xml"<br /><br />Graeme Wellingtonhttps://www.blogger.com/profile/11726673749828651591noreply@blogger.comtag:blogger.com,1999:blog-15319370.post-24688459567422757962019-12-22T21:40:17.694+01:002019-12-22T21:40:17.694+01:00Interesting information, thank you for sharing. Bt...Interesting information, thank you for sharing. Btw, merry christmas and happy new year. May God bless you and your family. Greetings...Nerfxhttp://www.nerfx.comnoreply@blogger.comtag:blogger.com,1999:blog-15319370.post-47355068422891974982019-12-09T00:46:10.095+01:002019-12-09T00:46:10.095+01:00Thanks @Artoo!Thanks @Artoo!rpboumanhttps://www.blogger.com/profile/13365137747952711328noreply@blogger.com