While Loop Example in PL/SQL

This is a simple while loop example to loop a date variable in oracle pl/sql :

SET SERVEROUTPUT ON;
DECLARE
V_COUNTER NUMBER;
V_DATEEND DATE := '12-JUL-18';
V_CURRDATE DATE;
BEGIN
V_CURRDATE := '01-JUL-18';

WHILE V_CURRDATE <= V_DATEEND LOOP
DBMS_OUTPUT.PUT_LINE('TODAY IS ' || V_CURRDATE);
V_CURRDATE := V_CURRDATE + 1;
END LOOP;
END;
/

In the above example we loop V_CURRDATE variable when the value is less or equals V_DATEEND. It will loop until 12-jul-18.

And the result will be something like this :

TODAY IS 01-JUL-18
TODAY IS 02-JUL-18
TODAY IS 03-JUL-18
TODAY IS 04-JUL-18
TODAY IS 05-JUL-18
TODAY IS 06-JUL-18
TODAY IS 07-JUL-18
TODAY IS 08-JUL-18
TODAY IS 09-JUL-18
TODAY IS 10-JUL-18
TODAY IS 11-JUL-18
TODAY IS 12-JUL-18

Talend Tutorial : Oracle to CSV with Custom Query

In this section i have Oracle Table and i want to use a custom query using aggregate function and store the result in csv file.

The job :

In the TOracleInput component, i defined the Attribute as follows, and in the Query i use "Select Gender, count(GENDER) FROM CHILDSTAT GROUP BY GENDER". The result will be 2 column, GENDER and count(GENDER) so we have to define a built in schema that contains 2 column. the first one is GENDER with string type and second column for storing count(GENDER) is integer type.

After that we have to define the component for OutputDelimited, the filename, row separator, and field separator.

the result is like this, GENDER will contain M for male and F for Female and the next column is the total of Male and Female.

Talend Tutorial : Create File Delimited in Talend

First, in the Repository menu select Metadata and it will show the sub like shown below. Right click on File Delimited then select Create File Delimited.

Enter the name, Purpose, and Description and then click next :

Next in the file click browse and find the file that you want. In this example i name my file pegawai.csv and it is located in D: and in the file viewer we can see the record of our file :

And then, we have to define the field separator, in this eample i use comma as field separator and enter as row separator. And in the Preview, i checked the Set heading row as column name because i defined the column name in the first row of the file.
After that we can see our record is properly served in table format and then click finish.

Membuat Koneksi R ke SQL SERVER

Buat bikin koneksi, kita pertama harus bikin koneksi dulu di ODBC data sources bro,

Jadi, disini udah ada koneksi ODBC sebelumnya yang namanya ODBCSQLServer, uid itu user id yang dipake pas setting ODBC nya bro, pwd juga password yang dipake sama user id yang lo bikin.

conn <- odbcConnect("ODBCSqlServer", uid = "sa", pwd = "Pokemon123321")

abis itu, kita coba bikin query buat ngambil data dari tabel di SQL Server bro, query nya disimpen di variabel data.

data <- "SELECT TOP 50 [CustomerKey], [FirstName], [LastName], [BirthDate], [MaritalStatus], [Gender], [EmailAddress], [yearlyIncome], [EnglishEducation], [EnglishOccupation]
FROM [AdventureWorksDW2014].[dbo].[DimCustomer]"

Terus, buat nampilin hasilnya pake perintah sqlQuery(connection, query) connectionnya disimpen di variabel conn, terus query nya disimpen di variabel data. query.result itu nama variabel buat nyimpen hasilnya bro.
odbcClose itu buat close koneksi,
dim(query.result) buat ngambil banyaknya kolom dan baris di dalam query.result ini.

query.result <- sqlQuery(conn, data)
odbcClose(conn)
dim(query.result)
query.result

Contoh Operator logika di R

Perhatikan, di bawah ini contoh operator logika di R. Dipake buat nentuin Bilangan yang di input itu ganjil atau genap.

Buat ngitungnya pake variabel modulus yaitu angka && 2, jadi kalo terus terusan dibagi 2 hasilnya 0, berarti itu bilangan genap, tapi kalo hasilnya bukan 0, berarti itu bilangan ganjil. 

Screenshot gambar pertama angka yang di input 10, jadi hasilnya Bilangan Genap :

Kalo pas di screenshot kedua ini angka yang dimasukin 7, jadi hasilnya Bilangan Ganjil :

SQL Server Tutorial : Soal - Soal Northwind 3

Link download database Northwind disini.

-- Get all Customer where sorting by ContactName ASC

