[program-l] Re: Oracle SQL question
- From: "Eileen Lafond" <Eileen.Lafond@xxxxxxxxxxx>
- To: <program-l@xxxxxxxxxxxxx>
- Date: Tue, 18 Nov 2008 08:58:01 -0800
Thanks for the information about the convert command. I will check to
see the formatting of the pay end dt field.
Eileen La Fond
Phone (206) 386-0011
e.mail Eileen.LaFond@xxxxxxxxxxx
>>> "Birkir Gunnarsson" <birkir.gunnarsson@xxxxxxxxx> 11/18/2008 8:16
AM >>>
Hi
Ian is right, there are two ways to go about this query and one
additional
thing to watch out for.
In the query below you could include the b table in the main query and
then
match, like so:
SQL> Select count (*) from css_tbl_tl_data_dst a, css_tbl_tl_data_dst
b
Where a.pay_end_dt = b.pay_end_dt
And b.pay_end_dt = (select max(b.pay_end_dt) from css_tbl_tl_data_dst
b);
Or you can skip the b reference altogether and just retrieve the value
i.e.
where a.pay_end_d_dt = (select max(b.pay_end_dt) from
css_tbl_tl_data_dst);
One word of caution here though, make sure the pay_end_dt is stored in
a
predictable format and not with a time component like (10/17/2008
14:15)
because then you only get that time, if you want just the date
component you
have to either use
Trunk(pay_end_dt) on both sides of the where condition or you have to
use a
format specifier, such as
Convert(pay_end_dt,'mm.dd.yyyy');
(itðsnot convert, can't remember the function name off the top of my
head
but can easily look it up).
Cheers
-B
-----Original Message-----
From: program-l-bounce@xxxxxxxxxxxxx
[mailto:program-l-bounce@xxxxxxxxxxxxx]
On Behalf Of Ian Sharpe
Sent: 18. nóvember 2008 04:56
To: program-l@xxxxxxxxxxxxx
Subject: [program-l] Re: Oracle SQL question
Hi Eileen
Your subquery that defines table b only returns a single field, the
max
pay_end_dat. You are then trying to add conditions to the where
clause
using columns that don't exist in table b. What are you tryijng to do?
Looks
like you just don't need line 4 at all to me if you're simply trying to
find
the number of rows with the max pay_end_dat?
Cheers
Ian
-----Original Message-----
From: program-l-bounce@xxxxxxxxxxxxx
[mailto:program-l-bounce@xxxxxxxxxxxxx]
On Behalf Of Eileen Lafond
Sent: 17 November 2008 23:56
To: Program-l
Subject: [program-l] Oracle SQL question
Hi,
I have a script that I run every Monday morning that I want to put into
a
Cron Job. In order to do it I have to have it set up to automatically
find
the max pay period end date.
I have come up with the following code, but I keep getting errors.
Can
anyone tell me what I am doing rong?
***
SQL> Select count (*) from css_tbl_tl_data_dst a
2 Where a.pay_end_dt =
3 (select max(b.pay_end_dt) from css_tbl_tl_data_dst b)
4 and a.pay_end_dt = b.pay_end_dt;
and a.pay_end_dt = b.pay_end_dt
*
ERROR at line 4:
ORA-00904: "B"."PAY_END_DT": invalid identifier
SQL>
Eileen La Fond
Phone (206) 386-0011
e.mail Eileen.LaFond@xxxxxxxxxxx
** To leave the list, click on the immediately-following link:-
** [mailto:program-l-request@xxxxxxxxxxxxx?subject=unsubscribe]
** If this link doesn't work then send a message to:
** program-l-request@xxxxxxxxxxxxx
** and in the Subject line type
** unsubscribe
** For other list commands such as vacation mode, click on the
** immediately-following link:-
** [mailto:program-l-request@xxxxxxxxxxxxx?subject=faq]
** or send a message, to
** program-l-request@xxxxxxxxxxxxx with the Subject:- faq
** To leave the list, click on the immediately-following link:-
** [mailto:program-l-request@xxxxxxxxxxxxx?subject=unsubscribe]
** If this link doesn't work then send a message to:
** program-l-request@xxxxxxxxxxxxx
** and in the Subject line type
** unsubscribe
** For other list commands such as vacation mode, click on the
** immediately-following link:-
** [mailto:program-l-request@xxxxxxxxxxxxx?subject=faq]
** or send a message, to
** program-l-request@xxxxxxxxxxxxx with the Subject:- faq
** To leave the list, click on the immediately-following link:-
** [mailto:program-l-request@xxxxxxxxxxxxx?subject=unsubscribe]
** If this link doesn't work then send a message to:
** program-l-request@xxxxxxxxxxxxx
** and in the Subject line type
** unsubscribe
** For other list commands such as vacation mode, click on the
** immediately-following link:-
** [mailto:program-l-request@xxxxxxxxxxxxx?subject=faq]
** or send a message, to
** program-l-request@xxxxxxxxxxxxx with the Subject:- faq
** To leave the list, click on the immediately-following link:-
** [mailto:program-l-request@xxxxxxxxxxxxx?subject=unsubscribe]
** If this link doesn't work then send a message to:
** program-l-request@xxxxxxxxxxxxx
** and in the Subject line type
** unsubscribe
** For other list commands such as vacation mode, click on the
** immediately-following link:-
** [mailto:program-l-request@xxxxxxxxxxxxx?subject=faq]
** or send a message, to
** program-l-request@xxxxxxxxxxxxx with the Subject:- faq
Other related posts:
