zlika's

If you are not capable to hear the fireflies in the night, don’t push ‘play’ :-)

Félicitations

The cursors in MS SQL Server are not the best way to manipulate the data, because they need more recourse. If you can escape the usage of cursors, do it. OK, the cursors give us a flexible way of manipulating the information. You can imagine a bundle of information, that we use for move through other information and modify it using the information of our bundle (puting the information in the WHERE clause).

Let’s say we need to modify a table containing user information. We need to mark those of them, who are registered more than a month (or 30 days) ago. Simultaneously, we need to write in another table only the modified users and the time of modification.

First we create tables, add keys and populate tables.

USE zlika
GO

-- Create Tables
CREATE TABLE Departments
(
	ID INT NOT NULL
	, DepartmentName VARCHAR(50)
)
GO

CREATE TABLE Person
(
	ID INT IDENTITY
	, FirstName VARCHAR(20)
	, LastName VARCHAR(50)
	, DepartmentID INT
	, RegisteredDate DATETIME
	, CheckStatus BIT
)
GO

CREATE TABLE Checked
(
	PersonID INT
	, CheckDate DATETIME
)
GO

-- Add Keys
ALTER TABLE Departments
ADD CONSTRAINT PK_DepartmentID PRIMARY KEY CLUSTERED
(
	ID ASC
)
GO

ALTER TABLE Person
WITH CHECK ADD CONSTRAINT
FK_Departments_DepartmentID FOREIGN KEY
(
	DepartmentID
)
REFERENCES Departments
(
	ID
)
GO

-- Populate Tables
INSERT INTO Departments (ID, DepartmentName) VALUES
(1, 'IT')
, (2, 'Administration')
, (3, 'Technical Support')
, (4, 'Accounting')
, (5, 'Marketing')
GO

INSERT INTO Person (Firstname, LastName, DepartmentID, RegisteredDate) VALUES
('First', 'Smith', 5, '2010-02-01')
, ('Second', 'Brown', 4, '2010-02-02')
, ('Third', 'Marchal', 3, '2010-02-03')
, ('Fourth', 'Jameson', 2, '2010-02-04')
, ('Fifth', 'Anderson', 1, '2010-02-05')
, ('Sixth', 'Cameron', 1, '2010-02-06')
, ('Seventh', 'Blake', 2, '2010-02-07')
, ('Eight', 'Robert', 3, '2010-02-08')
, ('Ninth', 'Jameson', 4, '2010-02-09')
, ('Tenth', 'Wiliams', 5, '2010-02-10')
GO

-- Preview Tables
SELECT
	*

FROM
	dbo.Person AS P INNER JOIN dbo.Departments AS D
		ON P.DepartmentID = D.ID
GO

Create and populate tables

Let’s say we need to mark only the users of IT, Technical Support and Marketing departments who are registered 28 days ago.

USE zlika
GO

SELECT
	P.FirstName
	, P.LastName
	, D.DepartmentName
	, CONVERT(CHAR(10), P.RegisteredDate, 103) AS RegisteredDate

FROM
	dbo.Person AS P INNER JOIN dbo.Departments AS D
		ON P.DepartmentID = D.ID
GO

Select all persons

The information that we’re going to use as filter (in the WHERE clause) is PersonID, RegisteredDate from Person table. That’s the query that is going to populate the cursor.

USE zlika
GO

SELECT
	P.ID
	, P.RegisteredDate

FROM
	dbo.Person AS P INNER JOIN dbo.Departments AS D
		ON P.DepartmentID = D.ID

WHERE
	D.DepartmentName IN ('IT', 'Technical Support', 'Marketing')
	AND P.RegisteredDate < DATEDIFF(DAY, 28, GETDATE()) -- 2010-02-06
GO

Populate Cursor Statement

The work we need to do is between BEGIN and END keywords. First we mark the status as ‘checked’ (1), then we add the PersonID and current date in another table (Checked).

USE zlika
GO

-- Execute the UPDATE Statement
UPDATE Person
SET CheckStatus = 1
WHERE ID IN (1, 3, 5)

-- Execute the INSERT Statement
INSERT INTO Checked (PersonID, CheckDate) VALUES
(1, GETDATE())
, (3, GETDATE())
, (5, GETDATE())

OK, here’s the guy who do the real work – the cursor itself. We store the content of the cursor in a variables and we execute the two steps for every row of it. The variables are our filters – we use them in the WHERE clause.

USE zlika
GO

-- Declare the variables
DECLARE @PersonID INT
DECLARE @Date DATETIME = DATEDIFF(DAY, 28, GETDATE())

-- Declare the cursor
DECLARE Update_Person_Cursor CURSOR FOR

-- Populate the cursor
SELECT
	P.ID
	, P.RegisteredDate

FROM
	dbo.Person AS P INNER JOIN dbo.Departments AS D
		ON P.DepartmentID = D.ID

WHERE
	D.DepartmentName IN ('IT', 'Technical Support', 'Marketing')
	AND P.RegisteredDate < @Date
	AND P.CheckStatus IS NULL

-- Open the cursor to work with it
OPEN Update_Person_Cursor

-- Select The first row into the variables (@PesronID = 1, @Date = 2010-02-01)
FETCH NEXT FROM Update_Person_Cursor INTO
	@PersonID
	, @Date

-- While there are no more rows in the cursor...
WHILE @@FETCH_STATUS = 0

-- Do the two steps!
BEGIN
	-- Execute the UPDATE statement
	UPDATE Person
	SET CheckStatus = 1
	WHERE ID = @PersonID

	-- Execute the INSERT statement
	INSERT INTO Checked (PersonID, CheckDate) VALUES
	(@PersonID, GETDATE())

	-- Select The second row into the variables (@PesronID = 3, @Date = 2010-02-03)
	FETCH NEXT FROM Update_Person_Cursor INTO
		@PersonID
		, @Date
END

-- Close The cursor
CLOSE Update_Person_Cursor

-- Kill the cursor
DEALLOCATE Update_Person_Cursor
GO

The records in the ‘Checked’ table are those we needed.

USE zlika
GO

SELECT * FROM Checked

Content of 'Checked' table

…and the status in the ‘Person’ table is changed to ‘1′.

USE zlika
GO

SELECT
	*

FROM
	dbo.Person AS P INNER JOIN dbo.Departments AS D
		ON P.DepartmentID = D.ID
GO

The content of 'Person' table

Félicitations

There is an old, old, old bulgarian tradition. On March 1 everybody ties a bracelet, prepared of red and white threads to all the persons he loves. That’s a way to wish good health and to meet the spring. When the guy, to whom the martenica has been tied, sees a stork, he has to replace it of his hand and tie it to a tree – for fruitfulness.

Few days ago I received an invitation to visit the post office. My sister has told me that she has sent me a letter with several martenitsa. I took my letter and today I’m gonna wear one of them. :-)

As a bad painter, I drew one not so beautiful martenitsa for all the people I love. I’m wishing you good health, guys! :-)

One not so beauty martenica for all the people I love :-)

Félicitations

My ex-colleague asked me how to insert query result in a variable for generate report in Microsoft SQL Server. After few explanations I noticed that she has decided to use SSIS (SQL Server Integration Services) to automate. She sent me an example in which she uses YEAR(), MONTH() and DAY() functions.

-- YEAR(), MONTH(), DAY()
SELECT
	'Test_'
	+ CAST(YEAR(GETDATE()) AS CHAR(4))
	+ '-'
	+ CAST(MONTH(GETDATE()) AS CHAR(2))
	+ '-'
	+ CAST(DAY(GETDATE()) AS CHAR(2))
	+ '.txt' AS DynamicFileName
GO

Result

The result is strange, because the return data type of those functions is integer. To fix this, we need to add leading zeros in front of days, months, hours, minutes and seconds.

-- Add leading zeroes
DECLARE
	@Day AS CHAR(2)
	, @Month AS CHAR(2)
	, @Year AS CHAR(4)
	, @Hour AS CHAR(2)
	, @Minute AS CHAR(2)
	, @Second AS CHAR (2)
	, @NewFileName AS CHAR(26)

SELECT @Day = DATEPART(DD, GETDATE())
SELECT @Day = RIGHT('0', 2 - LEN(@Day)) + @Day

SELECT @Month = DATEPART(MM, GETDATE())
SELECT @Month = RIGHT('0', 2 - LEN(@Month)) + @Month

SELECT @Year = DATEPART (YY, GETDATE())

SELECT @Hour = DATEPART(HH, GETDATE())
SELECT @Hour = RIGHT('0', 2 - LEN (@Hour)) + @Hour

SELECT @Minute = DATENAME(MI, GETDATE())
SELECT @Minute = RIGHT('0', 2 - LEN (@Minute)) + @Minute

SELECT @Second = DATEPART(SS, GETDATE())
SELECT @Second = RIGHT('0', 2 - LEN (@Second)) + @Second

SELECT @NewFileName = 'Test_' + @Year + '-' + @Month + '-' + @Day + '_' + @Hour + @Minute + @Second + '.txt'

SELECT @NewFileName AS DynamicFileName
GO

Result

The best way is to convert the result of GETDATE() function to CHAR and extract the different parts of it with the SUBSTRING() function.

-- SUBSTRING()
DECLARE
	@Date CHAR(20) = CONVERT(CHAR(10), GETDATE(), 112) -- yyyymmdd
	, @Time CHAR(20) = CONVERT(CHAR(10), GETDATE(), 108) -- hh:mi:ss

SELECT
	'Test_'
	+ SUBSTRING(@Date, 1, 4) -- Year
	+ '-'
	+ SUBSTRING(@Date, 5, 2) -- Month
	+ '-'
	+ SUBSTRING(@Date, 7, 2) -- Day
	+ '_'
	+ SUBSTRING(@Time, 1, 2) -- Hour
	+ SUBSTRING(@Time, 4, 2) -- Minute
	+ SUBSTRING(@Time, 7, 2) -- Second
	+ '.txt' AS DynamicFileName
GO

Result

My ex-colleague needs the dynamic filename to generate reports with SSIS. I’m gonna show her soon how to create simple project and the way for the same manipulation of the date in SSIS.

Félicitations

Тo be consumed in August at the time of your vacance

Ingredients
1. Mp3 Player
2. Deep Purple – Slaves and Masters
3. Rainbow – Strangers in Us All

Method
1. Put those two albums in the Mp3 Player
2. Stir them well
3. Find a few good friends to pass together the vacance on the beach. Drink some beers (excluding the driver). Spontaneously after midnight get in the car. You sit back. Start you Mp3 Player. Close your eyes. Go somewhere else.

Félicitations

Last night I had a dream: a big city, an airport, a city highway with a large curve leading to a mountain. There was other city or town. Very cute. I lived there :-)

The interesting thing is that exactly the same city and mountain I dreamed about half an year ago. The dreams have their own memories. :-)

Félicitations

Time: July 2000
Place: MASKATA (Sofia, Bulgaria)
Event: First or second Subtierra’s live
Starring: Julian Daskalov (guitars), Nino Gomez (vocals), zlika (bass), Rossen Dimitrov (drums)

Subtierra

I played in a cool band in 2000 – 2001. This is a pic, shot by a fan. I am the guy with the coolest trousers and the red bass :-D

The music is all we got,
The Music is all we need… :-)

… is my e-mail signature, taken by a Subtierra’s song

Today, February 11, is Sabina’s birthday. OK, it’s ‘yesterday’ in Montreal, but ‘today’ in Sofia. That’s a good reason to say

Happy birthday, Sabina. I wish you a good health and tons of smiles! The above  song is for you!

Félicitations

I was interested of the different SQL “accents”. According to Microsoft SQL Server learning books, we query like this:

SELECT
 *

FROM
 table1 INNER JOIN table2
  ON table1.PK = table2.FK
GO

The MySQL guys makes queries like this:

SELECT *
FROM table1, table2
WHERE table1.PK = table2.FK;

Using Microsoft SQL Server 2008, I wrote a script to create and populate two tables (the model data is found somewhere in internet).

IF EXISTS (
SELECT 1
 FROM sys.objects
 WHERE OBJECT_ID = OBJECT_ID(N'City')
 AND type = (N'U')
)
DROP TABLE City
GO

IF EXISTS (
 SELECT 1
 FROM sys.objects
 WHERE OBJECT_ID = OBJECT_ID(N'Country')
 AND type = (N'U')
)
DROP TABLE Country
GO

CREATE TABLE Country (
 CountryID INT IDENTITY(1, 1) NOT NULL PRIMARY KEY CLUSTERED
 , CountryName VARCHAR(50) NOT NULL
)
GO

CREATE TABLE City (
 CityID INT IDENTITY(1, 1) NOT NULL PRIMARY KEY CLUSTERED
 , CityName VARCHAR(50) NOT NULL
 , CountryID INT NOT NULL REFERENCES Country(CountryID)
)
GO

