CODEWITHSUNDEEP
comparesubstringselectionwhere-clausecognos
user2690652
user2690652

Reputation: 73

Tricky selection in COGNOS

I have a table with a list of part numbers in Column A. There exist what I will call "raw numbers" (12345), and "7-" numbers, which start with "7-" and then just add a raw number onto the end (7-12345). I am trying to select only the "7-" numbers for which a raw number also exists upon removing the leading two characters.

I am having trouble defining WHERE conditions to make sure that the "7-" part numbers selected also have a corresponding raw number which exists in the same column (some "7-" numbers do not have a corresponding raw number in the table - I only want those which do).

Once I have identified these pairs, I need to compare a value in Column B of the "7-" part number with a value in Column C of the corresponding "raw" part number. Any thoughts?

Thanks in advance!

Upvotes: 1

Views: 413

Answers (1)

Andrew
Andrew

Reputation: 8703

You are going to have to use two queries in Cognos. One query will get you the partnumbers that start with '7-', and the portion of that part number with the '7-' removed.

select
pn.partnum,
right(pn.partnum,len(pnpartnum) -2) as Trimmed
from
partnumbers pn
where
pn.partnum like ''7-%'

Your second query will just get you all part numbers not starting with '7-'.

select 
* from 
partnumbers 
where partnum not like '7%'

The you will add a join query, and use these two queries in it, resulting in something along these lines:

select
query1.partnum,
query2.partnum
from
(
select
pn.partnum,
right(pn.partnum,len(pnpartnum) -2) as Trimmed
from
partnumbers pn
where
pn.partnum like '7-%' ) Query1

inner join
(select 
* from 
partnumbers 
where partnum not like '7%') Query2
on query1.trimmed = query2.partnum

Here's a grossly over-simplified SQL Fiddle example.

Upvotes: 1

Related Questions