×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Jobs

SUBQueries

SUBQueries

SUBQueries

(OP)
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 

RE: SUBQueries

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
http://www.mirtheil.com

RE: SUBQueries

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.


RE: SUBQueries

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.

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close