IBM Mainframe Forum

by **shyamsaravan** » Wed Aug 03, 2011 5:18 pm

Hi
The Stored proc runs the below query and retrieves the rows and send to Front end.

SELECT DISTINCT
T2.LOCN
,T2.PART_BSNo
,T2.SRCE_TYPE
,T1A.EFF_IN_DATE
,T1A.CAPA_QT
,CHAR(T1A.First_CREATED)
,CHAR(T1A.LAST_UPDATE_date)
FROM base_table T2

LEFT OUTER JOIN
Joint_table T1A

ON T1A.LOCN = T2.LOCN
AND T1A.PART_BSNo = T2.PART_BSNO
AND T1A.SRCE_TYPE = T2.SRCE_TYPE

WHERE

T2.PART_BSNo BETWEEN :WS-PART-BSNo-LOW
AND :WS-PART-BSNO-HIGH
AND ((T2.LOCN BETWEEN :WS-LOC1-CODE-LOW
AND :WS-LOC1-CODE-HIGH)
OR (T2.LOCN BETWEEN :WS-LOC2-CODE-LOW
AND :WS-LOC2-CODE-HIGH)
OR (T2.LOCN BETWEEN :WS-LOC3-CODE-LOW
AND :WS-LOC3-CODE-HIGH)
OR (T2.LOCN BETWEEN :WS-LOC4-CODE-LOW
AND :WS-LOC4-CODE-HIGH)
OR (T2.LOCN BETWEEN :WS-LOC5-CODE-LOW
AND :WS-LOC5-CODE-HIGH)
OR (T2.LOCN BETWEEN :WS-LOC6-CODE-LOW
AND :WS-LOC6-CODE-HIGH))
ORDER BY T2.LOCN
,T2.PART_BSNO
,T2.SRCE_TYPE
,T1A.EFF_IN_DATE
DESC

Select all

Location BaseNO Type StartDate Capacity Created Time Updated Time
1111B 12345 S 11 DEC 2007 34 23 MAR 2005 22 NOV 2011
1111B 12345 X 13 SEP 2010 11 21 JAN 2009 12 MAY 2010
1111B 66666 Y 10 DEC 2010 41 22 MAR 2009 21 NOV 2011
2222A 11111 T 14 DEC 2009 23 11 FEB 2008 13 AUG 2010
2222A 11111 U 21 DEC 2007 34 23 MAR 2005 22 NOV 2011
2222A 77777 W 11 DEC 2010 41 22 MAR 2009 21 NOV 2011

my new requirement is changed below like this..Then we have to allow ,If customer wants to edit update the any existing column value and inserts the new row except 'Location' and 'BaseNo' and should save it to the tables.LOCN,PART_BSNO,SRCE_TYPE and EFF_IN_DATE is a combined primary key .
How to get the results.Can we need to add any new column in the table?

Select all

Location BaseNO Type StartDate Capacity Created Time Updated Time
1111B 12345 S 11 DEC 2007 34 23 MAR 2005 22 NOV 2011
12345 X 13 SEP 2010 11 21 JAN 2009 12 MAY 2010
66666 Y 10 DEC 2010 41 22 MAR 2009 21 NOV 2011
2222A 11111 T 14 DEC 2009 23 11 FEB 2008 13 AUG 2010
11111 U 21 DEC 2007 34 23 MAR 2005 22 NOV 2011
77777 W 11 DEC 2010 41 22 MAR 2009 21 NOV 2011

Thanks
Shyam

by **GuyC** » Thu Aug 04, 2011 1:07 pm

as far as I can see it's a front end issue, not a db2 problem.
- a stored procedure is quite capable of updating/inserting rows with a composite key
- DB2 doesn't "blank out" columns when they have the same value as previous row

by **shyamsaravan** » Thu Aug 04, 2011 3:39 pm

Thanks for the reply; It is not the issue..It is my new rquirement..Just i am showing the front end screen how the values should be appeared...We can not change the column values of LOCN,PART_BSNO,SRCE_TYPE,EFF_IN_DATE,but we are going to edit the remaining columns in the front end screen and the updated values has to store in the DB2 tables.More over the new query should allow to insert the new records.

For example...if the customer wants to edit the below record

Select all

1111B 12345 S 11 DEC 2007 34 23 MAR 2005 22 NOV 2011

like below

Select all

1111B 12345 S 11 DEC 2007 90 23 MAR 2005 04 AUG 2011

The above new value should save in the DB2 table

If the customer wants to insert another new row after the last record of same LOCN and PART_BSNO

Select all

66666 Y 10 DEC 2010 41 22 MAR 2009 21 NOV 2011

means the new query should allow below these values

Select all

66666 Z 15 OCT 2006 55 12 FEB 2005 04 AUG 2011

Please help and let me know ,if any

by **GuyC** » Thu Aug 04, 2011 3:50 pm

I think you need something called "a program" for that.

by **shyamsaravan** » Thu Aug 04, 2011 3:57 pm

I want the possibilties of insert and update query and can we add a seqence number as one column in the table

by **NicC** » Thu Aug 04, 2011 4:52 pm

I do not see what your problem is unless it is that you know nothing about DB2! Of course you can insert data, you can read it back and update it. You can put anything you want in a column as long as it meets the data typing of that column.

IBM Mainframe Forum

How to change the query

How to change the query

Re: How to change the query

Re: How to change the query

Re: How to change the query

Re: How to change the query

Re: How to change the query