Query in SQL400

110 pts.
Tags:
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

Answer Wiki

Thanks. We'll let you know when a new response is added.

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.

Discuss This Question: 10  Replies

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
  • Gowriking
    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;
    65 pointsBadges:
    report
  • philpl1jb
    or perhaps the OR's should be AND's ?
    49,540 pointsBadges:
    report
  • ssssssssssssssss
    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?
    110 pointsBadges:
    report
  • philpl1jb
    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
    49,540 pointsBadges:
    report
  • carlosdl
    Sssss, you don't need to be an "Expert" at all to build a query for that.
    68,405 pointsBadges:
    report
  • TomLiotta
    ...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
    125,585 pointsBadges:
    report
  • 9783444184
    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
    1,355 pointsBadges:
    report
  • ssssssssssssssss
    Thanks Phil
    110 pointsBadges:
    report
  • AS400Operator
    Good Pointers Phil and 9783444184 Thanks
    80 pointsBadges:
    report
  • TomLiotta
    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
    125,585 pointsBadges:
    report

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Thanks! We'll email you when relevant content is added and updated.

Following