View previous topic :: View next topic
|
Author |
Message |
sumivel
New User
Joined: 13 Sep 2005 Posts: 18
|
|
|
|
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 |
|
|
vasanthz
Global Moderator
Joined: 28 Aug 2007 Posts: 1742 Location: Tirupur, India
|
|
|
|
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 |
|
|
Pandora-Box
Global Moderator
Joined: 07 Sep 2006 Posts: 1592 Location: Andromeda Galaxy
|
|
|
|
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 |
|
|
sumivel
New User
Joined: 13 Sep 2005 Posts: 18
|
|
|
|
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 |
|
|
Pandora-Box
Global Moderator
Joined: 07 Sep 2006 Posts: 1592 Location: Andromeda Galaxy
|
|
|
|
Agreed
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 |
|
|
dick scherrer
Moderator Emeritus
Joined: 23 Nov 2006 Posts: 19244 Location: Inside the Matrix
|
|
|
|
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 |
|
|
chandan.inst
Active User
Joined: 03 Nov 2005 Posts: 275 Location: Mumbai
|
|
|
|
Hi Sumivel
It would be great if you provide column names and primary keys for both tables
Regards,
Chandan |
|
Back to top |
|
|
dick scherrer
Moderator Emeritus
Joined: 23 Nov 2006 Posts: 19244 Location: Inside the Matrix
|
|
|
|
Hello,
Make up some names that match the the initial post.
Knowing which are the primary keys should not be needed . . . |
|
Back to top |
|
|
Chiranjeevi9
New User
Joined: 14 Dec 2011 Posts: 11 Location: India
|
|
|
|
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 |
|
|
Chiranjeevi9
New User
Joined: 14 Dec 2011 Posts: 11 Location: India
|
|
|
|
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 |
|
|
Chiranjeevi9
New User
Joined: 14 Dec 2011 Posts: 11 Location: India
|
|
|
|
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 |
|
|
Akatsukami
Global Moderator
Joined: 03 Oct 2009 Posts: 1788 Location: Bloomington, IL
|
|
|
|
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 |
|
|
xknight
Active User
Joined: 22 Jan 2008 Posts: 117 Location: Liberty city
|
|
|
|
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 |
|
|
sumivel
New User
Joined: 13 Sep 2005 Posts: 18
|
|
|
|
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 |
|
|
Bill Woodger
Moderator Emeritus
Joined: 09 Mar 2011 Posts: 7309 Location: Inside the Matrix
|
|
|
|
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 |
|
|
dick scherrer
Moderator Emeritus
Joined: 23 Nov 2006 Posts: 19244 Location: Inside the Matrix
|
|
|
|
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 - www.ibmmainframeforum.com/
There are less "flames" there |
|
Back to top |
|
|
|