Home » SQL & PL/SQL » SQL & PL/SQL » need to understand syntax (Oracle R12,SQL developer)
need to understand syntax [message #602041] Fri, 29 November 2013 01:18 Go to next message
megamind
Messages: 11
Registered: November 2013
Junior Member
SELECT distinct d.ATTRIBUTE1 "Supplier Name",
d.check_date "Cheque Date",
d.CHECK_NUMBER "Cheque #",
d.DOC_SEQUENCE_VALUE "PV Number",
d.AMOUNT "Paid Amount",
f.user_name "Payment User",
d.status_lookup_code "Cheque Status OF"

FROM apps.fnd_user F, ap.ap_invoice_payments_all C, AP.AP_CHECKS_all D

where d.CREATED_BY = f.USER_Id

AND c.check_id = d.check_id


AND d.bank_account_id = 10374

AND trunc(d.check_date) > '21-Jul-2010'


I am new to PL/SQL and I am given the queries which I run but I need to understand the syntax and how the above code works.
Thanks in anticipation
Re: need to understand syntax [message #602046 is a reply to message #602041] Fri, 29 November 2013 01:25 Go to previous messageGo to next message
Michel Cadot
Messages: 59422
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
SQL> SELECT distinct d.ATTRIBUTE1 "Supplier Name",
  2  d.check_date "Cheque Date",
  3  d.CHECK_NUMBER "Cheque #",
  4  d.DOC_SEQUENCE_VALUE "PV Number",
  5  d.AMOUNT "Paid Amount",
  6  f.user_name "Payment User",
  7  d.status_lookup_code "Cheque Status OF"
  8  
  9  FROM apps.fnd_user F, ap.ap_invoice_payments_all C, AP.AP_CHECKS_all D
 10  
 11  where d.CREATED_BY = f.USER_Id
 12  
 13  AND c.check_id = d.check_id
 14  
 15  
 16  AND d.bank_account_id = 10374
 17  
 18  AND trunc(d.check_date) > '21-Jul-2010'
 19  /
FROM apps.fnd_user F, ap.ap_invoice_payments_all C, AP.AP_CHECKS_all D
                                                       *
ERROR at line 9:
ORA-00942: table or view does not exist
Re: need to understand syntax [message #602049 is a reply to message #602041] Fri, 29 November 2013 01:28 Go to previous messageGo to next message
Littlefoot
Messages: 19694
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
What exactly do you not understand?

You are selecting number of columns (specified in the SELECT) that belong to 3 tables (listed in the FROM clause) which are as written in the WHERE clause. Then, there are two filters: one that selects only records whose BANK_ACCOUNT_ID equals 10374. Another one is
and trunc(d.check_date) > '21-jul-2010'
TRUNC removes time part from thte CHECK_DATE column (so that only date remains) and selects records which are "after" 21-jul-2010.

However, that is a wrong way to do that - '21-jul-2010' is a string (enclosed into single quotes), so you'd rather convert it to DATE. In order to do that, you'd use TO_DATE function:
and trunc(d.check_date) > to_date('21.07.2010', 'dd.mm.yyyy')
Or, you could use DATE literal, as
and trunc(d.check_date) > date '2013-07-21'


Anyway: perhaps you should take some time and have a look at documentation which explains such things. This is Oracle 10gR2 documentation page (yes, I know, 12c is the current release) which contains several interesting sections - Getting Started (have a look at "Concepts" book first; then, "SQL Reference" (to learn what SQL is), and - as you work with PL/SQL - "Application Developer's Guide - Fundamentals" and "PL/SQL User's Guide and Reference".
Re: need to understand syntax [message #602061 is a reply to message #602049] Fri, 29 November 2013 03:24 Go to previous message
megamind
Messages: 11
Registered: November 2013
Junior Member
thanks brother,,
In your debt:)
Previous Topic: Using oracle wallets to move data from Oracle to teradata
Next Topic: Need code
Goto Forum:
  


Current Time: Thu Oct 23 12:17:15 CDT 2014

Total time taken to generate the page: 0.19844 seconds