How to change the query



IBM's flagship relational database management system

How to change the query

Postby 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           


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
shyamsaravan
 
Posts: 40
Joined: Tue May 11, 2010 7:56 pm
Has thanked: 0 time
Been thanked: 0 time

Re: How to change the query

Postby 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
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: How to change the query

Postby 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
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
shyamsaravan
 
Posts: 40
Joined: Tue May 11, 2010 7:56 pm
Has thanked: 0 time
Been thanked: 0 time

Re: How to change the query

Postby GuyC » Thu Aug 04, 2011 3:50 pm

I think you need something called "a program" for that.
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: How to change the query

Postby 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
shyamsaravan
 
Posts: 40
Joined: Tue May 11, 2010 7:56 pm
Has thanked: 0 time
Been thanked: 0 time

Re: How to change the query

Postby 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.
The problem I have is that people can explain things quickly but I can only comprehend slowly.
Regards
Nic
NicC
Global moderator
 
Posts: 3025
Joined: Sun Jul 04, 2010 12:13 am
Location: Pushing up the daisies (almost)
Has thanked: 4 times
Been thanked: 136 times


Return to DB2

 


  • Related topics
    Replies
    Views
    Last post