islingtonclaret Posted March 17, 2015 Share Posted March 17, 2015 Going through database hell, if any of the Villatalk developers fancy throwing a few ideas around. I need a dataset for an Analytics package - the package can execute Oracle PL/SQL, talking directly to the Oracle database through JDBC. The dataset has to be exactly correct - by that, I mean what the analytics package expects as the correct form of dataset to build a chart from. Data looks like this: RequestID RequestDate TypeofData 21315115 01/03/2015 001,010,011,002,005 21345477 04/03/2015 001,006,014 What is needed out of this requires a monster amount of processing, with no temp tables, no procedures - just pure SELECT goodness. By using WITH x AS nesting, the latter parts of the processing are fine, it's just the beginning bit. TypeofData needs to be split, with new rows per comma seperated value. So, we get this: RequestID RequestDate TypeofData 21315115 01/03/2015 001 21315115 01/03/2015 010 21315115 01/03/2015 011 21315115 01/03/2015 002 21315115 01/03/2015 005 21345477 04/03/2015 001 21345477 04/03/2015 006 21345477 04/03/2015 014 I won't bore anyone with the transposing and month grouping, as that's pretty much in hand. Just to pre-empt anyone: I have no access to this database to write functions, procedures, create new tables....the only thing I have to manipulate the data is JavaScript functions, and that happens only after the fetch. Long, boring post, apologies. ANY pointers would be awesome.... Link to comment Share on other sites More sharing options...
limpid Posted March 17, 2015 Administrator Share Posted March 17, 2015 You do know it's 2015, right? Link to comment Share on other sites More sharing options...
islingtonclaret Posted March 17, 2015 Author Share Posted March 17, 2015 You do know it's 2015, right? Can I have your sarcasm in English? Are you asking why I'm not using a programming language to do this, despite saying I have to do it in SQL? Link to comment Share on other sites More sharing options...
limpid Posted March 17, 2015 Administrator Share Posted March 17, 2015 I'm sorry if my reply didn't help. (PL/SQL is a procedural language. The clue is in the name.) Link to comment Share on other sites More sharing options...
islingtonclaret Posted March 18, 2015 Author Share Posted March 18, 2015 OK. The functions and packages/stored procedures are all written in PL/SQL, obviously. But, being read only from my end, I can't write any PL/SQL. So yes, just SQL to query it. Link to comment Share on other sites More sharing options...
limpid Posted March 18, 2015 Administrator Share Posted March 18, 2015 That's a bugger then. Sorry. If you could use PL/SQL it would be quite straightforward, although it's more than 10 years since I did anything like this. Link to comment Share on other sites More sharing options...
islingtonclaret Posted March 18, 2015 Author Share Posted March 18, 2015 Well, it would be fine if I could use middleware to get Python, Ruby or PHP onto it to transform it, but alas- no platform to do it with. Link to comment Share on other sites More sharing options...
limpid Posted March 18, 2015 Administrator Share Posted March 18, 2015 Could you use this as an opportunity to look into something which doesn't use tables? Link to comment Share on other sites More sharing options...
islingtonclaret Posted March 19, 2015 Author Share Posted March 19, 2015 I think I've got what I'm going to do. I'm going to create my own middleware to capture the records, get PHP to chuck it all into arrays and explode them then throw them in a PostgreSQL db. I figured if there's no middleware, I might as well force it myself! Link to comment Share on other sites More sharing options...
Recommended Posts