| :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.
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 |
|
|
|
|
|
|
|
|
|
| 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.
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 #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?
|
|
|
|
|
|
| Re: URGENT condition needed [message #505678 is a reply to message #505676] |
Wed, 04 May 2011 08:54 |
cookiemonster
Messages: 13962
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 |
John Watson
Messages: 8962
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 |
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 #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:
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 #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.
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 |
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 |
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 #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:29it 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 #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?
|
|
|
|
|
|
|
|
| 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:47Michael 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 #506036 is a reply to message #505974] |
Fri, 06 May 2011 04:23 |
John Watson
Messages: 8962
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.
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
