Advertisement
tok124

SQL Query get item stats

Nov 4th, 2017
112
Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
  1. INSERT INTO item_template (
  2. entry, class, subclass,
  3. name, displayid, quality,
  4. inventorytype, StatsCount,
  5. stat_type1, stat_value1,
  6. stat_type2, stat_value2,
  7. stat_type3, stat_value3,
  8. stat_type4, stat_value4,
  9. stat_type5, stat_value5,
  10. stat_type6, stat_value6,
  11. stat_type7, stat_value7,
  12. stat_type8, stat_value8,
  13. stat_type9, stat_value9,
  14. stat_type10, stat_value10,
  15. dmg_min1, dmg_max1, armor,
  16. delay, ammo_type, RangedModRange,
  17. bonding, description, Material,
  18. sheath, itemset, socketColor_1,
  19. socketColor_2, socketColor_3,
  20. socketContent_1, socketContent_2,
  21. socketContent_3, socketBonus)
  22. SELECT MAX(entry)+1, class, subclass, name, displayid, quality, inventoryType, StatsCount, stat_type1,
  23.     CASE inventoryType
  24.         WHEN 1 THEN (SELECT stat_value1*300 FROM item_template WHERE stat_type1 > 0 AND inventoryType = 1 AND name LIKE "%sanctified%") -- Head
  25.         WHEN 2 THEN (SELECT stat_value1*300 FROM item_template WHERE stat_type1 > 0 AND inventoryType = 2 AND name LIKE "%sanctified%") -- Shoulder
  26.         WHEN 2 THEN (SELECT stat_value1*300 FROM item_template WHERE stat_type1 > 0 AND inventoryType = 2 AND name LIKE "%sanctified%") -- Chest
  27.         WHEN 2 THEN (SELECT stat_value1*300 FROM item_template WHERE stat_type1 > 0 AND inventoryType = 2 AND name LIKE "%sanctified%") -- Legs
  28.         WHEN 2 THEN (SELECT stat_value1*300 FROM item_template WHERE stat_type1 > 0 AND inventoryType = 2 AND name LIKE "%sanctified%") -- Hands
  29.         ELSE 0
  30.     END, stat_type2,
  31.     CASE inventoryType
  32.         WHEN 1 THEN (SELECT stat_value2*300 FROM item_template WHERE stat_type2 > 0 AND inventoryType = 1 AND name LIKE "%sanctified%") -- Head
  33.         WHEN 2 THEN (SELECT stat_value2*300 FROM item_template WHERE stat_type2 > 0 AND inventoryType = 2 AND name LIKE "%sanctified%") -- Shoulder
  34.         WHEN 2 THEN (SELECT stat_value2*300 FROM item_template WHERE stat_type2 > 0 AND inventoryType = 2 AND name LIKE "%sanctified%") -- Chest
  35.         WHEN 2 THEN (SELECT stat_value2*300 FROM item_template WHERE stat_type2 > 0 AND inventoryType = 2 AND name LIKE "%sanctified%") -- Legs
  36.         WHEN 2 THEN (SELECT stat_value2*300 FROM item_template WHERE stat_type2 > 0 AND inventoryType = 2 AND name LIKE "%sanctified%") -- Hands
  37.         ELSE 0
  38.     END, stat_type3,
  39.     CASE inventoryType
  40.         WHEN 1 THEN (SELECT stat_value3*300 FROM item_template WHERE stat_type3 > 0 AND inventoryType = 1 AND name LIKE "%sanctified%") -- Head
  41.         WHEN 2 THEN (SELECT stat_value3*300 FROM item_template WHERE stat_type3 > 0 AND inventoryType = 2 AND name LIKE "%sanctified%") -- Shoulder
  42.         WHEN 2 THEN (SELECT stat_value3*300 FROM item_template WHERE stat_type3 > 0 AND inventoryType = 2 AND name LIKE "%sanctified%") -- Chest
  43.         WHEN 2 THEN (SELECT stat_value3*300 FROM item_template WHERE stat_type3 > 0 AND inventoryType = 2 AND name LIKE "%sanctified%") -- Legs
  44.         WHEN 2 THEN (SELECT stat_value3*300 FROM item_template WHERE stat_type3 > 0 AND inventoryType = 2 AND name LIKE "%sanctified%") -- Hands
  45.         ELSE 0
  46.     END, stat_type4,
  47.     CASE inventoryType
  48.         WHEN 1 THEN (SELECT stat_value4*300 FROM item_template WHERE stat_type4 > 0 AND inventoryType = 1 AND name LIKE "%sanctified%") -- Head
  49.         WHEN 2 THEN (SELECT stat_value4*300 FROM item_template WHERE stat_type4 > 0 AND inventoryType = 2 AND name LIKE "%sanctified%") -- Shoulder
  50.         WHEN 2 THEN (SELECT stat_value4*300 FROM item_template WHERE stat_type4 > 0 AND inventoryType = 2 AND name LIKE "%sanctified%") -- Chest
  51.         WHEN 2 THEN (SELECT stat_value4*300 FROM item_template WHERE stat_type4 > 0 AND inventoryType = 2 AND name LIKE "%sanctified%") -- Legs
  52.         WHEN 2 THEN (SELECT stat_value4*300 FROM item_template WHERE stat_type4 > 0 AND inventoryType = 2 AND name LIKE "%sanctified%") -- Hands
  53.         ELSE 0
  54.     END, stat_type5,
  55.     CASE inventoryType
  56.         WHEN 1 THEN (SELECT stat_value5*300 FROM item_template WHERE stat_type5 > 0 AND inventoryType = 1 AND name LIKE "%sanctified%") -- Head
  57.         WHEN 2 THEN (SELECT stat_value5*300 FROM item_template WHERE stat_type5 > 0 AND inventoryType = 2 AND name LIKE "%sanctified%") -- Shoulder
  58.         WHEN 2 THEN (SELECT stat_value5*300 FROM item_template WHERE stat_type5 > 0 AND inventoryType = 2 AND name LIKE "%sanctified%") -- Chest
  59.         WHEN 2 THEN (SELECT stat_value5*300 FROM item_template WHERE stat_type5 > 0 AND inventoryType = 2 AND name LIKE "%sanctified%") -- Legs
  60.         WHEN 2 THEN (SELECT stat_value5*300 FROM item_template WHERE stat_type5 > 0 AND inventoryType = 2 AND name LIKE "%sanctified%") -- Hands
  61.         ELSE 0
  62.     END, stat_type6,
  63.     CASE inventoryType
  64.         WHEN 1 THEN (SELECT stat_value6*300 FROM item_template WHERE stat_type6 > 0 AND inventoryType = 1 AND name LIKE "%sanctified%") -- Head
  65.         WHEN 2 THEN (SELECT stat_value6*300 FROM item_template WHERE stat_type6 > 0 AND inventoryType = 2 AND name LIKE "%sanctified%") -- Shoulder
  66.         WHEN 2 THEN (SELECT stat_value6*300 FROM item_template WHERE stat_type6 > 0 AND inventoryType = 2 AND name LIKE "%sanctified%") -- Chest
  67.         WHEN 2 THEN (SELECT stat_value6*300 FROM item_template WHERE stat_type6 > 0 AND inventoryType = 2 AND name LIKE "%sanctified%") -- Legs
  68.         WHEN 2 THEN (SELECT stat_value6*300 FROM item_template WHERE stat_type6 > 0 AND inventoryType = 2 AND name LIKE "%sanctified%") -- Hands
  69.         ELSE 0
  70.     END, stat_type7,
  71.     CASE inventoryType
  72.         WHEN 1 THEN (SELECT stat_value7*300 FROM item_template WHERE stat_type7 > 0 AND inventoryType = 1 AND name LIKE "%sanctified%") -- Head
  73.         WHEN 2 THEN (SELECT stat_value7*300 FROM item_template WHERE stat_type7 > 0 AND inventoryType = 2 AND name LIKE "%sanctified%") -- Shoulder
  74.         WHEN 2 THEN (SELECT stat_value7*300 FROM item_template WHERE stat_type7 > 0 AND inventoryType = 2 AND name LIKE "%sanctified%") -- Chest
  75.         WHEN 2 THEN (SELECT stat_value7*300 FROM item_template WHERE stat_type7 > 0 AND inventoryType = 2 AND name LIKE "%sanctified%") -- Legs
  76.         WHEN 2 THEN (SELECT stat_value7*300 FROM item_template WHERE stat_type7 > 0 AND inventoryType = 2 AND name LIKE "%sanctified%") -- Hands
  77.         ELSE 0
  78.     END, stat_type8,
  79.     CASE inventoryType
  80.         WHEN 1 THEN (SELECT stat_value8*300 FROM item_template WHERE stat_type8 > 0 AND inventoryType = 1 AND name LIKE "%sanctified%") -- Head
  81.         WHEN 2 THEN (SELECT stat_value8*300 FROM item_template WHERE stat_type8 > 0 AND inventoryType = 2 AND name LIKE "%sanctified%") -- Shoulder
  82.         WHEN 2 THEN (SELECT stat_value8*300 FROM item_template WHERE stat_type8 > 0 AND inventoryType = 2 AND name LIKE "%sanctified%") -- Chest
  83.         WHEN 2 THEN (SELECT stat_value8*300 FROM item_template WHERE stat_type8 > 0 AND inventoryType = 2 AND name LIKE "%sanctified%") -- Legs
  84.         WHEN 2 THEN (SELECT stat_value8*300 FROM item_template WHERE stat_type8 > 0 AND inventoryType = 2 AND name LIKE "%sanctified%") -- Hands
  85.         ELSE 0
  86.     END, stat_type9,
  87.     CASE inventoryType
  88.         WHEN 1 THEN (SELECT stat_value9*300 FROM item_template WHERE stat_type9 > 0 AND inventoryType = 1 AND name LIKE "%sanctified%") -- Head
  89.         WHEN 2 THEN (SELECT stat_value9*300 FROM item_template WHERE stat_type9 > 0 AND inventoryType = 2 AND name LIKE "%sanctified%") -- Shoulder
  90.         WHEN 2 THEN (SELECT stat_value9*300 FROM item_template WHERE stat_type9 > 0 AND inventoryType = 2 AND name LIKE "%sanctified%") -- Chest
  91.         WHEN 2 THEN (SELECT stat_value9*300 FROM item_template WHERE stat_type9 > 0 AND inventoryType = 2 AND name LIKE "%sanctified%") -- Legs
  92.         WHEN 2 THEN (SELECT stat_value9*300 FROM item_template WHERE stat_type9 > 0 AND inventoryType = 2 AND name LIKE "%sanctified%") -- Hands
  93.         ELSE 0
  94.     END, stat_type10,
  95.     CASE inventoryType
  96.         WHEN 1 THEN (SELECT stat_value10*300 FROM item_template WHERE stat_type10 > 0 AND inventoryType = 1 AND name LIKE "%sanctified%") -- Head
  97.         WHEN 2 THEN (SELECT stat_value10*300 FROM item_template WHERE stat_type10 > 0 AND inventoryType = 2 AND name LIKE "%sanctified%") -- Shoulder
  98.         WHEN 2 THEN (SELECT stat_value10*300 FROM item_template WHERE stat_type10 > 0 AND inventoryType = 2 AND name LIKE "%sanctified%") -- Chest
  99.         WHEN 2 THEN (SELECT stat_value10*300 FROM item_template WHERE stat_type10 > 0 AND inventoryType = 2 AND name LIKE "%sanctified%") -- Legs
  100.         WHEN 2 THEN (SELECT stat_value10*300 FROM item_template WHERE stat_type10 > 0 AND inventoryType = 2 AND name LIKE "%sanctified%") -- Hands
  101.         ELSE 0
  102.     END,
  103. dmg_min1 * 300, dmg_max1 * 300, armor,
  104. delay, ammo_type, RangedModRange,
  105. bonding, "Tier1", Material,
  106. sheath, itemset, socketColor_1,
  107. socketColor_2, socketColor_3,
  108. socketContent_1, socketContent_2,
  109. socketContent_3, socketBonus FROM item_template WHERE name LIKE "%sanctified%";
Advertisement
Advertisement
Advertisement
RAW Paste Data Copied
Advertisement