November 11, 2015 at 7:30 am
I've Table and Data as follow,* Table Design and Full Data as attachment - sql-1.zip
idx |chart_code| group_chart_code_1
---------------------------------------------------
111101NULL
212101NULL
312102NULL
412103NULL
512105NULL
612106NULL
712107NULL
812108NULL
912109NULL
1012110NULL
1112111NULL
1212112NULL
1312113NULL
1412115NULL
1512116NULL
1612117NULL
1712199NULL
1812601NULL
1913101NULL
2013102NULL
2113103NULL
2214101NULL
2315102NULL
2415103NULL
2515106NULL
2615111NULL
2715114NULL
2815199NULL
2921101NULL
3021102NULL
I want to
Update test_Chart_Code SET
group_chart_code_1 =
CASE
WHEN chart_code Between 11100 and 11199 THEN '11100' /*Starting from 11100 */
WHEN chart_code Between 11200 and 11299 THEN '11200'
WHEN chart_code Between 11300 and 11399 THEN '11300'
WHEN chart_code Between 11400 and 11499 THEN '11400'
WHEN chart_code Between 11500 and 11599 THEN '11500'
WHEN chart_code Between 11600 and 11699 THEN '11600'
......
WHEN chart_code Between 11900 and 11999 THEN '11900' /*This is end for 11000++ */
WHEN chart_code Between 12100 and 12199 THEN '12100' /*Starting from 12100 */
....
....
....
WHEN chart_code Between 12900 and 12999 THEN '12900' /*This is end for 12000++ */
....
....
WHEN chart_code Between 13100 and 12199 THEN '13100' /*Starting from 13100 */
....
WHEN chart_code Between 13900 and 13999 THEN '13900' /*This is end for 13000++ */
....
....
....
WHEN chart_code Between 99200 and 99299 THEN '99200'
....
....
WHEN chart_code Between 99900 and 99999 THEN '99900' /*This is THE END. Only 5 Digit Number*/
ELSE '-1' END
The Between Rules, and group_chart_code_1 value - Have a pattern
I need help to re-write my Update Statement WITHOUT hardcoded the Between Rules, and WITHOUT hardcoded the group_chart_code_1 value
Please help. I'm stuck
November 11, 2015 at 7:40 am
Assuming group_chart_code_1 is an integer, this should work
Update test_Chart_Code SET group_chart_code_1 = group_chart_code_1 / 100 * 100
November 11, 2015 at 4:42 pm
Hello Sir,
Your guidance is my inspiration
Update test_Chart_Code SET group_chart_code_1 = chart_code / 100 * 100
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply