Re: testtttttttttststststst
I think your keyboard is stuck.. ;-) -- Original Message -- Reply-To: [EMAIL PROTECTED] Date: Tue, 19 Mar 2002 11:28:57 -0800 -- Daniþment Gazi Ünal http://www.unal-bilisim.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Danisment Gazi Unal (Unal Bilisim) INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Johnston, Steve INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Flatten data help please?
Still learning! Appreciate the use of bandwidth ahead of time! Oracle 8.1.4 Data exists in 3 tables tblIdWeeks WeekID,WeekStart, WeekEnd tblIDDates AirID, WeekID,DayNum (0 thru 6),DayDate Leave_Detl_tbl Effective_Dt, Leave_type, Input_AM existing sql is:SELECT tblIDAirWeeks.WeekStart, tblIDAirWeeks.WeekEnd, tblIdAirDates.AirDayDate, [ORIG_LEAVE_TYPE_CD] [ORIGINAL_INPUT_AM] AS Expr1 FROM tblIDAirWeeks INNER JOIN (tblIdAirDates INNER JOIN AMS_AHRSADM_LEAVE_DETL_TBL ON tblIdAirDates.AirDayDate = AMS_AHRSADM_LEAVE_DETL_TBL.EFFECTIVE_DT) ON tblIDAirWeeks.AirWkID = tblIdAirDates.AirWkID WHERE (((tblIDAirWeeks.WeekStart)#12/27/2000#) AND ((tblIDAirWeeks.WeekEnd)#1/5/2002#) AND ((AMS_AHRSADM_LEAVE_DETL_TBL.INTERNAL_EMPL_ID)=000357)); I get data returned like so: WeekStart WeekEnd AirDayDate Expr1 1/21/011/27/01 1/23/01SICKF 450 1/21/011/27/01 1/24/01FLH 450 1/21/011/27/01 1/25/01FLH 450 1/21/011/27/01 1/26/01VAC 450 And would like a result that looks like this: WeekStart MONTUES WED THURS FRIWeekEnd 1/21/01SICKF 450 FLH 450 FLH 450 VAC 450 1/27/01 -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Johnston, Steve INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Outer join sql help?
This sql doesn't give me the additional rows I'm expecting.. Is this because of the
sum and group by expressions? Is there a way around this behaviour?
Oracle 8.1.6
SELECT
a.LEAVE_CATEG_ID,
a.LEAVE_TYPE_CD,
a.LPET_LONG_DD,
Sum(l.ORIGINAL_INPUT_AM)
FROM EFF_LEVPOL_EVNT_TYPE_TBL a, LEAVE_DETL_TBL l
WHERE a.LEAVE_TYPE_CD(+) = l.evnt_type_cd AND l.INTERNAL_EMPL_ID='000357'
AND l.EFFECTIVE_DT Between TO_DATE('01/01/2001','MM/DD/')
And TO_DATE('12/31/2001','MM/DD/')
GROUP BY a.LEAVE_CATEG_ID, a.LEAVE_TYPE_CD, a.LPET_LONG_DD;
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Johnston, Steve
INET: [EMAIL PROTECTED]
Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051
San Diego, California-- Public Internet access / Mailing Lists
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
RE: Outer join sql help?
I want to show all the available leave types in table a even if there are no records
in table l
-- Original Message --
Reply-To: [EMAIL PROTECTED]
Date: Wed, 12 Dec 2001 10:15:33 -0800
SELECT a.LEAVE_CATEG_ID,
a.LEAVE_TYPE_CD,
a.LPET_LONG_DD,
Sum(l.ORIGINAL_INPUT_AM)
FROM EFF_LEVPOL_EVNT_TYPE_TBL a, LEAVE_DETL_TBL l
WHERE a.LEAVE_TYPE_CD(+) = l.evnt_type_cd
AND l.INTERNAL_EMPL_ID='000357'
AND l.EFFECTIVE_DT Between TO_DATE('01/01/2001','MM/DD/')
And TO_DATE('12/31/2001','MM/DD/')
GROUP BY a.LEAVE_CATEG_ID, a.LEAVE_TYPE_CD, a.LPET_LONG_DD;
This should work if you are looking for LEAVE_DETL_TBL records without
records in EFF_LEVPOL_EVNT_TYPE, which does not sound right.
I'm thinking you want the a records even when there is no supporting l
records:
SELECT a.LEAVE_CATEG_ID,
a.LEAVE_TYPE_CD,
a.LPET_LONG_DD,
Sum(l.ORIGINAL_INPUT_AM)
FROM EFF_LEVPOL_EVNT_TYPE_TBL a, LEAVE_DETL_TBL l
WHERE l.LEAVE_TYPE_CD(+) = a.evnt_type_cd
AND l.INTERNAL_EMPL_ID(+) = '000357'
AND l.EFFECTIVE_DT Between TO_DATE('01/01/2001','MM/DD/')
And TO_DATE('12/31/2001','MM/DD/')
GROUP BY a.LEAVE_CATEG_ID, a.LEAVE_TYPE_CD, a.LPET_LONG_DD;
But then the date range gives you a problem, so:
SELECT a.LEAVE_CATEG_ID,
a.LEAVE_TYPE_CD,
a.LPET_LONG_DD,
Sum(decode(sign(l.EFFECTIVE_DT -
TO_DATE('01/01/2001','MM/DD/'),
1,0,NULL,0,
decode(l.EFFECTIVE_DT -
TO_DATE('12/31/2001','MM/DD/'),
1, 0 , l.ORIGINAL_INPUT_AM
) ) )
FROM EFF_LEVPOL_EVNT_TYPE_TBL a, LEAVE_DETL_TBL l
WHERE l.LEAVE_TYPE_CD(+) = a.evnt_type_cd
AND l.INTERNAL_EMPL_ID(+) = '000357'
GROUP BY a.LEAVE_CATEG_ID, a.LEAVE_TYPE_CD, a.LPET_LONG_DD;
Nasty - or is my initial guess off base?
Brian Norrell
Manager, MPI Development
QuadraMed
511 E John Carpenter Frwy, Su 500
Irving, TX 75062
(972) 831-6600
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Norrell, Brian
INET: [EMAIL PROTECTED]
Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051
San Diego, California-- Public Internet access / Mailing Lists
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Johnston, Steve
INET: [EMAIL PROTECTED]
Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051
San Diego, California-- Public Internet access / Mailing Lists
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
RE: Outer join sql SOLVED!
Brian and all, thanks for the help, here is the sql that does what I wanted to do!
This gives me all the leave types in table 'a' and either a sum or null for values
from table 'b'. Thanks again!
SELECT
a.LEAVE_CATEG_ID,
a.LEAVE_TYPE_CD,
a.LPET_LONG_DD,
Sum(l.ORIGINAL_INPUT_AM)
FROM EFF_LEVPOL_EVNT_TYPE_TBL a, LEAVE_DETL_TBL l
WHERE a.LEAVE_TYPE_CD = l.evnt_type_cd(+)
AND l.INTERNAL_EMPL_ID(+)='000357'
AND l.EFFECTIVE_DT(+) Between TO_DATE('01/01/2001','MM/DD/')
And TO_DATE('12/31/2001','MM/DD/')
GROUP BY a.LEAVE_CATEG_ID, a.LEAVE_TYPE_CD, a.LPET_LONG_DD
-- Original Message --
Reply-To: [EMAIL PROTECTED]
Date: Wed, 12 Dec 2001 10:15:33 -0800
SELECT a.LEAVE_CATEG_ID,
a.LEAVE_TYPE_CD,
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Johnston, Steve
INET: [EMAIL PROTECTED]
Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051
San Diego, California-- Public Internet access / Mailing Lists
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
Access to Oracle sql conversion question
Oracle version 7.3.2 Access Sql that works SELECT FIRST([columnName]) as ftmp from [tablename] where etc Oracle doesn't work... looks like FIRST function doesn't exist.. How can I replace this functionality with Oracle SQL? Any help greatly appreciated! Steve Johnston -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Johnston, Steve INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: GAD! Access to Oracle sql conversion question
It always amazes me how 20 seconds after I post a question to a newsgroup for all to see that I find a solution that answers! Thanks anyway for the bandwidth... -- Original Message -- Reply-To: [EMAIL PROTECTED] Date: Tue, 07 Aug 2001 05:55:23 -0800 Oracle version 7.3.2 Access Sql that works SELECT FIRST -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Johnston, Steve INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
SQL translation from Access/VB/ADO to Oracle/VB/ADO
I have a sql string generated in a vb function that returns a sql string. Worked fine in Access but as it uses internal vb functions in a couple of places it fails in Oracle. If someone could point the error of my ways I'd appreciate the language lesson.. (I take no responsibility for the table design!.. existing app!) Working Access String: SELECT H.SSNO, CDbl([A.GIFTVAL]*-1) AS GVal, H.EMPNO, H.FNAME, H.LNAME FROM H1EMP H INNER JOIN AWARDS_AWARDS A ON A.EmpNo = H.EmpNo WHERE ((A.ORDERNO)=' CStr(6) ' AND ((A.BATCHKEY)=6) ORDER BY H.LNAME, H.FNAME; Non-Working Oracle String: SELECT H.SSNO, TO_NUMBER([A.GIFTVAL]*-1) AS GVal, H.EMPNO, H.FNAME, H.LNAME FROM H1EMP H INNER JOIN AWARDS_AWARDS A ON A.EmpNo = H.EmpNo WHERE ((A.ORDERNO)= TO_CHAR(6) AND ((A.BATCHKEY)=6) ORDER BY H.LNAME, H.FNAME; -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Johnston, Steve INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Sql loader help please (newbie)
Thanks in advance for help, been lurking for a couple of weeks. I am using sqlldr73.exe to load a text file into an oracle 7.3.2.1.1 table. Field datatype is number(14,0). What is the correct statement in the tablename.clr file that will correctly load the data. The clr file info latest attempt for the two fields looks like this SICK_ACCRUAL position (569:583) number(14,0),VAC_ACCRUAL position (585:599) number(14,0) and doesn't work. I have seen example showing an INTEGER EXTERNAL definition but I'm too wet behind the ears to know what is missing... -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Johnston, Steve INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
