| SQL/PLSQL [message #635916] |
Sun, 12 April 2015 00:48  |
 |
ifti6650
Messages: 10
Registered: March 2015
Location: DHAKA
|
Junior Member |
|
|
HI I HAVE WROTE A QUERY WHICH I WANT TO SHOW DATE BETWEEN FROM MIN DATE TO A DATE RANGE GIVEN BY BIND VARIABLE.
>>>> LINE BETWEEN MIN DATE AND :FDATE <<<
EXEMPLE IS BELOW WITH MY QUERY..
------------------------------------
SELECT SUM(NVL(B.CREDIT,0))- SUM(NVL(B.DEBIT,0)) INTO X
FROM SUBHEAD_NOA A, GL_DTL B, TYPE_OF_ACCOUNTS C, NAME_OF_ACCOUNTS D, GL_MST E
WHERE B.SUBHEAD_NOA_ID = A.SUBHEAD_NOA_ID
AND C.TOA_ID = D.TOA_ID
AND D.NOA_ID = A.NOA_ID
and D.NOA_ID NOT IN ('105-0041','105-0040')
AND B.VOUCHER_NO = E.VOUCHER_NO
AND A.SUBHEAD_NOA_id = '105-0039-61'
>>> AND TO_DATE(E.VOUCHER_DATE,'DD/MM/RRRR') BETWEEN TO_DATE(MIN(E.VOUCHER_DATE),'DD/MM/RRRR') AND TO_DATE(:UPTO,'DD/MM/RRRR') <<<
AND C.TOA_ID = '105'
GROUP BY c.TOA_NAME,D.NOA_NAME,a.SUBHEAD_NOA_NAME;
------------------------------------
|
|
|
|
| Re: SQL/PLSQL [message #635917 is a reply to message #635916] |
Sun, 12 April 2015 01:30  |
 |
Michel Cadot
Messages: 68645
Registered: March 2007
Location: Nanterre, France, http://...
|
Senior Member
Account Moderator |
|
|
1/ Do not post in UPPER case this is read as SHOUTING.
2/ Please read OraFAQ Forum Guide and How to use [code] tags and make your code easier to read.
3/ Also always post your Oracle version, with 4 decimals.
4/ With any SQL or PL/SQL question, please, Post a working Test case: create table (including all constraints) and insert statements along with the result you want with these data then we will be able work with your table and data. Explain with words and sentences the rules that lead to this result.
5/ Quote:HOW TO USE MIN FUNCTION USED IN WHERE CONDITION
Generally speaking you use HAVING
6/ Quote:I WANT TO SHOW DATE BETWEEN FROM MIN DATE TO A DATE RANGE GIVEN BY BIND VARIABLE
Just use mydate <= :bindvardate as no date is less than MIN(mydate).
In other words:
mydate between min(mydate) and :bindvardate
is equivalent to
mydate <= :bindvardate
7/ Quote:TO_DATE(E.VOUCHER_DATE,'DD/MM/RRRR')
If E.VOUCHER_DATE is of DATE datatype, TO_DATE on a date is silly
If it is not then you have a design error, a field containing a date MUST be of DATE datatype
In addition, in this case, "MIN(E.VOUCHER_DATE)" may not give the min date.
[Updated on: Wed, 15 April 2015 02:41] Report message to a moderator |
|
|
|
|
|
| Re: SQL/PLSQL [message #636026 is a reply to message #636025] |
Wed, 15 April 2015 02:42 |
|
Michel Cadot
Messages: 68645
Registered: March 2007
Location: Nanterre, France, http://...
|
Senior Member
Account Moderator |
|
|
So what is the data type of VOUCHER_DATE and what is the final query?
Post them as it may then help other people coming here to find a solution to their issue.
[Updated on: Wed, 15 April 2015 02:43] Report message to a moderator |
|
|
|
|
|
|
|
| Re: SQL/PLSQL [message #636035 is a reply to message #636029] |
Wed, 15 April 2015 05:21 |
Lalit Kumar B
Messages: 3174
Registered: May 2013
Location: World Wide on the Web
|
Senior Member |
|
|
ifti6650 wrote on Wed, 15 April 2015 14:05data type is date, I wanted to use to_date function in column ''VOUCHER_DATE'' for formatting date. That was my objective.
Never, ever use TO_DATE on a DATE. Oracle will do an implicit conversion. First, from date into string. And then, from string into date using locale-specific nls_date_format.
For example,
TO_DATE(sysdate, 'dd-mm-yyyy')
is implicitly converted by Oracle as:
TO_DATE(TO_CHAR(sysdate, '<locale-specific nls_date_format>'), 'dd-mm-yyyy')
So, if your nls_date_format is not same as the format you mentioned in the TO_DATE expressions(in the example it is 'dd-mm-yyyy'), you are highly likely to get an error.
You could verify this in the explain plan of the query.
Regards,
Lalit
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
