Home » SQL & PL/SQL » SQL & PL/SQL » How to Show only the address for The Yes Tax Reporting Site (11.5.10.2)
icon9.gif  How to Show only the address for The Yes Tax Reporting Site [message #594021] Thu, 22 August 2013 13:05 Go to next message
gihall
Messages: 3
Registered: August 2013
Location: North Carolina
Junior Member
I have a sql to pull all payments from vendors for a specific time period; however, now tasked to only show the Address of each Tax Reporting Site. I have tried several commands but have not been successful. Could someone please help me?
Re: How to Show only the address for The Yes Tax Reporting Site [message #594022 is a reply to message #594021] Thu, 22 August 2013 13:13 Go to previous messageGo to next message
BlackSwan
Messages: 23048
Registered: January 2009
Senior Member
Please read and follow the forum guidelines, to enable us to help you:

http://www.orafaq.com/forum/t/88153/0/

Since we don't have your tables or data, we can not run, test, debug or improve posted code.

How will you, I or anyone recognize when correct solution has been posted?

SELECT pv.vendor_type_lookup_code, 
       pv.segment1, 
       pv.tax_reporting_name, 
       pv.vendor_name, 
       PS.vendor_site_code, 
       PS.tax_reporting_site_flag, 
       ps.address_line1, 
       ps.address_line2, 
       ps.city, 
       ps.state, 
       ps.zip, 
       pv.type_1099, 
       pv.num_1099, 
       c.check_number, 
       c.check_date, 
       IA.invoice_num, 
       IA.invoice_date, 
       IA.invoice_amount, 
       IA.amount_paid 
FROM   ap.ap_invoices_all IA, 
       ap.ap_invoice_payments_all IP, 
       ap.ap_checks_all c, 
       po.po_vendors PV, 
       po.po_vendor_sites_all PS 
WHERE  /*pv.vendor_type_lookup_code like '%1099%' 
       AND */ 
  IP.invoice_id = IA.invoice_id 
  AND IA.vendor_id = PV.vendor_id 
  AND PV.vendor_id = PS.vendor_id 
  /*AND PS.TAX_REPORTING_SITE_FLAG = 'Y'*/ 
  AND invoice_date > '01-DEC-11' 
  AND invoice_date < '01-SEP-13' 
  /*AND ia.org_id = 27779*/ 
  AND IP.check_id = C.check_id 
ORDER  BY ps.address_line1 
Re: How to Show only the address for The Yes Tax Reporting Site [message #594028 is a reply to message #594021] Thu, 22 August 2013 13:35 Go to previous messageGo to next message
Michel Cadot
Messages: 59775
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Welcome to the forum.

Please read OraFAQ Forum Guide and How to use [code] tags and make your code easier to read.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code, use code tags and align the columns in result.
Use the "Preview Message" or "Preview Quick Reply" button to verify.
Also always post your Oracle version, with 4 decimals.

With any SQL or PL/SQL question, please, Post a working Test case: create table (including all constraints) and insert statements along with the result you want with these data then we will be able work with your table and data. Explain with words and sentences the rules that lead to this result.

Regards
Michel
Re: How to Show only the address for The Yes Tax Reporting Site [message #594029 is a reply to message #594028] Thu, 22 August 2013 13:38 Go to previous messageGo to next message
BlackSwan
Messages: 23048
Registered: January 2009
Senior Member
>AND invoice_date > '01-DEC-11'
if INVOICE_DATE is really a DATE datatype, then NEVER rely on default datatype conversion.
In this case TO_DATE() function should be used.
if INVOICE_DATE is really a string datatype, then designer/architect should be summarily terminated for basic incompetence & terminal stupidity.

[Updated on: Thu, 22 August 2013 13:41]

Report message to a moderator

Re: How to Show only the address for The Yes Tax Reporting Site [message #594030 is a reply to message #594029] Thu, 22 August 2013 13:46 Go to previous messageGo to next message
gihall
Messages: 3
Registered: August 2013
Location: North Carolina
Junior Member
This is my 1st time on this forum and I just paid a compliment to someone, but your comment is very rude, was there not a time when you were learning sql? If "Author: BlackSwan" is going to be only "person" to reply, please remove my question from this forum. Please note also had you read my message that the invoice date was NEVER my issue.
Re: How to Show only the address for The Yes Tax Reporting Site [message #594034 is a reply to message #594030] Thu, 22 August 2013 14:51 Go to previous messageGo to next message
Michel Cadot
Messages: 59775
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
It seems you didn't see my post:

Michel Cadot wrote on Thu, 22 August 2013 20:35
Welcome to the forum.

Please read OraFAQ Forum Guide and How to use [code] tags and make your code easier to read.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code, use code tags and align the columns in result.
Use the "Preview Message" or "Preview Quick Reply" button to verify.
Also always post your Oracle version, with 4 decimals.

With any SQL or PL/SQL question, please, Post a working Test case: create table (including all constraints) and insert statements along with the result you want with these data then we will be able work with your table and data. Explain with words and sentences the rules that lead to this result.

Regards
Michel

Re: How to Show only the address for The Yes Tax Reporting Site [message #594074 is a reply to message #594030] Fri, 23 August 2013 03:04 Go to previous messageGo to next message
pablolee
Messages: 2655
Registered: May 2007
Location: Scotland
Senior Member
gihall wrote on Thu, 22 August 2013 19:46
This is my 1st time on this forum
Ys, welcome to the forum, hopefully you will find it a useful resource, and eventually, it will find you to be a useful resource.
Quote:
and I just paid a compliment to someone,
Sorry, but I don't see any compliments, are you sure that you did?
Quote:
but your comment is very rude,
Well, maybe it was a bit rude, it definitely wasn't very rude (the second post that is, assuming that you are indeed the designer of the table, if you're not, then the comment is aimed firmly at someone else, so don't stress it - if you are the designer, then take heed of the comment, it's good advice) The first post from BlackSwan was perfectly polite.. You have to remember that people here give up their time for free. Most people expect a certain level of effort and commitment from those looking for help. The most common requirements would be reading and following the guidelines. Sometimes some of us can be brusque, often through simple frustration at people not following the rules.

Quote:
was there not a time when you were learning sql?
Of course, the same goes for everyone, different people have different levels of ability and put in different levels of effort.
Quote:
If "Author: BlackSwan" is going to be only "person" to reply, please remove my question from this forum.

1. Your post comes several minutes after another prolific poster responded to you, so this is obviously not the case.
2. Why "person", are you implying that BlackSwan is not human? Do you feel that insults are necessary?
3. We don't remove posts/questions unless they contravene forum rules, so that won't happen here.

Quote:
Please note also had you read my message that the invoice date was NEVER my issue.

Not entirely accurate. It was never the issue that you stated, but it is most certainly an issue in either your code or design.
Now, on to your question:
Quote:
now tasked to only show the Address of each Tax Reporting Site
You don't really give us much to go on do you? Based on what you have supplied, I'd suggest that you remove the columns from your select list that you don't want to display, and include the columns that you do want to display. If that does not answer your question, then please respond with a working test case (there's a link in Michel Cadot's post with details on how to post a test case) along with expected results and an explanation of those results.
Re: How to Show only the address for The Yes Tax Reporting Site [message #594090 is a reply to message #594074] Fri, 23 August 2013 06:40 Go to previous message
gihall
Messages: 3
Registered: August 2013
Location: North Carolina
Junior Member
Thank you to Michel Cadot and a special thank you to Pablolee. I will follow Pablolee's advice. No I was not the designer of the table, and there have been many issues that I cannot change as I have to work with what I have. Pablolee, thank you for the time to restore my faith! Have a great weekend.
Previous Topic: Decode Query
Next Topic: How to make IS NOT NULL condition to use index
Goto Forum:
  


Current Time: Tue Nov 25 14:59:10 CST 2014

Total time taken to generate the page: 0.11755 seconds