Articles - Joel Lipman .Com

Basic Oracle Function Structure

: Joel Lipman; Oracle PL/SQL; 15 January 2012; Hits: 9986

What?
I need to refer to how to create a function in Oracle PL/SQL and sites on the net just attempt to overcomplicate everything and have forgotten how it is to be new to Oracle. I need a function in it's simplest form and if I want to torture my successors, I'll complicate the function myself.

How?
Functions are supposed to return a single value, which is all I need in this case. In my example, I need to submit an Active Directory (AD) username and receive a student ID number instead, all with the aim to improve performance on some SSRS queries which accept either an ID number or an AD name as user parameters.

Syntax:

copy raw

CREATE [OR REPLACE] FUNCTION function_name
    [ (parameter [,parameter]) ]

    RETURN return_datatype

IS | AS
    [declaration_section]

BEGIN
    executable_section

[EXCEPTION
    exception_section]

END [function_name];

CREATE [OR REPLACE] FUNCTION function_name
[ (parameter [,parameter]) ]
RETURN return_datatype
IS | AS
[declaration_section]
BEGIN
executable_section
[EXCEPTION
exception_section]
END [function_name];

Category: Oracle PL/SQL :: Article: 409

: Joel Lipman; MySQL; 02 January 2012; Hits: 8177

What?
I have a mySQL database table of room assets that has a field containing the ID numbers of images relevant to this room.

The Problem?
When I select specifying the statement "WHERE IN (c.RoomImages)", this is interpreted as a string and when converted to a number only retrieves the first value before the first comma. Consider the following, the first query is how MySQL interprets the query and the second is what I want it to do:

copy raw

SELECT value FROM my_table WHERE my_id IN ('1, 2, 3')
SELECT value FROM my_table WHERE my_id IN ('1', '2', '3')

SELECT value FROM my_table WHERE my_id IN ('1, 2, 3')
SELECT value FROM my_table WHERE my_id IN ('1', '2', '3')

Category: MySQL :: Article: 408

: Joel Lipman; SQL Server Reporting Services; 14 December 2011; Hits: 24674

What?
I have created an SSRS report which can compare 4 reports side by side and brings up their latest execution times to the nearest millisecond. The report has 4 parameters. Each parameter is a dropdown populated by a list of all available reports.

Why?
I want the report to be run with the 3rd and 4th parameter as OPTIONAL. When I leave the 3rd and 4th parameter untouched (="<Select a value>"), the report complains saying "Report #3 parameter cannot be blank!". Before you ask, I have ticked both "Allow Blank" and "Allow NULL".

How?
This is the tough part. I was reading up on the MSDN page for the closest solution but it still didn't work for me. But the idea of inserting a NULL entry to select sounded good.

Aim / Objective
The plan will be to replace the default "<Select a Value>" with a custom null entry and the end-user will be none the wiser.

: Joel Lipman; MediaWiki; 06 December 2011; Hits: 5862

What?
In not as many words as others, here's my MySQL query to extract details on images stored in the MediaWiki CMS system (v1.14).

Why?
Prior to a migration and just after another change freeze, I had sent all Wiki articles modified since the last export but then needed to send all images that had also been either added/modified since.

How?
Using MySQL, the following query lists the image name, size, user who uploaded, timestamp and the path. Remember that the paths are determined using the MD5 Hash of the filename:

: Joel Lipman; MySQL; 06 December 2011; Hits: 11358

Just putting a note as I have spent ages looking for a solution and getting it to work in my environment.

What?
Need to be able to omit HTML tags in certain fields of a mySQL database.

Why?
We are preparing to migrate old content to a new system. From a MediaWiki CMS to a SaaS called Service-Now. The previous interlinking between images could no longer be used.

: Joel Lipman; SQL Server Reporting Services; 02 December 2011; Hits: 14023

Why?
I've recently written a report for SQL Server Reporting Services 2008 R2 (SSRS) which will compare up to 4 reports and will compare the time taken for each one. The breakdown or what I was able to measure with the default installation are the times taken for "data retrieval", "processing", "rendering", and then the totals of these.

I haven't Googled this at the time of print so there may be a million better solutions out there, this is just how I did it. This may look like a horrible report which would fail an accessibility test but visually it says straight away which is the better report; and when comparing to the previous runs (using a second dataset) you can tell where changes were made and how this affected the reports' performance.

What?
What I'm trying to do is display a set of results (comparing various reports) in a table and then to color the backgrounds based on whether they are the fastest or slowest in the set.

Something like:

: Joel Lipman; Databases; 28 November 2011; Hits: 15609

