For first subuser then switch to super user,can be R user login to servers and
give command to them.
#awk '$1 = "2023-07-05"' alert_orcl.log
server :/tmp # cat myfile.sh
UserName="xyz"
Password="xyzuserpassword"
Password_root="rootuserpassword"
RemoteCmd1="hostname; find /abc/xyz/data/ -name *.dat
|grep -v punjabuniversity | awk -F '/' '{++S[\$5]} END {for(a in S) print
a,S[a]}'"
for HOST in `cat /tmp/pu.txt |awk '{print $2}'`
do {
expect<<EOF
set timeout 3
spawn ssh -o
"StrictHostKeyChecking no" ${UserName}@${HOST}
expect
"*assword:"
send
"${Password}\n"
expect
"~>"
send "su
-\n"
expect
"*assword:"
send
"${Password_root}\n"
expect
"#"
send
"${RemoteCmd1}\n"
expect "#"
send
"exit\n"
expect
"~>"
send
"exit\n"
expect
"#"
EOF
}
done
Host list for above script.
pu.txt --file
IP Adresss name
-------------------------------------------------------------------------------------------------------------------------------------------
user@host:~$ var_a="Hello World"
user@host:~$ another_var=42
{START..END..INCREMENT} syntax:
#!/bin/bash
echo "Bash version ${BASH_VERSION}..."
for i in {0..10..2}
do
echo "Welcome $i times"
done
-----------------------------------------------------------------
#!/bin/bash
for i in {1..5}
do
echo "Welcome $i times"
done
-------------------------------------------------------------------
for loop syntax
Numeric ranges for syntax is as follows:
for VARIABLE in 1 2 3 4 5 .. N
do
command1
command2
commandN
done
OR
for VARIABLE in file1 file2 file3
do
command1 on $VARIABLE
command2
commandN
done
OR
for OUTPUT in $(Linux-Or-Unix-Command-Here)
do
command1 on $OUTPUT
command2 on $OUTPUT
commandN
done
-------------------------------------------------------------------------------------
for I in 1 2 3 4 5
do
statements1 #Executed for all values of ''I'', up to a disaster-condition if any.
statements2
if (condition)
then
continue #Go to next iteration of I in the loop and skip statements3
fi
statements3
done
------------------------------------------------------------------------------------------------
This script make backup of all file names specified on command line. If .bak file exists, it will skip the cp command.
#!/bin/bash
FILES="$@"
for f in $FILES
do
# if .bak backup file exists, read next file
if [ -f ${f}.bak ]
then
echo "Skiping $f file..."
continue # read next file and skip the cp command
fi
# we are here means no backup file exists, just use cp command to copy file
/bin/cp $f $f.bak
done
---------------------------------------------------------------------------
#!/bin/bash
# for loop starts. it will iterate over all the files in current directory
for file in *\ *; do
# it will replace spacesin file names
mv "$file" "${file// /-}"
done
---------------------------------------------------------------------------
In the following example, it will check firefox.desktop file exists or not in applications/ directory.
#!/bin/bash
for file in /usr/share/applications/*
do
if [ "${file}" == "/usr/share/applications/firefox.desktop" ]
then
echo "The file is found in applications directory";
fi
done
------------------------------------------------------------------------------------
For Loop with Array Elements
By using For Loop we can iterate over array elements. It’s very easy to iterate over array elements. In the following example we are going to iterate over number ranges by using for loop:
#!/bin/bash
FRUITS=('Apple' 'Mango' 'Strawberry' 'Orange' 'Banana')
for item in "${FRUITS[@]}";
do
echo "FRUIT: $item"
done
----------------------------------------------------
#!/bin/bash
# for loop for preinting 5 to 1 numbers
for (( i=5; i>=1; i-- ))
do
echo "COUNT: $i"
done
---------------------------
#!/bin/bash
for city in Tokyo London Paris Dubai Mumbai
do
if [[ "$city" == 'Dubai' ]]; then
break
fi
echo "CITY: $city"
done
echo "Loop Ended"
---------------------------------------------------
In the following example, it will check firefox.desktop file exists or not in applications/ directory.
#!/bin/bash
for file in /usr/share/applications/*
do
if [ "${file}" == "/usr/share/applications/firefox.desktop" ]
then
echo "The file is found in applications directory";
fi
done
In the following example, it will rename files names whose names contain spaces.
#!/bin/bash
# for loop starts. it will iterate over all the files in current directory
for file in *\ *; do
# it will replace spacesin file names
mv "$file" "${file// /-}"
done
---------------------------------------------------------------------------------------------------------------
Example to write your own SQL STATEMENT in Power BI in Excel using currentWorkbook
-------------------------------------------------------------------------------------------------------------------------------------------
BASIC Linux Syntax and Loop & IF condition and basic scripts
user@host:~$ var_a="Hello World"
user@host:~$ another_var=42
{START..END..INCREMENT} syntax:
#!/bin/bash
echo "Bash version ${BASH_VERSION}..."
for i in {0..10..2}
do
echo "Welcome $i times"
done
-----------------------------------------------------------------
#!/bin/bash
for i in {1..5}
do
echo "Welcome $i times"
done
-------------------------------------------------------------------
for loop syntax
Numeric ranges for syntax is as follows:
for VARIABLE in 1 2 3 4 5 .. N
do
command1
command2
commandN
done
OR
for VARIABLE in file1 file2 file3
do
command1 on $VARIABLE
command2
commandN
done
OR
for OUTPUT in $(Linux-Or-Unix-Command-Here)
do
command1 on $OUTPUT
command2 on $OUTPUT
commandN
done
-------------------------------------------------------------------------------------
for I in 1 2 3 4 5
do
statements1 #Executed for all values of ''I'', up to a disaster-condition if any.
statements2
if (condition)
then
continue #Go to next iteration of I in the loop and skip statements3
fi
statements3
done
------------------------------------------------------------------------------------------------
This script make backup of all file names specified on command line. If .bak file exists, it will skip the cp command.
#!/bin/bash
FILES="$@"
for f in $FILES
do
# if .bak backup file exists, read next file
if [ -f ${f}.bak ]
then
echo "Skiping $f file..."
continue # read next file and skip the cp command
fi
# we are here means no backup file exists, just use cp command to copy file
/bin/cp $f $f.bak
done
---------------------------------------------------------------------------
#!/bin/bash
# for loop starts. it will iterate over all the files in current directory
for file in *\ *; do
# it will replace spacesin file names
mv "$file" "${file// /-}"
done
---------------------------------------------------------------------------
In the following example, it will check firefox.desktop file exists or not in applications/ directory.
#!/bin/bash
for file in /usr/share/applications/*
do
if [ "${file}" == "/usr/share/applications/firefox.desktop" ]
then
echo "The file is found in applications directory";
fi
done
------------------------------------------------------------------------------------
For Loop with Array Elements
By using For Loop we can iterate over array elements. It’s very easy to iterate over array elements. In the following example we are going to iterate over number ranges by using for loop:
#!/bin/bash
FRUITS=('Apple' 'Mango' 'Strawberry' 'Orange' 'Banana')
for item in "${FRUITS[@]}";
do
echo "FRUIT: $item"
done
----------------------------------------------------
#!/bin/bash
# for loop for preinting 5 to 1 numbers
for (( i=5; i>=1; i-- ))
do
echo "COUNT: $i"
done
---------------------------
#!/bin/bash
for city in Tokyo London Paris Dubai Mumbai
do
if [[ "$city" == 'Dubai' ]]; then
break
fi
echo "CITY: $city"
done
echo "Loop Ended"
---------------------------------------------------
In the following example, it will check firefox.desktop file exists or not in applications/ directory.
#!/bin/bash
for file in /usr/share/applications/*
do
if [ "${file}" == "/usr/share/applications/firefox.desktop" ]
then
echo "The file is found in applications directory";
fi
done
In the following example, it will rename files names whose names contain spaces.
#!/bin/bash
# for loop starts. it will iterate over all the files in current directory
for file in *\ *; do
# it will replace spacesin file names
mv "$file" "${file// /-}"
done
---------------------------------------------------------------------------------------------------------------
Example to write your own SQL STATEMENT in Power BI in Excel using currentWorkbook
let
Source = Excel.CurrentWorkbook(){[Name="tablename"]}[Content],Query="SELECT * FROM [tablename]"
in
Source
-------------------------------------------------------------------------------------------
VB SAMPLE CODE
--------------------
C:\Users\ComputerName\AppData\Microsoft\Excel\XLSTART
Dim wbk As Workbook
Dim Filename As String
Dim Path As String
Application.DisplayAlerts = False
Path =
"D:\PunjabUniversity\" 'CHANGE PATH
Filename = Dir(Path & "AG*.csv")
'--------------------------------------------
'OPEN EXCEL FILES
Workbooks.Open
("D:\PunjabUniversity\AG.xlsx")
Set wbk =
Workbooks.Open(Path & Filename)
'
Range("A2:C2").Select
Range(Selection,
Selection.End(xlDown)).Select
Selection.Copy
Windows("AG.xlsx").Activate
Sheets("Sheet1").Select
Range("A2").Select
Selection.End(xlDown).Select
ActiveCell.Offset(1, 0).Select
ActiveSheet.Paste
'OPEN EXCEL FILES
Range("D2").Select
Selection.End(xlDown).Select
Selection.Copy
ActiveCell.Offset(0, -1).Select
Selection.End(xlDown).Select
ActiveCell.Offset(0, 1).Select
Range(Selection,
Selection.End(xlUp)).Select
ActiveSheet.Paste
'again for
another kpi
Windows("Combine.csv").Activate
Sheets("AG_Combine").Select
Range("D2:E2").Select
Range(Selection,
Selection.End(xlDown)).Select
Selection.Copy
Windows("AG.xlsx").Activate
Sheets("Sheet1").Select
Range("E2").Select
Selection.End(xlDown).Select
ActiveCell.Offset(1, 0).Select
ActiveSheet.Paste
'Again Past the formula
Range("G2").Select
Selection.End(xlDown).Select
Selection.Copy
ActiveCell.Offset(0, -1).Select
Selection.End(xlDown).Select
ActiveCell.Offset(0, 1).Select
Range(Selection,
Selection.End(xlUp)).Select
ActiveSheet.Paste
'Again Past the formula
Range("H2").Select
Selection.End(xlDown).Select
Selection.Copy
ActiveCell.Offset(0, -1).Select
Selection.End(xlDown).Select
ActiveCell.Offset(0, 1).Select
Range(Selection,
Selection.End(xlUp)).Select
ActiveSheet.Paste
'Again Past the formula
Range("I2").Select
Selection.End(xlDown).Select
Selection.Copy
ActiveCell.Offset(0, -1).Select
Selection.End(xlDown).Select
ActiveCell.Offset(0,
1).Select
Range(Selection,
Selection.End(xlUp)).Select
ActiveSheet.Paste
'J2 Column the formula
Range("J2").Select
Selection.End(xlDown).Select
Selection.Copy
ActiveCell.Offset(0, -1).Select
Selection.End(xlDown).Select
ActiveCell.Offset(0, 1).Select
Range(Selection,
Selection.End(xlUp)).Select
ActiveSheet.Paste
'K2 Column the formula
Range("K2").Select
Selection.End(xlDown).Select
Selection.Copy
ActiveCell.Offset(0, -1).Select
Selection.End(xlDown).Select
ActiveCell.Offset(0, 1).Select
Range(Selection,
Selection.End(xlUp)).Select
ActiveSheet.Paste
Range("K2").Select
Selection.End(xlDown).Select
WName =
ActiveCell.Value
ActiveWorkbook.RefreshAll
Sheets("Sheet1").Select
'PivotTable1
With
ActiveSheet.PivotTables("PivotTable3").PivotFields("Name")
.PivotItems(WName).Visible = True
End With
Sheets("Partly").Select
'PivotTable2
With
ActiveSheet.PivotTables("PivotTable3").PivotFields("Name")
.PivotItems(WName).Visible = True
End With
ActiveWorkbook.Save
ActiveWorkbook.Close
Sub Macro_ABC()
Dim Sum_Of_drc_drops As Variant
Dim Sum_Of_drc_answers As Variant
Dim WName As Variant
Dim DName As Date
Dim wbk As Workbook
Dim Filename As String
Dim Path As String
Application.DisplayAlerts = False
Path =
"D:\PunjabUniversity\" 'CHANGE PATH
Filename = Dir(Path &
"ABC*.csv")
'--------------------------------------------
'OPEN EXCEL FILES
Workbooks.Open
("D:\PunjabUniversity\DEFV4.xlsx")
Set wbk =
Workbooks.Open(Path & Filename)
'
Range("A2:F2").Select
Range(Selection,
Selection.End(xlDown)).Select
Selection.Copy
Windows("DEFV4.xlsx").Activate
Sheets("Raw
Data").Select
Range("A2").Select
Selection.End(xlDown).Select
ActiveCell.Offset(1, 0).Select
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''code
for the date extract
dr1 =
ActiveCell.Address
ActiveSheet.Paste
Selection.End(xlDown).Select
dr2 =
ActiveCell.Address
'''get the
date range to
Range(dr1 &
":" & dr2).Select
Selection.Copy
Range("G2").Select
G2 = ActiveCell.Address
ActiveSheet.Paste
Selection.End(xlDown).Select
gl = ActiveCell.Address
Range(G2 &
":" & gl).RemoveDuplicates Columns:=1, Header:=xlNo
Selection.End(xlUp).Select
glafdup =
ActiveCell.Address
Range(G2 &
":" & glafdup).Select
ctr = Range(G2 &
":" & glafdup).Count
Dim NEWDateArray() As
Variant
NEWDateArray = Range(G2
& ":" & glafdup).Value
For u = 1 To ctr
MsgBox
(NEWDateArray(u, 1))
Next u
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
'For CASE1 Users
''''''''''''''''''''''
Sheets("Raw data
Pt2").Select
ActiveWorkbook.RefreshAll
With
ActiveSheet.PivotTables("PivotTable10").PivotFields("date_t")
For u = 1 To ctr
date_t = NEWDateArray(u, 1)
.PivotItems(date_t).Visible = True
Next u
End With
'''''' populate the data for the CASE1 USER Array,
Dim NEWArrayForHD_Users_KA() As
Variant
Range("A6").Select
Selection.End(xlDown).Select
ActiveCell.Offset(-1,
1).Select
HD_USEER_KA_LB_ADR
= ActiveCell.Address
ActiveCell.Offset(-(ctr
- 1), 0).Select 'go upercell boundary
HD_USEER_KA_UB_ADR
= ActiveCell.Address
Range(HD_USEER_KA_LB_ADR
& ":" & HD_USEER_KA_UB_ADR).Select
NEWArrayForHD_Users_KA
= Range(HD_USEER_KA_LB_ADR & ":" & HD_USEER_KA_UB_ADR).Value
For v = 1 To ctr
MsgBox
(NEWArrayForHD_Users_KA(v, 1))
Next v
Cells.Find(What:="KA",
After:=ActiveCell, LookIn:=xlFormulas, LookAt:= _
xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False _
, SearchFormat:=False).Activate
Cells.FindNext(After:=ActiveCell).Activate
ActiveCell.Offset(-1,
1).Select
Dim
NEWArrayForHD_Users_EA() As Variant
'HD_User_Count_V1_EA
= ActiveCell.Value
HD_USEER_EA_LB_ADR
= ActiveCell.Address
ActiveCell.Offset(-(ctr
- 1), 0).Select 'go upercell boundary
HD_USEER_EA_UB_ADR
= ActiveCell.Address
Range(HD_USEER_EA_LB_ADR
& ":" & HD_USEER_EA_UB_ADR).Select
NEWArrayForHD_Users_EA
= Range(HD_USEER_EA_LB_ADR & ":" & HD_USEER_EA_UB_ADR).Value
For w = 1 To ctr
MsgBox
(NEWArrayForHD_Users_EA(w, 1))
Next w
'For CASE 2 Users
Sheets("Raw data
Pt2").Select
ActiveWorkbook.RefreshAll
Windows("DEF.xlsx").Activate
Sheets("Raw data
Pt2").Select
ActiveWorkbook.RefreshAll
With
ActiveSheet.PivotTables("PivotTableSD").PivotFields("date_t")
For u2 = 1 To ctr
date_t = NEWDateArray(u2, 1)
.PivotItems(date_t).Visible = True
Next
u2
End With
Range("G6").Select
Selection.End(xlDown).Select
ActiveCell.Offset(-1,
1).Select
SD_User_Count_V1_KA = ActiveCell.Value
''''''''''''''''''''''''''''''''''''''''''''''
Dim NEWArrayForSD_Users_KA() As
Variant
SD_USEER_KA_LB_ADR
= ActiveCell.Address
ActiveCell.Offset(-(ctr
- 1), 0).Select 'go upercell boundary
SD_USEER_KA_UB_ADR
= ActiveCell.Address
Range(SD_USEER_KA_LB_ADR
& ":" & SD_USEER_KA_UB_ADR).Select
NEWArrayForSD_Users_KA
= Range(SD_USEER_KA_LB_ADR & ":" & SD_USEER_KA_UB_ADR).Value
''''''''''''''''''''''''''''''''''''''''''''''''
''' populate for data in the
array
Cells.Find(What:="KA",
After:=ActiveCell, LookIn:=xlFormulas, LookAt:= _
xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False _
, SearchFormat:=False).Activate
Cells.FindNext(After:=ActiveCell).Activate
ActiveCell.Offset(-1,
1).Select
SD_User_Count_V1_EA = ActiveCell.Value
Dim
NEWArrayForSD_Users_EA() As Variant
SD_USEER_EA_LB_ADR
= ActiveCell.Address
ActiveCell.Offset(-(ctr
- 1), 0).Select 'go upercell boundary
SD_USEER_EA_UB_ADR
= ActiveCell.Address
Range(SD_USEER_EA_LB_ADR
& ":" & SD_USEER_EA_UB_ADR).Select
NEWArrayForSD_Users_EA
= Range(SD_USEER_EA_LB_ADR & ":" & SD_USEER_EA_UB_ADR).Value
''''''''''''''''''''''''''''''''''''''''
Sheets("Working
Sheet").Select
Range("A1").Select
Selection.End(xlDown).Select
ActiveCell.Offset(1, 0).Select
ActiveCell.Offset(0, 5).Select
'''go to
column F and past SD USER EA
ActiveCell.Value
= SD_User_Count_V7_EA
'''NEWArrayForSD_Users_EA
For
sdea = 1 To ctr
MsgBox
(NEWArrayForSD_Users_EA(sdea, 1))
ActiveCell.Value
= NEWArrayForSD_Users_EA(sdea, 1)
ActiveCell.Offset(1, 0).Select
Next sdea
'''go to
column G and past CASE1 USER EA
Range("G2").Select
Selection.End(xlDown).Select
ActiveCell.Offset(1, 0).Select
ActiveCell.Value = NEWArrayForHD_Users_EA(hdea, 1)
ActiveCell.Offset(1,
0).Select
Next hdea
Range("E2").Select
Selection.End(xlDown).Select
Selection.Copy
ActiveCell.Offset(0, 1).Select
Selection.End(xlDown).Select
ActiveCell.Offset(0, -1).Select
Range(Selection,
Selection.End(xlUp)).Select
ActiveSheet.Paste
Range("F2").Select
Selection.End(xlDown).Select
ActiveCell.Offset(1, 0).Select
'''
'''go to column F and past SD USER KA
For sdka = 1 To
ctr
ActiveCell.Value = NEWArrayForSD_Users_KA(sdka, 1)
ActiveCell.Offset(1,
0).Select
Next sdka
'ActiveCell.Value
= SD_User_Count_V7_KA
''''''''
'''
'''go to column G and past HD USER KA
Range("G2").Select
Selection.End(xlDown).Select
ActiveCell.Offset(1, 0).Select
'NEWArrayForHD_Users_KA(v,
1)
For
hdka = 1 To ctr
ActiveCell.Value = NEWArrayForHD_Users_KA(hdka, 1)
ActiveCell.Offset(1,
0).Select
Next hdka
Range("E2").Select
Selection.End(xlDown).Select
Selection.Copy
ActiveCell.Offset(0, 1).Select
Selection.End(xlDown).Select
ActiveCell.Offset(0, -1).Select
Range(Selection,
Selection.End(xlUp)).Select
ActiveSheet.Paste
Range("H2").Select
Selection.End(xlDown).Select
Selection.Copy
ActiveCell.Offset(0, -1).Select
Selection.End(xlDown).Select
ActiveCell.Offset(0, 1).Select
Range(Selection,
Selection.End(xlUp)).Select
ActiveSheet.Paste
Range("I2").Select
Selection.End(xlDown).Select
Selection.Copy
ActiveCell.Offset(0, -1).Select
Selection.End(xlDown).Select
ActiveCell.Offset(0, 1).Select
Range(Selection,
Selection.End(xlUp)).Select
ActiveSheet.Paste
'COPY KA
FROM LAST CELL OF D2 AND PAST IN THE LAST 7 CELL OF THE SMAME COLUMN
Range("D2").Select
Selection.End(xlDown).Select
Selection.Copy
ActiveCell.Offset(0, 1).Select
Selection.End(xlDown).Select
ActiveCell.Offset(0, -1).Select
Range(Selection,
Selection.Offset(-(ctr - 1), 0)).Select
'Selection.Offset(-1,
0).Select
ActiveSheet.Paste
'''''''
AGAIN COPY EA FROM D2 AND PAST IN THE LAST 7 CELL OF THE SMAME COLUMN
Range("D2").Select
Selection.Copy
Selection.End(xlDown).Select
ActiveCell.Offset(1, 0).Select
Range(Selection,
Selection.Offset(ctr - 1, 0)).Select
'Selection.Offset(-1, 0).Select
ActiveSheet.Paste
'''''''''''''''''''''''''''''''''''
Selection.End(xlUp).Select
Range("C2").Select
Selection.End(xlDown).Select
ActiveCell.Offset(1,
0).Select
For
dt = 1 To ctr
MsgBox
(NEWDateArray(dt, 1))
ActiveCell.Value = NEWDateArray(dt, 1)
ActiveCell.Offset(1,
0).Select
Next dt
ActiveCell.Offset(-1,
0).Select
Range(Selection,
Selection.Offset(-(ctr - 1), 0)).Select
Selection.Copy
Selection.End(xlDown).Select
ActiveCell.Offset(1,
0).Select
Range(Selection,
Selection.Offset((ctr - 1), 0)).Select
ActiveSheet.Paste
'''copy formula B2
Range("B2").Select
Selection.End(xlDown).Select
Selection.Copy
ActiveCell.Offset(0, 1).Select
Selection.End(xlDown).Select
ActiveCell.Offset(0, -1).Select
Range(Selection,
Selection.End(xlUp)).Select
ActiveSheet.Paste
'''copy
formula B2
Range("A2").Select
Selection.End(xlDown).Select
Selection.Copy
ActiveCell.Offset(0, 1).Select
Selection.End(xlDown).Select
ActiveCell.Offset(0, -1).Select
Range(Selection,
Selection.End(xlUp)).Select
ActiveSheet.Paste
Range("J2").Select
Selection.End(xlDown).Select
Selection.Copy
ActiveCell.Offset(0, -1).Select
Selection.End(xlDown).Select
ActiveCell.Offset(0, 1).Select
Range(Selection,
Selection.End(xlUp)).Select
ActiveSheet.Paste
Range("K2").Select
Selection.End(xlDown).Select
Selection.Copy
ActiveCell.Offset(0, -1).Select
Selection.End(xlDown).Select
ActiveCell.Offset(0, 1).Select
Range(Selection,
Selection.End(xlUp)).Select
ActiveSheet.Paste
Range("K1").Select
Selection.End(xlDown).Select
'store the
WeekName from last cell of I column
WName =
ActiveCell.Value
Sheets("Partly").Select
ActiveWorkbook.RefreshAll
With
ActiveSheet.PivotTables("PivotTable14").PivotFields("Name")
.PivotItems(WName).Visible
= True
End With
With
ActiveSheet.PivotTables("PivotTable2").PivotFields("Name")
.PivotItems(WName).Visible = True
End With
ActiveWorkbook.Save
ActiveWorkbook.Close
End Sub
------------------------------------------------------------------------------------------------------------
USE OF INSTR() & SUBSTR() Function in ORacle
-----------------------------------------------------------------
SELECT substr('ABC_DEFGHIJ-KLMNOP_XYZ.csv',INSTR('ABC_DEFGHIJ-KLMNOP_XYZ.csv','_',-1,2)+1,(INSTR('ABC_DEFGHIJ-KLMNOP_XYZ.csv','_',-1,1)-INSTR('ABC_DEFGHIJ-KLMNOP_XYZ.csv','_',-1,2))-1) from dual
SELECT INSTR('SORSORAZE BDOOR','OR', 3, 2) "Result" FROM DUAL;
Result
----------
14
SELECT INSTR('SORSORAZE BDOOR','OR', -3, 2) "Result in Reversed" FROM DUAL;
Result in Reversed
-----------------
2
SQL Scripting Example using partition by and order by clause with rank function
--------------------------------------------------------------------------------------
with temp as (
select
id,
Amount_KG as WeightKG,
dense_rank() over (order by Amount_KG desc) as ranks,
app_id
from
(
select
id,
SUM(D1+D2)/9.8 Weight_KG,
your_id
from yourTableName
where
id like 'abc%'
and date='20220101'
group by id,your_id
)A ---note all the columns are alreay groupy in the inner query no need to group by the out query in the temp table
)--end temp
--query temp
select ranks,id, WeightKG,your_id from temp where ranks>=1 and ranks<=10
------------------------------------------------------------------------------------------------------------------------------------------------------
getting the your top your_ids for each of ids having order by weighKG wise
-----------------------------------------------------------------------------------------------------------------------------------------------------
with temp as (
select
your_id,
id,
Weight_KG,
dense_rank() over (partition by id order by Weight_KG desc) as ranks
from
(
select
your_id,
id,
SUM(d1+d2) Weight_KG
from yourTableName
where
id in('abc123', 'edf456') --your given ids
and DATE='20201202'
group by your_id,id
)A
)
select ranks,your_id,id, Weigh_KG from temp where ranks>=1 and ranks<=10
select
id,
Amount_KG as WeightKG,
dense_rank() over (order by Amount_KG desc) as ranks,
app_id
from
(
select
id,
SUM(D1+D2)/9.8 Weight_KG,
your_id
from yourTableName
where
id like 'abc%'
and date='20220101'
group by id,your_id
)A ---note all the columns are alreay groupy in the inner query no need to group by the out query in the temp table
)--end temp
--query temp
select ranks,id, WeightKG,your_id from temp where ranks>=1 and ranks<=10
------------------------------------------------------------------------------------------------------------------------------------------------------
getting the your top your_ids for each of ids having order by weighKG wise
-----------------------------------------------------------------------------------------------------------------------------------------------------
with temp as (
select
your_id,
id,
Weight_KG,
dense_rank() over (partition by id order by Weight_KG desc) as ranks
from
(
select
your_id,
id,
SUM(d1+d2) Weight_KG
from yourTableName
where
id in('abc123', 'edf456') --your given ids
and DATE='20201202'
group by your_id,id
)A
)
select ranks,your_id,id, Weigh_KG from temp where ranks>=1 and ranks<=10
-----------------------------------------------------------------------------------------------------------------------------Cal PH using Row_Number Function
----------------------------------------------------------------------------------------------------------------------------------------------------------
with temp as (
select substr(`name`,1,10) as ABC_NAME,
Hr,
sum(`Pra`) Pk_Ta,
sum(`wC`) Cp,
sum(`Pra`)/sum(`WC`) Ut
from df
--where Name='ABCDEFG'
group by ABC_NAME,Hr
),
t2 as (select ABC_NAME,
Hr,
Pk_Ta,
Ut,
max(Pk_Ta) over(PARTITION BY Hr) Mx_Ta
from temp
order by Pk_Ta desc
),
t3 as(
select row_number() over(partition by ABC_NAME) N,* from t2
)
select * from t3 where N=1
