/* ************************************************** ************************ */
/* */
/* ::: :::::::: */
/* 94 View item.sql :+: :+: :+: */
/* +:+ +:+ +:+ */
/* By: Gangor <
[Only registered and activated users can see links. Click Here To Register...]> +#+ +:+ +#+ */
/* +#+#+#+#+#+ +#+ */
/* Created: 2017/10/25 00:01:34 by Gangor #+# #+# */
/* Updated: 2017/10/25 00:01:34 by Gangor ### ########.fr */
/* */
/* ************************************************** ************************ */
USE [Arcadia94]
GO
/****** Object: View [dbo].[ItemReferenceResource] Script Date: 25/10/2017 00:01:34 ******/
IF OBJECT_ID('dbo.ItemReferenceResource') IS NOT NULL
BEGIN
DROP VIEW [dbo].[ItemReferenceResource]
END
/****** Object: View [dbo].[ItemReferenceResource] Script Date: 25/10/2017 00:01:34 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE VIEW [dbo].[ItemReferenceResource]
AS
WITH subrequest AS
(
SELECT id, '1' [type], reference FROM ItemResource
unpivot(
reference for referencies in (model_00, model_01, model_02, model_03, model_04, model_05, model_06, model_07, model_08, model_09, model_10, model_11, model_12, model_13, model_14, model_15, model_16, model_17)
) unpiv
UNION
SELECT id, '2' [type], reference FROM ItemResource
unpivot(
reference for referencies in (texture_filename)
) unpiv
UNION
SELECT id, '3' [type], reference FROM ItemResource
unpivot(
reference for referencies in (drop_type)
) unpiv
UNION
SELECT id, '4' [type], reference FROM ItemResource
unpivot(
reference for referencies in (icon_file_name)
) unpiv
)
SELECT
ROW_NUMBER() OVER(ORDER BY min(id), min(type)) - 1 [id],
reference
FROM subrequest
GROUP BY reference
ORDER BY min(id), min(type)
OFFSET 0 ROWS
GO
USE [Arcadia94]
GO
/****** Object: View [dbo].[Item] Script Date: 25/10/2017 00:01:34 ******/
IF OBJECT_ID('dbo.Item') IS NOT NULL
BEGIN
DROP VIEW [dbo].[Item]
END
/****** Object: View [dbo].[Item] Script Date: 25/10/2017 00:01:34 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE VIEW [dbo].[Item]
AS
SELECT [id]
,[name_id]
,[tooltip_id]
,[type]
,[group]
,[class]
,[wear_type]
,[set_id]
,[set_part_flag]
,[grade]
,[rank]
,[level]
,[enhance]
,[socket]
,[status_flag]
,[limit_deva]
,[limit_asura]
,[limit_gaia]
,[job_depth]
,[limit_fighter]
,[limit_hunter]
,[limit_magician]
,[limit_summoner]
,[use_min_level]
,[use_max_level]
,[target_min_level]
,[target_max_level]
,[range]
,[weight]
,[price]
,[huntaholic_point]
,[arena_point]
,[ethereal_durability]
,[endurance]
,[material]
,[summon_id]
,[item_use_flag]
,[available_period]
,[decrease_type]
,[throw_range]
,[base_type_0]
,[base_var1_0]
,[base_var2_0]
,[base_type_1]
,[base_var1_1]
,[base_var2_1]
,[base_type_2]
,[base_var1_2]
,[base_var2_2]
,[base_type_3]
,[base_var1_3]
,[base_var2_3]
,[opt_type_0]
,[opt_var1_0]
,[opt_var2_0]
,[opt_type_1]
,[opt_var1_1]
,[opt_var2_1]
,[opt_type_2]
,[opt_var1_2]
,[opt_var2_2]
,[opt_type_3]
,[opt_var1_3]
,[opt_var2_3]
,i.[effect_id]
,[enhance_id]
,[skill_id]
,[state_id]
,[state_level]
,[state_time]
,[cool_time]
,[cool_time_group]
,[model_type_dem]
,[model_type_def]
,[model_type_asm]
,[model_type_asf]
,[model_type_gam]
,[model_type_gaf]
,[deco_model_change]
, ( select id from dbo.ItemRef where reference = model_00) as model_00
,( select id from dbo.ItemRef where reference = model_01) as model_01
,( select id from dbo.ItemRef where reference = model_02) as model_02
,( select id from dbo.ItemRef where reference = model_03) as model_03
,( select id from dbo.ItemRef where reference = model_04) as model_04
,( select id from dbo.ItemRef where reference = model_05) as model_05
,( select id from dbo.ItemRef where reference = model_06) as model_06
,( select id from dbo.ItemRef where reference = model_07) as model_07
,( select id from dbo.ItemRef where reference = model_08) as model_08
,( select id from dbo.ItemRef where reference = model_09) as model_09
,( select id from dbo.ItemRef where reference = model_10) as model_10
,( select id from dbo.ItemRef where reference = model_11) as model_11
,( select id from dbo.ItemRef where reference = model_12) as model_12
,( select id from dbo.ItemRef where reference = model_13) as model_13
,( select id from dbo.ItemRef where reference = model_14) as model_14
,( select id from dbo.ItemRef where reference = model_15) as model_15
,( select id from dbo.ItemRef where reference = model_16) as model_16
,( select id from dbo.ItemRef where reference = model_17) as model_17
,( select id from dbo.ItemRef where reference = [texture_filename]) as texture_filename
,( select id from dbo.ItemRef where reference = [drop_type]) as drop_type
,[icon_id]
,( select id from dbo.ItemRef where reference = [icon_file_name]) as icon_file_name
,[script_text]
,[2nd_array]
,[group_bit_set]
,ISNULL([enhance01].[effect_id], 0) [effect01]
,ISNULL([enhance02].[effect_id], 0) [effect02]
,ISNULL([enhance01].[value_01], 0) [value1_1]
,ISNULL([enhance01].[value_05], 0) [value1_2]
,ISNULL([enhance01].[value_09], 0) [value1_3]
,ISNULL([enhance01].[value_13], 0) [value1_4]
,ISNULL([enhance01].[value_17], 0) [value1_5]
,ISNULL([enhance01].[value_21], 0) [value1_6]
,ISNULL([enhance01].[value_22], 0) [value1_7]
,ISNULL([enhance01].[value_23], 0) [value1_8]
,ISNULL([enhance01].[value_24], 0) [value1_9]
,ISNULL([enhance01].[value_25], 0) [value1_10]
,ISNULL([enhance02].[value_01], 0) [value2_1]
,ISNULL([enhance02].[value_05], 0) [value2_2]
,ISNULL([enhance02].[value_09], 0) [value2_3]
,ISNULL([enhance02].[value_13], 0) [value2_4]
,ISNULL([enhance02].[value_17], 0) [value2_5]
,ISNULL([enhance02].[value_21], 0) [value2_6]
,ISNULL([enhance02].[value_22], 0) [value2_7]
,ISNULL([enhance02].[value_23], 0) [value2_8]
,ISNULL([enhance02].[value_24], 0) [value2_9]
,ISNULL([enhance02].[value_25], 0) [value2_10]
FROM ItemResource i
LEFT OUTER JOIN dbo.EnhanceEffectResource enhance01 on enhance01.sid = enhance_id and enhance01.sub_id = 1
LEFT OUTER JOIN dbo.EnhanceEffectResource enhance02 on enhance02.sid = enhance_id and enhance02.sub_id = 2
GO
Using wrong views, original have bad coallation: