Solved

Getting missing rows

Posted on 2016-11-09
16
89 Views
Last Modified: 2016-11-21
Table a
Emplid.   Code.  Period
123          744      3
123.         510.     3
123.          744.    4
123.           890.    4
123.           615.    5
123.            510.   5

I want to query table and want to get period 4 where code not in 510

Thanks
0
Comment
Question by:sam2929
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 8
  • 7
16 Comments
 
LVL 18

Expert Comment

by:Dave Ford
ID: 41881110
Something like this should do it:

select emplId,
       code,
       period
  from TableA
 where period = 4
   and code <> 510

Open in new window


HTH,
DaveSlash
0
 
LVL 45

Expert Comment

by:Kent Olsen
ID: 41881111
That seems pretty simple.

Select * from Table where period=4 and code <> 150


Or am I missing something?

Kent
1
 

Author Comment

by:sam2929
ID: 41881122
Few thing it can happen in any period for 2016 and happen for many employee
0
Is Your DevOps Pipeline Leaking?

Is your CI/CD pipeline a hodge-podge of randomly connected tools? You’ve likely got a tool to fix one problem & then a different tool to fix another, resulting in a cluster of tools with overlapping functionality. Learn how to optimize your pipeline with Gartner's recommendations

 
LVL 45

Expert Comment

by:Kent Olsen
ID: 41881129
Can you give some more detail to your question?  Both Dave and I seem to read it the same way.

Thanks...
0
 

Author Comment

by:sam2929
ID: 41881140
Sure I want to find emplid  and the period for which code not equal 510 .The missing code could be in period 1 ,2,3 etc
0
 
LVL 45

Expert Comment

by:Kent Olsen
ID: 41881145
Both of our proposals do that, so apparently we're still not understanding the question.

123.          744.    4
123.           890.    4

Our proposals should return the data shown above.  What are you expecting?
0
 

Author Comment

by:sam2929
ID: 41881157
This is what I want

Emplid.  Period.  Cd
123.          1.           789
123.          1.           894

123.           2.           510

123.         3.            894
123.         3.            999

123.          4.          510
123.           4.          999

I want result as
123.     1.     510notfound
123.      3.     510notfound
0
 
LVL 45

Expert Comment

by:Kent Olsen
ID: 41881175
Ah.  Ok.

A couple more questions are in order then.  

Are there just 4 periods and if not, how many?
If no rows with any code exist for a given period, do you still want the period listed?
0
 
LVL 45

Expert Comment

by:Kent Olsen
ID: 41881226
Anyway, this should be close.  If there are more periods just add them to the CTE.  It's pretty easy to adjust if we need to.

WITH periods (period)
AS
(
  SELECT period FROM (values (1), (2), (3), (4)) t0 (period)
)
SEKECT T.EmplId, P.period, '510 not found'
FROM periods p
LEFT JOIN Table t
  on P.period = t.period
 and code = '510
where t.period is NULL
0
 

Author Comment

by:sam2929
ID: 41881497
Getting sqlo551n error is there any else way to do
0
 
LVL 45

Expert Comment

by:Kent Olsen
ID: 41881529
Hi Sam,

That error is a permissions error.  The user does not have the permission necessary to perform the operation.  Kinda strange.

There is a typo in it.  Try the corrected version below.

WITH periods (period)
AS
(
  SELECT period FROM (values (1), (2), (3), (4)) t0 (period)
)
SELECT T.EmplId, P.period, '510 not found'
FROM periods p
LEFT JOIN Table t
  on P.period = t.period
 and code = 510
where t.period is NULL
0
 

Author Comment

by:sam2929
ID: 41882104
Getting same error i have select privs only do I need more then select privs ?
0
 
LVL 45

Expert Comment

by:Kent Olsen
ID: 41882111
DB2 has been known to produce error messages that are deceptive in that they may reference an issue, the programmer addressable issue is masked.  I once suggested that they have a catch all message similar to "Failure to do right" and default to it when they don't know what went wrong.  Obviously they didn't take my suggestion....

Can you post the table description?  What are the data types in play here?  What is the table's real name?  

What version of DB2 are you running and what's the underlying host Operating System?

Kent
0
 

Author Comment

by:sam2929
ID: 41882267
emplid is vargraphic 11
period is smallint
code is vargraphic 11

db2/aix64 9.7.11
0
 

Author Comment

by:sam2929
ID: 41882288
My bad kent it seems k I have some typo so testing it sorry about that
0
 
LVL 45

Accepted Solution

by:
Kent Olsen earned 500 total points
ID: 41882344
It looks like you have something going on behind the scenes that has nothing to do with this query.  The commands below show me creating a table with the same column definitions that we're using in your query, inserting the same data that you posted, and running the query that is giving you an SQL error.  The query does need a small modification so that the EmplId column shows, but that's not the issue that you're seeing.

Can you execute a simple query against that table?

SELECT * FROM MyTable WHERE EmplId = '123'






db2 => create table MyTable (emplid vargraphic(11), period smallint, code vargraphic(11))
DB20000I  The SQL command completed successfully.
db2 => insert into MyTable values ('123', 3, '744')
DB20000I  The SQL command completed successfully.
db2 => insert into MyTable values ('123', 3, '510')
DB20000I  The SQL command completed successfully.
db2 => insert into MyTable values ('123', 4, '744')
DB20000I  The SQL command completed successfully.
db2 => insert into MyTable values ('123', 4, '890')
DB20000I  The SQL command completed successfully.
db2 => insert into MyTable values ('123', 5, '615')
DB20000I  The SQL command completed successfully.
db2 => insert into MyTable values ('123', 5, '510')
DB20000I  The SQL command completed successfully.
db2 => Select * from MyTable where period=4 and code <> '150'

EMPLID                 PERIOD CODE
---------------------- ------ ----------------------
123                         4 744
123                         4 890

  2 record(s) selected.

db2 => quit
db2 => WITH periods (period)
db2 (cont.) => AS
db2 (cont.) => (
db2 (cont.) =>   SELECT period FROM (values (1), (2), (3), (4), (5)) t0 (period)
db2 (cont.) => )
db2 (cont.) => SELECT T.EmplId, P.period, '510 not found'
db2 (cont.) => FROM periods p
db2 (cont.) => LEFT JOIN MyTable t
db2 (cont.) =>   on P.period = t.period
db2 (cont.) =>  and code = '510'
db2 (cont.) => where t.period is NULL
db2 (cont.) => ;

EMPLID                 PERIOD      3
---------------------- ----------- -------------
-                                1 510 not found
-                                2 510 not found
-                                4 510 not found

  3 record(s) selected.

db2 =>

Open in new window

0

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

November 2009 Recently, a question came up in the DB2 forum regarding the date format in DB2 UDB for AS/400.  Apparently in UDB LUW (Linux/Unix/Windows), the date format is a system-wide setting, and is not controlled at the session level.  I'm n…
Recursive SQL in UDB/LUW (it really isn't that hard to do) Recursive SQL is most often used to convert columns to rows or rows to columns.  A previous article described the process of converting rows to columns.  This article will build off of th…
Are you ready to implement Active Directory best practices without reading 300+ pages? You're in luck. In this webinar hosted by Skyport Systems, you gain insight into Microsoft's latest comprehensive guide, with tips on the best and easiest way…

738 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question