SQL SERVER : CASE WHEN THEN ELSE

Post by Goborijung at 2020-08-17 16:20:00 | ID: 686

CASE
    WHEN condition1 THEN result1
    WHEN condition2 THEN result2
    WHEN conditionN THEN resultN
    ELSE result
END;

>> เปรียบเทียบ [ CASE X WHEN VS CASE WHEN X ]
>> แทนค่า X = 10

SELECT CASE X 
WHEN < 0 THEN 'A' 
WHEN > 100 THEN 'B' 
ELSE 'C' 
END

//คำอธิบาย
เคส 10 
เมื่อ < 0 แล้ว A
เมือ > 100 แล้ว B
นอกนั้น C
จบนะ

----------------------

SELECT CASE
WHEN X < 0 THEN 'A'
WHEN X > 100 THEN 'B'
ELSE 'C' 
END

//คำอธิบาย
เคส 
เมื่อ 10 < 0 แล้ว A
เมื่อ 10 > 100 แล้ว B
นอกนั้น C
จบนะ

>> Example
SELECT JobTitle,
        CASE Gender
           WHEN 'M' THEN 'Male'
           WHEN 'F' THEN 'Female'
           ELSE 'Unknown Value'
        END
 FROM   HumanResources.Employee

SQL SERVER : Concat String

Post by Goborijung at 2021-07-29 10:07:35 | ID: 1296

Select CONCAT(f.Customer,' - ',cus.Name) as Customer 
From FBPurchaseReceipt f left join Customer cus on cus.OID = f.Customer Where TransactionNo = 'ref017-26023'

SQL SERVER : Convert Decimal tox Hex

Post by Goborijung at 2021-07-05 13:54:54 | ID: 1273

SELECT FORMAT(9,'X')
SELECT FORMAT(10,'X')
SELECT FORMAT(11,'X')
SELECT FORMAT(12,'X')

SQL SERVER : CREATE TABLE (ตัวอย่างการสร้าง Table ทีมี DataType แบบต่างๆ)

Post by Goborijung at 2022-03-26 11:47:40 | ID: 1426

CREATE TABLE books (
  id              INT           NOT NULL    IDENTITY    PRIMARY KEY,
  title           VARCHAR(100)  NOT NULL,
  primary_author  VARCHAR(100),
)

Create Table GOODS_NAME
(
	Goods_id char(10) default '' not null Primary key,
	Good_name char(30) default '' not null,
	cost_unit decimal(8,2) default 0 not null
)

Create Table CUS_NAME
(
	Cus_id char(5) default '' not null Primary key,
	Cus_name char(30) default '' not null
)

Create Table H_ORDER
(
	Order_no int default 0 not null Primary key,
	Cus_id char(5) default '' not null,
	Order_Date datetime default '2000-01-01' not null,
	Foreign key (Cus_id) References CUS_NAME(Cus_id)
)

Create Table  D_ORDER
(
	Order_no  int  default 0 not null,
	Goods_id  char(10)  default '' not null,
	Ord_date  datetime  default '2000-01-01' not null,
	Fin_date datetime default '2000-01-01' not null,
	Amount  decimal(10,2)  default 0 not null,
	COST_UNIT decimal(8,2) default 0 not null,
	TOT_PRC decimal(10,2) default 0 not null,
	 Primary key (Order_no,Goods_id),
	 Foreign key  (Order_no) References H_ORDER(Order_no),
	Foreign key (Goods_id) References GOODS_NAME(Goods_id)
)

Create Table M_ORDER
(
	Cus_id char(5) default '' not null,
	Goods_id char(10) default '' not null,
	Doc_date datetime default '2000-01-01' not null,
	Ord_date datetime default '2000-01-01' not null,
	Fin_date datetime default '2000-01-01' not null,
	Sys_date datetime default '2000-01-01' not null,
	Amount decimal(10,2) default 0 not null,
	cost_tot decimal(10,2) default 0 not null,
	Primary key(Cus_id,Goods_id,Doc_date,Ord_date,Fin_date)
)

Select * From GOODS_NAME
Select * From CUS_NAME
Select * From H_ORDER
Select * From D_ORDER
Select * From M_ORDER

SQL SERVER : Setup Tabs

Post by Goborijung at 2021-05-21 08:37:05 | ID: 1213

// How to Setup Tab
1. ไปที่ Menu Tools > Text Editor > XML > Tabs

SQL SERVER : Add ODBC Driver

Post by Goborijung at 2023-03-23 14:30:32 | ID: 1870

Controlpanel > เลือก View by: Small Icons > Windows Tools > ODBC Data Sources

SQL SERVER : ALL Function in MSSQL

Post by Goborijung at 2020-07-01 09:50:31 | ID: 644

https://www.w3schools.com/sql/sql_ref_sqlserver.asp

SQL SERVER : ASCII (Conver Number to Ascii) , CHAR (Conver Ascii to Number)

Post by Goborijung at 2023-08-16 11:18:52 | ID: 1962

Select 
ASCII(6)-5 as Number_to_Ascii --54 to 49 | 6-5 = 1 , 1 ใน Ascii คือ 49
,CHAR(49) as Ascii_to_Char_Number --1 | รหัส Ascii 49 คือเลข 1
,CHAR(ASCII(SUBSTRING(PASSWORD,1,1))-5) + CHAR(ASCII(SUBSTRING(PASSWORD,2,1))-5) + CHAR(ASCII(SUBSTRING(PASSWORD,3,1))-5) + CHAR(ASCII(SUBSTRING(PASSWORD,4,1))-5) as pwDecode
,'|' as [|],* From PDS_USER Where 1=1 And EMPID = '6501112'

SQL SERVER : Basic SQL

Post by Goborijung at 2020-06-26 12:41:24 | ID: 638

http://www.widebase.net/database/sql/sqlquery/sqlquery.shtml

SQL SERVER : Bypass Single Quote

Post by Goborijung at 2020-05-29 10:45:41 | ID: 591

Example

INSERT INTO my_table VALUES ('hi, my name''s tim')

Output : hi, my name's tim