New Query to Replace QP06

[Former Member]

Hi,

I am attempting to create a very basic query in SQVI to replace QP06, due to its poor layout and lack of fields.

So far i only have two tables joined MAPL and QMAT

I want to identify all materials that have inspection type 01 (QMAT-ART) specified and the active box (QMAT-AKTIV) ticked but have not been assigned to an inspection plan (QMAT-PPL).

My query lists all materials that have been assigned to an inspection plan but will not list materials which haven't.

In SQVI, how do generate an output when the field is blank?

Thanks in advance

Ben

[anand_rao3 · Accepted solution]

Hi Ben,

I doubt whether this can be addressed through query as this requirement demands for task list usage consideration also.

For an example, in QMAT you get the material activated with 01 inspection type. If you get this material in MAPL with task list type Q your conclusion would be "task list exists"

I look this slight differently. Even if you get this material in MAPL that doesn't mean that plan exists. You must have to check with usage also. For 01 inspection type ideally usage should be 5. But someone has created plan with usage 9 still that will reflect in MAPL. This may lead to false conclusion.

I think, you should rely on QP06. It can be downloaded in excel. Simply filter out or delete unwanted columns / rows. Otherwise Z report would be another solution with consideration of usage (PLKO table) rather than query.

Regards,

Anand Rao

[Former Member · Accepted solution]

Hi Anand,

Thanks for the reply.

I have managed to solve it and it was quite simple in the end.

I simply joined QMAT to  MAPL with Left outer joins and in the selection field I searched for Insp Type 01, Active Key = X and NULL values in the "Key for Task List Group" (MAPL-PLNNR) field.

The query only takes 20 seconds to run compared to X minutes for QP06.

Cheers

Ben