DB: Insert with a select

Inspite of so many years into db i still found i didnt knew this one :)

Aim: To write an insert after selecting from some other table.

SQL:
INSERT INTO A (column1, col2, col3)
SELECT otherCol1, otherCol2, otherCol3
From table
Where condition here

To Note: no use of the normal 'VALUES' kyword

The arrow anti pattern

Check it here

Finding the primary keys for a table

SELECT cols.table_name, cols.column_name, cols.position, cons.status, cons.owner
FROM all_constraints cons, all_cons_columns cols
WHERE cons.constraint_type = 'P'
AND cons.constraint_name = cols.constraint_name
AND cons.owner = cols.owner
and upper(cons.constraint_name) = 'SYS_C009448' --name of the constarint that got violated

ORDER BY cols.table_name, cols.position;

Query - Combination of columns

select * FROM tmpTable sc WHERE sc.file_id = 681 and (sc.colA,sc.colB) in (('ABC',55), ('FED',54));

Javascript Prototype object

basics of the javascript prototype object here
p.s. Still unable to directly refresh my blog thru google docs...sigh!!

MySQL tip - logging results in a file

While using a mySQL command line client has it ever happened that you fire a query and the result runs into pages scrolling your entire command line. Obviously you need some better way to view the results. probably redirecting result to a file would help.

Naah! traditional redirection operator does not work. Instead use this mysql> tee c:/result.txt

Thats it......
After this command everything you do on your client window will be logged into the file c:/result.txt

Its for your command line session obviously. For the next session, repeat the activity.

Disclaimer: Anything can be found on google. I just jot it down here again because this acts my repository. And yeah! I have a better memory if I write down things ;)

Oracle and null values

I have a table say Employee with the following structure
Employee ( id number, name varchar2(50) )

If i try inserting (1,'')into this above table guess what will be the name of the employee with id 1? Yeah its null. I thought that was weird, since i meant a 0-length string and not null. Alright, so i surrendered to the fact that Oracle would store a null every time I added a zero length string.

But that's not done. Now assume, my table has the following values
Id - Name
1 - null
2 - Anita
3 - null
4 - Beena
5 - Neha

What would this query return -
Select * from Employee where name not in('Anita', 'Neha');

If you thought it would return me 3 rows, you are wrong (as was I). it just returns one record -
4 Beena
Thats because when you fire a not in clause it will not consider null. So if you thought that null != Anita/Neha you are partially right but not completely

You have to explicitly change the above query as -

Select * from Employee where name not in('Anita', 'Neha') or name is null;

Morally of the story: Deal with null value columns carefully. A small mistake will be really difficult to debug

Modifying the right click in Win XP

Folks, this is no big deal and I am sure most of you already know about it but I always seem to forget the registry entry needed and waste time in googling it. Hence jotting it down at this place.

1) To add the command prompt entry to your right click Open Explorer -> Tools(menu) -> Option -> FileTypes (tab) -> Folder Click on the Advanced button. In the dialog box , click on the New Button. In the new dialog box that pops up
Action: Command Prompt (any title)
Application used to perform action : cmd.exe
Click all the Ok buttons and your work is done. Now everytime you right click on any folder, you will see the Command Prompt option which you can use to directly go to the command prompt.

Note: Instead of choosing ‘Folder’(Open Explorer -> Tools(menu) -> Option -> FileTypes (tab)-> Folder) if you choose ‘File Folder’ you are in for some trouble. Everytime now you double click a folder, instead of opening it , it will open the command prompt. Now to solve this problem read point 2) below

2) To change the default action of a right click Many times after you add/remove new actions to a right click it may so happen that because of some mistake your default folder behaviour is changed. In my case every time I double clicked any folder it would open the command prompt (see note in point 1)). This is because some mistake of mine had affected the default behaviour of the folder double click. Set the HKEY_CLASSES_ROOT\Directory\shell to none and this problem should go away.

3) To remove the options added by various software’s when you install them. E.g. Winzip, Winmerge, etc You have to delete them from - HKEY_CLASSES_ROOT\*\shellex\ContextMenuHandlers