PL SQL Add Decimal to make a dollar value

You may need to design a report with financial information, where the dollar value is in a text field without decimal points. 

So how is this possible and not a number field?? Well, in the original business process, legacy systems, the value had always been a real number, then last year, after 20 years, the business began to use cents in payment as well. There is no decision to change the column type, to the change was required in the reports, which contained that column.

The situation is that some number had decimals and others didn't... 

In the report the data looked like this:
TOTAL_AMOUNT
-------------------------
33
24.95
12
56.90

I then used NUMERIC_FORMATTING to present the column data in a currency format: 

select TO_CHAR(TOTAL_AMOUNT, '9999D99') from TABLE_NAME; 

The end result is: 

TOTAL_AMOUNT
-------------------------
33.00
24.95
12.00
56.90

When the value is null, a null will be returned.


You could also use: 
select TO_CHAR(TOTAL_AMOUNT, '9999.99') from TABLE_NAME; 

Get extra records between two tables - Oracle

Here are examples of each when you wish to see matching data and data differences between two tables.

Example:
You have primary Person Table that has 100 records, unique identifier person_id,
then you have a another Table Person_Depts with only 63 records that has Person_id.

Table : Persons
person_id number,
person_name, etc...

Table : Person_Dept
person_id number,
dept_id number,
status char(1), etc...

When you wish to find the 63 matching records it's easy:

select distinct
from persons p,
person_dept pd
where p.person_id = pd.person_id

When you wish to return the 37 rows of the persons in person_dept and do not have a match in persons, you extract them using one of the followig two queries:

select distinct pd.person_id from person_dept pd
minus
select p.person_id from persons p

Or you could use the following lines on small sets of data, using this on long non-indexed tables

select tp.person_ID
from
person_dept tp
where (tp.person_ID ) NOT IN ( select p.person_id from persons p)

update one table with data from another table

This blog post illustrates how to update more than one column in a table with values from columns in another table and explains how to do it in two RDBMS that I use.

Table Structures and values:
TableA has four columns: a, b, c, d (a is the primary key column)
TableB has five columns: a1, b1, c1, d1, e1 (a1 and b1 together constitute the primary key for this table)
The foreign key relationship between the two tables is based on A.a = B.a1
The data in these 2 tables is as follows:
I. TableA
a b c d
1 x y z
2 a b c
3 t x z

II. TableB
a1 b1 c1 d1 e1
1 x1 y1 z1 40
2 a1 b1 c1 50

The requirement is to write a SQL to update columns b, c and d in TableA from the columns b1, c1 and d1 from TableB where-ever the join condition satisfies and e1 > 40 in TABLEB.

Oracle:
UPDATE TABLEASET (b, c, d) = (SELECT b1, c1, d1 from TABLEB WHERE TABLEB.a1 = TABLEA.a and TABLEB.e1 > 40)WHERE EXISTS (SELECT 1 from TABLEB WHERE TABLEB.a1 = TABLEA.a and TABLEB.e1 > 40)
/

Results after the update:
a b c d
————————————
1 x y z
2 a1 b1 c1
3 t x z


SQL Server:
UPDATE TABLEA
SET b = TABLEB.b1,
c = TABLEB.c1,
d = TABLEB.d1
FROM TABLEA, TABLEB
WHERE TABLEA.a = TABLEB.a1
AND TABLEB.e1 > 40
GO

Note: This is an extension in SQL Server i.e. the FROM clause – it does make it simple to understand and is a nice feature.
Results after the update:
a b c d
————————————
1 x y z
2 a1 b1 c1
3 t x z



Happy writing SQL

Alter table column

This is something so simple, yet can cause you to pull your hair out. All you want to do is alter the length of a column in an Oracle table:

An example table:
Members
MbrID number(5)
FirstName varchar2(30)
Surname varchar2(30)

Now you wish to increase the length of the surname to 50 characters, as someone has a longer than usual surname.
Here is the syntax to do this:

alter table members
modify surname varchar2(50)

This has now been updated to :
Members
MbrID char(5)
FirstName varchar2(30)
Surname varchar2(50)


When you wish to add a column:
alter table members
add DateOfBirth date

When you wish to remove a column:
alter table members
drop column DateOfBirth

When you wish to rename a column
alter table customers
rename column surname to lastname

Find and replace text in MySQL and Oracle

Today someone told me that they have a blog with over 300 posts and just changed their URL, the problem is that the new blog still had reference to their previous blog's url. And all the images, and reference were to the previous URL which had a 401 re-direct to the new blog. The images, etc were already deleted from the previous blog and imported into the new URL.