INSERT INTO Country (CountryName) VALUES
('Afghanistan')
, ('Algeria')
, ('American Samoa')
, ('Angola')
, ('Anguilla')
, ('Argentina')
, ('Armenia')
, ('Australia')
, ('Austria')
, ('Azerbaijan')
, ('Bahrain')
, ('Bangladesh')
, ('Belarus')
, ('Bolivia')
, ('Brazil')
, ('Brunei')
, ('Bulgaria')
, ('Cambodia')
, ('Cameroon')
, ('Canada')
, ('Chad')
, ('Chile')
, ('China')
, ('Colombia')
, ('Congo, The Democratic Republic of the')
, ('Czech Republic')
, ('Dominican Republic')
, ('Ecuador')
, ('Egypt')
, ('Estonia')
, ('Ethiopia')
, ('Faroe Islands')
, ('Finland')
, ('France')
, ('French Guiana')
, ('French Polynesia')
, ('Gambia')
, ('Germany')
, ('Greece')
, ('Greenland')
, ('Holy See (Vatican City State)')
, ('Hong Kong')
, ('Hungary')
, ('India')
, ('Indonesia')
, ('Iran')
, ('Iraq')
, ('Israel')
, ('Italy')
, ('Japan')
, ('Kazakstan')
, ('Kenya')
, ('Kuwait')
, ('Latvia')
, ('Liechtenstein')
, ('Lithuania')
, ('Madagascar')
, ('Malawi')
, ('Malaysia')
, ('Mexico')
, ('Moldova')
, ('Morocco')
, ('Mozambique')
, ('Myanmar')
, ('Nauru')
, ('Nepal')
, ('Netherlands')
, ('New Zealand')
, ('Nigeria')
, ('North Korea')
, ('Oman')
, ('Pakistan')
, ('Paraguay')
, ('Peru')
, ('Philippines')
, ('Poland')
, ('Puerto Rico')
, ('Romania')
, ('Runion')
, ('Russian Federation')
, ('Saint Vincent and the Grenadines')
, ('Saudi Arabia')
, ('Senegal')
, ('Slovakia')
, ('South Africa')
, ('South Korea')
, ('Spain')
, ('Sri Lanka')
, ('Sudan')
, ('Sweden')
, ('Switzerland')
, ('Taiwan')
, ('Tanzania')
, ('Thailand')
, ('Tonga')
, ('Tunisia')
, ('Turkey')
, ('Turkmenistan')
, ('Tuvalu')
, ('Ukraine')
, ('United Arab Emirates')
, ('United Kingdom')
, ('United States')
, ('Venezuela')
, ('Vietnam')
, ('Virgin Islands, U.S.')
, ('Yemen')
, ('Yugoslavia')
, ('Zambia')
GO

