SQL Quick Reference

Update using From Clause

Most of the time we need to update table using reference from another table where we need to do join. The SQL query used in Netezza/teradata is slightly different than oracle. The syntax for Netezza is as

update tbl1 a
    SET a.col1=b.col1
FROM (select col1,col2 from tbl2) B
    WHERE a.col2=b.col2

MS-DOS Helpful Commands

Overview: Record count check

We often need to get record count of a file in Windows. We can always open the file in editplus or notepad++ to get the record count in status bar of GUI, but not all the computer has these editors. Also if the file is of higher size, it is difficult to open in editor. We can get the record count using MS-DOS commands.


Print Line Number in the begining of each line
Command: findstr /R /N "^" file1.txt

Get record count of a file using MS-DOS
Command: findstr /R /N "^" file.txt | find /C ":"

Description - This will tell the record count in a file. It basically searches the string with ":". ":" is added as part of first part where line number is appeneded with ":"

Note: Above commands are searched from some other tutorial websites and I just placed here for ease of access in one place.

Get List of all files from a directory and its subdirectory

dir /s /b /o:gn
 

FTPing multiple files

Sometime there is a need to ftp multiple files from Unix server. In such cases mget and mput command can be used. But normally they prompt for a user request to enter key as yes or no. This can be disabled by typing "prompt" before executing ftp command. If you type prompt in Unix prompt it will tell if interactive mode is on or off. If interactive mode is off then it will not prompt for any user confirmation.

Thanks!

Ashish

SQL output using XML functions

I am not sure how XML function works in Oracle but it indeed gives ouptput in a much formatted way which we need most of the times.

Consider a table with following records
col1 col2
1 A
2 B
3 C
1 D
2 S

Execute this query
select col1,
rtrim (xmlagg (xmlelement (e, col2 ',')).extract ('//text()'), ',') col2 from tbl1
group by col1
the output will be
col1 col2
1 A,D
2 B,S
3 C

Most of the times we need similar output as shown above which we will do by writing long PL/SQL codes by there is a much easier way to achieve this by using Oracle XML functions. Explore your ideas in XML / Oracle functions

Using Escape character in Oracle

In Oracle the '_' character is used to match exactly one character, wherease '%' is used to match zero or more occurrences of any characters. For example if we need to search a string in Oracle with '_' prsence in the column, then in such cases normal LIKE operator will not work. We need to escape the character '_'.

Here is the syntax for escaping the character in Oracle

SELECT name FROM emp WHERE id LIKE '%/_%' ESCAPE '/';

Oracle: Difference between Rank and Dense_Rank

RANK leaves gaps while ranking the records whereas DENSE_RANK doesn't leave any gaps. For eg, if we have a tie at the 4th position and there are 2 records with the same value then

RANK - will place both the records in the 4th position, and will have the next record at the 6th position (leaving a gap of 1 position)

DENSE_RANK - will also place both the records in the 4th position, but the next record will be placed at 5th position (no gap will be placed).