How to enter a single quotation mark in Oracle SQL Output?

SELECT S.IMSI||','||CHR(39)||s.SIM||CHR(39)||','||s.STATUS FROM SRC_ESN1 S

Will create output like

404909090123,'02320E34',A

Summing of absolute values in a range using EXCEL

The sum of absolute values present in column AB1 to AB100.

At cell AB 101 enter command =sum(abs(ab1:ab100))
after entering the values type CTRL+SHIFT+ENTER at last. will execute the function as an array function and the function is changed as {=sum(abs(ab1:ab100))}

Another option to perform the same is
=sumproduct(abs(ab1:ab100))

Nested IF using EXCEL Function

=IF(D2=E2,"SAME",(IF(D2>E2,"TGT","SRC")))

Smart use of Substr and Instr function to split first name and last name of subscriber

Query that will split first name and last name where total length of first name is greater than 30 and the last name part is null
=================================================================================================================================

[This query smartly (maximum) makes use of the substr() and instr() function inbuilt available within Oracle]

select csm.SUBSCRIBER_CODE_N,csm.FIRST_NAME_V,csm.LAST_NAME_V,length(trim(csm.FIRST_NAME_V)) LEN_F_NAME,length(trim(csm.LAST_NAME_V)) LEN_L_NAME,
substr(csm.FIRST_NAME_V,1,(length(trim(csm.first_name_v))-length(substr(csm.FIRST_NAME_V,instr(csm.FIRST_NAME_V,' ',1,2),length(trim(csm.first_name_v)))))) FNAMEPART_SMART_CONVERT,
substr(csm.FIRST_NAME_V,instr(csm.FIRST_NAME_V,' ',1,2),length(trim(csm.first_name_v))) LNAMEPART_SMART_CONVERT ,
length(substr(csm.FIRST_NAME_V,1,(length(trim(csm.first_name_v))-length(substr(csm.FIRST_NAME_V,instr(csm.FIRST_NAME_V,' ',1,2),length(trim(csm.first_name_v))))))) FNAME_LEN_SMART_CONVERT,
length(substr(csm.FIRST_NAME_V,instr(csm.FIRST_NAME_V,' ',1,2),length(trim(csm.first_name_v)))) LNAME_LEN_SMART_CONVERT
from SERVICE_LEVEL gsm,INVOICE_ACCOUNT cam,PROFILE_LEVEL csm
where gsm.ACCOUNT_CODE_N=cam.ACCOUNT_CODE_N and cam.SUBSCRIBER_CODE_N=csm.SUBSCRIBER_CODE_N
and gsm.CONTRACT_TYPE_V='N' and gsm.STATUS_CODE_V not in ('ER','PR','RE','IA')
and csm.SUBS_CATEGORY_CODE_V<>'T'
and (length(trim(csm.FIRST_NAME_V))>30 or length(trim(csm.LAST_NAME_V))>30)
and length(substr(csm.FIRST_NAME_V,instr(csm.FIRST_NAME_V,' ',1,2),length(trim(csm.first_name_v))))<=30
and length(substr(csm.FIRST_NAME_V,1,(length(trim(csm.first_name_v))-length(substr(csm.FIRST_NAME_V,instr(csm.FIRST_NAME_V,' ',1,2),length(trim(csm.first_name_v)))))))<=30
and length(trim(csm.LAST_NAME_V)) is null
order by 1 asc

Excel equivalent for substr(instr()) function

suppose you need to truncate the leading and trailing characters from within an excel file , mainly to remove the prefixes and sufixes adding by rating engine (telecom) and the filenames attached here for example like
(Huawei.00377819.DAT.20091021185132.cc.conv
and
Huawei1.00215367.DAT.20091020101704.cc.conv
)
Huawei. and Huawei1. are the prefixes to remove and .cc.conv is the sufix that need to remove). Since the string .20091 is repeating allover suppose the list is huge like around 50000 plus entries ,we can use the following function in excel which is equivalent for substr(instr()) in Oracle SQL.

=MID(C2566,SEARCH(".20091",C2566)-12,27)

Sample for CASE in Oracle SQL

select a.MOBILENUMBER,a.PLANCODE,
a.SRC_CUR_MTH_CHRG, a.TGT_CUR_MTH_CHRG, abs(abs(a.SRC_CUR_MTH_CHRG)-abs(a.TGT_CUR_MTH_CHRG)) DIFF,
CASE WHEN
(abs(nvl(a.SRC_CUR_MTH_CHRG,0))>abs(nvl(a.TGT_CUR_MTH_CHRG,0)))
THEN
(round(nvl((a.SRC_CUR_MTH_CHRG-
.TGT_CUR_MTH_CHRG)/nullif(a.SRC_CUR_MTH_CHRG,0)*100,0),2))
ELSE
(round(nvl((a.SRC_CUR_MTH_CHRG-a.TGT_CUR_MTH_CHRG)/nullif(a.TGT_CUR_MTH_CHRG,0)*100,0),2))
END
VAR_CURMTH_CHRG from reconn.br_master_table a where a.MOBILENUMBER='911120093786'

