Alexey Gaidukov wrote:
>
> 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.
>
This is the command reproducing the core with trace-back you sent 5 days
ago where
a061copy_colinfo +0x005c
could be found in?
As this select does not look that easy and so many tables/views are
concerned it would be really helpful to see (if you don't like to send
to the whole list send it to me directly)
- the table definitions of all tables concerned, even those in the
FROM-close of LIS.all_techniques (and other views, if there are some
more)
- the info why there is a select * from around the main select?
- if the problem occurs, too, if you avoid this superfluous (and for
performance reasons bad) select * from around the main select
- the info which SQLMODE and which client-tool you use or if this select
is part of a dbproc/function
Elke
SAP Labs Berlin
> 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]
--
MaxDB Discussion Mailing List
For list archives: http://lists.mysql.com/maxdb
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
