|
View previous topic :: View next topic
|
| Author |
Message |
rohanthengal
Active User
.jpg)
Joined: 19 Mar 2009
Posts: 206
Location: Globe, India
|
|
|
|
I am in requirement to increment YEAR_COLUMN (defined as SMALLINT) column by 1 when YEAR part of CURRENT TIMESTAMP is greater.
So, i have tried below SQL. Please suggest if there can be other better/efficient approach to do this as i am new to these types of SQLs.
My SQL:
| Code: |
UPDATE TABLE_NAME
SET YEAR_COLUMN
( CASE WHEN CHAR(YEAR_COLUMN) < (CHAR(YEAR(CURRENT TIMESTAMP)))
THEN YEAR_COLUMN = YEAR_COLUMN + 1
ELSE YEAR_COLUMN
END
)
WITH CS; |
I doubt if i really need to convert YEAR - SMALL INT to CHAR before comparing with YEAR(CURRENT TIMESTAMP) ?
I cant test these as i want to place into design first for approval. |
|
| Back to top |
|
 |
Pandora-Box
Global Moderator
Joined: 07 Sep 2006
Posts: 1592
Location: Andromeda Galaxy
|
|
|
|
| Quote: |
| I cant test these as i want to place into design first for approval. |
Dont you have a place where you can test things??
And how would you ensure what ever placed in Design could work fine?? |
|
| Back to top |
|
|
rohanthengal
Active User
Joined: 19 Mar 2009
Posts: 206
Location: Globe, India
|
|
|
|
| Thats why i need help here.. It should be checked for concept and syntax first |
|
| Back to top |
|
|
Pandora-Box
Global Moderator
Joined: 07 Sep 2006
Posts: 1592
Location: Andromeda Galaxy
|
|
|
|
Rohan you didnt get my point
what is stopping you from trying in the place where you test your codes ??
Personally it is a very bad idea to ask someone else to test the code for you to incorporate it in your design |
|
| Back to top |
|
|
singhju
New User
Joined: 01 Dec 2010
Posts: 25
Location: Gurgaon
|
|
|
|
I just ran the below SQL without using CHAR and it just worked fine.
| Code: |
SELECT
( CASE WHEN YEAR(CURRENT DATE) < (YEAR(CURRENT TIMESTAMP))
THEN 1
ELSE 0
END
)
FROM SYSIBM.SYSDUMMY1
WITH UR;
---------+---------+---------+---------+---------+---------+-
---------+---------+---------+---------+---------+---------+-
0
|
|
|
| Back to top |
|
|
gylbharat
Active Member
.jpg)
Joined: 31 Jul 2009
Posts: 565
Location: Bangalore
|
|
|
|
| Quote: |
UPDATE TABLE_NAME
SET YEAR_COLUMN
( CASE WHEN CHAR(YEAR_COLUMN) < (CHAR(YEAR(CURRENT TIMESTAMP)))
THEN YEAR_COLUMN = YEAR_COLUMN + 1
ELSE YEAR_COLUMN
END
)
WITH CS;
|
I think isolation level is only used for select queries. Update queries need exclusive lock. |
|
| Back to top |
|
|
rohanthengal
Active User
Joined: 19 Mar 2009
Posts: 206
Location: Globe, India
|
|
|
|
yes, think so.. i tried above query in SPUFI using select and found few small corrections in above code:
1. no need to convert into CHAR for comparison
2. YEAR_COLUMN + 1 IS ENOUGH.. NO NEED TO WRITE YEAR_COLUMN = YEAR_COLUMN + 1
3. NO NEED OF ELSE CONSTRUCT AS WE DONT NEED TO CHANGE IT... |
|
| Back to top |
|
|
|
|
