I have a table with transactional data in a DB2 database that I want to retrieve the last record, per location and product. The date is unfortunately stored as a YYYYMMDD string. There is not a transaction id or similar field I can key in on. There is no primary key. DATE LOCATION PRODUCT QTY 20210105 A P1 4 …
Tag: db2
How perform a transaction in ibm db2
I’m working with an accounts table I tried to run this sql which transfer funds from one account to another. I got this error savepoint “FIRST_SAVEPOINT” does not exist or is invalid in this context.. SQLCODE=8080, SQLSTATE=3B001, DRIVER=3.69.56 What could i do ? I’m using IBM Data Stu…
Cannot use a CTE in a scalar subquery in Db2
I’m trying to use a CTE in a DB2 LUW v11.5.4.0 scalar subquery, but this doesn’t work: I’m getting this error: SQL Error [42601]: An unexpected token “AS” was found following “1 IN ( WITH t (x)”. Expected tokens may include: “JOIN”.. SQLCODE=-104, SQLSTATE…
Replace values for each ID and each month in IBM DB2 SQL?
I have about several milion record table in IBM DB2 with 4 columns: yeas, month, personal_number (2 milion), personal_id. The key is that I want to get same table from this one, but I want to fill in personal_id column value from last month (in all previous months). Point is to get 1-1: 1 personal_id relate t…
How to use a sub-query to pick out corresponding value for another minimum
I’m trying to find a name of a Community Area that corresponds to the minimum safety score in another column. Current code is resulting table What I want is a table that has COMMUNITY_AREA_NAME defined as WASHINGTON PARK and SAFETY SCORE defined as 1. COMMUNITY_AREA_NAME SAFETY_SCORE WASHINGTON PARK 1 D…
Is there a way to create an ‘indicator’ in an SQL(DB2) select query to indicate when a column is filled?
This title is vague as hell and doesn’t completely cover the problem, but I don’t know how else to word it. Basically, I have the following situation: I have two tables: Table 1: COMPANY. shows company parent-child structure company parent-company A NULL B A C A D A E B Table 2: ACCOUNTS. accounts…
How to return values where one value is in both tables but another is in only one?
I have two tables, which look roughly like this: I want to query these tables to return only the customers who are in both tables, but also to only return the rows where that combination of customer & offer does not exist in table2. In other words, the results should look like this: Does anyone know how b…
How to create generated columns in DB2/400
I wanna create a virtual column which concatenates two columns to 1. My attempt was: This should work according to the docs. ( https://www.ibm.com/support/knowledgecenter/ssw_ibm_i_73/db2/rbafzpdf.pdf?view=kc ) (page 851). Has anyone an idea how to get this done? Thanks in advance. edit: I doublechecked it. T…
db2: how to remove the decimal from number column?
A very simple query which work the result is I want to remove the ,00000000000 to obtain I have tried cast and round but without success. I’m on Db2 This nice ltrim works fine for all type of cars but using with avg return the string with the 00000 the result is.. 🙁 Only the , is changed. Answer casting…
DB2 : SQL: Change in granularity for SCD type-2 table
I have a order table in SCD type-2 like below (Delivery Date in Order granularity and it create history when when delivery date changes) Order_Id Order_Status Order_Create_Dt Delivery_Dt Start_Date …