OELI_Order_No| Oeli_Guid| Field_name | Mask
014587 | asd456gf | PO | 1457
014567 |asd456gf | Eta |2017/11/11
Hi have a table in this format with 50+ different Field_Name for one Order some orders could have more then others.
I Would like Do a Select that would put each Field name in a column such as.
OELI_Order_No| Oeli_Guid| PO | ETA
014587 | asd456gf | 1457 |2017/11/11
this query Works but it is slow as you can see I only Selecting 4 field_names plus and it takes about 1 min in pervasive Grid.
is there a better way to Query this table.
Thanks
014587 | asd456gf | PO | 1457
014567 |asd456gf | Eta |2017/11/11
Hi have a table in this format with 50+ different Field_Name for one Order some orders could have more then others.
I Would like Do a Select that would put each Field name in a column such as.
OELI_Order_No| Oeli_Guid| PO | ETA
014587 | asd456gf | 1457 |2017/11/11
this query Works but it is slow as you can see I only Selecting 4 field_names plus and it takes about 1 min in pervasive Grid.
is there a better way to Query this table.
Thanks
Code:
SELECT
Orde_WB_main.ORD_ORDER_NO AS Number,
Orde_WB_main.Shipped,
cm.MASK AS ColourMach,
ct.MASK AS CounterTops,
SubString(ct.MASK, 12, 1) AS STD_top,
SubString(ct.MASK, 26, 1) AS cst_top,
SubString(ct.MASK, 37, 2) AS NO_top,
SubString(ct.MASK, 53, 1) AS TopbyCW,
SubString(ct.MASK, 62, 1) AS CWDoors,
deta.MASK AS DoorETA,
dbox.MASK AS DrawerBox_ETA
FROM
Orde_WB_main
LEFT JOIN (SELECT
OELI_WB_mask.OELI_ORDER_NO AS oe,
OELI_WB_mask.OELI_GUID AS link,
OELI_WB_mask.MASK
FROM
OELI_WB_mask
WHERE
OELI_WB_mask.FIELD_NAME = 'ColourMatc') cm ON cm.oe = Orde_WB_main.ORD_ORDER_NO
LEFT JOIN (SELECT
OELI_WB_mask.OELI_ORDER_NO AS oe,
OELI_WB_mask.OELI_GUID AS link,
OELI_WB_mask.MASK
FROM
OELI_WB_mask
WHERE
OELI_WB_mask.FIELD_NAME = 'CountTops') ct ON ct.link = cm.link
LEFT JOIN (SELECT
OELI_WB_mask.OELI_ORDER_NO AS oe,
OELI_WB_mask.OELI_GUID AS link,
OELI_WB_mask.MASK
FROM
OELI_WB_mask
WHERE
OELI_WB_mask.FIELD_NAME = 'DoorETA') deta ON deta.link = cm.link
LEFT JOIN (SELECT
OELI_WB_mask.OELI_ORDER_NO AS oe,
OELI_WB_mask.OELI_GUID AS link,
OELI_WB_mask.MASK
FROM
OELI_WB_mask
WHERE
OELI_WB_mask.FIELD_NAME = 'DoorETA') dbox ON dbox.link = cm.link
WHERE
cm.oe = Number AND
ct.oe = Number AND
deta.oe = Number AND
dbox.oe = Number