Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Register Log in
  • Congratulations Mike Lewis on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

SUBQueries 1

Status
Not open for further replies.
JRCharlie

JRCharlie

Programmer
Jan 10, 2016
15
CA
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
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
 
Sort by date Sort by votes
A couple of questions:
1. What version of PSQL are you using?
2. When you run the query into the Grid and it takes 1 minute, how many records are you returning?
3. What indexes are on the tables, specifically on Orde_WB_main and OELI_WB_mask?

Mirtheil
 
Upvote 0 Downvote
Thanks Mirtheil for your quick reply.
First of all this still JRCharlie I had to create a new account because I was unable to login nor reset my password.
1st Im currently using ver12
2nd This query returns 200 records out of 49000
3rd OEli_WB_mask has 3 keys
KEY0 OELI_Order_No,OELI_Guid,Field_Name
KEY1 Mask, OELI_Order_No,OELI_Guid,Field_Name
Key2 FielName, Mask, OELI_Order_No,OELI_Guid,Field_Name

Orde_WB_Main has 52 Keys I'm not going to write all but will try to explain how they are set.
Ord_Order_NO, 2nd column
Ord_Order_NO, 3rd column
Ord_Order_NO, 4rth column and so on.


 
Upvote 0 Downvote
Hi Finely have it solved by understanding how the tables were indexed.
By joining the tables with the indexed columns, it executes quick and I don't get any time out errors.
 
Upvote 0 Downvote
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top