Trunc function in SQL

In sql when when we use the BETWEEN function to bring out data for a period of date range, some of the records might not come up in the result

for e.g.

SELECT *
FROM orders
WHERE order_date between to_date ('2003/01/01', 'yyyy/mm/dd')
AND to_date ('2003/12/31', 'yyyy/mm/dd');

If the order_date column in database has a time componenet in it, then the results might not come up for the dates 2003/01/01 and 2003/12/31

To solve this problem, we remove the time componenet from the query using the TRUNC function

SELECT *
FROM orders
WHERE trunc(order_date) between to_date ('2003/01/01', 'yyyy/mm/dd')
AND to_date ('2003/12/31', 'yyyy/mm/dd');

DECODE function in SQL

In sql, we can put validation logic by using the DECODE statement

For e.g. if we have a scenario such that all First Names are to be printed out for people who are aged 60, this can be written using decode statement such as

decode(age, 60, first_name, null),


If we add an extra condition to it such that print out the First Names only if they are a Male, then we write as below

decode(age, 60, decode(gender, male, first_name, null), null)

Subquery with NVL

Example 1
select
"EMPLOYEENUMBER",
"LASTNAME",
"FIRSTNAME",
NVL((select extension from classiccars.employee where lastname = 'Murphy'), 'Unavailable'),
"EMAIL",
"OFFICECODE",
"REPORTSTO",
"JOBTITLE"
from
"CLASSICCARS"."EMPLOYEE"

Example 2

SELECT
Parcel.Alt_Key,
substr(trim(Parcel.Full_Parcel_ID),1,8) SubDiv_Number,
NVL(LS.Sub_Name,
(SELECT NVL(V2.Sub_name,'Unknown Subdivision')
FROM SUBDIV V2
WHERE substr(trim(Parcel.Full_Parcel_ID),1,8) =
substr(trim(V2.Sub_Number),1,8)
AND V2.rowid = (select min(sub2.rowid)
from SUBDIV sub2
where (trim(substr(V2.sub_number,1,8))) =
(trim(substr(sub2.sub_number,1,8)))))) SubDiv_Name,
Parcel.Full_Parcel_ID
FROM PARCEL INNER JOIN SALES
ON Parcel.Alt_Key = Sales.Alt_Key
LEFT JOIN
SUBDIV LS
ON substr(trim(Parcel.Full_Parcel_ID),1,10) =
substr(trim(LS.Sub_Number),1,10)

Pumpkin Soup

Ingredients
1/2 pumpkin
1 carrot
1/2 onion
1/2 tomato
Salt to taste
Pepper to taste

Method

Pressure cook or boil all the ingredients together with water
Mash them all together in a blender and serve it warm

Subselects in database queries

While inserting records into a table A, if a need arises to fill a column with value from a table B, then use the following sql

insert into "CLASSICCARS"."EMPLOYEE"
(
"EMPLOYEENUMBER",
"OFFICECODE"

)
values
(
1703,
(select max(officecode) from classiccars.OFFICE)
)

Helpful Unix commands - Part 2

- When shell script complains of ^M character which is a windows character
sh: /opt/tmp/testPerson.sh: /usr/bin/ksh^M: bad interpreter: No such file or directory

Reason is there may be DOS linefeeds in your file. Convert the file to Unix linefeeds, either by copying the file using ASCII mode FTP, or one of:

tr -d '\r' < script > script.new

perl -pi -e 's/\r$//;' script

dos2unix < script > script.new



-To Change permissions
chmod 775 <>
chmod -R 775 <> [for recursive]


-To Move Files from One server to another
scp username@servername:/opt/tmp/filename filename


To connect to B server from A server
From A server type
ssh username@serverB


-To check if a directory exist

if [ -d mydir ]
then
echo "Directory found"
else
echo "Directory not found"
fi


-To check if a file exists

#(-f === if file exists)
if test -f $TMP;
then
if test ! -w $TMP ;
then
printf the temp file $TMP exists and cannot be overwritten aborting >&2
exit 192
fi
fi


- To copy a file
cp -p <> <>
- To copy a directory
cp -p -R <> <>

- To remove a file
rm <>
- To remove a directory
rm -d -R <,filename>>


- To find out if perl or bash or sh file exists
which perl
which bash
which sh


- To find out the version of linux
cat /etc/redhat-release

- To change group name of a file
chgrp <> <>

Pancakes

Ingredients:
2 cups plain flour
1.5 or 2 cups milk
2 eggs
2 tablespoon sugar
3 teaspoon baking soda
60g melted and cooled butter or ghee
some more butter or ghee for cooking

Serves: about 8 pancakes

Procedure:
Mix the four and sugar together in a vessel
In a bowl, mix the milk, eggs, baking soda and melted butter or ghee
Make a well in the flour. Gradually add the milk mixture to the well in flour and keep mixing to form a smooth batter
Keep aside the batter for about 10 mins

In a pan, add some butter or ghee and spread it nicely
Keep the flame in low heat
Pour the batter to the pan in small circles.
When bubbles start to appear on top, toss the pancake and cook the other side
Pancakes ready!
Serve hot. Can also be served with golden syrup or coconul milk + sugar