The following select always crashes MaxDB. In the select there is one
view (LIS.all_techniques see below). Without this view select works
correct. If it will help we can try to send test database.
select * from (
SELECT
c.priority,
c.route,
a.scheduled_invest,
150739 scheduled_container,
CASE
WHEN 150739 = e.scheduled_container AND c.route =
e.route THEN 'containerinwork'
WHEN 150739 <> e.scheduled_container AND c.route =
e.route THEN 'sampleinwork'
ELSE NULL
END inwork,
c.name route_name,
a.invest_state,
d.technique_name,
w.device
FROM
LIS.scheduled_invests a,
LIS.rt_deviceinstances v,
LIS.rt_devices w,
LIS.sr_instancesofroute b,
LIS.sr_routes c,
LIS.all_techniques d,
LIS.sr_containersonroute e
WHERE
a.scheduled_sample = 822362
AND a.device_instance = v.device_instance
AND v.device = w.device
AND a.technique NOT IN (select technique FROM
LIS.sr_instancesofroute WHERE technique IS NOT NULL)
AND a.device_instance = b.device_instance
AND b.route = c.route
AND a.technique = d.technique
AND a.scheduled_sample = e.scheduled_sample (+)
AND c.route = e.route (+)
UNION ALL
SELECT
c.priority,
c.route,
a.scheduled_invest,
150739 scheduled_container,
CASE
WHEN 150739 = e.scheduled_container AND c.route =
e.route THEN 'containerinwork'
WHEN 150739 <> e.scheduled_container AND c.route =
e.route THEN 'sampleinwork'
ELSE NULL
END inwork,
c.name route_name,
a.invest_state,
d.technique_name,
w.device
FROM
LIS.scheduled_invests a,
LIS.rt_deviceinstances v,
LIS.rt_devices w,
LIS.sr_instancesofroute b,
LIS.sr_routes c,
LIS.all_techniques d,
LIS.sr_containersonroute e
WHERE
a.scheduled_sample = 822362
AND a.device_instance = v.device_instance
AND v.device = w.device
AND a.technique = b.technique
AND b.route = c.route
AND a.technique = d.technique
AND a.scheduled_sample = e.scheduled_sample (+)
AND c.route = e.route (+)
)
ORDER BY
1 desc, 2, 3
//
CREATE VIEW LIS.all_techniques
AS
SELECT
b.technique,
b.technique_type,
b.compound,
n.test_name || ' - ' || c.device_name || VALUE(' - ' ||
i.reagent_name, '') || ' (' || d.material_name || ')' technique_name,
b.technique_desc,
b.result_type,
b.active,
b.useful_volume,
b.rround,
b.test,
a.test_type,
a.test_code,
n.test_name,
c.device,
c.device_name,
d.material,
d.material_name,
d.unit material_unit,
i.reagent,
i.reagent_name,
l.gid
FROM
LIS.rt_techniques b,
LIS.rt_tests a,
LIS.rt_test_properties n,
LIS.rt_test_groups l,
LIS.rt_devices c,
LIS.rt_materials d,
LIS.rt_reagents i
WHERE
b.technique_type = 0
AND b.test = a.test
AND a.test = n.test
AND n.FromRevisionNo <= a.RevisionNo
AND a.RevisionNo < DECODE(n.ToRevisionNo, NULL, a.RevisionNo + 1,
n.ToRevisionNo)
AND a.test = l.test
AND b.device = c.device
AND b.material = d.material
AND b.reagent = i.reagent (+)
UNION ALL
SELECT
b.technique,
b.technique_type,
b.compound,
DECODE(b.technique_name, NULL, '', b.technique_name || ' - ') ||
c.device_name || VALUE(' - ' || i.reagent_name, '') || ' (' ||
d.material_name || ')' technique_name,
b.technique_desc,
b.result_type,
b.active,
b.useful_volume,
b.rround,
null test,
null test_type,
null test_code,
null test_name,
c.device,
c.device_name,
d.material,
d.material_name,
d.unit material_unit,
i.reagent,
i.reagent_name,
b.gid
FROM
LIS.rt_techniques b,
LIS.rt_devices c,
LIS.rt_materials d,
LIS.rt_reagents i
WHERE
b.technique_type = 1
AND b.device = c.device
AND b.material = d.material
AND b.reagent = i.reagent (+)
UNION ALL
SELECT
b.technique,
b.technique_type,
b.compound,
n.test_name technique_name,
b.technique_desc,
b.result_type,
b.active,
b.useful_volume,
b.rround,
b.test,
a.test_type,
a.test_code,
n.test_name,
null device,
null device_name,
null material,
null material_name,
null material_unit,
null reagent,
null reagent_name,
l.gid
FROM
LIS.rt_techniques b,
LIS.rt_tests a,
LIS.rt_test_properties n,
LIS.rt_test_groups l
WHERE
b.technique_type = 3
AND b.test = a.test
AND a.test = n.test
AND n.FromRevisionNo <= a.RevisionNo
AND a.RevisionNo < DECODE(n.ToRevisionNo, NULL, a.RevisionNo + 1,
n.ToRevisionNo)
AND a.test = l.test
UNION ALL
SELECT
b.technique,
b.technique_type,
b.compound,
b.technique_name technique_name,
b.technique_desc,
b.result_type,
b.active,
b.useful_volume,
b.rround,
null test,
null test_type,
null test_code,
null test_name,
null device,
null device_name,
null material,
null material_name,
null material_unit,
null reagent,
null reagent_name,
null gid
FROM
LIS.rt_techniques b
WHERE
b.technique_type = 4
--
MaxDB Discussion Mailing List
For list archives: http://lists.mysql.com/maxdb
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
