Do you use analytic functions?

Posted on by
Reply

I’m currently programming a who’s online for leads at work, and was having lots of difficulty in getting the results I needed to properly display the most recent activity.

We have three tables that need to be looked up due to the way the schema was designed. The primary table is a user_log table that stores all actions that a user performed along with a timestamp. However, this log table does not include any user information aside from a user_id. So, to get the information about a user, we need to join that user_id to the user_id in a users table.

But, here’s where it gets really messed up – the users table doesn’t contain the information we need. Instead, it has a contact_id field in the users table, which must be then joined to a contacts table.

So it goes like this:

.. WHERE user_log.user_id = users.user_id AND users.contact_id = contacts.contact_id

Yeah, three tables just to get contact information.

I was finding that the standard SQL Distinct/Group By keywords were proving ineffective in getting the results I needed, which would be to just give me one user_id and the most recent created timestamp in the user_log table. Instead, I was getting multiple rows for the same user_id.

Fortunately, I found out about analytic functions in Oracle, which helped me isolate out unique user_ids and get the latest timestamp for that user by using ROW_NUMBER( ) where the sequence_number = 1 (giving me the topmost result for each user_id).

Unfortunately, I also learned that mySQL does not have analytic functions built in, but it can be emulated in native SQL (Part 1) (Part 2).

The best explanation I can give about the common aggregation functions that most people tend to use like SUM(), AVG(), etc vs analytic functions is:

Assume you have a set of rows.

The aggregation function will take that set of rows and output only one row with the results, while an analytic function will output that set of rows with the results appended.

The analytic functions do not have to work this way, but it’s useful for performing ranking analysis or sorting rows into individual sets of results.

How to stop or kill data pump jobs in Oracle the CORRECT way

Posted on by
7

In my never ending frustrations with using Oracle (seriously, I loathe Oracle above all else), I could not find an absolute answer on how to stop or kill or delete data pump jobs being executed. I found the answer via Metalink, and I’m going to share it because I feel these answers should be easily accessible. It’s a two step process.

1. Get the list of datapump jobs:

SET lines 200
COL owner_name FORMAT a10;
COL job_name FORMAT a20
COL state FORMAT a11
COL operation LIKE state
COL job_mode LIKE state

-- locate Data Pump jobs:

SELECT owner_name, job_name, operation, job_mode,
state, attached_sessions
FROM dba_datapump_jobs
WHERE job_name NOT LIKE 'BIN$%'
ORDER BY 1,2;

The output might look something like this:

OWNER_NAME JOB_NAME             OPERATION   JOB_MODE    STATE       ATTACHED_SESSIONS
---------- -------------------- ----------- ----------- ----------- -----------------
SCHEMA_USER SYS_IMPORT_SCHEMA_01 IMPORT      SCHEMA      EXECUTING                   1

There are two things needed to perform the kill:
1. OWNER_NAME (Which is SCHEMA_USER)
2. JOB_NAME (Which is SYS_IMPORT_SCHEMA_01)

With that information, we can now stop and kill the job:

SET serveroutput on
SET lines 100
DECLARE
   h1 NUMBER;
BEGIN
  -- Format: DBMS_DATAPUMP.ATTACH('[job_name]','[owner_name]');
   h1 := DBMS_DATAPUMP.ATTACH('SYS_IMPORT_SCHEMA_01','SCHEMA_USER');
   DBMS_DATAPUMP.STOP_JOB (h1,1,0);
END;
/

Check that the job has stopped:

SQL> SET lines 200
SQL> COL owner_name FORMAT a10;
SQL> COL job_name FORMAT a20
SQL> COL state FORMAT a11 
SQL> COL operation LIKE state
SQL> COL job_mode LIKE state
SQL> 
SQL> -- locate Data Pump jobs:
SQL> 
SQL> SELECT owner_name, job_name, operation, job_mode,
  2  state, attached_sessions
  3  FROM dba_datapump_jobs
  4  WHERE job_name NOT LIKE 'BIN$%'
  5  ORDER BY 1,2;

no rows selected

Installing SuSE Enterprise 10 Linux on Hyper-V (The CORRECT way)

Posted on by
Reply

Things I learned:
1. Mouse still does not work in a Remote Desktop session. You’ll have to use VNC, and if you’re using Windows, the port is 5901 not 5801 like the VNC server in SuSE tells you.
2. Contrary to what the other blogs are saying, you do NOT need to run ./setup.pl x2v or do any kind of patching/compiling. All you need to do is insert the integrated components disc (Download from Microsoft Connect for free), and run ./setup.pl drivers
3. When you install SuSE, you’re better off not installing any kind of network adapter. Install the normal network adapter after installing the IC drivers.
4. There is no heartbeat monitoring in the IC drivers.
5. When you configure your time settings, use local time, not UTC time, or your system clock will screw up like mentioned above.
6. The system is significantly faster with the IC installed.
7. Be sure to use version SP2. This is what I used. My first mistake was to use the version prior to SP1.
8. DO NOT UPDATE THE KERNEL if you already installed the drivers. Or if you want to, always take a snapshot of the system first so you can roll back, reboot then reinstall the drivers. It needs to be recompiled for every new kernel version.
9. THE DVD DRIVE IS NO LONGER RECOGNIZED after driver installation :/

MySQL to pure utf-8 character set configuration

Posted on by
Reply

I’m programming a new version of my Japanese translator in the python Django framework as practice. Aside from some hard lessons, such as you cannot install a Python 2.5 x64 binary in Windows and expect the mod_python installer to work, the following was my next problem:

Had some trouble importing a file with Japanese in UTF-8 encoding into mySQL. The import would go fine, then would completely come out garbage on output. I eventually figured out what to do. Edit the my.cnf for the server, and use the following parameters:


