Articles & Content

StackOverflow

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.

Database: DB2

Anyway I can remove a duplicate that may result like this?:

ID| NAME | VALUE1 | Test | 0.3455%1 | Test2 | 0.435%5 | Test | 0.3455%5 | Test2 | 0.435%

Sample data:

ID | NAME | VALUE42 | Water Fill Level | 0.78345%42 | Valve Fill Level | 0.8546%65 | Water Fill Level | 0.8934%65 | Valve Fill Level | 0.93564%
How to delete all non-numerical letters in db2

I have some data in DATA column (varchar) that looks like this:

Nowshak 7,485 mMaja e Korabit (Golem Korab) 2,764 mTahat 3,003 mMorro de Moco 2,620 mCerro Aconcagua 6,960 m (located in the northwestern corner of the province of Mendoza)Mount Kosciuszko 2,229 mGrossglockner 3,798 m

What I want is this:

7485276430032620696022293798

Is there a way in IBM DB2 version 9.5 to remove/delete all those non-numeric letters by doing something like this:

SELECT replace(DATA, --somekind of regular expression--, '') FROM TABLE_A

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:

 public void createResources(List addedResources) throws SQLException{ Connection conn = null; PreparedStatement statement = null; int i = 0; String values = "INSERT INTO GROUPS (GROUP_NAME, ENTRY_NAME, ENTRY_TYPE, LAST_REQ, CREATE_BY, REQ_BY, LAST_CHANGED, LAST_REQ_TIME) VALUES " + System.getProperty("line.separator"); while(i < addedResources.size()){ Timestamp LAST_CHANGED = Timestamp.valueOf(addedResources.get(i).getLAST_CHANGED().trim()); Timestamp LAST_REQ_TIME = Timestamp.valueOf(addedResources.get(i).getLAST_REQ_TIME().trim()); values = values + "('" + addedResources.get(i).getGROUP_NAME().trim() + "', '" + addedResources.get(i).getENTRY_NAME().trim() + "', '" + addedResources.get(i).getENTRY_TYPE().trim() + "', '" +addedResources.get(i).getLAST_REQ().trim() + "', '" +addedResources.get(i).getCREATE_BY().trim() + "', '" +addedResources.get(i).getREQ_BY().trim() + "', '" + LAST_CHANGED + "', '" + LAST_REQ_TIME + "')," + System.getProperty("line.separator"); i = i + 1; } values = values.substring(0,values.trim().length()-1); Log(Integer.toString(values.length())); Log(values); try { // Get the DB connection conn = this.ds.getConnection(); conn.setAutoCommit(false); // Prepare the statement and populate with data statement = conn.prepareStatement(values); // Perform the INSERT operation statement.executeUpdate(); //Commit the changes conn.commit(); Log("Employee Successfully Added!"); } finally{ // Any exceptions will be propagated // Close database objects, regardless of what happened if ( statement != null ) { statement.close(); } if ( conn != null ) { conn.close(); } } }

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:

INSERT INTO GROUPS (GROUP_NAME, ENTRY_NAME, ENTRY_TYPE, LAST_REQ, CREATE_BY, REQ_BY, LAST_CHANGED, LAST_REQ_TIME) VALUES ('JOHN', 'TEST1', 'FILE', 'N/A', 'SSDP071', 'N/A', '2018-09-17 19:36:34.004', '2018-09-17 19:36:34.004'),('JOHN', 'TEST2', 'FILE', 'N/A', 'SSDP071', 'N/A', '2018-09-17 19:36:37.771', '2018-09-17 19:36:37.771'),('JOHN', 'TEST3', 'FILE', 'N/A', 'SSDP071', 'N/A', '2018-09-17 19:36:42.021', '2018-09-17 19:36:42.021')

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][50053][12311][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,

  • parses it
  • validates
  • gives out XML as output

I am stuck while parsing the XML. I have used XMLTABLE. Below is the sample XML that my proc would consume.

 requestid1 source1  123456789 234567890  CREATE PROCEDURE XMLPARSE ( IN IN_REQ XML, OUT OUT_REQ XML ) DECLARE V_REQUEST CHAR(10) DEFAULT ' '; DECLARE V_SOURCE CHAR(10) DEFAULT ' '; DECLARE V_ACCOUNT CHAR(09) DEFAULT ' '; DECLARE CURSOR1 CURSOR FOR SELECT X.* FROM XMLTABLE ('$d/root/accountlist/account' PASSING IN_REQ as "d" COLUMNS "request" CHAR(10) PATH '../request', "source" CHAR(10) PATH '../source', "account" CHAR(9) PATH '.') AS X; FETCH CURSOR1 INTO V_REQUEST, V_SOURCE, V_ACCOUNT; Rest of the logic to process the above obtained data

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?

 **request** **source** **account** requestid1 source1 123456789 requestid1 source1 234567890

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:

Data table ORDERS:

ORDDATE ORDNUM ORDACCT ORDLOAD 20180901 1 ABC99 101 20180901 2 XYZ00 102 20180901 3 ZZZ12 103 

Data table LOADS:

LOADDATE LOADNUM LOADDRIV LOADHLP1 LOADHLP2 RATEDRIV RATEHLP1 RATEHLP2 20180901 101 57 1 20180901 102 60 71 1 2 20180901 103 58 81 85 1 3 3 

This SQL works, but the noted subquery is duplicated twice:

SELECT ORDDATE, ORDNUM, ORDACCT, ORDLOAD, TYPECODE AS LOADTYPE, EMPID, RATEFROM CERTODB.ORDERS AS ORDERS -- THE SUBQUERY BELOW IS THE FIRST DUPLICATIONINNER JOIN ( SELECT LOADDATE, LOADNUM, 1 AS LOADROLE, LOADDRIV AS EMPID, RATEDRIV AS RATE FROM CERTODB.LOADS WHERE LOADDRIV>0 UNION SELECT LOADDATE, LOADNUM, 2 AS LOADROLE, LOADHLP1 AS EMPID, RATEHLP1 AS RATE FROM CERTODB.LOADS WHERE RATEHLP1>0 UNION SELECT LOADDATE, LOADNUM, 3 AS LOADROLE, LOADHLP2 AS EMPID, RATEHLP2 AS RATE FROM CERTODB.LOADS WHERE RATEHLP2>0 ) AS LOADEMPS ON ORDERS.ORDDATE = LOADEMPS.LOADDATE AND ORDERS.ORDLOAD = LOADEMPS.LOADNUMINNER JOIN ( SELECT LOADDATE, LOADNUM, COUNT(LOADROLE) AS TYPECODE -- THE SUBQUERY BELOW IS THE SECOND DUPLICATION FROM ( SELECT LOADDATE, LOADNUM, 1 AS LOADROLE, LOADDRIV AS EMPID, RATEDRIV AS RATE FROM CERTODB.LOADS WHERE LOADDRIV>0 UNION SELECT LOADDATE, LOADNUM, 2 AS LOADROLE, LOADHLP1 AS EMPID, RATEHLP1 AS RATE FROM CERTODB.LOADS WHERE RATEHLP1>0 UNION SELECT LOADDATE, LOADNUM, 3 AS LOADROLE, LOADHLP2 AS EMPID, RATEHLP2 AS RATE FROM CERTODB.LOADS WHERE RATEHLP2>0 ) AS LOADEMPS GROUP BY LOADDATE, LOADNUM ) AS LOADTYPE ON ORDERS.ORDDATE = LOADTYPE.LOADDATE AND ORDERS.ORDLOAD = LOADTYPE.LOADNUMORDER BY ORDDATE, ORDNUM, ORDLOAD, LOADROLE

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 [42704]: [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..?

SELECT ORDDATE, ORDNUM, ORDACCT, ORDLOAD, TYPECODE AS LOADTYPE, EMPID, RATEFROM CERTODB.ORDERS AS ORDERSINNER JOIN ( SELECT LOADDATE, LOADNUM, 1 AS LOADROLE, LOADDRIV AS EMPID, RATEDRIV AS RATE FROM CERTODB.LOADS WHERE LOADDRIV>0 UNION SELECT LOADDATE, LOADNUM, 2 AS LOADROLE, LOADHLP1 AS EMPID, RATEHLP1 AS RATE FROM CERTODB.LOADS WHERE RATEHLP1>0 UNION SELECT LOADDATE, LOADNUM, 3 AS LOADROLE, LOADHLP2 AS EMPID, RATEHLP2 AS RATE FROM CERTODB.LOADS WHERE RATEHLP2>0 ) AS LOADEMPS ON ORDERS.ORDDATE = LOADEMPS.LOADDATE AND ORDERS.ORDLOAD = LOADEMPS.LOADNUMINNER JOIN ( SELECT LOADDATE, LOADNUM, COUNT(LOADROLE) AS TYPECODE -- BELOW AN ATTEMPT IS MADE TO ACCESS THE PREVIOUS CALL OF THE SUBQUERY 'LOADEMPS', THUS REMOVING THE DUPLICATE FROM LOADEMPS GROUP BY LOADDATE, LOADNUM ) AS LOADTYPE ON ORDERS.ORDDATE = LOADTYPE.LOADDATE AND ORDERS.ORDLOAD = LOADTYPE.LOADNUMORDER BY ORDDATE, ORDNUM, ORDLOAD, LOADROLE
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:

Power BI interface

The connection information of IBM DB2 is shown as below:

Host name: dashdb-enterprisex-xx-xxxx-01.services.xxx.bluemix.netPort number: 50001Database name: BLUDBUser ID: useridPassword: ********

What should I fill in?

Valid date verification in SQL

I have a column that holds LossDate in the format of (yyyymmdd). I need to verify that if the month ends in 30 days, then no loss was reported with a loss date of 31. I have millions of records. Help will be highly appreciated.

LossDate--------201201282015052020180631

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)

library(ibmdbR)driver.name <- "{IBM i Access ODBC Driver}"db.name <- "DBName"host.name <- "HostName"port <- "23"user.name <-"placeholderForYourUserName"pwd <- "placeholderForYourPassword"con.text <- paste("ConnectDb2;DRIVER=",driver.name, ";Database=",db.name, ";Hostname=",host.name, ";Port=",port, ";PROTOCOL=TCPIP", ";UID=", user.name, ";PWD=",pwd,sep="")# Connect to using a odbc Driver Connection string to a remote databasecon <- idaConnect(con.text)

I am getting an error message

Missing system name needed for connection

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):

DECLARE @MyStatement AS NVARCHAR(max) = 'SELECT * FROM MYTABLE'EXEC (@MyStatement)

However, I've spend hours on IBM DB2 without being able to do the same. I was hoping this would work:

DECLARE myStatement VARCHAR(1000);SET myStatement = 'SELECT * FROM MYTABLE';PREPARE s1 FROM myStatement;EXECUTE s1;

But I get the following error message:

Elément syntaxique VARCHAR n'est pas correct. Eléments possibles : DYNAMIC SENSITIVE ASENSITIVE INSENSITIVE.. SQLCODE=-104, SQLSTATE=42601, DRIVER=4.18.60

which I would translate to:

Syntax element VARCHAR is incorrect. Possible elements: DYNAMIC SENSITIVE ASENSITIVE INSENSITIVE.. SQLCODE=-104, SQLSTATE=42601, DRIVER=4.18.60

DB2: Won't Allow "NULL" column?

Part of a complex query that our app is running contains the lines:...(inner query)

SELECT...NULL as column_A,NULL as column_B,...FROM...

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:

'' as column_A,'' as column_B, 

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??