Data WareHouse Helpdesk

run this in command prompt of windows: sc delete [service name] - to delete services from administrative tools > service

http://cisnet.baruch.cuny.edu/holowczak/oracle/install/9i/

SELECT COLUMNS.COLNO, COLUMNS.TABSCHEMA, COLUMNS.TABNAME, COLUMNS.COLNAME, COLUMNS.TYPENAME, COLUMNS.LENGTH, COLUMNS.SCALE, COLUMNS.NULLS, COLUMNS.IDENTITY, COLUMNS.GENERATED
FROM SYSCAT.COLUMNS COLUMNS
WHERE (COLUMNS.TABSCHEMA='STAGE') OR (COLUMNS.TABSCHEMA='EDW')
ORDER BY COLUMNS.TABSCHEMA, COLUMNS.TABNAME, COLUMNS.COLNO

Labels: SQL

http://datawarehousingfaq.blogspot.com/2008/08/data-warehousing-faqs-ii.html

Labels: Data WareHousing

http://guelphdad.wefixtech.co.uk/sqlhelp/index.shtml

Labels: SQL

By David J. Lake (2006-09-09)

There are two basic types of joins INNER joins and OUTER joins. An outer join can be LEFT OUTER or RIGHT OUTER. If you think about your reading habits it is best to use the join in the direction you read from. English is read from left to right for instance so I always use LEFT joins. That way I remember the first table I'm joining is the one on the left.

Note that the word OUTER is optional so you may see both LEFT OUTER JOIN and LEFT JOIN used. I like to see easily that I'm using an outer join so always label it as such.

When should you use an INNER join and when an OUTER? When you want your results to return the data from the two tables that are in both tables then use an INNER join. When you want all rows from a table, irrespective of whether or not there are matched rows in the other table, then use an OUTER join.

For our scenario we will use a basic team example with tables for players, teams and playersonteams.

create table players(
playerid int auto_increment primary key,
firstname varchar(15),
lastname varchar(25));

insert into players values
(1,'Roy','Halladay'),
(2,'Roger','Clemens'),
(3,'Bobby', 'Abreu');

create table teams(
teamid int auto_increment primary key,
teamname varchar(40));

insert into teams values
(1, 'Toronto Blue Jays'),
(2,'Philadelphia Phillies'),
(3,'Houston Astros');

create table playersonteams(
pid int,
tid int,
primary key (pid,tid));

insert into playersonteams values
(1,1),(3,2);

Let's use an inner join to list the players and each team they are on.

select
playerid,
firstname,
lastname,
teamname
from players
inner join playersonteams
on playerid = pid
inner join teams
on teamid = tid


+----------+-----------+----------+-----------------------+
| playerid | firstname | lastname | teamname |
+----------+-----------+----------+-----------------------+
| 1 | Roy | Halladay | Toronto Blue Jays |
| 3 | Bobby | Abreu | Philadelphia Phillies |
+----------+-----------+----------+-----------------------+

You'll note that the list does not include Roger Clemens. At the beginning of the year he was thinking about retiring again! But what if you wanted to list him as well? In that case switch the join to an outer join and it will list all players irrespective of whether or not they are on a team.

select
playerid,
firstname,
lastname,
teamname
from players
left outer join playersonteams
on playerid = pid
left outer join teams
on teamid = tid


+----------+-----------+----------+-----------------------+
| playerid | firstname | lastname | teamname |
+----------+-----------+----------+-----------------------+
| 1 | Roy | Halladay | Toronto Blue Jays |
| 2 | Roger | Clemens | NULL |
| 3 | Bobby | Abreu | Philadelphia Phillies |
+----------+-----------+----------+-----------------------+

Note that NULL is the correct team to show up for Clemens, his team status is unknown, you should not show a blank value or use an empty string in place of a NULL. Doing so can throw your count off for instance. When you use an aggregate like sum or count, rows with NULL are not counted. That being said, you can leave the value NULL but display it differently. Suppose you want to show any players that are not on a team as None to indicate they aren't on a team. In that case, modify the query as follows. It will still leave the status in the table as NULL and thus won't count incorrectly.

select
playerid,
firstname,
lastname,
coalesce(teamname,'None') as teamname
from players
left outer join playersonteams
on playerid = pid
left outer join teams
on teamid = tid


+----------+-----------+----------+---------------------------+
| playerid | firstname | lastname | teamname |
+----------+-----------+----------+---------------------------+
| 1 | Roy | Halladay | Toronto Blue Jays |
| 2 | Roger | Clemens | None |
| 3 | Bobby | Abreu | Philadelphia Phillies |
+----------+-----------+----------+---------------------------+

The coalesce says that in the case where a NULL would show up for the team name then write the word None in place of the NULL.

A final important note is to notice that I have joined one table and then the next table, they have been done specifically using an ON clause. I then join the final table using another ON clause. Please get into this habit. I see many people using the syntax that I used to use, which is a list of the tables being joined with a where clause and a bunch of ANDs listing the conditions joining the tables.


select
playerid,
firstname,
lastname,
teamname
from players, teams, playersonteams
where
playerid = pid
and teamid = tid

This is an easy habit to get into, especially when the mysql manual uses it in most of their examples (and I'd say that is unfortunate as it promotes bad habits). The problem with using list syntax is that it is not as clear to see what tables are joined on what clause. It is also possible, when getting into such a habit, to mix the list syntax and the ON clause syntax when writing more complicated joins. It is also possible to mix a condition into the where clause of such joins when it actually belongs in the ON clause instead.

Labels: SQL

SELECT 'SELECT '''
|| table_name
|| ''', '''
|| column_name
|| ''', MIN(LENGTH('
|| column_name
|| ')) AS MIN, MAX(LENGTH('
|| column_name
|| ')) AS MAX FROM '
|| table_name
|| ' UNION ALL' AS "--QUERY"
FROM all_tab_cols
WHERE owner = 'OWNER' AND column_name LIKE 'COL%'
AND table_name NOT LIKE 'VIEW_%'

Labels: Oracle, SQL