MySQL is just the best
Unfortunately I make a living using Microsoft and Oracle products. I shouldn't say unfortunately as I don't see myself doing any other job and it beats daytime television any day.

I use this quite a lot so I thought I'd put an article here somewhere. Based on the following concept:

copy raw

RowID     column_to_return_as_string   
--------- --------------------------
1         Me
2         Myself
3         I

-- to be returned as
RowID     my_field_name   
--------- --------------------------
1         Me,Myself,I

RowID column_to_return_as_string
--------- --------------------------
1 Me
2 Myself
3 I
-- to be returned as
RowID my_field_name
--------- --------------------------
1 Me,Myself,I

Category: Databases :: Article: 403

: Joel Lipman; SQL Server Reporting Services; 17 November 2011; Hits: 35119

How to Display Report Execution Time in SQL Server Reporting Services 2008 R2
So there are other articles out there but I was looking to display in milliseconds the execution time it took for a particular report (which searches for results matching the submitted parameter) to run.

Lifted from Dattatray Sindol's blog
Other sites have this solution as well so who copied off who is not my concern as this is not the solution to our problem. This is the solution that I initially used but my end-users were asking why is it always 0 seconds. This was because we were using the following MDX statement which had seconds as its smallest denominator:

copy raw

="Execution Time: " +
CStr(System.DateTime.Now.Subtract(Globals!ExecutionTime).Hours) + " hour(s)" + " , " +
CStr(System.DateTime.Now.Subtract(Globals!ExecutionTime).Minutes) + " minute(s)" + ", " +
CStr(System.DateTime.Now.Subtract(Globals!ExecutionTime).Seconds) + " second(s)"

// yields
// 0 hour(s), 1 minute(s), 2 second(s)

="Execution Time: " +
CStr(System.DateTime.Now.Subtract(Globals!ExecutionTime).Hours) + " hour(s)" + " , " +
CStr(System.DateTime.Now.Subtract(Globals!ExecutionTime).Minutes) + " minute(s)" + ", " +
CStr(System.DateTime.Now.Subtract(Globals!ExecutionTime).Seconds) + " second(s)"
// yields
// 0 hour(s), 1 minute(s), 2 second(s)

This isn't what I want... It wasn't accurate enough and always saying 0 seconds was just confusing the end-user...

Category: SQL Server Reporting Services :: Article: 401

SSRS Zero Padding

: Joel Lipman; SQL Server Reporting Services; 10 November 2011; Hits: 201444

Quick Reminder
I didn't want to do this at the database level, mainly because it meant modifying the SQL query. The zero padding would need to be applicable within an MDX query.

The Situation
We have a database using Oracle 10g, and a SQL Server Reporting Services v2008 R2 environment. My use for this was when displaying an audit log displaying the oracle errors.

Oracle Errors
An Oracle error usually returns in the format of -12345. If we want to look them up the error is ORA-12345. Unfortunately Oracle also returns errors of less than 10000 so ORA-00201 would actually be returned as "-201". As I wanted a link so that the user can just click on this link and it would take them to http://ora-00201.ora-code.com/.

: Joel Lipman; SQL Server Reporting Services; 07 November 2011; Hits: 10901

Intro
If you ever want to check the parameters submitted with a report for alpha numeric characters (so it doesn't contain symbols, punctuations, etc) then you should do this at the database level, and then get the report to complete the check:

The Plan

User enters value in parameters and clicks on "View Report"
Report passes parameter to dataset which gets formatted by the database
Report retrieves (select) formatted parameter as a field value to use
Report loads with changes based on returned value.

The Gist

Add database level parameter check
Add IIF in SSRS to confirm

: Joel Lipman; SQL Server Reporting Services; 04 November 2011; Hits: 11116

What?
We have a report in SQL Server Reporting Services 2008 R2 (SSRS) reading from an Oracle 10g database which works great and lists all the details on a specific student. An additional request is that there appears a link that will run a stored procedure which

Updates a timestamp in an existing table
Inserts a row into an audit table

I need the report to run the stored procedure, then based on the errorlevel, return a message.

How?

: Joel Lipman; MySQL; 31 October 2011; Hits: 9739

Why?
I recently made a joomla module that displays the lastest members to signup. It goes a little further and counts activated accounts for the past day, week, month and year (the below examples count all accounts irrespective of being activated or not). It needs to pick up trends as well and compare for example todays, with yesterdays up to the same hour.

How?
I used to use a lot more PHP but since becoming an analyst, I do more at the database level now. What follows should be usable mySQL statements to get all the numbers:

Credit where Credit is Due:

RSS Feed

Related Articles

Joes Revolver Map

Joes Word Cloud

Accreditation

Donate & Support