All Questions
159 questions
1
vote
2
answers
75
views
Number of Unique Ice Cream Flavors Each Year
Here is a table with different ice cream flavors in different years (name: ice_cream_table):
Year Flavor
2008 Mint
2008 Mint
2008 Cookie Dough
2008 Cookie Dough
2008 Pistachio
...
0
votes
3
answers
121
views
How to display the current info base on date
My SQL script is to display the company, employee number, last name, first name, current job, and current salary.
To arrive at the current something implies there are dates involved.
How do I display ...
- 11
-1
votes
1
answer
54
views
DB2 Select statement with join using aggregate functions
I have two tables in DB2, and I want to issue a single query for multiple rows in the 1st table, including one or more aggregate functions on relevant data in the 2nd table.
cust
cust_id | ...
- 63
-1
votes
2
answers
331
views
SQL DB2: How to update field in one table to max value of another table?
I have two tables, one with units and mileage (unit field is unique: one row per unit):
Table A (unit master)
unit
mileage
1
0
2
0
3
0
And another table with units and mileage (multiple rows per ...
- 1
0
votes
1
answer
98
views
DB2 : many-2-many join - converting json
I have 2 tables (in DB2) which are actually relates as many-2-many example below
Dataset-1
FP_ID|WS_ID|SC_ID|INC_NUM|INC_DESC
"FP1"|10101|"SC1"|1000001|"INC_11"
"FP1&...
- 1,471
0
votes
2
answers
263
views
DB2 SQL update using 3 tables, update 1, join with 2 more to select rows to update
I'm working with 3 tables. Based on the contents of 1 or more fields in each table, I need to update 1 of the tables with constant data. My select works great to discover how many records will need to ...
- 1
-1
votes
2
answers
266
views
DB2 | Left outer join | Avoid duplicates
I have two DB2 tables, expenses and environments, which I need to join.
expenses
ID Expense Job
1 10 AAAAAA
2 5 BBBBBB
3 3 AAAAAAC
4 7 AAAAAA01
...
- 225
-1
votes
2
answers
148
views
Sql join to table based on most recent date and time
I want to write a SQL query joining two tables where I can see driver names and the most recent route's destination, arrive date, and arrive time.
drivers:
driver_num
name
home
1
Bob
Columbus
...
- 1
1
vote
1
answer
313
views
Is there a way to turn a single row from a DB2 SQL statement result set into multiple rows based on one value from that resulting row?
If I have a SQL query like this:
SELECT A.W, A.X, B.Y, B.Z
FROM A
INNER JOIN B ON A.value1 = B.value2;
Which is returning a row like this:
W | X | Y | Z
---+---+---+--
w | x | y | z
Is there a ...
- 474
-1
votes
1
answer
45
views
SQL Joins to retrieve NULL values for rows which are not present in another table
I have 2 tables in DB2 as below:
Table A:
Key ID SubID
Abc123 576 10
Abc124 577 12
Abc125 578 14
Table B:
ID SubID
576 10
576 11
577 12
577 13
578 14
578 15
I want output something ...
- 43
0
votes
1
answer
68
views
Create multiple columns on sql
Query written :
CREATE TABLE CEREAL_LIST_TAB
CEREAL_DESC VARCHAR(10)NOT NULL
INSERT INTO CEREAL_LIST_TAB
SELECT
NVL(CASE WHEN INDICATOR >= '01' AND INDICATOR <= '05' THEN 'WHEAT'
ELSE CASE ...
- 11
-4
votes
1
answer
135
views
SQL Poor Performance on join query
i'm experiencing really poor performance with this query, probably because of my poor experience with join.
The following query have to run on a DB2 database.
SELECT A.n_ann_ord
|| A.n_prg_ord ...
0
votes
2
answers
353
views
Group By with Case Statement Doesn't Count Zeros
I have the following SQL:
WITH MYFILTER AS (
SELECT
CASE WHEN STATE IN ('A', 'B') then
case
when (NUM/DENOM - 1.0) < -0.3 then 'Less than -30%'
when (NUM/DENOM - 1.0) >= -0.3
...
- 1,260
-1
votes
2
answers
1k
views
DB2 SQL HOW TO RETURN 1 ROW ON JOIN TABLE FROM INNER JOIN TABLE
SELECT GARAGE, MAKE
FROM NEIGHBORHOOD_TABLE A
JOIN VEHICLES_TABLE B ON B.MAKE = A.MAKE DISTINCT BY B.MAKE
WHERE A.ZIPCODE = MY_ZIP_CODE
;
Now I want to return all the garages in my zip code with a ...
- 29
-5
votes
3
answers
98
views
join statement in db2 table
I have two tables with ID and date columns. ID and date are the key in both tables. The values are such that, for example, an ID with 15 different dates is stored. How can I use the join statemnet to ...
- 1
1
vote
2
answers
1k
views
JOIN when no data exists
Disclaimer: I don't have a lot of tech background and just learning SQL so apologies.
I have 2 table for acct information - one ref data (ACCT_RD) and one txn data (ACCT_TD).
ACCT_RD is like this
...
- 11
-1
votes
3
answers
93
views
SQL NESTED JOIN NOT USING WHERE
What is the current price of each product? Display product code, product description, unit, and its current price. Always assume that NOT ALL products HAVE unit price BUT you need to display it even ...
-1
votes
3
answers
139
views
SQL Nested join showing the current price of each product and using MAX()
What is the current price of each product? Display product code, product description, unit, and its current price.
HINT: You will use MAX(). This is a nested join.
PRODUCT TABLE
prodCode
...
- 17
0
votes
0
answers
190
views
DB2 Update SQL WHERE clause containing subquery
following situation:
I have 2 tables on a DB2 database:
orders
order_id
order_date
customer_id
stats
order_id
status_code
By using a SELECT with JOIN I can get all of the orders with open status:
...
-1
votes
1
answer
51
views
I Want to Join two tables in DB2 using SQL based on Hostname Field on tables, but have to use some String Functions,
I Want to Join two tables in DB2 based on Hostname Field on tables, but the problem is Hostname field contains IP addresses as well as Hostname of the server. But while considering the Hostname I have ...
- 26
0
votes
1
answer
94
views
Can I use CASE in JOIN condition
I am getting error while using CASE in JOIN statement. Below is the query. I have masked the contents. Please let me know where I am going wrong. As it looks fine to me. Pasted the error aswell.
...
0
votes
1
answer
72
views
How to join where some values only exist in one table?
I have two tables, which look something like this:
Table_1:
Shop_ID | Offer_ID | Metric_1
--------|----------|---------
AAA | 111 | 1
AAA | 222 | 2
BBB | 222 | 3
Table 2:
...
- 473
0
votes
1
answer
42
views
How to join datasets on a column with NULLs?
I have two tables, which look like this:
Table_1:
Shop_ID | Offer_ID | Metric_1
--------|----------|---------
AAA | 111 | 1
AAA | 222 | 2
BBB | 111 | 3
BBB | 222 | ...
- 473
0
votes
1
answer
1k
views
db2 sql query - joins between char and decimal
Im trying to do a Left outer join sql query for 2 DB2 tables -through aqt editor. Join to happen between char and decimal fields.
For the where clause im providing ,the char and decimal fields always ...
0
votes
2
answers
36
views
Need a SQL query to use result from first query
I have a table t1 that has fields(columns) aa,bb,cc unique combination set provides matches one isk in the same table so each record can be identified in t1 either using the combination of aa,bb,cc or ...
- 21
1
vote
1
answer
202
views
SQL DB2 - problem with combining rows from 2 tables into one output
I am having issues combining rows from 2 tables, and I can't figure out how to get UNION to work.
I have 2 database tables:
TFBCR
TFBCAI
TFBCR table contains these columns:
FBC_REQ_REF_NBR (primary ...
- 85
-1
votes
3
answers
141
views
SQL subquery with aggregate value
Sorry for the vague title, I'm a little lost here. I have two simple aggregate SQL queries, but I struggle to combine them in a functional way (likely through a subquery) in IBM DB2. Main goal is to ...
- 137
0
votes
1
answer
1k
views
In SQL DB2, how would I check if a list of values in one table is available in another table?
I have two tables. One is a comparatively small table with 20000 records with columns with id(Unique key),name, zipcode. And the other is a huge table with nearly 1 billion+ records with columns id(...
- 389
0
votes
1
answer
38
views
SQL Need Date of first service
I have two files the first with Model, date sold, customer, and serial number (and other data), 2nd has serial number, date of service, service type (there can be more than one date of service for the ...
- 11
-2
votes
1
answer
335
views
SQL Right join returning blank rows
I am trying to join the below tables:
Table A:
Type1 Type2 ID Object Location
Sample Dummy 1 X111 111222
Sample Dummy 2 X333 333444
Sample Dummy 2 X333 555666
Sample ...
- 9
0
votes
3
answers
201
views
[SQL][DB2] Returns 0 when no records
The following query returns the number of transactions per month.
I would like the query to return 0 in the month when there were no transactions in the TV_TRANSACTION table (no records).
WITH
msc (...
- 31
1
vote
1
answer
745
views
DB2, Trying to Insert a new record and deleting an old one in the same transaction
Suppose I have a table with like so TABLE1:
|---------------------|------------------|
| Field Type | ID |
|---------------------|------------------|
| 1 | ...
- 11
-2
votes
2
answers
54
views
Complex Join on 2 Tables
I have a ZIP Neighborhood Table that goes like this -
ZIP NEIGHBOR
------- --------
01026 01330
01026 01331
01026 01332
01026 01333
01026 01026
.............
Same ZIP can ...
-1
votes
3
answers
117
views
Join tables only if the first join yields no results
I have 5 tables A, B, C, D, E.
The primary key of A is a foreign key in B, C, D, E. They don't have their own primary key.
I have to implement a certain logic where
if ("A JOIN B JOIN C" IS NOT ...
- 1,444
0
votes
2
answers
399
views
Recursive CTE using inner join in SQL
I have to make an Recursive CTE to give me back all the actors who knew kevin bacon, to get better idea about my database I have Movies table and Actors and Movies2Actors table that contains only the ...
- 11
0
votes
1
answer
106
views
DB2 SQL Find department numbers having the same first and last hiring date as deptno 10 and count the years
Find departments which hired also the same year as department 10 did. Suppose department 10 hires every year. Use aggregate functions and the year function.
This is my SQL:
select a.deptno, year(a....
- 55
0
votes
2
answers
2k
views
Db2 joining sysibm.systables to sysibm.syscolumns
when joining sysibm.systables to sysibm.syscolumns what are the best keys to use? accessesing db2 through sql server 2014 openquery
select a.name table_name, b.name column_name
from openquery(dw,'
...
- 46
1
vote
2
answers
2k
views
How to inner join two tables using DATE clause in DB2
SELECT d.local_reference_no
FROM emcs_ct.t_draft_movement_snapshot d
INNER JOIN emcs_ct.t_movement_snapshot M
ON M.local_reference_no = d.local_reference_no
WHERE created_date > '2019-06-...
1
vote
3
answers
128
views
Inner join on multiple subqueries?
I am writing a select that contains a few subqueries (only one in snippet of code below), however I am having a hard time only returning rows where there is 1 existing row in the AIRFI_TCD subquery. I ...
- 85
0
votes
1
answer
97
views
Find all departments which have engineers AND analysts. Use Join and don't forget about the NULL values
I'm having problem getting the right solution on the asked question. I have 3 tables: employees, departement, salgrade. This is what i got so far:
SELECT e0.deptno
FROM EMP AS e0
INNER JOIN dept AS ...
- 51
1
vote
1
answer
114
views
JOIN in UPDATE sql for DB2
Is there a way to use joins in update statements for DB2?
Google has really let me down.
This is roughly what I'm trying to achieve (... except obviously working ....)
Update gk.WR_VEHICLE_WARRANTY ...
- 364
-1
votes
1
answer
89
views
Can't order query correctly
A while ago I requested help to code a LEFT JOIN filtering in a particular way that the result postition the desired value in the first row.
Need to retrieve table's last inserted/updated record ...
-2
votes
1
answer
56
views
Understanding where clause vs join
Can somebody explain the difference between below query examples.
select column1,column2
from table1
join table2
on table1.columna = table2.columna
and columna='1234'
vs
select column1,...
- 41
0
votes
1
answer
335
views
DB2 Multiple Table Joins Yields Duplicate Rows
I'm having difficulties joining multiple tables together; when doing so, I'm getting duplicate data which I do not expect.
Essentially, every Application can have one or more forms. Each applicant ...
- 143
0
votes
3
answers
721
views
Best way to filter a huge table by another huge table in DB2 / SQL?
I'm facing a problem with a big query I made.
In simple terms, [TABLE A] contains around 6 million IDs, and [TABLE B] have around 4 million IDs. I need to append to [TABLE B] the IDs from [TABLE A] ...
0
votes
1
answer
57
views
Join subqueries serval times
I have code that run:
How can I join another query in this subqueries?
I whant a third table that joins with the other tables.
How can I achive this?
select *
from
( select irart, fiben1 as "...
- 381
0
votes
1
answer
257
views
SQL merge minimum time difference
I'm having trouble with a SQL statement that is a bit over my skill level. Running this in a DB2 datawarehouse.
I need to join two columns (CODE1 and CODE2) from TABLE2 into TABLE1 based on some IDs ...
0
votes
2
answers
37
views
How to use search multiple record for IN condition with different spellings in DB2
I make little steps forward in my SQL knowing.
Now i have a problem and hope you'd help me.
I wanted to make an select on a table which shows me all records which are available several times (in ...
0
votes
1
answer
814
views
Left joining multiple tables with select statements and aliases
I'm doing a db2 stored procedure where I'm left joining multiple tables into a new table and adding tablename as a prefix to all column names. Running into a lot of obstacles and I could use some help ...
9
votes
4
answers
25k
views
group by all columns from one table
I have a simple query:
select c.* , max(TLO.BILL_DATE)
from
client c
left outer join TLORDER TLO
on TLO.CUSTOMER = c.CLIENT_ID
OR TLO.ORIGIN = c.CLIENT_ID
OR TLO.DESTINATION = c.CLIENT_ID
GROUP BY ...
- 174