Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 116
  • Last Modified:

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
0
sam2929
Asked:
sam2929
  • 8
  • 7
1 Solution
 
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
0
 
Kent OlsenData Warehouse Architect / DBACommented:
That seems pretty simple.

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


Or am I missing something?

Kent
1
 
sam2929Author Commented:
Few thing it can happen in any period for 2016 and happen for many employee
0
Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

 
Kent OlsenData Warehouse Architect / DBACommented:
Can you give some more detail to your question?  Both Dave and I seem to read it the same way.

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

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

Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

  • 8
  • 7
Tackle projects and never again get stuck behind a technical roadblock.
Join Now