OraFAQ Forum: Oracle Fusion Apps & E-Business Suite » :Last review date

Home » Applications » Oracle Fusion Apps & E-Business Suite » :Last review date > 6 months (5 Merged)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

:Last review date > 6 months (5 Merged) [message #505666]

Wed, 04 May 2011 08:29

pallavigs
Messages: 37
Registered: March 2006
Location: India

Member

Hi All,

I am trying to get a condition for my query like "last review date > 6months"

the last review date is attribute6 defined as standard date in dff column.

Code: [Select all] [Show/ hide]

SELECT DISTINCT pv.vendor_name,
                      pv.segment1,      
                      pv.attribute5 "SUPAMT",
                      pv.attribute7 "SUPCENTRALREF",
                      pv.end_date_active,
                      pv.attribute6 "LASTREVIEWDATE"
           FROM po_vendors pv
            WHERE attribute6 >= TO_CHAR(TRUNC(add_months(SYSDATE, -6)))       
       ORDER BY 1;

Its urgent,need regardinG same.i DEVELOPED IT..BUT ITS NOT GIVING ME THE RIGHT OUTPUT.

[Edit MC: remove URGENT from title)

[Updated on: Thu, 05 May 2011 10:17] by Moderator

Report message to a moderator

Re: URGENT :Last review date > 6 months [message #505668 is a reply to message #505666]

Wed, 04 May 2011 08:31

Michel Cadot
Messages: 68756
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

1) If attribute6 is a date then why do you convert your date into string using TO_CHAR?
2) it is an error to use TO_CHAR without a format.

Note that:
1/ Nothing is urgent in a forum
2/ Do NOT post in UPPER case

Regards
Michel

[Updated on: Wed, 04 May 2011 08:32]

Report message to a moderator

Re: URGENT :Last review date > 6 months [message #505670 is a reply to message #505668]

Wed, 04 May 2011 08:35

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

>ITS NOT GIVING ME THE RIGHT OUTPUT.

1) We have no idea what output SQL produces.
2) We have no idea what you deem to be "right output".

It would be helpful if you followed Posting Guidelines - http://www.orafaq.com/forum/t/88153/0/
It would be helpful if you provided expected/desired results & a detailed explanation how & why the test data gets transformed or organized.

Report message to a moderator

