Page 1 of 1

How to change the query

PostPosted: Wed Aug 03, 2011 5:18 pm
by shyamsaravan
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           


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?
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

Re: How to change the query

PostPosted: Thu Aug 04, 2011 1:07 pm
by GuyC
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

Re: How to change the query

PostPosted: Thu Aug 04, 2011 3:39 pm
by shyamsaravan
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
1111B     12345         S     11 DEC 2007         34             23 MAR 2005    22 NOV 2011

like below
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
             66666               Y     10 DEC 2010         41             22 MAR 2009    21 NOV 2011


means the new query should allow below these values
           66666         Z     15 OCT 2006         55             12 FEB 2005    04 AUG 2011


Please help and let me know ,if any

Re: How to change the query

PostPosted: Thu Aug 04, 2011 3:50 pm
by GuyC
I think you need something called "a program" for that.

Re: How to change the query

PostPosted: Thu Aug 04, 2011 3:57 pm
by shyamsaravan
I want the possibilties of insert and update query and can we add a seqence number as one column in the table

Re: How to change the query

PostPosted: Thu Aug 04, 2011 4:52 pm
by NicC
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.