Solved

Sub Query Error

Posted on 2013-11-06
10
550 Views
Last Modified: 2013-11-06
See query below.  I get an error on the subquery (line 10) part and not sure how to fix?
Query works fine with that part removed.

ERROR:
ORA-00904: "POSTING_DATE": invalid identifier
00904. 00000 -  "%s: invalid identifier"
*Cause:    
*Action:
Error at Line: 24 Column: 77




select 
    distinct 
    p.last_name || ', ' || p.first_name as MANAGER,
    (SELECT LAST_NAME || ', ' || FIRST_NAME FROM PERSON P1 WHERE P1.ID = fm.employee_person_id) AS AUDITOR,
    client.name CUSTOMER,
    deals.name as deals,
      aut.account_number as ACCOUNT_NUMBER,
    address.state_abbrev,
    to_char(trunc(posting_date))posting_date,
    (SELECT COUNT(*) FROM (SELECT start_date + LEVEL d FROM (SELECT to_date(posting_date,'dd-MON-RR') start_date, trunc(sysdate) end_date FROM DUAL) CONNECT BY start_date + LEVEL <= end_date) WHERE TO_CHAR(d, 'Dy') NOT IN ('Sat', 'Sun')) as D1,
    aut.deals_id,
    client_deals.audit_interval,
    to_char(trunc(client_deals.last_audit_date))last_audit_date     
from autr,client_deals, deals_address,address, client, deals, AVI_QUADRANTS avq, field_manager fm, person p
where client.id IN (select distinct aut.CLIENT_ID
from aut, client
where client.id = aut.client_id
and trunc(sysdate) > trunc(posting_date)+14
and inspection_status_id in (3,7)
and client_id <> 100)
and aut.client_id = client.id
and deals.id = aut.deals_id
and deals.id = client_deals.deals_id
and client.id = client_deals.client_id
and deals_address.deals_ID = aut.deals_id
and deals_address.address_id = address.ID
and trunc(sysdate) > trunc(posting_date)+14
and inspection_status_id in (3,7)
AND aut.avizone_id = avq.quadrant_id
AND avq.TERRITORY_ID = fm.avizone_id
and p.ID=get_FM(fm.employee_person_id) 

Open in new window

0
Comment
Question by:patriotpacer
  • 4
  • 3
  • 2
  • +1
10 Comments
 
LVL 73

Expert Comment

by:sdstuber
ID: 39628338
can you create functions?

also, what is the full version of your database?  (like 11.2.0.3  not 11g)
0
 

Author Comment

by:patriotpacer
ID: 39628343
I can't create functions.  No rights.

Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
0
 
LVL 73

Accepted Solution

by:
sdstuber earned 500 total points
ID: 39628397
ok, in that case, it makes it a little more complicated, and unfortunately, makes me retract one of statements in a previous question.  
You can't use the extra layer of subquerying with the "start_date/end_date"  versions.

Note, this is a good example of one of my other statements that you don't do extra conversions on your column.
I'm assuming POSTING_DATE is a actually a DATE type, not a string.


select
    distinct
    p.last_name || ', ' || p.first_name as MANAGER,
    (SELECT LAST_NAME || ', ' || FIRST_NAME FROM PERSON P1 WHERE P1.ID = fm.employee_person_id) AS AUDITOR,
    client.name CUSTOMER,
    deals.name as deals,
      aut.account_number as ACCOUNT_NUMBER,
    address.state_abbrev,
    to_char(trunc(posting_date))posting_date,
   (    SELECT COUNT(
                       CASE WHEN TO_CHAR(posting_date + LEVEL, 'Dy') NOT IN ('Sat', 'Sun') THEN 1 END
                   )
              FROM DUAL
        CONNECT BY TRUNC(posting_date) + LEVEL <= TRUNC(SYSDATE))as D1,
    aut.deals_id,
    client_deals.audit_interval,
    to_char(trunc(client_deals.last_audit_date))last_audit_date    
from autr,client_deals, deals_address,address, client, deals, AVI_QUADRANTS avq, field_manager fm, person p
where client.id IN (select distinct aut.CLIENT_ID
from aut, client
where client.id = aut.client_id
and trunc(sysdate) > trunc(posting_date)+14
and inspection_status_id in (3,7)
and client_id <> 100)
and aut.client_id = client.id
and deals.id = aut.deals_id
and deals.id = client_deals.deals_id
and client.id = client_deals.client_id
and deals_address.deals_ID = aut.deals_id
and deals_address.address_id = address.ID
and trunc(sysdate) > trunc(posting_date)+14
and inspection_status_id in (3,7)
AND aut.avizone_id = avq.quadrant_id
AND avq.TERRITORY_ID = fm.avizone_id
and p.ID=get_FM(fm.employee_person_id)
0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 73

Expert Comment

by:sdstuber
ID: 39628405
as a side note,  if you have indexes on posting_date then

and trunc(sysdate) > trunc(posting_date)+14

Open in new window


would be better written as...


and trunc(sysdate) - 13 > posting_date

Open in new window


or, if the 14 is important

and trunc(sysdate) - 14 + 1 > posting_date

Open in new window



the important part being to avoid using functions on columns in a where clause
0
 
LVL 35

Expert Comment

