Solved

Getting missing rows

Posted on 2016-11-09
16
32 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
  • 8
  • 7
16 Comments
 
LVL 18

Expert Comment

by:daveslash
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:Kdo
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
 
LVL 45

Expert Comment

by:Kdo
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:Kdo
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:Kdo
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
6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

 
LVL 45

Expert Comment

by:Kdo
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:Kdo
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:Kdo
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:
Kdo 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

What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

Join & Write a Comment

Recursive SQL in UDB/LUW (you can use 'recursive' and 'SQL' in the same sentence) A growing number of database queries lend themselves to recursive solutions.  It's not always easy to spot when recursion is called for, especially for people una…
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…
This tutorial demonstrates a quick way of adding group price to multiple Magento products.
This video demonstrates how to create an example email signature rule for a department in a company using CodeTwo Exchange Rules. The signature will be inserted beneath users' latest emails in conversations and will be displayed in users' Sent Items…

747 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

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now