Case Substring Untuk Ms Sql Server. Tolonggg..

[ciqeane 1]

[b]sample data mcm kat bawah ni[/b]

CREATE TABLE Boo (
keycol INT PRIMARY KEY,
datacol VARCHAR(20));

INSERT INTO Boo VALUES(1, 'FV/B19S/7281520');
INSERT INTO Boo VALUES(2, 'NW/L11U/BALL 10/300');
INSERT INTO Boo VALUES(3, 'RK/H11S/65-2511RK');
INSERT INTO Boo VALUES(4, 'FI/G21U/0004/BN5-12');
INSERT INTO Boo VALUES(5, 'FN/199U/FABRICATE');

[b]orait, kemudian bila nak buat case substring mcm kat bawah ni dapat error pula,[/b]

SELECT keycol,
CASE
WHEN SUBSTRING(datacol, 1, 2) IN ('FI','FN') THEN REG
WHEN SUBSTRING(datacol, 1, 2) IN ('FV') THEN VALV
WHEN SUBSTRING(datacol, 1, 2) IN ('RK') THEN RIKE
ELSE NULL
END AS col2
FROM Boo;

[b]error dia mcm ni(see below),[/b]

Msg 207, Level 16, State 1, Line 6
Invalid column name 'REG'.
Msg 207, Level 16, State 1, Line 7
Invalid column name 'VALV'.
Msg 207, Level 16, State 1, Line 8
Invalid column name 'RIKE'.

[b]apa maksudnya,
tolonggg saya.[/b]

[b]saya nak result mcm ni sebenarnya,[/b]

keycol datacol
-----------------------
1 VALV
2 NULL
3 RIKE
4 REG
5 REG

[b]terima kasih sgt....[/b]

[akuccputsedut 147]

check balik..

aku kasi contoh kat bawah nih..
[code]This example uses CASE and THEN to produce a list of authors, the book identification numbers, and the book types each author has written.

USE pubs
SELECT SUBSTRING((RTRIM(a.au_fname) + ' '+
RTRIM(a.au_lname) + ' '), 1, 25) AS Name, a.au_id, ta.title_id,
Type =
CASE
WHEN SUBSTRING(ta.title_id, 1, 2) = 'BU' THEN 'Business'
WHEN SUBSTRING(ta.title_id, 1, 2) = 'MC' THEN 'Modern Cooking'
WHEN SUBSTRING(ta.title_id, 1, 2) = 'PC' THEN 'Popular Computing'
WHEN SUBSTRING(ta.title_id, 1, 2) = 'PS' THEN 'Psychology'
WHEN SUBSTRING(ta.title_id, 1, 2) = 'TC' THEN 'Traditional Cooking'
END
FROM titleauthor ta JOIN authors a ON ta.au_id = a.au_id

Here is the result set:

Name au_id title_id Type
------------------------- ----------- -------- -------------------
Johnson White 172-32-1176 PS3333 Psychology
Marjorie Green 213-46-8915 BU1032 Business
Marjorie Green 213-46-8915 BU2075 Business
Cheryl Carson 238-95-7766 PC1035 Popular Computing
Michael O'Leary 267-41-2394 BU1111 Business
Michael O'Leary 267-41-2394 TC7777 Traditional Cooking
Dean Straight 274-80-9391 BU7832 Business
[/code]
aku rasa code hang salah skit jer.. try tgk balik...

[ciqeane 1]

Dah dapat selesaikan problem ni.
Query saya dulu treats REG, VALV, AND RIKE sebagai Column names in the table.
Jadi saya guna single quote.

SELECT keycol,
CASE
WHEN SUBSTRING(datacol, 1, 2) IN ('FI','FN') THEN 'REG'
WHEN SUBSTRING(datacol, 1, 2) IN ('FV') THEN 'VALV'
WHEN SUBSTRING(datacol, 1, 2) IN ('RK') THEN 'RIKE'
ELSE NULL
END AS col2
FROM Boo;


Thanks all:-)

[akuccputsedut 147]

Problem solved..[b]CLOSED..[/b]