by:Mark Geerlings
ID: 39628419
These look like potentially serious performance problems to me:
1. "select distinct..." - Do you need the "distinct"?  That can add a big performance penalty, but this depends on a number of factors (size of the tables, size of your SGA, number of records that meet the criteria of the "where" clauses, etc.).
2. "trunc(posting_date)" - Whenever you apply an operator (trunc, upper, lower, to_char, to_date, trim, etc.) to a database column value, you prevent the use of indexes on that column, unless you have function-based index(es) that exactly match this syntax.  (The "trunc" on sysdate is no problem.)

Also, you have two references to: "trunc(posting_date)+14".  The second one is unecessary unless these two lines are intended to refer to different posting_date columns in two different tables.  But, then you will need a table alias with each one.
0
 
LVL 73

Expert Comment

by:sdstuber
ID: 39628432
There are multiple conditions that are repeated but some of them are within an IN-clause and others are part of the overall where clause.

However, the IN clause references client as well as does the main query, so it's possible that there is some redundant access there.
0
 

Author Closing Comment

by:patriotpacer
ID: 39628442
That seemed to work.

I realize the query is not written well (it was given to me by someone else).  I'm unfortunately under the gun to do only slight modifications just to get it to work.   My only goal for now is a correct answer.

But I will most definitely revisit the performance suggestions.  

Thank you for all the help.
0
 
LVL 35

Expert Comment

by:Mark Geerlings
ID: 39628479
"I'm ... under the gun to do only slight modifications just to get it to work."

I like two responses that I've heard from co-workers in IT over the years to this line of thinking:

1. "If you don't have time to the job right this time, when will you have time to do it over?"
2. "How bad do you want it?  That's how bad you will get it!"
0
 
LVL 48

Expert Comment

by:PortletPaul
ID: 39629141
Mmm
testing bad queries is a bad investment
doesn't matter if you under the gun, if it's bad the trigger is pulled sooner or later

!! there are missing aliases for posting_date & inspection_status_id

and if it were my query I'd bee looking at something like the following (and even then I'm not happy):
SELECT
      manager
    , auditor
    , customer
    , account_number
    , state_abbrev
    , to_char(posting_date) AS posting_date
    , (    /* perform this fewer times AFTER the distinct is performed */
        SELECT COUNT(CASE 
                     WHEN TO_CHAR(posting_date + LEVEL, 'Dy') NOT IN ('Sat', 'Sun')
                        THEN 1
                     END)
        FROM DUAL CONNECT BY TRUNC(posting_date) + LEVEL <= TRUNC(SYSDATE)
        ) AS D1
    , deals_id
    , audit_interval
    , last_audit_date
FROM (
        SELECT DISTINCT    /* would prefer to remove 'distinct' if possible */
              p.last_name || ', ' || p.first_name   AS MANAGER
            , p1.LAST_NAME || ', ' || p1.FIRST_NAME AS AUDITOR
            , client.NAME                           AS CUSTOMER
            , deals.NAME                            AS deals
            , aut.account_number                    AS ACCOUNT_NUMBER
            , address.state_abbrev
            , trunc(posting_date)                   AS posting_date
            , aut.deals_id
            , client_deals.audit_interval
            , to_char(trunc(client_deals.last_audit_date)) AS last_audit_date
            
        FROM aut         /* use ANSI join syntax */
        INNER JOIN deals_address     ON aut.deals_id = deals_address.deals_ID
        INNER JOIN address           ON deals_address.address_id = address.ID
        INNER JOIN client            ON aut.client_id = client.id
        INNER JOIN deals             ON aut.deals_id = deals.id
        INNER JOIN client_deals      ON deals.id = client_deals.deals_id AND client.id = client_deals.client_id
        INNER JOIN AVI_QUADRANTS avq ON aut.avizone_id = avq.quadrant_id
        INNER JOIN field_manager fm  ON avq.TERRITORY_ID = fm.avizone_id
        INNER JOIN person p          ON p.ID = get_FM(fm.employee_person_id) /* don't like functions for joins */
        INNER JOIN person p1         ON P1.ID = fm.employee_person_id

        WHERE inspection_status_id IN ( 3, 7 ) /* where clause is vastly simplified */
            AND aut.client_id <> 100
            AND trunc(sysdate) - 13 > trunc(posting_date) /* a "sargable" predicate */
     )
 ;
 

Open in new window

0
 

Author Comment

by:patriotpacer
ID: 39629173
Very nice.  I really appreciate the rewrite, as well as the other comments and suggestions.

Huge help, PortletPaul!
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
automatic email alert 1 53
Extract Currency data from a string and put them in a new field 3 38
Require data to appear on a single line 2 45
create a nested synonym 4 26
Working with Network Access Control Lists in Oracle 11g (part 1) Part 2: http://www.e-e.com/A_9074.html So, you upgraded to a shiny new 11g database and all of a sudden every program that used UTL_MAIL, UTL_SMTP, UTL_TCP, UTL_HTTP or any oth…
Checking the Alert Log in AWS RDS Oracle can be a pain through their user interface.  I made a script to download the Alert Log, look for errors, and email me the trace files.  In this article I'll describe what I did and share my script.
This video shows setup options and the basic steps and syntax for duplicating (cloning) a database from one instance to another. Examples are given for duplicating to the same machine and to different machines
This video shows how to configure and send email from and Oracle database using both UTL_SMTP and UTL_MAIL, as well as comparing UTL_SMTP to a manual SMTP conversation with a mail server.

831 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question