StackOverflow is a collaborative programming Q&A community venture
Note: The content of this blog is the opinion and thoughts of the blogger and does not necessarily represent the opinions of IDUG.
SQL - Removing duplicates
Although not technically a duplicate is there a way to remove results that only have 1 column different? Want to keep the highest percentage row.
Anyway I can remove a duplicate that may result like this?:
How to delete all non-numerical letters in db2
I have some data in DATA column (varchar) that looks like this:
What I want is this:
Is there a way in IBM DB2 version 9.5 to remove/delete all those non-numeric letters by doing something like this:
or any other ways?
This question follows from this question.
How do I insert multilple rows into a DB2 in one statement?
I am using Java and jdbc drivers to establish a connection to a DB2 database. I am attempting to insert multiple Rows into a Table Using a single statement.
Below is my Code:
To summarize, I have a loop that iterates through objects in a List and elongates the insert statement until it contains all rows in the ArrayList. I then clean the end of the String when the loop is complete, and attempt to execute the statement.
Here is a sample of how a query statement will look when the method is executed:
My code attempts to execute and throw the following error:
[9/17/18 19:36:42:834 GMT] 00000557 SystemOut O com.ibm.db2.jcc.am.SqlSyntaxErrorException: [jcc][4.22.37] T2zOS exception: [jcc][T2zos]T2zosPreparedStatement.readPrepareDescribeOutput_:nativePrepareInto:1583: DB2 engine SQL error, SQLCODE = -104, SQLSTATE = 42601, error tokens = ,;FOR NOT ATOMIC ERRORCODE=-104, SQLSTATE=42601
My error token is a comma and I am uncertain which it is referring to. Is there no way to Insert multiple rows into a DB2 Table? Help would be appreciated!
XML parsing in a DB2 native stored procedure using XMLTABLE
I am a new DB development. I have to create a procedure that accepts XML as input,
I am stuck while parsing the XML. I have used
1) First, i was looking for a way to parse all the XML elements using a single XMLTABLE operation using a cursor like above but it is not working, i am not able to fetch "request" and "source" despite of giving the PATH from root i.e. '../', i am expecting the cursor to run two times giving result set as below. Is anything missing?
2) If the above approach is not correct, can anyone please suggest the correct one to parse XML in a DB2 native stored procedure.
Can a nested SQL subquery be accessed by other subqueries in the same Select statement..?
This is for an IBM AS400 DB2 SQL server vr61m0, accessed via the DBeaver SQL client. I have what is for me the most complicated query I've yet created by manually writing any SQL, and parts of it have a duplication problem. Specifically, there's a subquery that gets used multiple times in different ways, and I'm hoping to mitigate that duplication so it only appears once.
I'm not sure what terminology to use for this, so it's been difficult to search on Google. I've tried bits like "subquery", "derived table", etc, but no luck. I get too many unrelated search results. It seems obvious to me this problem is an issue of scope: the various subquery environments cannot see each other.
To the point... If an SQL query has subqueries in the FROM clause, and one of them is used multiple times, but in some cases it's nested into deeper subqueries for application of aggregate functions, can the deeper subqueries refer to the shallower one to avoid duplication..??
Here's a working example that I've created, with working data. This is a very condensed example that's based on the real query & data that I'm working with. The duplicated subqueries are noted in the SQL with comments:
This SQL works, but the noted subquery is duplicated twice:
Here an attempt is made to refer to the first subquery later in the main query, but it doesn't work. It produces the error: "SQL Error : [SQL0204] LOADEMPS in CERTODB type *FILE not found." As stated above, it seems to be an issue of scope: the subquery environments can't see each other.
Is there a way to make this work, or something like it, all within a single SQL query, so the duplication isn't there..? For instance, no extra views, stored procedures, or other objects, but all done in one big SQL statement..?
How to connect Power BI to IBM DB2
Currently, I am trying to query data from IBM DB2 and then present it in Power BI. However, I notice the information that Power BI needs is different from what I got from the IBM DB2.
The information that Power BI needs is:
The connection information of IBM DB2 is shown as below:
What should I fill in?
Valid date verification in SQL
I have a column that holds
Query should return the last invalid record because 200806 ends in 30 days.
Connecting R to IBM DB2
I am trying to connect to DB2 (AS400 System)
I am getting an error message
Can anyone help to resolve it ?
Output the result of a SELECT executed on IBM DB2
It's easy to do it using T-SQL (SQL Server):
However, I've spend hours on IBM DB2 without being able to do the same. I was hoping this would work:
But I get the following error message:
which I would translate to:
DB2: Won't Allow "NULL" column?
Part of a complex query that our app is running contains the lines:...(inner query)
This syntax of creating columns with null values is not allowed in DB2 altough it is totally OK in MSSQL and Oracle DBs.Technically I can change it to:
But this doesn't have exactly the same meaning and can damage our calculation results.How can I create columns with null values in DB2 using other syntax??