Searching for sth like that?
PHP Code:
USE SRO_VT_SHARD
SELECT
chart.CharID,
chart.CharName16,
invent0.Slot as Slot,
CASE WHEN ref0.CodeName128 like 'DUMMY_OBJECT' THEN NULL ELSE ref0.CodeName128 END as 'ItemCode',
CASE WHEN ref0.CodeName128 like 'DUMMY_OBJECT' THEN NULL ELSE ref0.AssocFileIcon128 END as 'DDJ-Path',
CASE
WHEN ref0.CodeName128 like 'DUMMY_OBJECT' THEN NULL
WHEN invent0.Slot = 0 THEN 'Head'
WHEN invent0.Slot = 1 THEN 'Suit'
WHEN invent0.Slot = 2 THEN 'Shoulders'
WHEN invent0.Slot = 3 THEN 'Hands'
WHEN invent0.Slot = 4 THEN 'Legs'
WHEN invent0.Slot = 5 THEN 'Shoes'
WHEN invent0.Slot = 6 THEN 'Weapon'
WHEN invent0.Slot = 7 THEN 'Shield/Arrow'
WHEN invent0.Slot = 9 THEN 'Earring'
WHEN invent0.Slot = 10 THEN 'Necklace'
WHEN invent0.Slot = 11 THEN 'RingL'
WHEN invent0.Slot = 12 THEN 'RingR'
ELSE 'NO clue'
END as 'ItemType',
Name = es.EndTextString +
CASE
WHEN PATINDEX('%_11%_' + '%A_RARE',ref0.CodeName128) > 0 THEN ' (Seal of Nova)'
WHEN PATINDEX('%A_RARE',ref0.CodeName128) > 0 THEN ' (Seal of Star)'
WHEN PATINDEX('%B_RARE',ref0.CodeName128) > 0 THEN ' (Seal of Moon)'
WHEN PATINDEX('%C_RARE',ref0.CodeName128) > 0 THEN ' (Seal of Sun)'
ELSE ''
END,
items0.OptLevel as 'Item+',
'AdvE+' = CASE WHEN not exists(SELECT nOptValue FROM _BindingOptionWithItem WHERE nItemDBID = items0.ID64) THEN 0 ELSE bind.nOptValue END,
CASE WHEN ref0.CodeName128 like 'DUMMY_OBJECT' THEN NULL ELSE CAST(ref0.ReqLevel1 as varchar(5)) END as 'ItemLevel'
FROM _Inventory as invent0
JOIN _Items as items0 on invent0.ItemID = items0.ID64 AND ((invent0.Slot BETWEEN 0 AND 12) AND invent0.Slot != 8)
JOIN _RefObjCommon as ref0 on items0.RefItemID = ref0.ID
JOIN _Char as chart on invent0.CharID = chart.CharID
LEFT JOIN _BindingOptionWithItem as bind on items0.ID64 = bind.nItemDBID AND bind.bOptType = 2
LEFT JOIN C_EquipStrings as es on ref0.NameStrID128 = es.TextString
WHERE (invent0.Slot BETWEEN 0 AND 12) AND invent0.Slot != 8
GROUP BY chart.CharID, chart.CharName16, invent0.Slot, ref0.CodeName128, ref0.AssocFileIcon128,
es.EndTextString, items0.OptLevel, ref0.ReqLevel1, bind.nOptValue, items0.ID64
ORDER BY chart.CharID asc, invent0.Slot asc;
btw in case you want to try it out use..
PHP Code:
BULK INSERT dbo.C_EquipStrings FROM 'C:\textdata_equip&skill.txt'
..at first with the textdata_equip&skill.txt file of your current Media.pk2