EXCEL function to find percentage difference of data match/mismatch(numeric data only)

=IF(L61>J61,100-ABS(((J61-L61)/ABS(L61)))*100,100-ABS(((J61-L61)/ABS(J61)))*100)

GSM ?

Global System for Mobile Communication
//
Groupe Speciale Mobile

a committee under the
Conférence Européenne des Postes et Télécommunications (CEPT).

Mobile Number Portability

Mobile Number Portability
===================

Enables a subscriber of a telecommunication provider to port his/her service to another telecommunication provider
in that country, but retain his/her telephone number.

Mobile number portability (MNP) is the implementation in the GSM network of the NP concept.
MNP is introduced in GSM Release R98.

NP or MNP mean that a subscriber has a telephone number belonging to one operator
(number range holder network), while that subscriber is a subscriber of another operator (subscription network).

At least the following NP cases may be defined:

The terms ‘number range holder network’ and ‘subscription network’ are defined for MNP, but also apply
functionally to NP.

• number portability between two GSM networks;
• number portability between two PSTNs;
• number portability between GSM and PSTN.

NP between GSM and PSTN is less common than NP between homogeneous networks.
Denmark is one example of a European country that uses mobile-fixed NP.

One aspect of many telecommunications networks is that a subscriber is identified with a number,
whereby the number is part of a number range.

The number by means of which a subscriber is identified has various purposes, including:

• routing – when a call is established, the routing of that call through the network is based on the
number that is used to identify that subscriber, e.g. a call to +31 65 . . . is routed to the network
from KPN Mobile (‘65’) in The Netherlands (‘+31’).27

• network identification – the number indicates the network that a subscriber belongs to, e.g. a
subscriber with number +31 13 . . . is associated with a local exchange in a particular region of
the PSTN from KPN Netherlands.

• tariff – a subscriber from T-Mobile Netherlands may be charged a lower rate for calls to other
T-Mobile Netherlands subscribers than for calls to Orange Netherlands subscribers.
The introduction of NP affects the above associations of a telephone number.

Explanation of Transit Call

The Transit MSC simplifies the task of configuring a large network containing many interconnected switching elements, such as mobile switching centers and home location registers. By routing all inter-element signalling through the Transit MSC, operators are able to rationalize signalling routing and reduce time and money spent configuring new elements into the network.

But the Transit MSC goes beyond what conventional transit switches can do. Due to built-in mobile application part functionality, the Transit MSC is able to route incoming calls from a fixed network directly to the right MSC in the GSM network. Normally, such a call is routed from the fixed network to an MSC at random, even if the call is intended for a part of the network handled by a different MSC. This results in extra processing load on this "gateway" MSC, since it must forward the call to the correct one. The Transit MSC acts as a one-point gateway for the entire network, reducing the routing load on all the other MSCs and saving the operator the additional transmission costs. Moreover, the Transit MSC features full support for the state-of-the-art Intelligent Networks.

What is an MSISDN ?

Mobile Station Integrated Services Digital Network Number (MSISDN)
-------------------------------------------------------------------
Used to identify a subscriber, below mentioned the structure of the MSISDN.

Country Code (CC)||National Destination Code (NDC)||Subscriber Number (SN)

<--1,2or3 digits-->

<--------------------------Maximum 15 Digits ---------------------------->


Country Code --> Country or group of countries of the subscriber.

National Destination Code --> each PLMN in a country has one or more NDCs allocated to it.

NDC may used to route a call to the appropriate network.

Subscriber Number -->identifies the subscriber within the number plan of a PLMN.

Whats an IMSI?

International Mobile Subscriber Identity (IMSI) is embedded on SIM card and is used to identify a subscriber. 15 digits normally , Initial 3 digits stand for MCC , next 2 or 3 digits stands for MNC,
last digits stands for MSIN.

MCC--> Mobile Country Code identifies country for mobile networks. The MCC values are allocated and published by the ITU-T.

MNC--> Mobile Network Code identifies mobile network within a mobile country(as identified by MCC). MCC and MNC together identify a PLMN.

MSIN--> Mobile subscriber Identification Number (MSIN) - the MSIN is the subscriber identifier within a PLMN (Public Land Mobile Network).

Easy Decode for Data Validation

The following nesting of Oracle functions can be used for validating two different columns between source and target tables.

