32 or 64bits float division with PrestoSQL
In Presto SQL, SELECT 1 / 3
returns 0
, because /
performs integer division.
But SELECT 1.0 / 3
returns 0.3
... How can I get 0.3333333333
? (i.e., more specifically, 32 or 64bits precision instead of first decimal truncation?)
2 answers

You can
cast()
before dividing.To get a 64bit precision:
select cast(1 as double) / 3
To get a 32bit precision:
select cast(1 as real) / 3

I suppose the literal
1.0
is treated asDECIMAL(2, 1)
. You could use floating point literals instead:SELECT REAL '1' / 3  '1' is a 32bit float SELECT DOUBLE '1' / 3  '1' is a 64bit float SELECT 1e0 / 3  scientific notation implies 64bit float
See also questions close to this topic

ORA65096: invalid common user or role name
Hi I've tried to create a new user in Oracle 18c XE, but I get
ORA65096: invalid common user or role name
error when writingcreate user student identified by "student";
I've tried to change the container to PDB
SQL> alter session set container =PDB;
as I've understood that you should set that when trying to create a local user but I get the following error:ORA65011: Pluggable database PDB does not exist.
Do you have any idea how could I create a new user with all privileges from the command prompt?

I want to combine a NOT NULL string result from a function with a field(s) that can be NULL from a table
Ok, I have this function:
CREATE FUNCTION ReaderSearcher(id INT) RETURNS varchar(100) NOT DETERMINISTIC BEGIN DECLARE resultList varchar(100); IF id > 0 THEN SET result = 'The search was a success! Readers:'; ELSE SET result = 'The parameter has to be bigger than "0"!'; END IF; RETURN result; END $$
and this procedure:
CREATE PROCEDURE searcher(id INT) BEGIN SELECT ReaderSearcher(id) AS RESULT, readers.firstname AS NAME FROM readers WHERE READER_ID = id; END $$
and I want my results to be:
1) one column which is not null and contains at least the message 'The parameter has to be bigger than "0"!'
2) two columns, first has the message about the result and the second that has name of the reader or some kind of placeholder.
So far it works if the parameter is bigger than 0 but anything lower gives me nothing, just columns' labels. How can I make it work? I tried some stuff like COALESCE and CONCAT with ISNULL but it stays the same.

How to implement "SHOW COLUMN FROM (SELECT Result) WHERE Column_name = 'column_name'"?
Good Evning My Brothers/Sisters.
I want to check whether a column is exist from select result.
I have tried some effort like below but instead it gives me an error.
SHOW COLUMNS FROM (SELECT hasil.skpd, hasil.kode_skpd, SUM(hasil.sudah_isi) AS sudah_isi, SUM(hasil.belum_isi) AS belum_isi FROM ( SELECT a.id_pegawai, c.skpd, c.kode_skpd, a.id_satuan_organisasi, a.nama_pegawai, a.nip, CASE WHEN i.id_pegawai IS NULL THEN 0 ELSE 1 END AS sudah_isi, CASE WHEN i.id_pegawai IS NULL THEN 1 ELSE 0 END AS belum_isi FROM tbl_pegawai a LEFT JOIN ref_skpd c ON a.id_satuan_organisasi = c.id_skpd LEFT JOIN tbl_bapertarum i ON (a.id_pegawai = i.id_pegawai AND YEAR(i.tgl_lapor)='2015') GROUP BY a.id_pegawai ) hasil WHERE 1 AND hasil.kode_skpd LIKE 'Badan Kepegawaian Daerah%' GROUP BY hasil.id_satuan_organisasi ORDER BY hasil.kode_skpd) WHERE Field = "kode_skpd"
This is the full error result:
#1064  You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '(SELECT hasil.skpd, hasil.kode_skpd, SUM(hasil.sudah_isi) AS s' at line 2
How do i to find the existence of a column or columns from SELECT result ?
Thanks in advance Brothers/Sisters.

Finding the most occuring numbers among sets of numbers
Finding the most occurring number(mode) among sets of numbers using JavaScript..
var nums = [2,3,3,2,1,5,3,4,6,3,3,2]; Here, the most occurring number is 3..
How do we achieve that using JavaScript..

Need specific number format in Oracle S9(15)V9(2)
I have a requirement to produce amount fields in zoned decimal format with this specific syntax below.
I don’t know if I need to create a function to handle this or if I can tweak the Oracle number format model. I’m thinking it might require some conditional formatting within a function due to the different requirement for number of digits between positive and negative. I will be performing this formatting on a couple of dozen data elements in the procedure so that might be another reason to use a function. Thoughts?
Requirement: Amount should be represented by 17 characters (positive number) or 16 characters plus a “}” appended to the end (negative number).
Ex.
0.00
should show as00000000000000000
.
Ex.935,560.00
should show as00000000093556000}
Using Oracle 12c.

Find groups of thousands which sum to a given number, in lexical order
A large number can be comma formatted to read more easily into groups of three. E.g.
1050 = 1,050
and10200 = 10,200
.The sum of each of these groups of three would be:
1050=1,050
gives:1+50=51
10200=10,200
gives:10+200=210
I need to search for matches in the sum of the groups of threes. Namely, if I am searching for
1234
, then I am looking for numbers whose sum of 3s= 1234
.The smallest match is
235,999
since235+999=1234
. No other integer less than235,999
gives a sum of 3s equal to 1234.The next smallest match is
236,998
since236+998=1234
. One can add 999 each time, but this fails after reaching 999 since an extra digit of 1 is added to the number.More generally, I am asking for the solutions (smallest to highest) to:
a+b+c+d… = x
where a,b,c,d… is an arbitrary number of integers between 0999 and x is a fixed integer
Note there are infinite solutions to this for any positive integer x.
How would one get the solutions to this beginning with the smallest numbers (arbitrary length, even millions+)?
Is there a way to do this without brute force looping one by one? I'm dealing with potentially very large numbers, which could take years to loop through in a straight loop. Ideally, one should do this without failed attempts.

Is there any way to perform FOR LOOP in PrestoDB?
As the title says,
I'm using PrestoDB.
So, I have a table like this:
shopid  metric 1  metric 2  metric 3  metric 4
What I want to do is to perform some calculation on each columns of metric 2, 3, 4 by comparing their values to metric 1 (> this part can be done fine). However as the calculation is the same for the three columns, I'm wondering if there is anyway to perform such calculation on all columns at once (or via LOOP funciton) since I don't want to write the same stuff with just the column name being changed.
Sort of like this in Python, for example:
For col in [metric 2, metric 3, metric 4]: If statements
I've been search all around Google for a FOR LOOP solutions but there wasn't any thing that could help my need.
Thank you guys so much.

Alternative to presto with fallback mechanism
I am using presto as querying layer over cassandra for various aggregations but facing an issue where if the node goes down or timeout occurs for some reason, the running query fails. I need to have some kind of fallback mechanism.
Is there any alternative to presto with which i can implement fallback mechanism or if there's any way to implement it in presto itself.

Trying to connect presto in tableau
I am trying to connect presto in Tableau with catalog as cassandra. It gets connected but i am not able to see my tables or select my schema.
Downloaded the required drivers for presto from tableau download link.