1. INSTR(源字符串, 要查找的字符串, 从第几个字符开始, 要找到第几个匹配的序号)
返回找到的位置,如果找不到则返回0)
2.
NVL函数的功能是实现空值的转换,根据第一个表达式的值是否为空值来返回响应的列名或表达式,主要用于对数据列上的空值进行处理,语法格式如:NVL( string1, replace_with)。
NVL(E1, E2)的功能为:如果E1为NULL,则函数返回E2,否则返回E1本身。但此函数有一定局限,所以就有了NVL2函数。
3.
以case开头,end结束。
when和then相当于“如果…那么…”,else相当于如果以上都不成立,那么就。
4.select a.lockid,s.carton_no,c.imei,
substr(c.message,
instr(c.message, ':', instr(c.message, 'IMSI'), 1) + 1,
instr(c.message, ';', instr(c.message, 'IMSI'), 1) -
instr(c.message, ':', instr(c.message, 'IMSI'), 1) - 1) as imsi,--c.message中查找imsi, 从第1个字符开始, 要找到第几个匹配的序号)
d.dn_no,
d.invoice_no as ponumber,
a.sn,
nvl('',
(select f.item_part_sn
from sajet.g_sn_keyparts f, sajet.sys_part a
where f.item_part_id = a.part_id
and upper(a.spec1) like 'PCBA%'
and f.serial_number = s.serial_number)) as PCB, '' as GRA,--查找为pcb的关键料件,没有的话返回为空
nvl('',
(select f.item_part_sn
from sajet.g_sn_keyparts f, sajet.sys_part a
where f.item_part_id = a.part_id
and upper(a.spec1) like 'BAT%'
and f.serial_number = s.serial_number)) as bat,--查找关键料件为BAT的,没有的话返回为空
'motorsn' as motorsn,
'switchone' as switchone,
'' as swt,
substr(c.message,
instr(c.message, ':', instr(c.message, 'HARDWARE'), 1) + 1,
instr(c.message, ';', instr(c.message, 'HARDWARE'), 1) -
instr(c.message, ':', instr(c.message, 'HARDWARE'), 1) - 1) as HARDWARE,--截取HARDWARE的值
substr(c.message,
instr(c.message, ':', instr(c.message, 'SW_VER'), 1) + 1,
instr(c.message, ';', instr(c.message, 'SW_VER'), 1) -
instr(c.message, ':', instr(c.message, 'SW_VER'), 1) - 1) as software,--截取sw-vwr的值
substr(c.message,
instr(c.message, ':', instr(c.message, 'BLE_VER'), 1) + 1,
instr(c.message, ';', instr(c.message, 'BLE_VER'), 1) -
instr(c.message, ':', instr(c.message, 'BLE_VER'), 1) - 1) as BLU,--截取ver的值
c.bt_mac,
substr(c.message,
instr(c.message, ':', instr(c.message, 'VOT_BAT'), 1) + 1,
instr(c.message, ';', instr(c.message, 'VOT_BAT'), 1) -
instr(c.message, ':', instr(c.message, 'VOT_BAT'), 1) - 1) as VOL,--截取bat的值
c.result,
case--开始
when (select count(0)
from sajet.g_sn_repair h
where h.serial_number = s.serial_number) > 0 then--如果维修sn>0,输出结果为yes,否则为no
'YES'
else
'NO'
end as re,--结束
c.endtime testdonetime,
i.update_time shiptime,
s.pallet_no,
'立讯',
d.ship_to,
d.dn_no,
sysdate
from sajet.g_sn_status s,
sajet.g_sn_mb a,
sajet.g_sn_test c,
sajet.g_dn_base d,
sajet.g_shipping_sn e,
sajet.G_DN_DETAIL i
where 1 = 1
and s.serial_number = a.sn
and s.serial_number = c.sn
and s.shipping_id = d.dn_id
and s.shipping_id = e.shipping_id
and s.serial_number = e.serial_number
and s.shipping_id = i.dn_id
--and s.serial_number='YCB2370969'
and d.dn_no = '8860166110';
--and a.sn='MB17220513WB'