Java Solutions: June 2020

Tuesday, June 30, 2020

PostgreSQL: ROUND makes the value 0.5 returned from a calculation to 0 instead of 1 - Solved

In PostgreSQL, things can look odd as the following queries show.

postgres=> select round(3/cast(6 as float));
round
-------
0

postgres=> select 3/cast(6 as float);
?column?
----------
0.5

postgres=> select round(0.5);
round
-------
1

To make round(3/cast(6 as float)) to return 1 instead of 0, you need to put another layer of parenthesis to it.

postgres=> select round((3/cast(6 as numeric)));
round
-------
1

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

Watch the blessing and loving online channel: SupremeMasterTV live

If you have ever asked yourself these questions, this is the book for you. What is the meaning of life? Why do people suffer? What is in control of my life? Why is life the way it is? How can I stop suffering and be happy? How can I have a successful life? How can I have a life I like to have? How can I be the person I like to be? How can I be wiser and smarter? How can I have good and harmonious relations with others? Why do people meditate to achieve enlightenment? What is the true meaning of spiritual practice? Why all beings are one? Read the book for free here.

Friday, June 26, 2020

PostgreSQL: Losing decimal digits when divide two numbers

In Postgresql

select 4/100;

returns 0 instead of 0.04

There is more than one way to solve this problem.

A. select 4/100.0;

It will return 0.04000000000000000000.

If you want to keep only x digits after the decimal points, use

select round(4/100.0, x);

For example, select round(4/100.0, 2) will return 0.04.

B. select cast(4 as decimal)/100;

It will also return 0.04000000000000000000.

C. select cast(4 as float)/100;

This will return 0.04.

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

Watch the blessing and loving online channel: SupremeMasterTV live

Friday, June 19, 2020

Crystal Reports: java.sql.SQLException: ORA-00904: "table_name"."column_name": invalid identifier

This error can be very misleading. It may have nothing to do with the database table not having the column.

I had a Crystal Report query as below, and it kept throwing such error util I modified the query.

WITH preResult1 AS (
select
max(age) as age,
personDeptId,
personId,
personName
from person, department
where personDeptId = deptId
group by personDeptId, personId, personName
),
preResult2 as (
select
age,
personDeptId,
personId,
personName,
ROW_NUMBER() OVER (PARTITION BY personDeptId ORDER BY age desc) as age_rank
from preResult1
)
SELECT
personName,
age,
numberOfTitles,
awardsReceived
FROM preResult2
INNER JOIN activity on preResult2.personId = activity.personId
WHERE age_rank <= 5

After I modified the query as shown below, the error was gone.

WITH preResult1 AS (
select
max(age) as age,
personDeptId,
personId,
personName,
numberOfTitles,
awardsReceived
from person, department, activity
where personDeptId = deptId and person.personId = activity.personId
group by personDeptId, personId, personName, numberOfTitles, awardsReceived
),
preResult2 as (
select
age,
personDeptId,
personId,
personName,
numberOfTitles,
awardsReceived,
ROW_NUMBER() OVER (PARTITION BY personDeptId ORDER BY age desc) as age_rank
from preResult1
)
SELECT
personName,
age,
numberOfTitles,
awardsReceived,
FROM preResult2
WHERE age_rank <= 5

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

Watch the blessing and loving online channel: SupremeMasterTV live

Tuesday, June 2, 2020

PostgreSQL: Get the TimeStamp of the beginning of a year, a month, or a day

To get the timestamp of the beginning of a year:

//current year
select date_trunc('YEAR', CURRENT_TIMESTAMP);

//any year
select date_trunc('YEAR', CURRENT_TIMESTAMP - INTERVAL '4 year'); or
select date_trunc('YEAR', CURRENT_TIMESTAMP + INTERVAL '3 year'); or
select TIMESTAMP '2019-01-01';

To get the timestamp of the beginning of a month:

//current month
select date_trunc('MONTH', CURRENT_TIMESTAMP);

//any month
select date_trunc('MONTH', CURRENT_TIMESTAMP - INTERVAL '9 month'); or
date_trunc('MONTH', CURRENT_TIMESTAMP + INTERVAL '2 month'); or
select TIMESTAMP '2019-08-01';

To get the timestamp of the beginning of a day:

//current day
select date_trunc('DAY', CURRENT_TIMESTAMP);

//last day of the month
select date_trunc('MONTH', CURRENT_DATE) + INTERVAL '1 month - 1 day';

//last day of the month of a given date
  select date_trunc('MONTH', TIMESTAMP '2018-09-01) + INTERVAL '1 month - 1 day';

//last day of the year
  select date_trunc('YEAR', CURRENT_DATE) + INTERVAL '1 year - 1 day';

//last day of the year of a given date
  select date_trunc('YEAR', TIMESTAMP '2020-05-03') + INTERVAL '1 year - 1 day';

//any day
select date_trunc('DAY', CURRENT_TIMESTAMP - INTERVAL '12 day'); or
select date_trunc('DAY', CURRENT_TIMESTAMP + INTERVAL '22 day'); or
select TIMESTAMP '2019-08-25'; or
select date_trunc('DAY', CURRENT_TIMESTAMP + INTERVAL '2 month + 5 day');
select date_trunc('DAY', CURRENT_TIMESTAMP - INTERVAL '3 year - 2 month + 12 day');

Be aware when you minus an INTERVAL, the "- INTERVAL '3 year - 2 month - 12 day'" reduces three years from the current_timestamp, adds 2 months to it, and minuses twelve days from it.

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

Watch the blessing and loving online channel: SupremeMasterTV live

PostgreSQL: Get the TimeStamp of today, yesterday, tomorrow, and a number of days ago or later

To get the current timestamp:

select current_timestamp;
or
select now();

To get the timestamp of the beginning of today:

select current_date::timestamp;
or
select 'today'::timestamp;

To get the timestamps of yesterday and tomorrow:

select 'yesterday':timestamp, 'tomorrow'::timestamp;
or
select (current_date - INTERVAL '1 day')::timestamp, (now0 + INTERVAL '1 day')::timestamp;

To get the timestamps of n days ago:

select (current_date - INTERVAL 'n day')::timestamp;

To get the timestamp of m days later:

select (current_date + INTERVAL 'm day')::timestamp;
select (current_date + INTERVAL '1 month - 3 day')::timestamp

Also, see PostgreSQL: Get the TimeStamp of the beginning of a year, a month, or a day

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

Watch the blessing and loving online channel: SupremeMasterTV live