IBM Mainframe Forum

by **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.

by **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.

by **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.

by **Balesh013GG** » Thu Dec 20, 2012 6:08 pm

Select all

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

Select all

CBFIMN01
CFBKLL07
MIOK310,LOPM311,SAPM312,MPHM313

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

Select all

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.

Select all

CBFIMN01
CFBKLL07

But not for

Select all

MIOK310,LOPM311,SAPM312,MPHM313

Please advise what can be done to achieve this.