display output

35 pts.
Tags:
Display File
How to request only specific columns in a DB be display in the query results?

 



Software/Hardware used:
pc

Answer Wiki

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

Based on the new information provided by Stellada, the query sould be something like this:

<pre>SELECT ta.acc_number,ta.name,ta.marital_status,max(tb.change_date)
FROM table_a ta JOIN table_b tb
ON ta.acc_number = tb.acc_numer
AND ta.marital_status = tb.new_marital_status
GROUP BY ta.acc_number,ta.name,ta.marital_status;</pre>

If you really want to retrieve only the account number and marital status information, then you would not need to JOIN table_a.

This syntax would work on Oracle 9i or above. On previous versions you would need to use the old syntax to join tables in the WHERE clause.

-CarlosDL

———-

Discuss This Question: 8  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
  • carlosdl
    The question was added to the AS400 section because of the 'Display file' tag. Please confirm that this is AS400-related, or specify the database system and platform you are using. Also, please provide more details about your question. As you mention 'query results', I suppose you are talking about an SQL's SELECT statement. If that is the case, the answer is simple: You just have to include the names of the columns you need, in the select list of your SQL command.
    68,495 pointsBadges:
    report
  • Stellada
    My query is of two tables, one table had account number and marital status another table has acct number, new marital status, date of marital status update and previous marital status (and many other fields). I want to query the table to show acct number, marital status, date of MS update. As there could be multiple changes for an account, I want to eliminate the duplicates and display only the account number and the most recent marital status and the date of the MS status change. PC access to Oracle db Thank you
    35 pointsBadges:
    report
  • TomLiotta
    ...another table has acct number, new marital status, date of marital status update and previous marital status... And I want to query the table to show acct number, marital status, date of MS update. Isn't this the only necessary table? I'd think that something with MAX(date of marital status update) should be able to give all that was requested. Tom
    125,585 pointsBadges:
    report
  • Stellada
    Tom, Are you saying that I only need to query table#2?
    35 pointsBadges:
    report
  • carlosdl
    Yes, I guess that is what Tom is saying, and it is also what was previously mentioned in the answer section.
    68,495 pointsBadges:
    report
  • Stellada
    Well thank you! I thought so but I have not used sql in a very long time and do not remember a lot. Looking for a couse to refresh in NYC. I appreciate you help!
    35 pointsBadges:
    report
  • carlosdl
    Note that a single group by query like the one from the answer won't work if querying just the second table, as it would return the last record for each marital status the account has changed to. In such scenario you might need to use this group by query as an inline view to an upper level query from the same table, or to use a non-grouped query with a condition against a subquery, or a 'not exists' condition (also with a subquery), or some other method to find the last marital status change. Feel free to post any doubts/problems you encounter while doing this.
    68,495 pointsBadges:
    report
  • TomLiotta
    ...it is also what was previously mentioned in the answer section. Ah, you're right. I focused on the SQL and skipped the proper reading of the text. Never mind -- it's covered. Subsequent comments by Carlosdl are also filling details fine. I'm backing out of here. 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