He was actually going to update every single entry. I recommended that it the blog has all posts in it's SQL database and had to find all the fields which contained the text (old url address), with the new text (new url ).

Now I will show you and easy way to update this.

Go to your database SQL screen and type in as follows

UPDATE [your_table_name] SET [your_table_field] = REPLACE([your_table_field], '[string_to_find]' , '[string_to_be_replaced]');

example
UPDATE my_posts SET post_content = REPLACE(post_content, 'www.oldurl.com' , 'www.newurl.com');

So armed with only one query per column table combination, you can easily update your blog or database with the new text.

Combine text using Concatenation in Oracle

When wanting to combine text you can use the concatenation operator

Examples:
To place comma after every row returned use as follows:
SELECT table_name ',' FROM user_tables;

You may have a date that you wish to place in a CSV file, you can concentate as follows:
SELECT 'the date today is: ' to_char(sysdate, 'MM-DD-YYYY') ',' FROM sys.dual;

Sequence number in MSSQL

Today someone asked me how to place a sequential number as a primary key value in a table. He did not want to write a loop in his procedure to increment the primary key value, just a 'plain and simple insert statement' with the table doing the calculations.

In Oracle, the SEQUENCE is the object place holder to use on a table trigger event.

In MSSQL, INCREMENT is the property and can be used as follows:

-------------------------------------------------------------------------
/*
To test this, first set up your table, with the IDENTITY property,
followed with two variables (starting int , increment by value),
in this example the ProductID starts at one, and every new record
will increase the Product counter ID by 1.
*/

CREATE TABLE product
(productID INT IDENTITY(1,1) ,
productDescription VARCHAR(20),
CONSTRAINT ID_PK PRIMARY KEY (productID)
)



/*
Insert some data into the table
*/
insert into product (productDescription) values ('bicycles')
insert into product (productDescription) values ('cars')
insert into product (productDescription) values ('trains')
insert into product (productDescription) values ('buses')
insert into product (productDescription) values ('trams')


/*
Select the data, to test the increment
*/

select * from product

/*
Drop test table
*/
drop table product

-------------------------------------------------------------------------

I hope this article can you, and as always I welcome feedback on this article.

SQL Query to get customers with one product

SQL Query using min and max aggregate function

You may want to find Customer that only has only bought one particular product in the Sales table
You will know the Product_ID is the in the Products table, in this example we use ProductID=10

In this example for each client you will find the lowest value Product_ID and the highest value Product_ID, then filters rows to those where Product_ID is the same as the variable.

--Written by Rishka Booran-Johnson
-- Get Customers with only one product

CREATE TABLE #Customers (
customer_ID int,
firstname varchar(30),
lastname varchar(30)
)

CREATE TABLE #Sales (
Transaction_ID int,
Customer_ID int,
Product_ID int,
Sale_date datetime
)


CREATE TABLE #Products (
Product_ID int,
Product_name varchar(30),
Product_description varchar(50)
)


INSERT #customers SELECT 1, 'john', 'smith'
INSERT #customers SELECT 2, 'sally', 'johnson'
INSERT #customers SELECT 3, 'joe', 'bloggs'



INSERT #Products SELECT 1, 'bicycles', ''
INSERT #Products SELECT 2, 'trains', ''
INSERT #Products SELECT 3, 'dolls', ''



INSERT #Sales SELECT 1, 1, 2, '2009-06-01'
INSERT #Sales SELECT 2, 1, 1, '2009-06-08'
INSERT #Sales SELECT 2, 1, 1, '2009-06-07'
INSERT #Sales SELECT 3, 2, 2, '2009-06-12'
INSERT #Sales SELECT 4, 2, 2, '2009-06-05'
INSERT #Sales SELECT 5, 3, 3, '2009-06-06'
INSERT #Sales SELECT 6, 3, 2, '2009-06-01'
INSERT #Sales SELECT 7, 3, 1, '2009-06-03'


declare
@vproduct_ID int

set @vproduct_ID = 2; -- this would be your input variable for trains


SELECT s.customer_ID, c.firstname, c.lastname, MIN(s.product_ID), MAX(s.product_ID)
FROM #sales s, #customers c
WHERE s.customer_ID = s.customer_ID
AND s.customer_ID = c.customer_ID
GROUP BY s.customer_ID, c.firstname, c.lastname
HAVING MIN(s.product_ID) = @vproduct_ID and MAX(s.product_ID) = @vproduct_ID


DROP TABLE #Customers
DROP TABLE #Sales
DROP TABLE #Products