oracle query

I have a query for an exception report.


select customer_id,name
  from customers@plab.world
  where term_date is null
    and to_char(eff_date,'MM/DD/YYYY') >= to_char(sysdate,'MM/DD/YYYY')
    and telex_no = '137-000-0000'

CUSTOMER_ID,NAME
WAD-PA0352,ST ANTHONY - MICHIGAN CITY FRANCISCAN
WAD-RG0242,ST ANTHONY - MICHIGAN CITY FRANCISCAN

But I need help in also adding if the substr(telex_no,1,3) changes. There is a possibility that the first three digits may change as they represent the price code and direct bill or consignee. Right now I have the query for
telex_no = '137-000-0000' to give an example. But I need to add if any of the first three characters change.
LVL 6
anumosesAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

slightwv (䄆 Netminder) Commented:
>>and to_char(eff_date,'MM/DD/YYYY') >= to_char(sysdate,'MM/DD/YYYY')

Don't do this.  Compare dates with dates.  Why convert them to a string?

Try this instead:
and ( eff_date >= trunc(sysdate) and eff_date < trunc(sysdate+1))


Back to the question:
I don't understand.  Please post sample data and expected results.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
sdstuberCommented:
are you looking for something like this?

select customer_id,name
   from customers@plab.world
   where term_date is null
     and eff_date >= trunc(sysdate)
group by customer_id,name
having count(distinct substr(telex_no,1,3)) > 1
0
anumosesAuthor Commented:
As per the query I have telex_no = '137-000-0000'

substr(telex_no,1,3)

137 any of the three can change. 1 may become 2 and 37 may change to 20
1,1 is direct bill or consignee and 2,3 is price code. So I need to know if any of the three change for any customer.

Thanks for guiding me on the date part.
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

sdstuberCommented:
If what I posted doesn't do what you're looking for,  please explain what it doesn't do.
Or better yet - post sample data and expected results
0
anumosesAuthor Commented:
CREATE TABLE TEMP_LIFE_TRAK_CUST_TAB
(
  CUSTOMER_ID           VARCHAR2(10 BYTE)       NOT NULL,
  CHAPTER_ID            VARCHAR2(4 BYTE)        NOT NULL,
  ALPHA_KEY             VARCHAR2(10 BYTE)       NOT NULL,
  CUSTOMER_TYPE         VARCHAR2(2 BYTE)        NOT NULL,
  NAME                  VARCHAR2(50 BYTE)       NOT NULL,
  SHORT_NAME            VARCHAR2(15 BYTE)       NOT NULL,
  ADDRESS_1             VARCHAR2(50 BYTE)       NOT NULL,
  ADDRESS_2             VARCHAR2(50 BYTE),
  ADDRESS_3             VARCHAR2(50 BYTE),
  CITY                  VARCHAR2(15 BYTE)       NOT NULL,
  STATE                 VARCHAR2(2 BYTE)        NOT NULL,
  COUNTRY               VARCHAR2(50 BYTE)       NOT NULL,
  ZIP                   VARCHAR2(10 BYTE)       NOT NULL,
  PHONE_NO              VARCHAR2(20 BYTE),
  PHONE_EXT             VARCHAR2(5 BYTE),
  FAX_NO                VARCHAR2(20 BYTE),
  TELEX_NO              VARCHAR2(20 BYTE),
  PAYEE_ID              VARCHAR2(10 BYTE),
  COD_FLAG              VARCHAR2(1 BYTE),
  RETURN_ACCEPTED_FLAG  VARCHAR2(1 BYTE),
  AR_ACCOUNT            VARCHAR2(20 BYTE),
  CP6_DEST              VARCHAR2(4 BYTE),
  FI_PRICE_SCH_NO       VARCHAR2(2 BYTE),
  EFF_DATE              DATE                    NOT NULL,
  TERM_DATE             DATE,
  LAST_MODIFIED_DATE    DATE                    NOT NULL,
  AUDIT_KEY             NUMBER                  NOT NULL,
  PROGRAM_ID            VARCHAR2(30 BYTE)       NOT NULL,
  PRICE_CHANGE_FLAG     VARCHAR2(1 BYTE),
  EDN_GEN_FLAG          VARCHAR2(1 BYTE),
  PH_APPR_FLAG          VARCHAR2(1 BYTE),
  INS_APPR_FLAG         VARCHAR2(1 BYTE)
)


