Getting missing rows

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
sam2929Asked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Dave FordSoftware Developer / Database AdministratorCommented:
Something like this should do it:

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

Open in new window


HTH,
DaveSlash
Kent OlsenDBACommented:
That seems pretty simple.

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


Or am I missing something?

Kent
sam2929Author Commented:
Few thing it can happen in any period for 2016 and happen for many employee
Introduction to R

R is considered the predominant language for data scientist and statisticians. Learn how to use R for your own data science projects.

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

Thanks...
sam2929Author Commented:
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
Kent OlsenDBACommented:
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?
sam2929Author Commented:
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
Kent OlsenDBACommented:
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?
Kent OlsenDBACommented:
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
sam2929Author Commented:
Getting sqlo551n error is there any else way to do
Kent OlsenDBACommented:
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
sam2929Author Commented:
Getting same error i have select privs only do I need more then select privs ?
Kent OlsenDBACommented:
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
sam2929Author Commented:
emplid is vargraphic 11
period is smallint
code is vargraphic 11

db2/aix64 9.7.11
sam2929Author Commented:
My bad kent it seems k I have some typo so testing it sorry about that
Kent OlsenDBACommented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
DB2

From novice to tech pro — start learning today.