32- or 64-bits float division with PrestoSQL
In Presto SQL,
SELECT 1 / 3 returns
/ performs integer division.
SELECT 1.0 / 3 returns
0.3... How can I get
0.3333333333? (i.e., more specifically, 32- or 64-bits precision instead of first decimal truncation?)
To get a 64-bit precision:
select cast(1 as double) / 3
To get a 32-bit precision:
select cast(1 as real) / 3
I suppose the literal
1.0is treated as
DECIMAL(2, 1). You could use floating point literals instead:
SELECT REAL '1' / 3 -- '1' is a 32-bit float SELECT DOUBLE '1' / 3 -- '1' is a 64-bit float SELECT 1e0 / 3 -- scientific notation implies 64-bit float
See also questions close to this topic
ORA-65096: invalid common user or role name
Hi I've tried to create a new user in Oracle 18c XE, but I get
ORA-65096: invalid common user or role nameerror when writing
create 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:
ORA-65011: 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
var nums = [2,3,3,2,1,5,3,4,6,3,3,2]; Here, the most occurring number is 3..
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).
0.00should show as
-935,560.00should show as
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,050and
10200 = 10,200.
The sum of each of these groups of three would be:
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
The smallest match is
235+999=1234. No other integer less than
235,999gives a sum of 3s equal to 1234.
The next smallest match is
236+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 0-999 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.