Insert into TEMP_LIFE_TRAK_CUST_TAB
   (CUSTOMER_ID, CHAPTER_ID, ALPHA_KEY, CUSTOMER_TYPE, NAME, 
    SHORT_NAME, ADDRESS_1, ADDRESS_2, ADDRESS_3, CITY, 
    STATE, COUNTRY, ZIP, PHONE_NO, PHONE_EXT, 
    FAX_NO, TELEX_NO, PAYEE_ID, COD_FLAG, RETURN_ACCEPTED_FLAG, 
    AR_ACCOUNT, CP6_DEST, FI_PRICE_SCH_NO, EFF_DATE, TERM_DATE, 
    LAST_MODIFIED_DATE, AUDIT_KEY, PROGRAM_ID, PRICE_CHANGE_FLAG, EDN_GEN_FLAG, 
    PH_APPR_FLAG, INS_APPR_FLAG)
 Values
   ('WAD-RG0242', 'WAD', 'ST ANTHONY', 'RG', 'ST ANTHONY - MICHIGAN CITY FRANCISCAN', 
    'ST ANTHONY MICH', '301 West Homer Street', NULL, NULL, 'MICHIGAN CITY', 
    'IN', 'USA', '46360', '219-877-1508', NULL, 
    '219-877-1534', '137-000-0000', 'WAD-PA0005', 'Y', 'Y', 
    '0866', NULL, NULL, TO_DATE('07/30/2015 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), NULL, 
    TO_DATE('07/31/2015 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 83985673, 'MANUAL', NULL, NULL, 
    NULL, NULL);

Open in new window



	select customer_id,name
   from temp_life_trak_cust_tab
   where term_date is null
     --and eff_date >= trunc(sysdate)
group by customer_id,name
having count(distinct substr(telex_no,1,3)) > 1

Open in new window


I changed the telex_no field from 137-000-0000 to 237-000-0000

but the code did not catch it. Now I have changed it back.
0
slightwv (䄆 Netminder) Commented:
You only provided one row of data and you didn't provide any expected results.

>>I changed the telex_no field from 137-000-0000 to 237-000-0000

Did you do an update?  With only one row, what do you expect to "catch"?  There isn't any other data to compare it with.
0
anumosesAuthor Commented:
I gave the sample of one customer. If we change in the table the telex_no from 137-000-0000 to 237-000-000 the query you gave me has to catch it, in the sense we should get one line data. But did not give that. So I changed it back to the original data.

I should catch the customer_id and name.
0
slightwv (䄆 Netminder) Commented:
No query will catch the change for a single row with nothing to compare it with.

You would need auditing or a custom trigger to track before and after values of the row.
0
sdstuberCommented:
"catch it"  - how exactly do you expect a single row to "catch" a change?

In order to detect a change you must have a "before value" and an "after value"  to compare.
You have only one row, so only one value.  Therefore nothing to compare to.

If the change is recent then I suppose you could try a flashback query.
Change the timestamp to whatever point in time you want to compare.
But, it must still be in your flashback files, which are limited in space (and hence historical time.)
If you try to go back too far you'll get

ORA-08180: no snapshot found based on specified time


SELECT *
  FROM temp_life_trak_cust_tab cur
 WHERE EXISTS
           (SELECT NULL
              FROM (SELECT customer_id, name, telex_no
                      FROM temp_life_trak_cust_tab
                           AS OF TIMESTAMP FROM_TZ(TO_TIMESTAMP('2015-09-09 10:16:00', 'yyyy-mm-dd hh24:mi:ss.ff'), 'US/Eastern'))
                   old
             WHERE cur.customer_id = old.customer_id
               AND cur.name = old.name
               AND SUBSTR(cur.telex_no, 1, 3) != SUBSTR(old.telex_no, 1, 3));


if flashback won't work, then you'll need to capture the changes as they happen and record them somewhere.  You do NOT use a select statement to try to "catch" a change.

 It doesn't even make sense to try.
0
anumosesAuthor Commented:
thanks
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.