By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,255 Members | 2,717 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,255 IT Pros & Developers. It's quick & easy.

Building query with sub-queries

P: 12

I'm creating a query (queries more like) to build data for upload into a SAP system. For a given material I require a row of data defined, and for the same material another row with one field changed, repeat...

Normally I would just create a query, copy it, make change, save as and then create a final query utilising the UNION function. While this may appear a poor way of defining my queries, it works & I'm generally happy with performance.

However, I have been given a new requirement which requires me to do this several times & therefore my UNION query would end up with 10 queries within... there must be a better way!

From my readings, I believe that creating sub-queries are the way to go... can anyone give me a 101 on sub-query creation (if this is in fact the best way to proceed)?

Sample of final data:
Matl Plant SLoc ...
0001 AB11 AB01 ...
0001 AB11 AB02 ...
0001 AB11 AB03 ...
0002 AB11 AB01 ...
0002 AB11 AB02 ...
0002 AB11 AB03 ...

The SLoc data is defined where if a Matl exists in Plant AB11, then create SLoc AB01, AB02, AB03 and so on.

Here is an example
Expand|Select|Wrap|Line Numbers
  1. SELECT [Status 4 Materials - HIBE NZ02].MaterialUL, [Status 4 Materials - HIBE NZ02].Plant, '0201' AS SLoc
  2. FROM [Status 4 Materials - HIBE NZ02];
Sep 11 '07 #1
Share this Question
Share on Google+
2 Replies

P: 12
Actually, I just worked this out myself...
Expand|Select|Wrap|Line Numbers
  2. (SELECT [Status 4 Materials - HIBE NZ02].MaterialUL, [Status 4 Materials - HIBE NZ02].Plant, '0201' AS SLoc
  3. FROM [Status 4 Materials - HIBE NZ02])
  4. UNION
  5. (SELECT [Status 4 Materials - HIBE NZ02].MaterialUL, [Status 4 Materials - HIBE NZ02].Plant, '0202' AS SLoc
  6. FROM [Status 4 Materials - HIBE NZ02]);
But the real pain is when I then need to add other conditions, this doesn't work...
Expand|Select|Wrap|Line Numbers
  2. (SELECT [Status 4 Materials - HIBE NZ02].MESC, [Status 4 Materials - HIBE NZ02].MaterialUL, [Status 4 Materials - HIBE NZ02].Plant, SLoc: 0201', IIf(IsNull([MRP_ND].[PARTNO]),Null,'001') AS Picking_area
  3. FROM [Status 4 Materials - HIBE NZ02] LEFT JOIN MRP_ND ON [Status 4 Materials - HIBE NZ02].MESC = MRP_ND.PARTNO)
  4. UNION
  5. (SELECT [Status 4 Materials - HIBE NZ02].MESC, [Status 4 Materials - HIBE NZ02].MaterialUL, [Status 4 Materials - HIBE NZ02].Plant, SLoc: 0202', IIf(IsNull([MRP_ND].[PARTNO]),Null,'001') AS Picking_area
  6. FROM [Status 4 Materials - HIBE NZ02] LEFT JOIN MRP_ND ON [Status 4 Materials - HIBE NZ02].MESC = MRP_ND.PARTNO)
  7. UNION
  8. (SELECT [Status 4 Materials - HIBE NZ02].MESC, [Status 4 Materials - HIBE NZ02].MaterialUL, [Status 4 Materials - HIBE NZ02].Plant, SLoc: 0203', IIf(IsNull([MRP_ND].[PARTNO]),Null,'001') AS Picking_area
  9. FROM [Status 4 Materials - HIBE NZ02] LEFT JOIN MRP_ND ON [Status 4 Materials - HIBE NZ02].MESC = MRP_ND.PARTNO);
And I still need to work through other plants: [Status 4 Materials - HIBE NZ03].MESC, [Status 4 Materials - HIBE NZ04].MESC...
Sep 11 '07 #2

Expert Mod 10K+
P: 14,534
OK, I'm a little confused. Can we go back to the logic of what you are trying to do.

What is the structure of your table?

Here is an example of how to post table MetaData :
Table Name=tblBookings
Expand|Select|Wrap|Line Numbers
  1. Field; Type; IndexInfo
  2. StudentID; Autonumber; PK
  3. Family; String; FK
  4. Name; String
  5. University; String; FK
  6. Mark; Numeric
  7. LastAttendance; Date/Time
Next can you explain again exactly what you want to achieve with your query from a logic point of view.
Sep 13 '07 #3

Post your reply

Sign in to post your reply or Sign up for a free account.