Re: URGENT :Last review date > 6 months [message #505671 is a reply to message #505670]

Wed, 04 May 2011 08:35

Roachcoach
Messages: 1576
Registered: May 2010
Location: UK

Senior Member

I can see no error in the output you supplied.

Report message to a moderator

URGENT condition needed [message #505672 is a reply to message #505666]

Wed, 04 May 2011 08:37

pallavigs
Messages: 37
Registered: March 2006
Location: India

Member

Hi All,

Last review date >6months

The last review date is attribute6 and defined as decreptive field with valuset STANDARD DATE.

Code: [Select all] [Show/ hide]

SELECT DISTINCT pv.vendor_name,
                      pv.segment1,      
                      pv.attribute5 ,
                      pv.attribute7 ,
                      pv.end_date_active,
                      pv.attribute6 
           FROM po_vendors pv
            WHERE attribute6 >= TO_CHAR(TRUNC(add_months(SYSDATE, -6)))       
       ORDER BY 1;

This is urgently needed.being trying various option from morning.

Thanks,Pal

Report message to a moderator

Re: URGENT condition needed [message #505673 is a reply to message #505672]

Wed, 04 May 2011 08:39

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

> WHERE attribute6 >= TO_CHAR(TRUNC(add_months(SYSDATE, -6)))
not as above but as below?
WHERE attribute6 >= add_months(SYSDATE, -6)

Report message to a moderator

Re: URGENT condition needed [message #505674 is a reply to message #505672]

Wed, 04 May 2011 08:40

pallavigs
Messages: 37
Registered: March 2006
Location: India

Member

IGNORE THIS THREAD...

Report message to a moderator

Re: URGENT condition needed [message #505676 is a reply to message #505674]

Wed, 04 May 2011 08:48

pallavigs
Messages: 37
Registered: March 2006
Location: India

Member

Hi..

Quote:

Noted about CAPS (URGENT).

I am able to get the output for all the columns.But,I am not getting the data for last 6 months but am able to retrieve for present month.
Attachign the sample data file.
How do i attach the file?

Report message to a moderator

Re: URGENT condition needed [message #505677 is a reply to message #505676]

Wed, 04 May 2011 08:52

Michel Cadot
Messages: 68756
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

Why do you want to attach a file? Just put it inside the post.

And feedback to my questions and comments:
Quote:

1) If attribute6 is a date then why do you convert your date into string using TO_CHAR?
2) it is an error to use TO_CHAR without a format.

Regards
Michel

Report message to a moderator

Re: URGENT condition needed [message #505678 is a reply to message #505676]

Wed, 04 May 2011 08:54

cookiemonster
Messages: 13966
Registered: September 2008
Location: Rainy Manchester

Senior Member

Click on the reply button then go to the file attachment section in the new screen.
However a lot of people won't download files so you'd be better off posting the contents directly in code tags.

Have you tried Blackswan's suggestion?
What is the datatype of attribute6?

Report message to a moderator

Re: URGENT condition needed [message #505680 is a reply to message #505678]

Wed, 04 May 2011 09:01

John Watson
Messages: 8974
Registered: January 2010
Location: Global Village

Senior Member

Hi - you might want to post this question in the EBS forum, there a lot of very helpful people in this one but (as you can see from the replies) they are not familiar with the way flexfields are used.

Report message to a moderator

Re: URGENT condition needed [message #505682 is a reply to message #505680]

Wed, 04 May 2011 09:11

pallavigs
Messages: 37
Registered: March 2006
Location: India

Member

I have tried the suggestion given by BlackSwain :

1.I gave to_char as it was giving me error like below.

2.

Code: [Select all] [Show/ hide]

SELECT DISTINCT pv.vendor_name,
                      pv.segment1,      
                      pv.attribute5 "SUPAMT",
                      pv.attribute7 "SUPCENTRALREF",
                      pv.end_date_active,
                      TO_CHAR(attribute6) "LASTREVIEWDATE",
					  TO_CHAR(SYSDATE - Attribute6)
           FROM po_vendors pv
		   WHERE attribute6 >= add_months(SYSDATE, -6)

Error :- Literal does not match the format.

Output :-

Code: [Select all] [Show/ hide]

Supplier Name	Supplier number	Supplier amount	Support Central Cross Reference #	Inactive date	Last review date
1212`2`	qe32323	0 to 2 lacs	123		2011/05/03 00:00:00
AGILENT TECHNOLOGIES SINGAPORE SALES PTE LTD	CV9015	2 to 20 lacs	12345678		2011/05/05 00:00:00
AIC ENTERPRISES	SR04072	2 to 20 lacs	5678	04-MAY-11	2011/05/05 00:00:00
Baraj Limited	SR50356	0 to 2 lacs	55555	31-MAY-11	2011/05/05 00:00:00
Pallavi	sr1234	0 to 2 lacs	1234		2011/05/05 00:00:00
Pallavi Tech	SR5676	0 to 2 lacs	12345		2011/05/04 00:00:00
Paltest 1	SR1234	2 to 20 lacs	007	02-MAY-11	2011/03/31 00:00:00
Patest1	PS2345	0 to 2 lacs	12345		2011/04/28 00:00:00
Rao test1	LR4567	2 to 20 lacs	1234	16-MAY-11	2010/01/30 00:00:00
Rao test2	LR7890	0 to 2 lacs	3456		2011/01/30 00:00:00
Test 1 supplier	TS1111	2 to 20 lacs	1234	29-APR-11	2011/01/27 00:00:00
Test1	ts123	0 to 2 lacs	123		2010/05/03 00:00:00
Test123	tt123	0 to 2 lacs	123		2010/05/03 00:00:00
paltes1	ps1234	0 to 2 lacs		27-APR-11	2011/04/24 00:00:00
***End of Report***

Report message to a moderator

Re: URGENT condition needed [message #505684 is a reply to message #505682]

Wed, 04 May 2011 09:13

pallavigs
Messages: 37
Registered: March 2006
Location: India

Member

John :-
I agree with you but,till my flexfield knowledge goes I feel I am failing in the condition.

Thanks.

Report message to a moderator

Re: URGENT condition needed [message #505686 is a reply to message #505684]

Wed, 04 May 2011 09:29

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

it is an error to use TO_CHAR without a format.
>TO_CHAR(attribute6) "LASTREVIEWDATE", TO_CHAR(SYSDATE - Attribute6)

[Updated on: Wed, 04 May 2011 09:30]

Report message to a moderator

Re: URGENT condition needed [message #505688 is a reply to message #505686]

Wed, 04 May 2011 09:39

Roachcoach
Messages: 1576
Registered: May 2010
Location: UK

Senior Member

Indeed, although 11gR2 lets you, at least for simple things:

Code: [Select all] [Show/ hide]

BANNER
----------------------------------------------------------------
Personal Oracle Database 11g Release 11.2.0.1.0 - Production
PL/SQL Release 11.2.0.1.0 - Production
CORE    11.2.0.1.0      Production
TNS for 32-bit Windows: Version 11.2.0.1.0 - Production
NLSRTL Version 11.2.0.1.0 - Production

Elapsed: 00:00:00.03
SCOTT@ORA11GMK > create table t as select sysdate dt from dual;

Table created.

Elapsed: 00:00:01.00
SCOTT@ORA11GMK > select dt from t;

DT
---------
04-MAY-11

Elapsed: 00:00:00.09
SCOTT@ORA11GMK > select to_char(dt) from t;

TO_CHAR(D
---------
04-MAY-11

Elapsed: 00:00:00.01

It'll be implicit conversion I have no doubt. Frankly I wish it wouldn't.

Report message to a moderator

Re: URGENT condition needed [message #505723 is a reply to message #505688]

Wed, 04 May 2011 12:05

pallavigs
Messages: 37
Registered: March 2006
Location: India

Member

So,what would be my next step for solving the issue?

Report message to a moderator

Re: URGENT condition needed [message #505726 is a reply to message #505723]

Wed, 04 May 2011 12:13

Michel Cadot
Messages: 68756
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

Michel Cadot wrote on Wed, 04 May 2011 15:52

...
And feedback to my questions and comments:
Quote:
1) If attribute6 is a date then why do you convert your date into string using TO_CHAR?
2) it is an error to use TO_CHAR without a format.

Regards
Michel

Report message to a moderator

Re: URGENT condition needed [message #505756 is a reply to message #505723]

Wed, 04 May 2011 13:43

manubatham20
Messages: 566
Registered: September 2010
Location: Seattle, WA, USA

Senior Member

Hi Pallavigs,

What about carefully listening to others and follow that other members suggested.

Code: [Select all] [Show/ hide]

SELECT DISTINCT pv.vendor_name,
                      pv.segment1,      
                      pv.attribute5 "SUPAMT",
                      pv.attribute7 "SUPCENTRALREF",
                      pv.end_date_active,
                      attribute6 "LASTREVIEWDATE",
                      SYSDATE-Attribute6
           FROM po_vendors pv
		   WHERE attribute6 >= add_months(SYSDATE, -6)

Try to execute the code above. And just ask a question to yourself, do you really know how to use to_char with date datatypes?

Regards,
Manu

Report message to a moderator

Re: URGENT condition needed [message #505830 is a reply to message #505756]

Thu, 05 May 2011 03:44

pallavigs
Messages: 37
Registered: March 2006
Location: India

Member

Yes Manu i know how to use the to_char functionality.

I had understood all the above mentioned points and was trying it and my code gave me the output by insertign this logic as below ;

Code: [Select all] [Show/ hide]

where attribute6 <= to_char(add_months(sysdate,-6),'YYYYMMDDHH24MISS')

Quote:

I was missing the right format.

But,my one more challenge is if i give in attibute6 the date as "6-nov-2010".The data should not come.But,i observed the data is coming till 31st Dec.2010.I should be getting data till 5-nov-2010 only.

The data for 1-jan-2011 does not come.

Thanks

Report message to a moderator

Re: URGENT condition needed [message #505831 is a reply to message #505830]

Thu, 05 May 2011 03:47

pallavigs
Messages: 37
Registered: March 2006
Location: India

Member

Michael Cadot :-

And feedback to my questions and comments:

Quote:
1) If attribute6 is a date then why do you convert your date into string using TO_CHAR?
I corrected it.Thanks.

2) it is an error to use TO_CHAR without a format.
Took note of it.

Report message to a moderator

Re: URGENT condition needed [message #505832 is a reply to message #505831]

Thu, 05 May 2011 03:49

cookiemonster
Messages: 13966
Registered: September 2008
Location: Rainy Manchester

Senior Member

Again, what datatype is attribute6? Is it really DATE? or is it a VARCHAR that happens to hold date data?

Report message to a moderator

Re: URGENT condition needed [message #505834 is a reply to message #505686]

Thu, 05 May 2011 03:52

ThomasG
Messages: 3212
Registered: April 2005
Location: Heilbronn, Germany

Senior Member

BlackSwan wrote on Wed, 04 May 2011 16:29

it is an error to use TO_CHAR without a format.

Now this is where I would have to disagree.

It indeed is an error and a possible cause of problems to use a to_date without a format, but when you write a report that will be used internationally, then using to_char at the display point without an explicit format would basically be the way to write it so that the report will always use whatever language and date format setting the user chooses to have.

Report message to a moderator

Re: URGENT condition needed [message #505835 is a reply to message #505832]

Thu, 05 May 2011 03:53

pallavigs
Messages: 37
Registered: March 2006
Location: India

Member

Attribute6 is Date datatype

Report message to a moderator

Re: URGENT condition needed [message #505836 is a reply to message #505834]

Thu, 05 May 2011 03:54

Michel Cadot
Messages: 68756
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

Sorry, I should say:
It is an error to use TO_CHAR without a format in a WHERE clause.
It may be an option in the display part (SELECT clause).

Regards
Michel

Report message to a moderator

Re: URGENT condition needed [message #505838 is a reply to message #505836]

Thu, 05 May 2011 03:58

pallavigs
Messages: 37
Registered: March 2006
Location: India

Member

How should I achieve in same condition to get data only till 5th nov 2010?

If it is 6-NOV-2010 or anythign till 31-dec-2010....the data should not show?

Any inputs?

Report message to a moderator

Re: URGENT condition needed [message #505841 is a reply to message #505838]

Thu, 05 May 2011 04:10

ThomasG
Messages: 3212
Registered: April 2005
Location: Heilbronn, Germany

Senior Member

The code *should* work that way, post your actual SQL*Plus session so that we can see where the problem is.

Report message to a moderator

Re: URGENT condition needed [message #505892 is a reply to message #505841]

Thu, 05 May 2011 09:06

manubatham20
Messages: 566
Registered: September 2010
Location: Seattle, WA, USA

Senior Member

Again we don't have your data, we don't know the exact table structre. Please post script for creating table and for inserting some sample data.

Regards,
Manu

Report message to a moderator

Re: URGENT condition needed [message #505919 is a reply to message #505831]

Thu, 05 May 2011 12:43

joy_division
Messages: 4963
Registered: February 2005
Location: East Coast USA

Senior Member

pallavigs wrote on Thu, 05 May 2011 04:47

Michael Cadot :-

And feedback to my questions and comments:

Quote:
1) If attribute6 is a date then why do you convert your date into string using TO_CHAR?
I corrected it.Thanks.

Prove it. Show us your query. I think you are just ignoring all of the things being told to you here and still do not understand that TO_CHAR is used on a DATE and TO_DATE is used on a CHAR.

Report message to a moderator

Re: URGENT condition needed [message #505974 is a reply to message #505919]

Thu, 05 May 2011 15:59

Barbara Boehmer
Messages: 9105
Registered: November 2002
Location: California, USA

Senior Member

joy_division wrote on Thu, 05 May 2011 10:43

Prove it. Show us your query. I think you are just ignoring all of the things being told to you here and still do not understand that TO_CHAR is used on a DATE and TO_DATE is used on a CHAR.

Another common possibility is that the original poster does not understand the difference between a DATE datatype and a VARCHAR2 datatype containing date data, which is why we need to see the actual table description, either in a create table statement or a describe of the table.

Report message to a moderator

Re: URGENT condition needed [message #506036 is a reply to message #505974]

Fri, 06 May 2011 04:23

John Watson
Messages: 8974
Registered: January 2010
Location: Global Village

Senior Member

People, you are beating your heads against a brick wall. This is a descriptive flexfield. These are varchars, with a kind-of data dictionary that lets you define a structure within them. It is an EBS thing. The only way to get sensible results is to go through the EBS data retrieval routines. Trying to run queries like this reliably may be impossible.

@OP, as I said before, you might want to try the EBS forum. Explain what you want to achieve there, and I'm sure you'll get qan answer.

Report message to a moderator

Previous Topic:	Disallow invoice creation at customer level
Next Topic:	How to Add a Miscellaneous expenses On an Invoice With Match ?

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Mon Apr 07 11:13:33 CDT 2025