Oracle SQL - Convert given list (CSV) into Rows
- Joel Lipman
- Oracle PL/SQL
- Hits: 7944
Situation
I have one column of values that I've suffixed a comma to but I want to display this as a rows in a table. This is for Oracle PL/SQL.
InstanceName | nvarchar(38) | NOT NULL | Name of the report server instance that handled the request. Usually YOURSERVERNAME\MSSQLSERVER |
ReportID | uniqueidentifier | NULL | The ID of the report (looks like a hexadecimal SSID). It's the unique ID of the report but not unique in the table (can be referenced many times). |
UserName | nvarchar(260) | NULL | Windows authenticated username and domain of the person running the report (eg. MYDOMAIN\myusername) |
RequestType | bit | NOT NULL | User or System. Can be 1 or 0. This was zero "0" when I would run a report as a user. |
Format | nvarchar(26) | NULL | This is the rendering format. Mostly RPL if viewed in MS Internet Explorer. |
Parameters | ntext | NULL | Parameters and the values they were submitted with. |
TimeStart | datetime | NOT NULL | Time report started to run. |
TimeEnd | datetime | NOT NULL | Time report finished running? Need to check what finished? |
TimeDataRetrieval | int | NOT NULL | Milliseconds spent retrieving the data. |
TimeProcessing | int | NOT NULL | Milliseconds spent processing the report. |
TimeRendering | int | NOT NULL | Milliseconds spent rendering the report. |
Source | int | NOT NULL | Source of the report exection (1=Live, 2=Cache, 3=Snapshot, 4=History) |
Status | nvarchar(32) | NOT NULL | either rsSuccess or an error code; if multiple errors occur, only the first error is recorded |
ByteCount | bigint | NOT NULL | Size of rendered reports in bytes. |
RowCount | bigint | NOT NULL | Number of rows returned from queries. |
Situation:
I have a silly database table (not mine) storing CMIS Facility week numbers and their starting dates. For those of you unfamiliar with this system, the reason week numbers are different to normal people's week numbers is because these are academic week numbers. So I can't use the built-in functions.
The current structure looks similar to this:
ID SetID WeekNumber StartDate --------- ---------------- ---------------------- ---------------------------- 1 2011/2012 1 2011-07-18 00:00:00 ... 52 2011/2012 52 2012-07-09 00:00:00
The Why
So I find myself writing increasingly complex SQL scripts and it's at the stage where we need to optimize the queries because some scripts are noticeably slow (as observed by the customer...) and then others not.
The What
I'm going to run these benchmark tests against a system that is both up and running via the front-end and back-end. It's MediaWiki CMS used by Wikipedia.org and the like. I like queries against this database because it involves linking a lot of tables and outputting... just articles and their titles.
I have another table holding the audit trail of content approvers on the system. Approvers can e-sign an article (approving it) by clicking on a button. We want to bring back the articles that aren't listed in the audit table (articles yet to be approved).
=FormatDateTime(Fields!BirthDate.Value, DateFormat.ShortDate)
=FormatDateTime(Fields!BirthDate.Value, DateFormat.GeneralDate) =FormatDateTime(Fields!BirthDate.Value, DateFormat.LongDate) =FormatDateTime(Fields!BirthDate.Value, DateFormat.ShortDate) =FormatDateTime(Fields!BirthDate.Value, DateFormat.LongTime) =FormatDateTime(Fields!BirthDate.Value, DateFormat.ShortTime)
An error occurred during local report processing. An error has occurred during report processing. The Group expression used in grouping 'table1_month' returned a data type that is not valid.
Basically, I've started using so much more SQL in our new Business Intelligence projects that I've been revising all my scripts to see what SQL I can optimize.
One of my systems is a MediaWiki CMS which is used for the official Bournemouth University Knowledge Base. The skin itself is the index page loaded for any page in the Wiki system. It logs the IP address (VisitorIP), the URL (VisitorURL) entered via the browser useragent (VisitorUAgent), the User ID (VisitorID, 0 if not logged in) and of course the Timestamp (DateTimeStamp).
What? A quick article on how to populate a database column from another table using a string comparison. Why? I have...
What? In view of the fact that this error pops up in so many systems I take over, I have often gone down the wrong...
The title of this article implies something rather odd and upcoming considering that Sun Microsystems bought MySQL and...
What? A quick note on how I got round one this one. Why? Often enough, our requirement is that the latest record from...
What? Along with my DataJumble function and DataTumble procedure which also help scramble database tables sent to...
Applies to: MySQL Database v5.0.45 MySQL Workbench v6.0.8.11354 build 833 What? This is a quick article on how to get...
case google server first order added files note form version database joomla following deluge report data zoho script user using uploaded table field name creator error function system date website license work parameter would find source client page windows mysql create time need list code value file display used where JoelLipman.Com