Just another WordPress.com site

Posts tagged ‘Select’

Oracle : number check in database

if ( (LENGTH(TRIM(TRANSLATE(contentcode, ‘+0123456789′,’ ‘))) is not null) or (LENGTH(TRIM(TRANSLATE(otherfield, ‘+0123456789′,’ ‘))) is not null)) then
dbms_output.put_line(‘contentcode or otherfield is invalid’);

Advertisements

Ordering Menu Items Alphabetically

Faced with a tedious task of ordering a set of rows using a weight column? Don’t want to type the numbers out? Would rather find a difficult but more interesting method? I have the answer for you!

In Drupal, menu items (like the projects list on this site) are ordered by giving each one a weight. This means you can order the items in anyway you like! The problem I had was I imported about 60 menu items but not in alphabetical order. I didn’t really want to type all the numbers out again so I sought out a less tedious but much more complicated method – its what I do!

I quickly produced a simple select query which ordered my list…

SET @cnt = 0;
 
SELECT
  mid, title, weight,
  @cnt := (@cnt + 1) AS row_number
FROM menu
WHERE pid=36
ORDER BY title;

This selected all menu ID’s, title’s, weight’s and a row count column which incremented a variable called cnt, filtering the list by menu items with a parent ID of 36 and ordering by the title (default ascending). This produced a list like this (EXAMPLE):

ID Sport Weight Row Count
13 golf 19 1
1 rugby -2 2
4 soccer 11 3
20 swimming -8 4

This table shows that the only ordered columns are the title and row count, but the weight (which is used by Drupal) is NOT ordered… This means we’re half way to a solution!

Unfortunately, with the UPDATE command, you cant use the variable column additionally as you would in a select (eg, above)… However I have learned that you can include the formula part in the set section of the UPDATE… I shall Explain by example!

SET @cnt = 0;
 
UPDATE menu
SET weight = @cnt := (@cnt+1)
WHERE pid=36
ORDER BY title;

Same situation as above, (filtering and ordering) – basically I want to update all menu items with a parent ID of 36, ordered by title in ascending order and I want to set the weight to the value of @cnt which itself is equal to its current value plus one

I ran that and was politely told that a number of rows had been updated, so I ran the select again and was shown:

ID Sport Weight Row Count
13 golf 1 1
1 rugby 2 2
4 soccer 3 3
20 swimming 4 4

I hope this helps people out – certainly taken the mundanity out of that job!

How to understand SQL joins

SQL is a functional language. Try to think of a SELECT statement as a function. That is, a mathematical function, or mapping, which — this is important — maps an input to an output. When you select data from a table, think of the table as a source. Data streams out of the table. If it helps you, think of a little grinding cog icon. Then it streams out of the cog onto your screen as a familiar tabular result set. The cog is the SELECT statement, the function. It transforms the data. Maybe it just passes it straight through, but it really is a mapping of input to output. (By the way, if you take this approach when programming in XSLT or LISP, you will grasp things much more easily.)

A join is a SELECT statement with multiple data sources. The data streams from those sources into your cog icon, and a single stream flows out again. A SELECT statement always has one and only one output. (Why? Think of a function… think back to your math classes). Joins are functions that perform matching between data streams. The matching is necessary to merge the multiple input streams into a single output.

Let’s look at two tables of data, apples and oranges.

apples
Variety Price
Fuji 5.00
Gala 6.00
oranges
Variety Price
Valencia 4.00
Navel 5.00

Here is an example SELECT statement:

select apples.Variety, oranges.Price from apples 
 inner join oranges on apples.Price = oranges.Price

Here is (conceptually) what happens when we join these tables:

  1. Choose a left-hand table (the first table in the SELECT statement).
  2. For each row in the right-hand table, take the entire left-hand table and stack its rows next to the row in the right-hand table.
    apples and oranges
    Variety Price Variety Price
    Fuji 5.00 Valencia 4.00
    Gala 6.00
    Fuji 5.00 Navel 5.00
    Gala 6.00
  3. Fill in the missing rows in the right-hand table by duplicating them into the empty spaces.
    apples and oranges
    Variety Price Variety Price
    Fuji 5.00 Valencia 4.00
    Gala 6.00 Valencia 4.00
    Fuji 5.00 Navel 5.00
    Gala 6.00 Navel 5.00
  4. The result is a large table containing the cross-product or Cartesian productof the two data sets. Now satisfy the matching criteria by applying them as a predicate to each row in this new data set. If the predicate is true for the row, include it, otherwise exclude it. The result contains a single row:
    apples and oranges
    Variety Price Variety Price
    Fuji 5.00 Navel 5.00
  5. Now choose only the desired columns from the result:
    apples and oranges
    Variety Price
    Fuji 5.00

This may not be what a given query optimizer really does to execute a join, but the result is the same regardless of the algorithm. If a query optimizer does something different, it is for efficiency, not correctness. Every join always involves a cross product followed by choosing the desired data from the result.

%d bloggers like this: