Portal | Manuals | References | Downloads | Info | Programs | JCLs | Master the Mainframes
IBM Mainframe Computers Forums Index
 
Register
 
IBM Mainframe Computers Forums Index Mainframe: Search IBM Mainframe Forum: FAQ Memberlist Usergroups Profile Log in to check your private messages Log in
 

 

Query required to pull common locations

 
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2
View previous topic :: :: View next topic  
Author Message
sumivel

New User


Joined: 13 Sep 2005
Posts: 18

PostPosted: Tue Jan 10, 2012 1:58 pm    Post subject: Query required to pull common locations
Reply with quote

My input file will have ID and DEPT using these values I have to pull product id's from Product table and for those selected products I need Common locations for the product.

Example
I/P file

11222 dep1 ,dep2 , dep3

Product table:
11222 3 dep1
11222 4 dep2
11222 5 dep3


Location table
11222 3 loc1
11222 3 loc2
11222 3 loc3
11222 4 loc2
11222 4 loc3
11222 4 loc4
11222 5 loc5
11222 5 loc6
11222 5 loc2

I need a Query to pull the common location for the products i.e for above scenario the query should return loc2

Your support means a lot.
Back to top
View user's profile Send private message

vasanthz

Global Moderator


Joined: 28 Aug 2007
Posts: 1445
Location: Azeroth

PostPosted: Tue Jan 10, 2012 2:06 pm    Post subject:
Reply with quote

Quote:
for above scenario the query should return loc2

Please explain the logic involved in a more descriptive way.
Edited: forget it. got the logic

What have you tried so far?
Back to top
View user's profile Send private message
Pandora-Box

Moderator


Joined: 07 Sep 2006
Posts: 1529
Location: Andromeda Galaxy

PostPosted: Tue Jan 10, 2012 3:36 pm    Post subject:
Reply with quote

The logic looks to be straight forward and having more experience in the forum you should have cracked it by making use of vicarious experiences
Back to top
View user's profile Send private message
sumivel

New User


Joined: 13 Sep 2005
Posts: 18

PostPosted: Tue Jan 10, 2012 4:21 pm    Post subject:
Reply with quote

I don't have vicarious experiences in DB2 queries . so, Mr. Prem Stop judging people without even knowing them.

Since I don't know how to pull the locations I decided to take help from this Site. Fine.. There's whole lot of people outside.

Thank You
Back to top
View user's profile Send private message
Pandora-Box

Moderator


Joined: 07 Sep 2006
Posts: 1529
Location: Andromeda Galaxy

PostPosted: Tue Jan 10, 2012 4:34 pm    Post subject:
Reply with quote

Agreed icon_biggrin.gif

Well Vicarious means experiencing at second hand to be precise learning from others

I go by name ( Not be rude)

But still to reiterate you never said what have you have tried and I checked your earlier posts I found you have worked on Multiple fetch rows and to my knowledge this is much simpler than that
Back to top
View user's profile Send private message
dick scherrer

Site Director


Joined: 23 Nov 2006
Posts: 19270
Location: Inside the Matrix

PostPosted: Wed Jan 11, 2012 5:43 am    Post subject:
Reply with quote

Hello,

Quote:
I don't have vicarious experiences in DB2 queries. . . Stop judging people without even knowing them.
You do if you have read any of the topics in the DB2 part of the forum. Suggest You stop being offended without understanding what you were told. . .

Quote:
and to my knowledge this is much simpler than that
But the hope here was that someone would give the code with no effort required. . .
Back to top
View user's profile Send private message
chandan.inst

Active User


Joined: 03 Nov 2005
Posts: 269
Location: Mumbai

PostPosted: Wed Jan 11, 2012 10:46 am    Post subject:
Reply with quote

Hi Sumivel

It would be great if you provide column names and primary keys for both tables

Regards,
Chandan
Back to top
View user's profile Send private message
dick scherrer

Site Director


Joined: 23 Nov 2006
Posts: 19270
Location: Inside the Matrix

PostPosted: Wed Jan 11, 2012 11:31 am    Post subject:
Reply with quote

Hello,

Make up some names that match the the initial post.

Knowing which are the primary keys should not be needed . . .
Back to top
View user's profile Send private message
Chiranjeevi9

New User


Joined: 14 Dec 2011
Posts: 11
Location: India

PostPosted: Wed Jan 11, 2012 12:00 pm    Post subject:
Reply with quote

for understanding purpose i have given my own names for the tables and columns.

Please see the following for the query.

Example
I/P file

FIELD-1 FIELD-2 FIELD-3 FIELD-4
------- --------- ------- -------
11222 dep1, dep2, dep3


Product table: PRODUCT_TABLE
P_NO P_ID P_DEPT
---- ------ -------
11222 3 dep1
11222 4 dep2
11222 5 dep3


Location table: LOCATION_TABLE
L_P_NO L_P_ID LOC
------ ------ ------
11222 3 loc1
11222 3 loc2
11222 3 loc3
11222 4 loc2
11222 4 loc3
11222 4 loc4
11222 5 loc5
11222 5 loc6
11222 5 loc2


