Page 2 of 2

Re: Getting the part of data from DB2 query

PostPosted: Thu Nov 29, 2012 11:37 pm
by Balesh013GG
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.

Re: Getting the part of data from DB2 query

PostPosted: Fri Nov 30, 2012 2:16 pm
by GuyC
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.

Re: Getting the part of data from DB2 query

PostPosted: Fri Nov 30, 2012 2:18 pm
by NicC
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.

Re: Getting the part of data from DB2 query

PostPosted: Thu Dec 20, 2012 6:08 pm
by Balesh013GG
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.

Re: Getting the part of data from DB2 query

PostPosted: Thu Dec 20, 2012 7:35 pm
by GuyC
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