Getting the part of data from DB2 query



IBM's flagship relational database management system

Re: Getting the part of data from DB2 query

Postby Balesh013GG » Thu Nov 29, 2012 11:37 pm

SELECT SUBSTR(PARM_VALUE,INSTR(PARM_VALUE,'(')+1,INSTR(PARM_VALUE,')')-INSTR(PARM_VALUE,'(') - 1 ) FROM  PARM_TABLE 

is the query that is generating the values two rows of data.

xxxx,yyyy
zzzz

But I am just using the same query as a sub query

SELECT CYCLE_ID,DIRECT_PAY,OVERRIDE_OPTIONS                             
FROM CYCLE_Table                                         
WHERE CYCLE_ID IN (                                       
SELECT SUBSTR(PARM_VALUE,INSTR(PARM_VALUE,'(')+1,INSTR(PARM_VALUE,')')- INSTR(PARM_VALUE,'(') - 1 ) FROM PARM_TABLE
)

This query just checks the direct_pay status and override_options status for the cycle ID's that are gererated by the sub query.

But here I am getting the status for those row ID's which have only one data within parantheses like (zzzz)

I am not getting the status for those row ID's which have more than one values like (xxxx,yyyy)

Also If I use all the ID's that are generated by the sub query (xxxx,yyyy,zzzz) manually instead of aub query I am getting the status for all the ID's.
Balesh013GG
 
Posts: 20
Joined: Fri Oct 19, 2012 4:21 pm
Has thanked: 0 time
Been thanked: 0 time

Re: Re: Getting the part of data from DB2 query

 

Re: Getting the part of data from DB2 query

Postby GuyC » Fri Nov 30, 2012 2:16 pm

your subselect returns 1 value containing 'xx,yy,zz' .
it is not because it contains commas, it suddenly becomes 3 values.

There are a few solutions to your problem :
1) insert 3 rows in your parm table
or
2) split that one value into 3 rows using recursive SQL
or
3) use instr() to look for CYCLE_ID in that 1 value.
I can explain it to you, but i can not understand it for you.
GuyC
 
Posts: 315
Joined: Tue Aug 11, 2009 3:23 pm
Has thanked: 1 time
Been thanked: 4 times

Re: Getting the part of data from DB2 query

Postby NicC » Fri Nov 30, 2012 2:18 pm

You were asked to reply using code tags. You did not but I have done that for you but you had better check to ensure that everything looks ok. In future, if you are typing/pasting screen information then please use the code tags, otherwise your post may get deleted or the topic locked. after all, if you are not prepared to assist those who are helping you why should they help you? Coding up you screen data makes things much easier for people to read and understand.
The problem I have is that people can explain things quickly but I can only comprehend slowly.
Regards
Nic
NicC
Global moderator
 
Posts: 2690
Joined: Sun Jul 04, 2010 12:13 am
Location: Pushing up the daisys (almost)
Has thanked: 4 times
Been thanked: 105 times

Re: Getting the part of data from DB2 query

Postby Balesh013GG » Thu Dec 20, 2012 6:08 pm

SELECT
SUBSTR(PARM_VALUE,INSTR(PARM_VALUE,'(')+1,INSTR(PARM_VALUE,')')-
INSTR(PARM_VALUE,'(') - 1 ) FROM PARM_DETAIL
WITH UR;


This query gives me the result as follows

CBFIMN01
CFBKLL07
MIOK310,LOPM311,SAPM312,MPHM313


I am using these values by using aub query in the query mentioned below.

SELECT CYCLE_ID,DIRECT_PAY,OVERRIDE_OPTIONS
FROM BILLING_CYCLE
WHERE CYCLE_IDENTIFIER IN (
SELECT
SUBSTR(PARM_VALUE,INSTR(PARM_VALUE,'(')+1,INSTR(PARM_VALUE,')')-
INSTR(PARM_VALUE,'(') - 1 ) FROM PARM_DETAIL


But getting the status flags (DIRECT_PAY,OVERRIDE_OPTIONS) for those rows that have only one value.

CBFIMN01
 CFBKLL07


But not for

MIOK310,LOPM311,SAPM312,MPHM313


Please advise what can be done to achieve this.
Balesh013GG
 
Posts: 20
Joined: Fri Oct 19, 2012 4:21 pm
Has thanked: 0 time
Been thanked: 0 time

Re: Getting the part of data from DB2 query

Postby GuyC » Thu Dec 20, 2012 7:35 pm

repeating the question without reading replies is considered rude :evil:

I'm giving you a possible solution, but with flaws :
SELECT CYCLE_ID,DIRECT_PAY,OVERRIDE_OPTIONS
FROM BILLING_CYCLE
WHERE INSTR(PARM_VALUE,CYCLE_IDENTIFIER) > 0
I can explain it to you, but i can not understand it for you.
GuyC
 
Posts: 315
Joined: Tue Aug 11, 2009 3:23 pm
Has thanked: 1 time
Been thanked: 4 times

Previous

Return to DB2

 


  • Related topics
    Replies
    Views
    Last post