Home » Applications » Oracle Fusion Apps & E-Business Suite » :Last review date > 6 months (5 Merged)
:Last review date > 6 months (5 Merged) [message #505666] Wed, 04 May 2011 08:29 Go to next message
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.
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 Go to previous messageGo to next message
Michel Cadot
Messages: 67956
Registered: March 2007
Location: Nanterre, France, http://...
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 Go to previous messageGo to next message
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.
Re: URGENT :Last review date > 6 months [message #505671 is a reply to message #505670] Wed, 04 May 2011 08:35 Go to previous messageGo to next message
Roachcoach
Messages: 1576
Registered: May 2010
Location: UK
Senior Member
I can see no error in the output you supplied.
URGENT condition needed [message #505672 is a reply to message #505666] Wed, 04 May 2011 08:37 Go to previous messageGo to next message
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.

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
Re: URGENT condition needed [message #505673 is a reply to message #505672] Wed, 04 May 2011 08:39 Go to previous messageGo to next message
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)
Re: URGENT condition needed [message #505674 is a reply to message #505672] Wed, 04 May 2011 08:40 Go to previous messageGo to next message
pallavigs
Messages: 37
Registered: March 2006
Location: India
Member
IGNORE THIS THREAD...
Re: URGENT condition needed [message #505676 is a reply to message #505674] Wed, 04 May 2011 08:48 Go to previous messageGo to next message
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?
Re: URGENT condition needed [message #505677 is a reply to message #505676] Wed, 04 May 2011 08:52 Go to previous messageGo to next message
Michel Cadot
Messages: 67956
Registered: March 2007
Location: Nanterre, France, http://...
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
Re: URGENT condition needed [message #505678 is a reply to message #505676] Wed, 04 May 2011 08:54 Go to previous messageGo to next message
cookiemonster
Messages: 13894
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?
Re: URGENT condition needed [message #505680 is a reply to message #505678] Wed, 04 May 2011 09:01 Go to previous messageGo to next message
John Watson
Messages: 8598
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.
Re: URGENT condition needed [message #505682 is a reply to message #505680] Wed, 04 May 2011 09:11 Go to previous messageGo to next message
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.
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 :-

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***


Re: URGENT condition needed [message #505684 is a reply to message #505682] Wed, 04 May 2011 09:13 Go to previous messageGo to next message
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.
Re: URGENT condition needed [message #505686 is a reply to message #505684] Wed, 04 May 2011 09:29 Go to previous messageGo to next message
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 Go to previous messageGo to next message
Roachcoach
Messages: 1576
Registered: May 2010
Location: UK
Senior Member
Indeed, although 11gR2 lets you, at least for simple things:

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.
Re: URGENT condition needed [message #505723 is a reply to message #505688] Wed, 04 May 2011 12:05 Go to previous messageGo to next message
pallavigs
Messages: 37
Registered: March 2006
Location: India
Member
So,what would be my next step for solving the issue?

Re: URGENT condition needed [message #505726 is a reply to message #505723] Wed, 04 May 2011 12:13 Go to previous messageGo to next message
Michel Cadot
Messages: 67956
Registered: March 2007
Location: Nanterre, France, http://...
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

Re: URGENT condition needed [message #505756 is a reply to message #505723] Wed, 04 May 2011 13:43 Go to previous messageGo to next message
manubatham20
Messages: 562
Registered: September 2010
Location: Seattle, WA, USA
Senior Member

Hi Pallavigs,

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

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
Re: URGENT condition needed [message #505830 is a reply to message #505756] Thu, 05 May 2011 03:44 Go to previous messageGo to next message
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 ;
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
Re: URGENT condition needed [message #505831 is a reply to message #505830] Thu, 05 May 2011 03:47 Go to previous messageGo to next message
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.

Re: URGENT condition needed [message #505832 is a reply to message #505831] Thu, 05 May 2011 03:49 Go to previous messageGo to next message
cookiemonster
Messages: 13894
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?
Re: URGENT condition needed [message #505834 is a reply to message #505686] Thu, 05 May 2011 03:52 Go to previous messageGo to next message
ThomasG
Messages: 3211
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.

Re: URGENT condition needed [message #505835 is a reply to message #505832] Thu, 05 May 2011 03:53 Go to previous messageGo to next message
pallavigs
Messages: 37
Registered: March 2006
Location: India
Member
Attribute6 is Date datatype
Re: URGENT condition needed [message #505836 is a reply to message #505834] Thu, 05 May 2011 03:54 Go to previous messageGo to next message
Michel Cadot
Messages: 67956
Registered: March 2007
Location: Nanterre, France, http://...
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
Re: URGENT condition needed [message #505838 is a reply to message #505836] Thu, 05 May 2011 03:58 Go to previous messageGo to next message
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?
Re: URGENT condition needed [message #505841 is a reply to message #505838] Thu, 05 May 2011 04:10 Go to previous messageGo to next message
ThomasG
Messages: 3211
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.
Re: URGENT condition needed [message #505892 is a reply to message #505841] Thu, 05 May 2011 09:06 Go to previous messageGo to next message
manubatham20
Messages: 562
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
Re: URGENT condition needed [message #505919 is a reply to message #505831] Thu, 05 May 2011 12:43 Go to previous messageGo to next message
joy_division
Messages: 4953
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.
Re: URGENT condition needed [message #505974 is a reply to message #505919] Thu, 05 May 2011 15:59 Go to previous messageGo to next message
Barbara Boehmer
Messages: 8922
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.
Re: URGENT condition needed [message #506036 is a reply to message #505974] Fri, 06 May 2011 04:23 Go to previous message
John Watson
Messages: 8598
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.
Previous Topic: Disallow invoice creation at customer level
Next Topic: How to Add a Miscellaneous expenses On an Invoice With Match ?
Goto Forum:
  


Current Time: Tue Sep 28 12:14:27 CDT 2021