DEV Community: Sharan Kumar Paratala Rajagopal

ADD_MONTHS and TRUNC for calculating dates in Oracle PL/SQL

Sharan Kumar Paratala Rajagopal — Tue, 04 Aug 2020 16:52:36 +0000

There are business use cases where dates have to be calculated based on certain cut off days. Especially this is important for account payables when invoices have to be paid based on certain criteria which involves cut off days configured for a specific vendor.

This can be achieved by using Oracle PL/SQL inbuilt date functions and by using them we can leverage the cut off days criteria to get the expected date values for paying the invoices.
In this article let’s look take a business use case for paying vendor on 10th of every month based on the invoice cutoff date.

Example:
Invoice date = 7/25/2020 then Payment date has to be = 8/10/2020
Invoice date = 7/26/2020 then Payment date has to be = 9/10/2020

Here we are using the cut off days = 26 and months forward = 1. But that will not resolve the issue when the invoice is on the exact cutoff date.

Hence we will use Oracle PL/SQL inbuilt date function and resolve this date logic. We will use case function to determine the current day and then add the months using ADD_MONTHS.
TRUNC (Date, ‘MM’) provides the 1st of the month and + 9 will give the 10th of the month.

SQL Query:

Output:

Using SUBSTR and INSTR functions in ORACLE PLSQL

Sharan Kumar Paratala Rajagopal — Thu, 16 Jul 2020 23:00:42 +0000

For reporting purpose there might be multiple occasions where there will be requirement to select only part of a string before or after a specified delimiter is present. And most challenging part is to get the values as it requires some additional effort to find the part of the string itself.

Here is a simple example to fetch a number field in an address string.
Any value after (# should be fetched as line2 along with the character (# and any value before the (# is line 1.

PLSQL query is as below:

OUTPUT:

Now same example should work and column2 should be null if the matching string is not present in the input string.

OUTPUT:

QUERY:

OUTPUT:

QUERY:

OUTPUT:

CONCLUSION:
You will be now able to get the string before and after a specific character using SUBSTR and INSTR functions in oracle.

Transpose rows to columns in Oracle SQL using Oracle PIVOT clause

Sharan Kumar Paratala Rajagopal — Sun, 12 Jul 2020 22:00:13 +0000

In Oracle 11g PIVOT clause helps to convert the data from row to column. Below are the examples to convert two column table and three column table result sets to cross tab format.

This is very helpful for reporting and also queries where data has to be viewed as cross table. This is similar to excel PIVOT functionality.

Two column PIVOT:

INPUT:

SQL QUERY:

select * from (
select t.cstore_number, t.attr_Value,
row_number() over (partition by cstore_number order by attr_Value) rn from STORE_ATTR t)
pivot (
min(attr_Value)
for (rn) in (1 as DEALERCODE1, 2 as DEALERCODE2, 3 as DEALERCODE3, 4 as DEALERCODE4, 5 as DEALERCODE5)
);

Output:

Three column pivot:

Input:

SQL query:

OUTPUT:

Summary
Now you should be able to convert or transpose rows to columns into crosstab format by using oracle PIVOT clause.

Web Scraping using Python

Sharan Kumar Paratala Rajagopal — Tue, 17 Dec 2019 01:14:22 +0000

Web scraping, web harvesting, or web data extraction is data scraping used for extracting data from websites. Web scraping software may access the World Wide Web directly using the Hypertext Transfer Protocol, or through a web browser.

Web scraping is a process of automating the extraction of data in an efficient and fast way. With the help of web scraping, you can extract data from any website, no matter how large is the data, on your computer. Moreover, websites may have data that you cannot copy and paste.

All the job is carried out by a piece of code which is called a “scraper”. First, it sends a “GET” query to a specific website. Then, it parses an HTML document based on the received result.

The reason why Python is a preferred language to use for web scraping is that Scrapy and Beautiful Soup are two of the most widely employed frameworks based on Python. Beautiful Soup- well, it is a Python library that is designed for fast and highly efficient data extraction. It's more like an all-rounder and can handle most of the web crawling related processes smoothly.
To extract data using web scraping with python, you need to follow these basic steps:
• Find the URL that you want to scrape.
• Inspecting the Page.
• Find the data you want to extract.
• Write the code.
• Run the code and extract the data.
• Store the data in the required format.

Let’s walk through an example.
“The United States collects and analyzes demographic data from the U.S. population. The U.S. Census Bureau provides annual estimates of the population size of each U.S. state and region. Many important decisions are made using the estimated population dynamics, including the investments in new infrastructure, such as schools and hospitals.The census data and estimates are publicly available on the U.S. census website”.
Steps to extract the weblinks from HTML code:
a. Import all the required libraries as shown
b. Set the url variable to the https://www.census.gov/programs-surveys/popest.html
c. Use request.get to fetch the details from the url
d. Use BeautifulSoup package to parse the html code and get the content from the website
e. Soup.find_all(“a”) gives all the links from the website
f. Link.get(“href”) provides the details of all the reference links in the website.

Let’s determine if a link is a locator to another HTML page.
“href” tag in the html code provides the details of the link locator to another HTML page.

Now will see how relative links are saved as absolute URIs in the output file.
• Create function unique_links with parameters of tags and url.
• Loop to all the links with “href” tag
• If there is ‘None’ then continue to execute the code.
• If the link ends with ‘/’ or ‘#’ then remove them from the link.
• Parse the url fetched from steps c and d and join with the actual url with the cleaned url by removing the ‘/’ or ‘#’
• Return the cleaned url.

Will make sure there is no duplicate links in the output.
Set() function provides the unique url.

Write to a csv file:
filename = "output_unique_weblink.csv"

f = open(filename,"w")
header = "Output_Weblinks\n"
f.write(header)
write into the csv file all unique links from the website
for link in cleaned_links:
f.write(str(link) + '\n')
To execute the python code:
Go to the command prompt and execute the saved python code.
Python python_code.py

Conclusion:
By using python libraries we are able to scrap the website and extract the links successfully. And removed the duplicate links. Wrote the output to a csv file.