Monday, November 21, 2016

Useful Oracle SQL Info

     1.Trim Example

SELECT TRIM (' 7'),
       TRIM (' 777 77  '),
       TRIM (' ' FROM '  7 7 '),
       TRIM (LEADING '0' FROM '000123'),
       TRIM (TRAILING '1' FROM 'Tech1'),
       TRIM (BOTH '1' FROM '123Tech111')

  FROM DUAL







        2. Sql to add seconds, minutes, hours to current date

SELECT SYSDATE,
       SYSDATE + (1 / (24 * 60 * 60)) "1Sec Addition",
       sysdate + interval '1' second,
       SYSDATE + (1 / (24 * 60)) "1Min Addition",
       sysdate + interval '1' minute,
       SYSDATE + (1 / 24) "1HR Addition",
       sysdate + interval '1' hour
  FROM DUAL


1 3.Converting string into rows

with t as (select 'abcd,123,defoifcd,87765' as str from dual)
    select level as n, regexp_substr(str,'[^,]+',1,level) as val
    from   t
   connect by regexp_substr(str,'[^,]+',1,level) is not null



    4.Converting string into columns

with t as (select 'abcd,123,defoifcd,87765' as str from dual)
    select max(decode(level,1,regexp_substr(str,'[^,]+',1,level))) as val1
          ,max(decode(level,2,regexp_substr(str,'[^,]+',1,level))) as val2
          ,max(decode(level,3,regexp_substr(str,'[^,]+',1,level))) as val3
          ,max(decode(level,4,regexp_substr(str,'[^,]+',1,level))) as val4
          ,max(decode(level,5,regexp_substr(str,'[^,]+',1,level))) as val5
   from   t
  connect by regexp_substr(str,'[^,]+',1,level) is not null


    5.Listagg Test

create table dept_test
(
  dno       number,
  dname     varchar2(100 byte),
  location  varchar2(100 byte)
)

set define off;
insert into dept_test   (dno, dname, location) values   (1, 'Accounts', 'NY');
insert into dept_test   (dno, dname, location) values   (2, 'Delivery', 'NJ');
insert into dept_test   (dno, dname, location) values   (3, 'Customer Support', 'NJ');
insert into dept_test   (dno, dname, location) values   (4, 'Billing', 'NY');
insert into dept_test   (dno, dname, location) values   (5, 'Payments', 'NY');
commit;


select * from  dept_test
select location, listagg (dname,',') within group (order by dname) dept_list
from  dept_test
group by location


    6. Notepad++ add quotes at begin & end of each line in a file



7.Convert String with comma separated values into Columns Using SQL
1.       select * from  test
select  c1, trim(regexp_substr(c2,'[^,]+',1,level)) c2
from   test
connect by regexp_substr(c2,'[^,]+',1,level) is not null
and c2 = prior c2
and c1 = prior c1
and prior sys_guid() is not null

order by c1






Useful Unix Commands

1.Unix command to display column -3 info from file

Suppose the sample file (test4.txt) is as follows
col1|gcol2|col3|col4|col5
r1c1|gr1c2|r1c3|r1c4|r1c5
r2c1|gr2c2|r2c3|r2c4|r2c5
r3c1|gr3c2|r3c3|r3c4|r3c5
r4c1|gr4c2|r4c3|r4c4|r4c5

To display column-3 use following command

awk -F '|' '{print $3}' test4.txt

-F -> Field separator
$3 -> column 3

Or use cut command
cut -d'|' -f3 test4.txt
-d -> delimitor
-f3 -> column 3 values


2.Sed command to find and replace a character

Suppose file (test_1.txt) exists with below text and we want to replace pipe | with space

col1|col2|col3|col4|col5
r1c1|r1c2|r1c3|r1c4|r1c5
r2c1|r2c2|r2c3|r2c4|r2c5
r3c1|r3c2|r3c3|r3c4|r3c5
r4c1|r4c2|r4c3|r4c4|r4c5

sed command as follows

sed 's/\|/ /g' test_1.txt > t5.txt

Following is the command if we open the .txt file in vi editor
:%s/\|/ /g


3. Case Example in Unix

while getopts d:s:m: arg
do
case $arg in
dl)
date_list="$OPTARG"
;;
sl)
stage_list="$OPTARG"
;;
rm)
run_mode="$OPTARG"
;;
\?)
echo "Usage : $0 [-dl from_to_dates] [-sl from_to_stage] [-rm run_mode]"
echo "Ex: $0 -dl 01-jan-2001,01-dec-2001 -sl 1,2 -rm NORMAL"
exit 2
;;
esac
done

echo date_list $date_list
echo stage_list $stage_list
echo run_mode $run_mode


4. Unix IF ELSE Test

UNIX script to test the variable string is null or not

#! /usr/bin/ksh
run_mode="RERUN"
if [ "${run_mode}" = "" ]; then
echo With inif 
else
echo "${run_mode}"
fi

5. UNIX command to find files owned by user

find / -user rama 2>/dev/null | ls –ltr

6. Unix command to remove blank lines

sed '/^\s*$/d' 1.txt > 2.txt


7. IF ELSE Logic in shell script … search for a string in a filename

#!/bin/ksh
for filename in *file*; do
   #echo $filename
   fn=$filename
   if [ `ls $fn | grep -E file1` ]
   then
   echo "in if filename1 is -> $fn"
   elif [ `ls $fn | grep -E file2` ]
   then
   echo "in if filename2 is -> $fn"
   elif [ `ls $fn | grep -E file3` ]
   then
   echo "in if filename3 is -> $fn"     
   fi
done

8. Find command in Unix

find / -type f -exec grep -l "a_nwm_utt_data_mth" {} \;  2>/dev/null

find / -name hostway* 2>/dev/null

windows/dos search folder for  a text

findstr /n /i /c:"stg_ool_outage_utt_dly_chc" *

9.AWK in UNIX examples

GET_FROM_DATE="#01-jun-2015 05:50:31#1433152231"
echo $GET_FROM_DATE
#01-jun-2015 05:50:31#1433152231

FROM_DATE=`echo $GET_FROM_DATE | awk -F"#" '{ print $2 }'`
echo $FROM_DATE
01-jun-2015 05:50:31

FROM_DATE_EPOCH=`echo $GET_FROM_DATE | awk -F"#" '{ print $3 }'`
echo $FROM_DATE_EPOCH
1433152231

-F is a field separator

10.CUT In Unix 

Below displays the column-2 value considering # as a field separator
a='#2#'
echo $a
#2#
echo $a | cut -f2 -d '#'
2


I hope this info helps you :). Good Luck & Happy coding :) :)