[mysqld]
default-character-set=utf8
default-collation=utf8_general_ci
character-set-server=utf8
collation-server=utf8_general_ci
init-connect='SET NAMES utf8'

[mysql]
default-character-set=utf8

Now, if I did the following in mySQL:


mysql> SHOW VARIABLES LIKE 'char%';
+--------------------------+--------------------------------+
| Variable_name            | Value                          |
+--------------------------+--------------------------------+
| character_set_client     | utf8                           |
| character_set_connection | utf8                           |
| character_set_filesystem | binary                         |
| character_set_results    | utf8                           |
| character_set_server     | utf8                           |
| character_set_system     | utf8                           |
| character_sets_dir       | C:xamppmysqlsharecharsets |
+--------------------------+--------------------------------+
8 rows in set (0.00 sec)

mysql> SHOW VARIABLES LIKE 'collation%';
+----------------------+-----------------+
| Variable_name        | Value           |
+----------------------+-----------------+
| collation_connection | utf8_general_ci |
| collation_database   | utf8_general_ci |
| collation_server     | utf8_general_ci |
+----------------------+-----------------+
3 rows in set (0.00 sec)

If everything is utf8, then you are golden!

Hi, I create problems.

Posted on by
1

For the past month, on and off, I’ve been thinking about problem-solving – there’s too much of it. Everyone’s a problem solver, but since when did you hear someone openly say that they create problems? I think the relationship between a problem and solution is cyclical – a problem is a consequence of a solution, and a solution is a consequence of a problem. The relationship is symbiotic, they cannot exist without each other.

When did problems become… a problem? How did the word carry such a negative connotation? I’ve thought of problem-making as a form of opportunity – because the problem exists or is created, there will more-than-likely be someone devoted to finding the solution. Without the problem, the need for a solution wouldn’t exist.

The entire thought about problems came to me one day when I was thinking about outrageous ideas for a novel (which I would never write due to my lack of ability to properly describe scenarios). I thought about having a villain as the main character. Following that, I thought about the cruelest and evilest villain possible, and came up with a person who had the ability to solve any kind of problem without regard to the possible consequences of the solution.

At first, the reader would have the impression that this was a wonderful gift to have – solving any kind of problem, and that the main character is this righteous person who wants to help the world. However, as the main character begins to solve every kind of problem, s/he fails to realize the consequences of the act – people begin to lose meaning within their lives because the problems they lived and existed to solve, were no longer available.

What happens is the world sees an increase in suicide rates. I know someone will call plot hole because those people can find other problems to work with. No. The main character is out to solve EVERYTHING, and is able to do it with extreme ease. The world populace deems the main character as a threat to humanity, and the story ends with his him solving this problem with his own death. I never decided what happened to the world after that, but I’d imagine anarchy as if the idea is to promote problem generation as a ‘solution’, then the world would have to turn onto itself for people to strive to find solutions.

But that brings me to the question, why would a problem have to be chaotic, negative, so consequential?

That’s why I’m inclined to say that I’m a problem creator. There’s too many problem solvers out there already. I want to be the one that creates problems so opportunity lends itself to another’s future.

Or, I could be wrong completely. I recall situations where I have to solve a problem, and after solving that problem, there is no additional problems generated from the resolution. Maybe its kind of like division and multiplication:

Problem x Solution = Problem_New

Solution / Problem = undefined (in certain cases)

Facelist Improved: Facebook Autocompletion JQuery Plugin

Posted on by
3

I was evaluating all the available JQuery plugins to get a Facebook-like contact autocompletion functionality, and deemed the one by Ian Tearle to be the most complete available. However, most complete does not mean fully done, or bug free, and I learned this the hard way doing an initial implementation on a project I’m doing at work.

Spending all day with the lovely Firebug debugger and testing in both Firefox 3 and IE7, I can say I have it working the way I need it to. My version fixes the following issues:

- Not all results that are returned are displayed

- Duplicate contacts can be inserted

- When searching, the user is not notified that a search is being performed

- The ID was being displayed with the search results, this has now been removed

There is still one lingering issue: the values stored in the hidden field… the plugin needs the trailing comma at the end of the values in order to properly perform backspace deletion. It’s really a minor issue as long as you remember in your code that parses the value, that when exploding the commas, you’ll have to ignore the final array row as it will be blank or null.

Demo

Download

I do not provide any support for my modified plugin, but feel free to leave comments if you hit bugs, and if I have time, I will fix them. I’m also hoping Ian will accept my changes so that I can just do a link to his page for the fix.

Ian Tearle’s Page for the Plugin

Edit: Yay, Ian accepted the changes, and they are now updated on the JQuery Plugin project page! I’ve updated my download link to point to the page.

Convert Oracle Date to UNIX Time

Posted on by
1

Oracle unfortunately does not have a UNIX conversion function. However, you can do it mathematically in SQL. I’ve written a function in PHP that will convert a date field to a UNIX timestamp in Oracle.

/**
* Inserts the SQL necessary to format a date to a UNIX time in Oracle.
* @param string Field name to convert.
* @param string (Optional) Field Alias
* @return string The SQL necessary to convert to UNIX time.
*/
function toUnixTime($field, $fieldAlias=null){
if($fieldAlias== null){
$fieldAlias= $field;
}

return "(ROUND((".$field." - to_date('01-01-1970', 'DD-MM-YYYY')) * (86400) + (28800)) ".$fieldAlias;
}





Usage:


$sql = “SELECT “.toUnixTime(“created”).” FROM users”;


or


//CreatedOn alias

$sql = “SELECT “.toUnixTime(“created”, “createdOn”).” FROM users”;