Linux Tips - Delete the same file from multiple directories

Every now and then you find yourself in a situation where you want to perform a simple operation, like removing/deleting a particular file from multiple folders, and you don't know how to do it.

A quick example that comes to my mind is the following:
Assume that under a version control software you have a directory structure and you want to copy a specific folder along with its contents.
More often than not, version control software will create hidden (or not) files within your folder and its subfolders to keep track of the history of events like additions of new files and updates or deletions of existing files, etc.

Once you copy the desired folder you most probably want to get rid of the version control specific files.

The following command demonstrates an easy way of doing that under a Linux environment:


find . -type f -name "" -exec rm -f {} \;

This command has been of great help to me and I am sure it will help others as well...

Enjoy...

SQL Server - Simple CTE Example - Running Total

Hi all,

We all know how powerful and useful the SQL language can be. Optimise the code as well, and you get back what you asked for in a fraction of a second (well it’s a matter of speech, my stored procedures currently take a couple of hours to complete!!!)

Anyway, since last week, I have been trying to write some SQL code to perform something rather simple in programming terms, yet so complicated if you think in database terms. What I was trying to do was to calculate the running total of a column.

There are a lot of ways to deal with such requirements including self joins, cursors, etc. However, the aforementioned techniques are very slow and prone to error.

CTE (Common Table Expression) comes to the rescue!!! It was introduced in SQL Server 2005, and it can simplify the way we implement recursive queries to retrieve data presented in a hierarchical format. CTE can be thought of as a temporary result set which remains active for the duration of a single SELECT, INSERT, UPDATE, DELETE, or CREATE VIEW statement.

The basic syntax structure for a CTE is:

WITH expression_name [ ( column_name [,...n] ) ]
AS
( CTE_query_definition )


To give a more specific example (and play a bit with CTE), let’s create the following table:

DECLARE @CAR_IMPROVEMENT TABLE
(
      PART_ID INT NOT NULL,
      PART_DESC VARCHAR(100) NOT NULL,
      PART_VALUE NUMERIC(10,2) NOT NULL,
      RUNNING_TOTAL NUMERIC(15,2) NULL
);

INSERT INTO @CAR_IMPROVEMENT (PART_ID, PART_DESC, PART_VALUE)
      SELECT 1, 'BBS Alloys', 1500
UNION ALL
      SELECT 2, 'Induction Kit', 230
UNION ALL
      SELECT 3, 'ECU Remap', 435
UNION ALL
      SELECT 4, 'Anti-roll Bars', 350
UNION ALL
      SELECT 5, 'CD Player', 168;

SELECT * FROM @CAR_IMPROVEMENT;

Running the above code will create a table variable and populate it with the values we provided. The end result can be seen below:

Now that we have created are table, we are ready to move to the interesting bit of calculating the running total of our performance parts. The following code does that with the use of the new WITH clause:

WITH CAR_IMPRO_RUNNING_TOTAL
(
      PART_ID,
      PART_DESC,
      PART_VALUE,
      RUNNING_TOTAL
)
AS
(
      SELECT
             A.PART_ID,
            A.PART_DESC,
            A.PART_VALUE,
            (SELECT
                  SUM(B.PART_VALUE)
            FROM @CAR_IMPROVEMENT AS B
            WHERE B.PART_ID <= A.PART_ID)
      FROM @CAR_IMPROVEMENT AS A
)

SELECT * FROM CAR_IMPRO_RUNNING_TOTAL;

The above code is able to calculate the running total line by line, by referencing the original table we created and applying the condition that the sum must be calculated up to an including the current record. The resultant table is the following:

We can see that in the first row the running total is just the first part’s value. In the second row, the first and second part’s values have been summed up to give a total of £1,730.00 (1,500.00 + 230.00). And the summation is continued until the end of the table.

On the next thread we will look at more complex examples and how to retrieve the running total in each case.

Till next time…

SQL Server - List only NULL columns in a table - Ver 2

Following my last post on how to list all NULL columns within a table in SQL Server, below you can find a second version of the script where you can list the NULL columns of a table in an easier-to-copy format.

Here it goes:


DECLARE @NAME sysname
DECLARE CRS CURSOR

LOCAL FAST_FORWARD FOR
SELECT name FROM syscolumns
WHERE id=OBJECT_ID('<TABLE_NAME>')