decode(replace(trim(nvl(a.STREET_NAME,0)),trim(nvl(b.STREET_NAME,0)),'Y'),'Y','Y','N')

Will return 'Y' if the value matches between two columns and 'N' otherwise.

Please put some comments in my blog for any doubts regarding the same.

Formatting SQL*PLUS output

SQL> select max(account_code_n) from mastertable;

account_code_n
-----------------
2.000E+09

SQL> column account_code_n format 9999999999999

SQL> select max(account_code_n) from mastertable;

account_code_n
-----------------
2908006098712

First_Date function like Last_Day function in Oracle

create or replace FUNCTION first_day(value_in DATE)
RETURN DATE IS
vMo VARCHAR2(2);
vYr VARCHAR2(4);
BEGIN
vMo := TO_CHAR(value_in, 'MM');
vYr := TO_CHAR(value_in, 'YYYY');
RETURN TO_DATE(vMo || '-01-' || vYr, 'MM-DD-YYYY');
EXCEPTION
WHEN OTHERS THEN
RETURN TO_DATE('01-01-1900', 'MM-DD-YYYY');
END first_day;

Query to check Date against null date columns

nvl(trunc(gsm.ACTIVE_DATE_D),to_char(to_date('01/01/1900','DD/MM/YYYY'))) <= '01-Nov-2009'

Query to add 2 new columns to a table

alter table recon.BR_Target_Invoice_Data add (DB_ADJUSTMENTS_N NUMBER(15,2) ,cr_ADJUSTMENTS_N NUMBER(15,2) )

Query to alter a column in Oracle SQL

alter table recon.BR_Source_Invoice_Data modify(db_inv_rnd_off_n number(15,2))

BPMN

Business Process Modeling Notation (BPMN) is a standard method of representing business processes as graphical Models. It is widely adopted by Business Process Management. BPMN is a standard for Business Process Modeling (BPM).

Convert date stored in Character to Date Format.

If you store date in Character format and we need to perform a selection on date between.

Use the following query as an example.

select * from pdsn_cdrs where
trunc(to_date(to_char(charge_start_date),'MM/DD/YYYY HH24:MI:SS')) between '01-July-2009' and '31-July-2009'

Learn some Japanese also

"Otaku desu"
means "I'm a nerd"
in Japanese.

Excerpts from Interview with Lary Wall (PERL Guru).

Oracle substr() equivalent function in Excel

For converting 911120202020 into 20202020

please use the excel function

=MID(A1,SEARCH("9111",A1)+4,8)

Performance tuning sql loader .ctl files

For performance tuning for sql loader .ctl files
=================================================


increase the "bind array" for SQL*Loader tool.

There are some more steps we need to follow to get well tuned result.

Initially we need to estimate the RBS requirement (Rollback Segment).
1) determine size of a single row.

2) determine size of a bind array

memory= No.of Rows * rowsoze
bindArraysize=min(memory,bindsize)

3) estimate the size of RBS size required.

rollbacksegment size=1.3*bindarraysize.

When estimating the size of rollback segment size ,I recommend to add 30%
for overhead to the bindarray size.

Since SQL *Loader cant select the RBS for its own, we need to
make offline other rollback segments offline that are small as per the
calculation we have made.

another tips are

1)drop the indexes of the table you are loading data, then recreate
the same after loading.
(please dont drop indexes associated with primary and unique key constraints).

2)Use larger redolog files.

3)fixed width input data file faster than .csv file.

NB: Please perform all these steps only with the help of a DBA.

==============================================================

Entry made to the .ctl file is

OPTIONS (READSIZE=20971520, BINDSIZE=20971520, ROWS=20000)
==============================================================

Count(*) in Oracle Equals to countif() function in MS Excel

The count(*) function in Oracle and countif() function in MS Excel are similar in funcionality.

Oracle, Excel are respective trademark of the respective owners:
Oracle Corporation and Microsoft Corporation.

Every time a database is started, a system global area (SGA) is allocated and Oracle
background processes are started. The combination of the background processes and
memory buffers is called an ORACLE instance.

Database and Instance

The physical database structures of an Oracle database, including data files(grouped into tablespaces) ,redo log files, and control files. An Oracle user(login id) and the objects the user owns (tables ,indexes, stored procedures, triggers, packages, cursors etc) collectively known as a schema, an instance is the combination of background processes and memory buffers. Will get into detail of an Oracle instance in next session.

Rise of Phoenix

myself a telecom engineer by profession with a passion to work on Oracle DBMS products and having hands on Oracle DBA experience for around 8 years, I would like to introduce the basic,intermediate and advanced concepts of Oracle,Telecom,Excel ,SOA and Solution Design,Architect concepts that I have gained through years, By simple language. If you have any Oracle /Telecom related queries , Please feel free to post your doubts here.