INSERT INTO City (CityName, CountryID) VALUES
('A Corua (La Corua)', 87)
, ('Abha', 82)
, ('Abu Dhabi', 101)
, ('Acua', 60)
, ('Adana', 97)
, ('Addis Abeba', 31)
, ('Aden', 107)
, ('Adoni', 44)
, ('Ahmadnagar', 44)
, ('Akishima', 50)
, ('Akron', 103)
, ('al-Ayn', 101)
, ('al-Hawiya', 82)
, ('al-Manama', 11)
, ('al-Qadarif', 89)
, ('al-Qatif', 82)
, ('Alessandria', 49)
, ('Allappuzha (Alleppey)', 44)
, ('Allende', 60)
, ('Almirante Brown', 6)
, ('Alvorada', 15)
, ('Ambattur', 44)
, ('Amersfoort', 67)
, ('Amroha', 44)
, ('Angra dos Reis', 15)
, ('Anpolis', 15)
, ('Antofagasta', 22)
, ('Aparecida de Goinia', 15)
, ('Apeldoorn', 67)
, ('Araatuba', 15)
, ('Arak', 46)
, ('Arecibo', 77)
, ('Arlington', 103)
, ('Ashdod', 48)
, ('Ashgabat', 98)
, ('Ashqelon', 48)
, ('Asuncin', 73)
, ('Athenai', 39)
, ('Atinsk', 80)
, ('Atlixco', 60)
, ('Augusta-Richmond County', 103)
, ('Aurora', 103)
, ('Avellaneda', 6)
, ('Bag', 15)
, ('Baha Blanca', 6)
, ('Baicheng', 23)
, ('Baiyin', 23)
, ('Baku', 10)
, ('Balaiha', 80)
, ('Balikesir', 97)
, ('Balurghat', 44)
, ('Bamenda', 19)
, ('Bandar Seri Begawan', 16)
, ('Banjul', 37)
, ('Barcelona', 104)
, ('Basel', 91)
, ('Bat Yam', 48)
, ('Batman', 97)
, ('Batna', 2)
, ('Battambang', 18)
, ('Baybay', 75)
, ('Bayugan', 75)
, ('Bchar', 2)
, ('Beira', 63)
, ('Bellevue', 103)
, ('Belm', 15)
, ('Benguela', 4)
, ('Beni-Mellal', 62)
, ('Benin City', 69)
, ('Bergamo', 49)
, ('Berhampore (Baharampur)', 44)
, ('Bern', 91)
, ('Bhavnagar', 44)
, ('Bhilwara', 44)
, ('Bhimavaram', 44)
, ('Bhopal', 44)
, ('Bhusawal', 44)
, ('Bijapur', 44)
, ('Bilbays', 29)
, ('Binzhou', 23)
, ('Birgunj', 66)
, ('Bislig', 75)
, ('Blumenau', 15)
, ('Boa Vista', 15)
, ('Boksburg', 85)
, ('Botosani', 78)
, ('Botshabelo', 85)
, ('Bradford', 102)
, ('Braslia', 15)
, ('Bratislava', 84)
, ('Brescia', 49)
, ('Brest', 34)
, ('Brindisi', 49)
, ('Brockton', 103)
, ('Bucuresti', 78)
, ('Buenaventura', 24)
, ('Bydgoszcz', 76)
, ('Cabuyao', 75)
, ('Callao', 74)
, ('Cam Ranh', 105)
, ('Cape Coral', 103)
, ('Caracas', 104)
, ('Carmen', 60)
, ('Cavite', 75)
, ('Cayenne', 35)
, ('Celaya', 60)
, ('Chandrapur', 44)
, ('Changhwa', 92)
, ('Changzhou', 23)
, ('Chapra', 44)
, ('Charlotte Amalie', 106)
, ('Chatsworth', 85)
, ('Cheju', 86)
, ('Chiayi', 92)
, ('Chisinau', 61)
, ('Chungho', 92)
, ('Cianjur', 45)
, ('Ciomas', 45)
, ('Ciparay', 45)
, ('Citrus Heights', 103)
, ('Citt del Vaticano', 41)
, ('Ciudad del Este', 73)
, ('Clarksville', 103)
, ('Coacalco de Berriozbal', 60)
, ('Coatzacoalcos', 60)
, ('Compton', 103)
, ('Coquimbo', 22)
, ('Crdoba', 6)
, ('Cuauhtmoc', 60)
, ('Cuautla', 60)
, ('Cuernavaca', 60)
, ('Cuman', 104)
, ('Czestochowa', 76)
, ('Dadu', 72)
, ('Dallas', 103)
, ('Datong', 23)
, ('Daugavpils', 54)
, ('Davao', 75)
, ('Daxian', 23)
, ('Dayton', 103)
, ('Deba Habe', 69)
, ('Denizli', 97)
, ('Dhaka', 12)
, ('Dhule (Dhulia)', 44)
, ('Dongying', 23)
, ('Donostia-San Sebastin', 87)
, ('Dos Quebradas', 24)
, ('Duisburg', 38)
, ('Dundee', 102)
, ('Dzerzinsk', 80)
, ('Ede', 67)
, ('Effon-Alaiye', 69)
, ('El Alto', 14)
, ('El Fuerte', 60)
, ('El Monte', 103)
, ('Elista', 80)
, ('Emeishan', 23)
, ('Emmen', 67)
, ('Enshi', 23)
, ('Erlangen', 38)
, ('Escobar', 6)
, ('Esfahan', 46)
, ('Eskisehir', 97)
, ('Etawah', 44)
, ('Ezeiza', 6)
, ('Ezhou', 23)
, ('Faaa', 36)
, ('Fengshan', 92)
, ('Firozabad', 44)
, ('Florencia', 24)
, ('Fontana', 103)
, ('Fukuyama', 50)
, ('Funafuti', 99)
, ('Fuyu', 23)
, ('Fuzhou', 23)
, ('Gandhinagar', 44)
, ('Garden Grove', 103)
, ('Garland', 103)
, ('Gatineau', 20)
, ('Gaziantep', 97)
, ('Gijn', 87)
, ('Gingoog', 75)
, ('Goinia', 15)
, ('Gorontalo', 45)
, ('Grand Prairie', 103)
, ('Graz', 9)
, ('Greensboro', 103)
, ('Guadalajara', 60)
, ('Guaruj', 15)
, ('guas Lindas de Gois', 15)
, ('Gulbarga', 44)
, ('Hagonoy', 75)
, ('Haining', 23)
, ('Haiphong', 105)
, ('Haldia', 44)
, ('Halifax', 20)
, ('Halisahar', 44)
, ('Halle/Saale', 38)
, ('Hami', 23)
, ('Hamilton', 68)
, ('Hanoi', 105)
, ('Hidalgo', 60)
, ('Higashiosaka', 50)
, ('Hino', 50)
, ('Hiroshima', 50)
, ('Hodeida', 107)
, ('Hohhot', 23)
, ('Hoshiarpur', 44)
, ('Hsichuh', 92)
, ('Huaian', 23)
, ('Hubli-Dharwad', 44)
, ('Huejutla de Reyes', 60)
, ('Huixquilucan', 60)
, ('Hunuco', 74)
, ('Ibirit', 15)
, ('Idfu', 29)
, ('Ife', 69)
, ('Ikerre', 69)
, ('Iligan', 75)
, ('Ilorin', 69)
, ('Imus', 75)
, ('Inegl', 97)
, ('Ipoh', 59)
, ('Isesaki', 50)
, ('Ivanovo', 80)
, ('Iwaki', 50)
, ('Iwakuni', 50)
, ('Iwatsuki', 50)
, ('Izumisano', 50)
, ('Jaffna', 88)
, ('Jaipur', 44)
, ('Jakarta', 45)
, ('Jalib al-Shuyukh', 53)
, ('Jamalpur', 12)
, ('Jaroslavl', 80)
, ('Jastrzebie-Zdrj', 76)
, ('Jedda', 82)
, ('Jelets', 80)
, ('Jhansi', 44)
, ('Jinchang', 23)
, ('Jining', 23)
, ('Jinzhou', 23)
, ('Jodhpur', 44)
, ('Johannesburg', 85)
, ('Joliet', 103)
, ('Jos Azueta', 60)
, ('Juazeiro do Norte', 15)
, ('Juiz de Fora', 15)
, ('Junan', 23)
, ('Jurez', 60)
, ('Kabul', 1)
, ('Kaduna', 69)
, ('Kakamigahara', 50)
, ('Kaliningrad', 80)
, ('Kalisz', 76)
, ('Kamakura', 50)
, ('Kamarhati', 44)
, ('Kamjanets-Podilskyi', 100)
, ('Kamyin', 80)
, ('Kanazawa', 50)
, ('Kanchrapara', 44)
, ('Kansas City', 103)
, ('Karnal', 44)
, ('Katihar', 44)
, ('Kermanshah', 46)
, ('Kilis', 97)
, ('Kimberley', 85)
, ('Kimchon', 86)
, ('Kingstown', 81)
, ('Kirovo-Tepetsk', 80)
, ('Kisumu', 52)
, ('Kitwe', 109)
, ('Klerksdorp', 85)
, ('Kolpino', 80)
, ('Konotop', 100)
, ('Koriyama', 50)
, ('Korla', 23)
, ('Korolev', 80)
, ('Kowloon and New Kowloon', 42)
, ('Kragujevac', 108)
, ('Ktahya', 97)
, ('Kuching', 59)
, ('Kumbakonam', 44)
, ('Kurashiki', 50)
, ('Kurgan', 80)
, ('Kursk', 80)
, ('Kuwana', 50)
, ('La Paz', 60)
, ('La Plata', 6)
, ('La Romana', 27)
, ('Laiwu', 23)
, ('Lancaster', 103)
, ('Laohekou', 23)
, ('Lapu-Lapu', 75)
, ('Laredo', 103)
, ('Lausanne', 91)
, ('Le Mans', 34)
, ('Lengshuijiang', 23)
, ('Leshan', 23)
, ('Lethbridge', 20)
, ('Lhokseumawe', 45)
, ('Liaocheng', 23)
, ('Liepaja', 54)
, ('Lilongwe', 58)
, ('Lima', 74)
, ('Lincoln', 103)
, ('Linz', 9)
, ('Lipetsk', 80)
, ('Livorno', 49)
, ('Ljubertsy', 80)
, ('Loja', 28)
, ('London', 102)
, ('London', 20)
, ('Lublin', 76)
, ('Lubumbashi', 25)
, ('Lungtan', 92)
, ('Luzinia', 15)
, ('Madiun', 45)
, ('Mahajanga', 57)
, ('Maikop', 80)
, ('Malm', 90)
, ('Manchester', 103)
, ('Mandaluyong', 75)
, ('Mandi Bahauddin', 72)
, ('Mannheim', 38)
, ('Maracabo', 104)
, ('Mardan', 72)
, ('Maring', 15)
, ('Masqat', 71)
, ('Matamoros', 60)
, ('Matsue', 50)
, ('Meixian', 23)
, ('Memphis', 103)
, ('Merlo', 6)
, ('Mexicali', 60)
, ('Miraj', 44)
, ('Mit Ghamr', 29)
, ('Miyakonojo', 50)
, ('Mogiljov', 13)
, ('Molodetno', 13)
, ('Monclova', 60)
, ('Monywa', 64)
, ('Moscow', 80)
, ('Mosul', 47)
, ('Mukateve', 100)
, ('Munger (Monghyr)', 44)
, ('Mwanza', 93)
, ('Mwene-Ditu', 25)
, ('Myingyan', 64)
, ('Mysore', 44)
, ('Naala-Porto', 63)
, ('Nabereznyje Telny', 80)
, ('Nador', 62)
, ('Nagaon', 44)
, ('Nagareyama', 50)
, ('Najafabad', 46)
, ('Naju', 86)
, ('Nakhon Sawan', 94)
, ('Nam Dinh', 105)
, ('Namibe', 4)
, ('Nantou', 92)
, ('Nanyang', 23)
, ('NDjamna', 21)
, ('Newcastle', 85)
, ('Nezahualcyotl', 60)
, ('Nha Trang', 105)
, ('Niznekamsk', 80)
, ('Novi Sad', 108)
, ('Novoterkassk', 80)
, ('Nukualofa', 95)
, ('Nuuk', 40)
, ('Nyeri', 52)
, ('Ocumare del Tuy', 104)
, ('Ogbomosho', 69)
, ('Okara', 72)
, ('Okayama', 50)
, ('Okinawa', 50)
, ('Olomouc', 26)
, ('Omdurman', 89)
, ('Omiya', 50)
, ('Ondo', 69)
, ('Onomichi', 50)
, ('Oshawa', 20)
, ('Osmaniye', 97)
, ('ostka', 100)
, ('Otsu', 50)
, ('Oulu', 33)
, ('Ourense (Orense)', 87)
, ('Owo', 69)
, ('Oyo', 69)
, ('Ozamis', 75)
, ('Paarl', 85)
, ('Pachuca de Soto', 60)
, ('Pak Kret', 94)
, ('Palghat (Palakkad)', 44)
, ('Pangkal Pinang', 45)
, ('Papeete', 36)
, ('Parbhani', 44)
, ('Pathankot', 44)
, ('Patiala', 44)
, ('Patras', 39)
, ('Pavlodar', 51)
, ('Pemalang', 45)
, ('Peoria', 103)
, ('Pereira', 24)
, ('Phnom Penh', 18)
, ('Pingxiang', 23)
, ('Pjatigorsk', 80)
, ('Plock', 76)
, ('Po', 15)
, ('Ponce', 77)
, ('Pontianak', 45)
, ('Poos de Caldas', 15)
, ('Portoviejo', 28)
, ('Probolinggo', 45)
, ('Pudukkottai', 44)
, ('Pune', 44)
, ('Purnea (Purnia)', 44)
, ('Purwakarta', 45)
, ('Pyongyang', 70)
, ('Qalyub', 29)
, ('Qinhuangdao', 23)
, ('Qomsheh', 46)
, ('Quilmes', 6)
, ('Rae Bareli', 44)
, ('Rajkot', 44)
, ('Rampur', 44)
, ('Rancagua', 22)
, ('Ranchi', 44)
, ('Richmond Hill', 20)
, ('Rio Claro', 15)
, ('Rizhao', 23)
, ('Roanoke', 103)
, ('Robamba', 28)
, ('Rockford', 103)
, ('Ruse', 17)
, ('Rustenburg', 85)
, ('s-Hertogenbosch', 67)
, ('Saarbrcken', 38)
, ('Sagamihara', 50)
, ('Saint Louis', 103)
, ('Saint-Denis', 79)
, ('Sal', 62)
, ('Salala', 71)
, ('Salamanca', 60)
, ('Salinas', 103)
, ('Salzburg', 9)
, ('Sambhal', 44)
, ('San Bernardino', 103)
, ('San Felipe de Puerto Plata', 27)
, ('San Felipe del Progreso', 60)
, ('San Juan Bautista Tuxtepec', 60)
, ('San Lorenzo', 73)
, ('San Miguel de Tucumn', 6)
, ('Sanaa', 107)
, ('Santa Brbara dOeste', 15)
, ('Santa F', 6)
, ('Santa Rosa', 75)
, ('Santiago de Compostela', 87)
, ('Santiago de los Caballeros', 27)
, ('Santo Andr', 15)
, ('Sanya', 23)
, ('Sasebo', 50)
, ('Satna', 44)
, ('Sawhaj', 29)
, ('Serpuhov', 80)
, ('Shahr-e Kord', 46)
, ('Shanwei', 23)
, ('Shaoguan', 23)
, ('Sharja', 101)
, ('Shenzhen', 23)
, ('Shikarpur', 72)
, ('Shimoga', 44)
, ('Shimonoseki', 50)
, ('Shivapuri', 44)
, ('Shubra al-Khayma', 29)
, ('Siegen', 38)
, ('Siliguri (Shiliguri)', 44)
, ('Simferopol', 100)
, ('Sincelejo', 24)
, ('Sirjan', 46)
, ('Sivas', 97)
, ('Skikda', 2)
, ('Smolensk', 80)
, ('So Bernardo do Campo', 15)
, ('So Leopoldo', 15)
, ('Sogamoso', 24)
, ('Sokoto', 69)
, ('Songkhla', 94)
, ('Sorocaba', 15)
, ('Soshanguve', 85)
, ('Sousse', 96)
, ('South Hill', 5)
, ('Southampton', 102)
, ('Southend-on-Sea', 102)
, ('Southport', 102)
, ('Springs', 85)
, ('Stara Zagora', 17)
, ('Sterling Heights', 103)
, ('Stockport', 102)
, ('Sucre', 14)
, ('Suihua', 23)
, ('Sullana', 74)
, ('Sultanbeyli', 97)
, ('Sumqayit', 10)
, ('Sumy', 100)
, ('Sungai Petani', 59)
, ('Sunnyvale', 103)
, ('Surakarta', 45)
, ('Syktyvkar', 80)
, ('Syrakusa', 49)
, ('Szkesfehrvr', 43)
, ('Tabora', 93)
, ('Tabriz', 46)
, ('Tabuk', 82)
, ('Tafuna', 3)
, ('Taguig', 75)
, ('Taizz', 107)
, ('Talavera', 75)
, ('Tallahassee', 103)
, ('Tama', 50)
, ('Tambaram', 44)
, ('Tanauan', 75)
, ('Tandil', 6)
, ('Tangail', 12)
, ('Tanshui', 92)
, ('Tanza', 75)
, ('Tarlac', 75)
, ('Tarsus', 97)
, ('Tartu', 30)
, ('Teboksary', 80)
, ('Tegal', 45)
, ('Tel Aviv-Jaffa', 48)
, ('Tete', 63)
, ('Tianjin', 23)
, ('Tiefa', 23)
, ('Tieli', 23)
, ('Tokat', 97)
, ('Tonghae', 86)
, ('Tongliao', 23)
, ('Torren', 60)
, ('Touliu', 92)
, ('Toulon', 34)
, ('Toulouse', 34)
, ('Trshavn', 32)
, ('Tsaotun', 92)
, ('Tsuyama', 50)
, ('Tuguegarao', 75)
, ('Tychy', 76)
, ('Udaipur', 44)
, ('Udine', 49)
, ('Ueda', 50)
, ('Uijongbu', 86)
, ('Uluberia', 44)
, ('Urawa', 50)
, ('Uruapan', 60)
, ('Usak', 97)
, ('Usolje-Sibirskoje', 80)
, ('Uttarpara-Kotrung', 44)
, ('Vaduz', 55)
, ('Valencia', 104)
, ('Valle de la Pascua', 104)
, ('Valle de Santiago', 60)
, ('Valparai', 44)
, ('Vancouver', 20)
, ('Varanasi (Benares)', 44)
, ('Vicente Lpez', 6)
, ('Vijayawada', 44)
, ('Vila Velha', 15)
, ('Vilnius', 56)
, ('Vinh', 105)
, ('Vitria de Santo Anto', 15)
, ('Warren', 103)
, ('Weifang', 23)
, ('Witten', 38)
, ('Woodridge', 8)
, ('Wroclaw', 76)
, ('Xiangfan', 23)
, ('Xiangtan', 23)
, ('Xintai', 23)
, ('Xinxiang', 23)
, ('Yamuna Nagar', 44)
, ('Yangor', 65)
, ('Yantai', 23)
, ('Yaound', 19)
, ('Yerevan', 7)
, ('Yinchuan', 23)
, ('Yingkou', 23)
, ('York', 102)
, ('Yuncheng', 23)
, ('Yuzhou', 23)
, ('Zalantun', 23)
, ('Zanzibar', 93)
, ('Zaoyang', 23)
, ('Zapopan', 60)
, ('Zaria', 69)
, ('Zeleznogorsk', 80)
, ('Zhezqazghan', 51)
, ('Zhoushan', 23)
, ('Ziguinchor', 83)
GO

