Skip to content

Tag: db2

DB2 leading zeroes [closed]

Closed. This question needs details or clarity. It is not currently accepting answers. Want to improve this question? Add details and clarify the problem by editing this post. Closed last month. Improve this question How to trim only zeroes in a leading position in a string? I’m writing a DB2 script that removes all leading zeroes from a string that

Db2: perfomant way to check if item exists

what will be the fastest way to check if a specific item exists in table or not. I am evaluating the result of this select, but will be there a faster and Performance way? Answer This depends on what you want to do with the result. It’s generally not recommended to use aggregate functions, since you don’t need to scan

Converting decimal to Date

I have a column with dates formatted as decimals, for example: 20,210,830. I want to convert this number to date format as 08/30/2021 I have tried to use convert and the database shoots me an error that convert is not a valid function. Cast seems to work but, only returns a null value every time. This statement will validate: This

How to use result of SELECT in a limit

I have a table here that keeps repeating results over environments. Cf : … etc etc. I tried to use a group by but as there are slight changes in the encoding, it’s not really effective. I tried to specify an environment too but from a machine to another, it changes. But I do have a Column named ” Ordinal_Position

Concatenate the contents of two rows into one column (SQL DB2)

as it says in the title, I need to return two records but in the same column, for example (I clarify that the following code does not work, it is only to understand my case): When doing the query, it would have to return something like Johnny|Depp in the first column and 45 in the second Answer There are 2

Reverse EBCDIC sorts numbers before letters in ROW_NUMBER function

So I do have following SQL select Key is of the type VARCHAR(12). I wonder why the hell the numbers are sorted after the letters. Every other system including UTF-8 always begins with numbers. Answer So a solution you can do is to take advantage of the EBCDIC character order. Special characters are sorted before the letters! If the numeric

DB2 (mainframe DB2) – Select sql – CASE WHEN

My data is like this OUTPUT: My requirement: If there are more than one CODE, then display CODE as ‘MULTI’, But if there is one CODE, then display that CODE itself (e.g. ‘1A’) I want my output like below (if the data as above) CODE AMOUNT MULTI 2.50 If my data is like this: then I want my output like

Using FILTER in IBM DB2 query

I have some queries which works correctly in PostgreSQL but I have to use them to make IBM DB2 queries. It seems like “FILTER” doesn’t work with DB2. My PGSQL query : When I use this with DB2 I get this error : Answer In general, it should be possible to rewrite the FILTER into a CASE statement. Something like:

Update Timestamp field with the oldest record of fields within the row or those in a linked table

One table looks like this: TABLE A Nullables a_id not nullable create_timestamp not nullable edit_timestamp nullable closed_timestamp nullable last_mod_timestamp not nullable The other one like this: TABLE B Nullables b_id not nullable table_a_fk not nullable create_timestamp not nullable How would I set last_mod_timestamp to the oldest value of either (a.create_timestamp, a.edit_timestamp, a.closed_timestamp) or b.create_timestamp where b.table_a_fk = Datatypes are

DB2 functions returning error SQL CODE 4743?

I’ve been trying to solve this issue now for a while. I have a table called Students like: I am using DBVisualizer right now to execute my statements, but I am trying to play around with something called LISTAGG() as a DB2 function: However, every time I try to run this, I get this error: And I have no idea