Articles - Joel Lipman .Com

Stored Procedure to List Distinct Values and Counts

: Joel Lipman; Transact-SQL; 26 October 2012; Hits: 7798

What?
Yes you could just run a SELECT DISTINCT query along with collation to make it case-sensitive and/or accent-sensitive but using this stored procedure means I only specify the table and then the columns.

How?

: Joel Lipman; Databases; 26 October 2012; Hits: 5076

What?
This is a quick note to show you how to convert a given comma delimited string into a database table:

copy raw

Given:   "Title,Forenames,Surname"

Return:
  ID     Value
  ------ ----------------
  1      Title
  2      Forenames
  3      Surname

Note the below example omits the ID column and just leaves VALUE.

Given: "Title,Forenames,Surname"
Return:
ID Value
------ ----------------
1 Title
2 Forenames
3 Surname
Note the below example omits the ID column and just leaves VALUE.

Why?
Do we need a reason?

How?

Category: Databases :: Article: 440

: Joel Lipman; Databases; 11 October 2012; Hits: 34675

Why?
Data Consistency. I was tasked with finding variations of our default values. End-users were complaining that some default values get listed twice because their system was case-sensitive despite the collation of the SQL Server being case-insensitive.

What?
End-users said that they could see the options "Data Not Yet Available" as well as "Data not yet available". These are obviously the same values, so I should go through the database and change all variations to be typed the same way in the same case.

Wouldn't it be nice to search through your entire database for similar sounding values (so not just case), and in this example, find typos as well, eg. "Daat ont Ety Aviable".

How?

: Joel Lipman; Transact-SQL; 10 October 2012; Hits: 8531

What?
This article is for demonstrating how to use a SOUNDEX in a select and then listing all the variations based on case-sensitivity.

Why?
We have a database with data in it. For a particular column we have setup default values, let's use the example "Data Not Yet Available". Unfortunately the end-user reported these default values sometimes list twice, especially when the case is different, eg. "Data not yet available". The final system (qlikview) was case-sensitive despite our server collation being case-insensitive.

Furthermore, we now have the task of finding all the variations of the default values which we found we could do with the built-in SOUNDEX function.

How?

: Joel Lipman; Databases; 08 October 2012; Hits: 10220

What?
I have a PHP script which behaves like PhpMyAdmin, in that it automatically lists a database structure and the fields to modify similar to an excel spreadsheet. Now without using PHP, I need standalone SQL scripts that can simply be used to search an entire database for a particular string of word(s).

I've posted my own as well as some others I've lifted from elsewhere, as they worked with my environment, to put them in one place on a website I've bookmarked (my personal site :c)

: Joel Lipman; IT Support; 13 September 2012; Hits: 8830

Not a useful note and more of a personal opinion then factual, but beyond the inventors of the Internet or any hardware has to be the following notion from IBM:

a 1976 research report by Lance A. Miller and John C. Thomas of IBM, noted that "It would be quite useful to permit users to 'take back' at least the immediately preceding command (by issuing some special 'undo' command)."
Source: Behavioral Issues in the Use of Interactive Systems.

If you've guessed it, my vote goes for whoever invented the "Undo" button: cross-platform, multi-lingual, future-proof and as everlasting as human error.

More?

: Joel Lipman; MooTools Framework; 11 September 2012; Hits: 5798

Somewhat incomplete! Will post a final solution but here are the basics

What?
This article is a quick note to myself on the idea of a slideshow with the MooTools javascript framework. Basically, I want to create a div layer that I can see through (like a window with no glass) and for all the flashy stuff, to only appear within the boundary of the parent div layer.

Why?
I'm making a slideshow from scratch.

How?
The idea is:

: Joel Lipman; Cascading Stylesheets; 02 September 2012; Hits: 16504

What?
So I have an unordered list of values (*, 1, 2, 3...) and for styling reasons I want the asterisk to be the same size as the numbers but not overlap any list item beneath it. By increasing the size of the asterisk, this often increases the line-height and causes the overall line height to change for other objects in the same row. The problem afterwards was that the asterisk character would overlap the item beneath it (in this case #2) so when a user clicked on #2 they would in fact be clicking on the item containing the asterisk above it.

Objective

Box List Before:	Box List After
* 1 2 3	* 1 2 3

How?

: Joel Lipman; Joomla; 27 August 2012; Hits: 24331

Applies to Joomla 1.6+

What?
This is an article to describe how to add an article modal button in the Joomla Admin Panel (at time of print version 2.5.6) of your component including the all important clear button. What I found was that no one posted this solution which I found is compatible with almost any site and with all my components without having to modify the below script.

Why?
The article modal is a much easier way for the end-users to select a Joomla! article in the component parameters/options. The "clear" button is essential as some article_modal fields may not be required and are difficult to clear once set.

How?

: Joel Lipman; Oracle PL/SQL; 22 August 2012; Hits: 8750

What?
This article describes a function that will return random data based on a given value. The function intends to determine the data type and return data that is absolutely irrelevant to the original data but the same length and the same type of data.

Why?
The joy of working with the plethora of applications out there result in me using MySQL, Oracle PL/SQL and Transact SQL in my day job. The equivalent functions have to be written up all in the name of "seamless integration"...

How?

: Joel Lipman; Oracle PL/SQL; 21 August 2012; Hits: 6411

What?
How to use the randomization features of Oracle PL/SQL.

Why?
I've been tasked with writing a stored procedure that scrambles data for developers working with some of our databases containing sensitive data.

How?

copy raw

SELECT DBMS_RANDOM.option1[(option2)] FROM DUAL;

SELECT DBMS_RANDOM.option1[(option2)] FROM DUAL;

Category: Oracle PL/SQL :: Article: 431

: Joel Lipman; JavaScript; 21 August 2012; Hits: 5295

What?
This is to describe how to change the value of a field of a form from it's default when it is clicked on so that it auto-clears and changes to how it normally works.

Example?
Note the value in the below field. Now click on it and it disappears, allowing you to type normally. Clear whatever you type so the field is empty and click somewhere on the page (not on a link as this will take you to another page) and the field returns to displaying "Name:".
Why?
I've noticed a lot of solutions now use JQuery or Mootools but most of these will not work in Internet Explorer 7 which is unfortunately still in use. I needed a back-to-basics solution and this is the one I have used since my fad of JavaScript 1.2 in the 90s. For broader compatibility, this is my recommended solution.

Credit where Credit is Due:

RSS Feed

Related Articles

Joes Revolver Map

Joes Word Cloud

Accreditation

Donate & Support