Then I wrote two queries to examine the data in both ways.

-- FROM
SELECT
	*

FROM
	City AS ci INNER JOIN Country AS co
		ON ci.CountryID = co.CountryID

WHERE
	co.CountryID IN (44, 23, 103, 50, 60)
GO

-- WHERE
SELECT *
FROM City ci, Country co
WHERE ci.CountryID = co.CountryID
  AND co.CountryID IN (44, 23, 103, 50, 60)
GO

I asked an IT guy today “You write your queries in Oracle by using which manner?” and he answered me that he joins the tables in the WHERE clause. The execution plan told me there’s no difference in the execution.

As a Microsoft fan, I prefer to link the tables using the FROM clause, because I may have no need of WHERE clause :-)

Félicitations

I use Windows XP and I’m not sure when I’m gonna start using Windows 7. That’ s why I have installed it on a Virtual PC for quick preview. Today I found a post and I Googled it as “Windows 7 God Mode” to check what’s going on :-)

It looks like very smart hidden tool for personalization.

Félicitations

Several days ago I discussed with a friend of mine about our first impression on Canada. The first thing we mentioned that’s Pierre Elliott Trudeau International Airport in Montreal. Actually the first thing was the interior of the airport. My friend said “It was quite empty. It looked like that airport of the movie… Mmmm… The movie based on Stephen King’s novel…”. I immediately remembered the movie and on next day I found its title – The Langoliers. Cool movie! :-)

Probably there weren’t a lot of flights at that time :-/

I answered that my first impression (the same airport) on Canada and especially Quebec is the convenience. The airport was very cosy. The next day I marked that the public buildings also are cosy. The streets, the private homes… Everywhere you go in Quebec, it’s very cosy :-)

Happy New Year, guys! :-)

Félicitations