SELECT LOC FROM LOCATION_TABLE
WHERE L_P_ID ALL( SELECT P_ID FROM PRODUCT_TABLE
WHERE P_NO =:FIELD-1
AND P_DEPT IN(:FIELD-2,:FIELD-3,:FIELD-4))

next time onwards, please give example column and table name. that will be easy to everybody to write the query.
Back to top
View user's profile Send private message
Chiranjeevi9

New User


Joined: 14 Dec 2011
Posts: 11
Location: India

PostPosted: Wed Jan 11, 2012 12:15 pm    Post subject:
Reply with quote

After given the answer, i understood that, i have given the answer only for the given data.

For Accurate query, please provide Primary Key columns or sample data for 2 more product numbers(P_NO).
Back to top
View user's profile Send private message
Chiranjeevi9

New User


Joined: 14 Dec 2011
Posts: 11
Location: India

PostPosted: Wed Jan 11, 2012 12:40 pm    Post subject:
Reply with quote

i am not pointing to any one.

This site is there to help the people, who are having the doubts or non experienced.

Please don't discourage the people.
Everyone is a learner at any point of time in any one topic.
Back to top
View user's profile Send private message
Akatsukami

Global Moderator


Joined: 03 Oct 2009
Posts: 1738
Location: Bloomington, IL

PostPosted: Wed Jan 11, 2012 4:26 pm    Post subject:
Reply with quote

Chiranjeevi9 wrote:
This site is there to help the people, who are having the doubts or non experienced.

That is correct. It is not for lazy, incompetent, and/or dishonest IT "workers" to write, "Give me all the code I need to do X".

Note that the TS joined this site over six years ago; he presumably has at least that much experience.
Back to top
View user's profile Send private message
xknight

Active User


Joined: 22 Jan 2008
Posts: 117
Location: Liberty city

PostPosted: Wed Jan 11, 2012 5:12 pm    Post subject:
Reply with quote

Hello,

Quote:
SELECT LOC FROM LOCATION_TABLE
WHERE L_P_ID ALL( SELECT P_ID FROM PRODUCT_TABLE
WHERE P_NO =:FIELD-1
AND P_DEPT IN(:FIELD-2,:FIELD-3,:FIELD-4))


I dont think, the posted snippet is what TS looking for, he wants to pull only the common location i suppose.

Note there is an code tag / preview function before posting the content,helps for the better readability.
Back to top
View user's profile Send private message
sumivel

New User


Joined: 13 Sep 2005
Posts: 18

PostPosted: Wed Jan 11, 2012 9:30 pm    Post subject:
Reply with quote

I got common locations using group by loc having count = (select * from prod where details from file) .this worked fine ,when row set is 100 but failed for 500.since I had a special request job I wanted to take help from this site but anyway I moved my code with 100. It worked fine.

Lesson learned.

1.Vicarious
2.Do not ask for help when you have special request job.take your own decision.

Thanks
Back to top
View user's profile Send private message
Bill Woodger

DFSORT Moderator


Joined: 09 Mar 2011
Posts: 7233

PostPosted: Wed Jan 11, 2012 9:43 pm    Post subject: Reply to: Query required to pull common locations
Reply with quote

Lesson which should have been learned. Assume that the readers of your initial post know nothing about your situation, so include everything that they might need to know,: what you have tried; the results you got; and why you felt it did not suit; anything you feel might be useful.

Use the Code tags to preserve formatting. It is amazing how much it helps when we can see the same layouts you are looking at.
Back to top
View user's profile Send private message
dick scherrer

Site Director


Joined: 23 Nov 2006
Posts: 19270
Location: Inside the Matrix

PostPosted: Wed Jan 11, 2012 11:58 pm    Post subject:
Reply with quote

Hello,

Quote:
Lesson learned.

1.Vicarious
2.Do not ask for help when you have special request job.take your own decision.
Well, one out of 2 is really good sometimes. . . . Maybe not this time, but sometimes. . .

Hopefully, you will revise your Lesson 2 learned to be "If i ask my question clearly and completely, i am more likely to get a useful reply quickly".

Also, this forum is for people who are supposed to have considerable experience (i.e. expert). This was a rathr basic SQL question. We also have a separate entire forum for Students and Beginners - http://www.ibmmainframeforum.com/
There are less "flames" there icon_smile.gif
Back to top
View user's profile Send private message
View previous topic :: :: View next topic  
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2 All times are GMT + 6 Hours
Page 1 of 1

 

Search our Forum:

Similar Topics
Topic Author Forum Replies Posted
This topic is locked: you cannot edit posts or make replies. Db2 SQL query to convert rows into co... subratarec DB2 5 Thu Nov 17, 2016 4:51 pm
No new posts Two Selects in a single select query Rohit Umarjikar DB2 1 Fri Nov 04, 2016 8:46 pm
No new posts Problem in Running Query via JCL vickey_dw DB2 3 Tue Oct 18, 2016 11:11 pm
No new posts SQL Query optimization. arunsoods DB2 12 Sun Sep 04, 2016 6:27 am
No new posts Query on BLSR Nileshkul JCL & VSAM 2 Sat Aug 13, 2016 5:18 am


Facebook
Back to Top
 
Mainframe Wiki | Forum Rules | Bookmarks | Subscriptions | FAQ | Tutorials | Contact Us