select
TypeNo,
Type=left(TypeNo,1),
DocNo,
HasDocNo=(case when DocNo is null then 'No' else 'Yes' end)
from
表
TypeNo,
Type=left(TypeNo,1),
DocNo,
HasDocNo=(case when DocNo is null then 'No' else 'Yes' end)
from
表
TypeNo=TypeNo,
Type=left(TypeNo,1),
DocNo=DocNo,
HasDocNo=CASE WHEN DocNo is not null then 'YES' else 'NO' END
from 表
typeNo,
left(typeNo,1),
Isnull(DocNo,''),
HasDocNo = case DocNo when null then 'No' else 'Yes' end
from 表
TypeNo,
Type=left(TypeNo,1),
DocNo,
HasDocNo=(case DocNo when NULL then 'NO' else 'YES' end)
from 表
TypeNo TypeNo,
Type left(TypeNo,1),
DocNo DocNo,
HasDocNo CASE WHEN DocNo is not null then 'YES' else 'NO' END
from 表
TypeNo DocNo
---------------------
AAR-001 123456
AAR-002 123789
AAR-003
BBO-0004-01 963258
BBO-0003
BBO-0005 741258
表2:
TypeNo Date
---------------------
AAR-001 2006-01-01
AAR-002 2006-01-02
AAR-003 2006-01-03
BBO-0004-01 2006-01-04
BBO-0003 2006-01-05
BBO-0005 2006-01-06
求一sql语句,运行结果是:
TypeNo Type DocNo HasDocNo Date
------------------------------------------------------------
AAR-001 A 123456 Yes 2006-01-01
AAR-002 A 123789 Yes 2006-01-02
AAR-003 A No 2006-01-03
BBO-0004-01 B 963258 Yes 2006-01-04
BBO-0003 B No 2006-01-05
BBO-0005 B 741258 Yes 2006-01-06
case when docno is null then 'No' else 'Yes' end as hasdocno,date
from 表1 a inner join 表2 b on a.typeno=b.typeno
TypeNo=TypeNo,
SUBSTR(TypeNo,1,1) as Type,
DocNo=DocNo ,
DECODE(NVL(DocNo,0),0,'No','Yes') as HasDocNo
from
表
select
TypeNo,
SUBSTR(TypeNo,1,1) as Type,
DocNo,
DECODE(NVL(DocNo,0),0,'No','Yes') as HasDocNo
from
表9i以上版本:select
TypeNo,
SUBSTR(TypeNo,1,1) as Type,
DocNo,
(CASE WHEN DocNo IS NULL THEN 'No' ELSE 'Yes' END) as HasDocNo
from
表