SELECT * FROM Customers
ORDER BY ContactName ASC

Pada contoh ini kita tinggal select * untuk menampilkan semua field, lalu pakai order by ContactName ASC, untuk menampilkan record dari yang contactname nya dimulai dari A - Z karena yang dipakai adalah ascending.

-- Get Total Customer and ContactTitle where ContactTitle = Owner

SELECT count(CustomerID), ContactTitle
FROM Customers
WHERE ContactTitle = 'Owner'
Group By ContactTitle

Dari tabel Customers dimana ContactTitlenya adalah 'Owner', sudah pasti pada field ContactTitle hanya akan menghasilkan satu record yaitu Owner, kemudian count(CustomerID), akan menghitung customerID yang ContactTitle nya owner, Group by untuk menggroup berdasarkan ContactTitle nya.

-- Get Get total Customer and ContactTitle where count > 1

SELECT count(CustomerID) as Jumlah, ContactTitle 
FROM Customers
GROUP BY ContactTitle
Having Count(CustomerID) > 1

Hampir sama seperti sebelumnya, disini kita akan menghitung Jumlah Customers berdasarkan ContactTitle nya, namun disini kondisinya adalah jika Count(CustomerID) > 1, maka itu harus dalam Having karena Where tidak bisa menggunakan aggregate function.

-- Get TotalCustomer and ContactTitle where Count = 1

SELECT count(CustomerID) as Jumlah, ContactTitle 
FROM Customers
GROUP BY ContactTitle
Having Count(CustomerID) = 1

Ini sama dengan yang diatas, namun yang diambil adalah yang jumlahnya = 1.

/* Create a report that shows the number of employees and customers from each city that has employees in it */

SELECT count(distinct EmployeeID) as TotalEmployees, count(distinct CustomerID) as TotalCustomer, a.City 
From Employees a
LEFT JOIN Customers b
on a.City = b.City
Group By a.City

SQL Server Tutorial : Soal - Soal Northwind 2

Kalau ada yang mau download database nya untuk latihan, bisa lihat post berikut.


-- Get CustomerID, CompanyName, ContactName, ContactTitle, Address where Country Mexico

SELECT CustomerID, CompanyName, ContactName, ContactTitle, Address 
FROM Customers
WHERE Country = 'Mexico'

Pada soal diatas, kita diminta menampilkan CustomerID, CompanyName, ContactName, ContactTitle, dan Address dimana Country nya adalah Mexico. Jadi untuk select nya kita tinggal select field - field yang mau ditampilkan, from Customers karena tabel yang dipakai adalah tabel Customers dan semua field itu ada di tabel Customers jadi tidak usah join joinan.

Kemudian, untuk kondisinya menggunakan where Country = 'Mexico' agar mendapatkan record yang countrynya Mexico.

-- Get CustomerID, CompanyName,ContactName, ContactTitle, Address where ContactName like 'AN%'

SELECT CustomerID, CompanyName, ContactName, ContactTitle, Address 
FROM Customers
WHERE ContactName LIKE 'AN%'

Nah, untuk yang ini kita ingin menampilkan CustomerID, CompanyName, ContactName, ContactTitle, dan Address dimana ContactName like 'AN%'. Dalam kondisi like ini ada tanda % di belakang AN, jadi kita mau tampilkan yang ContacName nya diawali dengan AN. bisa dilihat di hasilnya CompanyName depannya AN semua.

-- Get No Fax if fax = NULL from Customers
SELECT CustomerID, CompanyName, ContactName, ContactTitle, Address, 
(CASE
WHEN Fax IS NULL THEN 'No Fax'
ELSE Fax
END) AS FaxNumber 
FROM Customers

Kalo kita select * di tabel Customers maka ada beberapa record Fax yang isinya NULL. Nah, disini kita diminta untuk mengganti NULL itu dengan 'No Fax'. Jadi caranya dengan membikin case yang akan mengisi field bernama FaxNumber.

-- Get All ContactTitle From Customers

Select distinct ContactTitle From Customers

Nah, disini kita diminta menampilkan ContactTitle tapi jangan ada yang rangkap. Kalo kita pake Select ContactTitle, maka yang muncul semua ContactTitle sebanyak record yang ada, Tapi kita disini cuma ingin tau ContactTitle apa aja yang ada, jadi bisa dilakukan dengan menggunakan Select distinct ContactTitle.

-- Get All Customer where sorting by Contact Name Desc

Select * FROM Customers
ORDER BY ContactName DESC

Nah ini kita tinggal select semuanya trus di order by ContactName desc