OPEN CRS
FETCH NEXT FROM CRS INTO @NAME

WHILE @@FETCH_STATUS = 0
BEGIN

      EXEC('IF EXISTS( SELECT ''' + @NAME + '''
            WHERE NOT EXISTS
                  (SELECT * FROM <TABLE_NAME>
                  WHERE ' + @NAME + ' IS NOT NULL))
            BEGIN
                  PRINT ''' + @NAME + '''
            END'
      )

      FETCH NEXT FROM CRS INTO @NAME

END

CLOSE CRS
DEALLOCATE CRS

Again, I hope you found this useful.

See ya laterz....

Happy New Year everyone

Hi all,

I just wanted to wish everyone Happy New Year  and I hope this year is a lot better from the previous one.

It's been a bit more than a month since my last post, but I have been extremely busy with work, a bit of studying and having a great time with my girlfriend and her parents :)

Anyways, all the best for 2011!!!

SQL Server - List only NULL columns in a table

Sometimes when you have a database with massive tables (in the order of millions of rows) getting fed by multiple sources, it is very difficult to find out which columns are completely empty (or contain the default NULL value).

I am pretty sure that I wasn't the only one who came against this issue, so in order to save others some time, you can find below a snippet of code that I wrote where it prints out all the columns of your table that contain only NULL values for all rows.

DECLARE @NAME sysname
DECLARE CRS CURSOR

LOCAL FAST_FORWARD FOR
SELECT name FROM syscolumns
WHERE id=OBJECT_ID('<TABLE_NAME>')

OPEN CRS
FETCH NEXT FROM CRS INTO @NAME

WHILE @@FETCH_STATUS = 0
BEGIN

      EXEC('SELECT ''' + @NAME + '''
            WHERE NOT EXISTS
                  (SELECT * FROM <TABLE_NAME>
                  WHERE ' + @NAME + ' IS NOT NULL)'
      )

      FETCH NEXT FROM CRS INTO @NAME

END

CLOSE CRS
DEALLOCATE CRS

Hope you found this helpful and that it will make your life a bit easier.

Till next time...

SQL Server - Get longest value in a column

The other day at work I was trying to find out what are the distinct values in one of the columns in a table within my database.

As you most probably know this can be done by using the DISTINCT keyword in SQL. So a quick example would be the following:

SELECT DISTINCT <COLUMN_NAME>
FROM <TABLE_NAME>

But what I wanted to find out was which value was the longest. Using the ORDER BY clause would sort out the resulting set alphabetically and not by length.

So a very nice and quick solution could be created using the DATALENGTH keyword. The following snippet will bring back all distinct values (along with their respective length) and will sort them in descending order by length:

SELECT DISTINCT <COLUMN_NAME>,
      DATALENGTH(<COLUMN_NAME>) AS StringLength
FROM <TABLE_NAME>
GROUP BY <COLUMN_NAME>
ORDER BY StringLength DESC

You can also modify that to bring back only the biggest string in the column by using the following code:

SELECT DISTINCT TOP 1 <COLUMN_NAME>,
      DATALENGTH(<COLUMN_NAME>) AS StringLength
FROM <TABLE_NAME>
GROUP BY <COLUMN_NAME>
ORDER BY StringLength DESC

So that was it... nice and easy and you can get the longest string in a column and the number of characters.

Hope that was useful!!!

Introduction to Financial Derivatives - Option Contracts

To quickly reiterate, there are the following four basic derivatives:

- Forward contracts
- Futures contracts
- Swap contracts
- Option contracts

In this post we will look at option contracts.

Option Contracts
An option contract grants its holder the right, but not the obligation, to buy or sell something at a specified price, on or before a specified date.

A simple example:
You are interested in a company's stock (let's say ABC company). You believe that the stock is undervalued and will increase over the next several months. You decide to enter into an option contract which grants you the right (but remember... not the obligation!!!) to buy the stock for the current price in 6 months time. In six months time, if the price of the stock has increased, you exercise the option and you buy the stock at the price you "locked" six months ago. The gain is the difference between the current price and the price you paid. If the price has decreased, then you let the option to expire without exercising it since you can buy the stock at less than the "locked" price.

* One of the main differences between the option contract and the rest of the derivatives is the premium price you pay in order to enter the option contract. For the rest of the contract no premium is involved.


This post completes the introduction to the four basic derivatives!!!
I hope you enjoyed it...