Home > IT Answers > AS/400 > Query in SQL400
ssssssssssssssss
80 pts.
 Query in SQL400
AS/400, SQL Query, SQL/400
Hi,

I want one query in SQL400, I will give small example?

Suppose there are two tables,

   TAb1                Tab2

1.CMP                 1.CMP

2.Item NO           2. Item NO

3.Status              3.Warehouse

4.Quantity           4.Costing type

 

Now I want the data from tab2 where warehouse = 502 , Costing type = '2'   and status = '20' (tab1)  so, how to write the query?  Plz read the two tables.

  



Software/Hardware used:
yes
ASKED: April 11, 2012  6:37 AM
UPDATED: April 20, 2012  7:27 PM
RELATED QUESTIONS
Answer Wiki:
Thank you for visiting ITKE. We are happy to help you with solving specific IT questions, but need as much information as possible to do so. Let us know about the problem you are trying to solve, how you are approaching it and what work you’ve done so far, and we can help guide you in the right direction.
Last Wiki Answer Submitted:  May 13, 2013  10:47 pm  by  CharlieBrowne   32,825 pts.
All Answer Wiki Contributors:  CharlieBrowne   32,825 pts. , Chris Leonard   2,595 pts.
To see all answers submitted to the Answer Wiki: View Answer History.


RSS - Discussions Help
Discuss This Question:
_ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _

 

plz try this query for your question.
query:

select aa.status,bb.warehouse,bb.costing from tbl1 aa, tbl bb where bb.warehouse=502 or bb.costing=2 or aa.status=20 and aa.itemno=bb.itemno;

Gowriking
 65 pts.
 

or perhaps the OR’s should be AND’s ?

philpl1jb
 44,130 pts.
 

Charlie we r not Oracle expert thats why asking? I’m not teling to do my homework? I’m just asking my query? bcoz that is necessarry in my work?

ssssssssssssssss
 80 pts.
 

So Gowriking gave you a solution.
I provided a useful comment.
You can try this in SQL/400 and see how it works.
Show us what you’ve done and discuss with us what it lacks
and we’ll guide you to greater knowledge and a solved problem.

Phil

philpl1jb
 44,130 pts.
 

Sssss, you don’t need to be an “Expert” at all to build a query for that.

carlosdl
 63,535 pts.
 

…we r not Oracle expert…

Now it’s becoming confused.

Is this a question about Oracle or SQL/400? Those can be very different. (But both may be just about the same for this specific question.)

Tom

TomLiotta
 107,925 pts.
 

try this

select * from tab2 a, tab1 b
where a.warehouse = 502
and a.Costing type = ’2′
and b.status = ’20′
and a.item no=b.item no

9783444184
 1,240 pts.
 

Thanks Phil

ssssssssssssssss
 80 pts.
 

Good Pointers Phil and 9783444184
Thanks

AS400Operator
 80 pts.
 

I want the data from tab2 where warehouse = 502 , Costing type = ’2′

That part is easy: 

select * from tab2 a where a.warehouse = 502 and a.Costingtype = '2'

…and status = ’20′ (tab1)

But adding that condition can make it more difficult because there are different ways it can be done. One possibility is to add this to the WHERE clause: 

and exists(select 1 from tab1 b where b.CMP = a.CMP and b.ItemNO = a.ItemNO and b.status = '20')

If you don’t need the data from the columns in tab1, then there might be no reason to do a JOIN of the two tables. If decent indexes are defined over tab1, a test of EXISTS() may be more efficient.

Tom

TomLiotta
 107,925 pts.