|
What you say makes sense. Rob Berendt -- Group Dekko Services, LLC Dept 01.073 PO Box 2000 Dock 108 6928N 400E Kendallville, IN 46755 http://www.dekko.com "Goodbar, Loyd (ETS - Water Valley)" <LGoodbar@xxxxxxxxxxxxxx> Sent by: midrange-l-bounces@xxxxxxxxxxxx 02/22/2005 10:03 AM Please respond to Midrange Systems Technical Discussion <midrange-l@xxxxxxxxxxxx> To Midrange Systems Technical Discussion <midrange-l@xxxxxxxxxxxx> cc Subject RE: That one row one column file for SQL I don't think so. Trying this on a table with 2.7 million records yields instantaneous results. Like Charles said, the optimizer should stop evaluating exists when the first row is found. Therefore, it takes as much time to evaluate not exists... as it does to find the first row. Using exist select * should be the fastest way to find rows since we want any row, without qualification. Also, DB2 already knows the current row count for the table. Or, I could argue the optimizer cached the result from the previous operation, and already knows there were rows existing. Loyd Goodbar Senior programmer/analyst BorgWarner E/TS Water Valley 662-473-5713 -----Original Message----- From: midrange-l-bounces+lgoodbar=borgwarner.com@xxxxxxxxxxxx [mailto:midrange-l-bounces+lgoodbar=borgwarner.com@xxxxxxxxxxxx] On Behalf Of rob@xxxxxxxxx Sent: Tuesday, February 22, 2005 08:36 To: Midrange Systems Technical Discussion Subject: RE: That one row one column file for SQL Wouldn't the not exists then have to read all records? Rob Berendt -- Group Dekko Services, LLC Dept 01.073 PO Box 2000 Dock 108 6928N 400E Kendallville, IN 46755 http://www.dekko.com "Goodbar, Loyd (ETS - Water Valley)" <LGoodbar@xxxxxxxxxxxxxx> Sent by: midrange-l-bounces@xxxxxxxxxxxx 02/22/2005 08:22 AM Please respond to Midrange Systems Technical Discussion <midrange-l@xxxxxxxxxxxx> To Midrange Systems Technical Discussion <midrange-l@xxxxxxxxxxxx> cc Subject RE: That one row one column file for SQL Hey Rob, It turns out that sysdummy1 is useful in this context. This will tell you whether there are records in a file without doing the count. File prod_dta/deptmap contains 38 reocrds. File loyd/norecords contains 0 records. Try this SQL: select 1 from sysibm/sysdummy1 where exists(select * from prod_dta/deptmap) union all select 0 from sysibm/sysdummy1 where not exists(select * from prod_dta/deptmap) Constant value 1 Replace "prod_dta/deptmap" with "loyd/norecords" Constant value 0 Loyd Goodbar Senior programmer/analyst BorgWarner E/TS Water Valley 662-473-5713 -----Original Message----- From: midrange-l-bounces@xxxxxxxxxxxx [mailto:midrange-l-bounces@xxxxxxxxxxxx] On Behalf Of rob@xxxxxxxxx Sent: Monday, February 21, 2005 15:13 To: Midrange Systems Technical Discussion Subject: RE: That one row one column file for SQL Ok, now I understand. Tried to use a CASE statement, but I hope it's just because I am doing something wrong. C/exec sql C+ Set :IsThere= C+ Case C+ When exists(select * from thisfile) SQL0104 Token EXISTS was not valid. Valid tokens: <IDENTIFIER> <INTEGER> <CHARSTRING> <GRAPHSTRING> C+ then '1' C+ else '0' C+ End C/end-exec Rob Berendt -- Group Dekko Services, LLC Dept 01.073 PO Box 2000 Dock 108 6928N 400E Kendallville, IN 46755 http://www.dekko.com "Walden H. Leverich" <WaldenL@xxxxxxxxxxxxxxx> Sent by: midrange-l-bounces@xxxxxxxxxxxx 02/21/2005 02:17 PM Please respond to Midrange Systems Technical Discussion <midrange-l@xxxxxxxxxxxx> To "Midrange Systems Technical Discussion" <midrange-l@xxxxxxxxxxxx> cc Subject RE: That one row one column file for SQL Because the exists clause only needs to know _if_ there are rows, not how many. If the optimizer has at least 1/2 a brain it will stop looking for rows once it finds one, where count() requires that it find all the rows. -Walden ------------ Walden H Leverich III President & CEO Tech Software (516) 627-3800 x11 WaldenL@xxxxxxxxxxxxxxx http://www.TechSoftInc.com Quiquid latine dictum sit altum viditur. (Whatever is said in Latin seems profound.) -----Original Message----- From: midrange-l-bounces@xxxxxxxxxxxx [mailto:midrange-l-bounces@xxxxxxxxxxxx] On Behalf Of rob@xxxxxxxxx Sent: Monday, 21 February, 2005 12:05 To: Midrange Systems Technical Discussion Subject: RE: That one row one column file for SQL And why would that be immensely better than select count(*) into :TheCount from notes where owner=22 Rob Berendt -- Group Dekko Services, LLC Dept 01.073 PO Box 2000 Dock 108 6928N 400E Kendallville, IN 46755 http://www.dekko.com "Walden H. Leverich" <WaldenL@xxxxxxxxxxxxxxx> Sent by: midrange-l-bounces@xxxxxxxxxxxx 02/21/2005 11:53 AM Please respond to Midrange Systems Technical Discussion <midrange-l@xxxxxxxxxxxx> To "Midrange Systems Technical Discussion" <midrange-l@xxxxxxxxxxxx> cc Subject RE: That one row one column file for SQL Life would be easier if DB2/400 allowed a select w/o a from clause. I've hit several occasions where I needed to know if a row existed, but I didn't care what it was or how many there were. I'd like to do: Select 1 where exists(select * from notes where Owner=22) However, SQL wants that from clause. -Walden ------------ Walden H Leverich III President & CEO Tech Software (516) 627-3800 x11 WaldenL@xxxxxxxxxxxxxxx http://www.TechSoftInc.com Quiquid latine dictum sit altum viditur. (Whatever is said in Latin seems profound.) -----Original Message----- From: midrange-l-bounces+waldenl=techsoftinc.com@xxxxxxxxxxxx [mailto:midrange-l-bounces+waldenl=techsoftinc.com@xxxxxxxxxxxx] On Behalf Of DeLong, Eric Sent: Monday, 21 February, 2005 11:45 To: 'Midrange Systems Technical Discussion' Subject: RE: That one row one column file for SQL Or, one of the workarounds for getting a full timestamp value (no 000): C/Exec SQL C+ Set :Timestp = current_timestamp C/End-Exec Eric DeLong Sally Beauty Company MIS-Project Manager (BSG) 940-297-2863 or ext. 1863 -----Original Message----- From: rob@xxxxxxxxx [mailto:rob@xxxxxxxxx] Sent: Monday, February 21, 2005 8:05 AM To: Midrange Systems Technical Discussion Subject: RE: That one row one column file for SQL That's all well and good, but one thing to keep in mind is that it is often not necessary to even use that file. For example replace select current date from sysibm/sysdummy1 into :mydate with Values current date into :mydate Rob Berendt -- Group Dekko Services, LLC Dept 01.073 PO Box 2000 Dock 108 6928N 400E Kendallville, IN 46755 http://www.dekko.com "Wilt, Charles" <CWilt@xxxxxxxxxxxx> Sent by: midrange-l-bounces@xxxxxxxxxxxx 02/18/2005 04:05 PM Please respond to Midrange Systems Technical Discussion <midrange-l@xxxxxxxxxxxx> To "Midrange Systems Technical Discussion" <midrange-l@xxxxxxxxxxxx> cc Subject RE: That one row one column file for SQL sysibm/sysdummy1 HTH, Charles Wilt iSeries Systems Administrator / Developer Mitsubishi Electric Automotive America ph: 513-573-4343 fax: 513-398-1121 > -----Original Message----- > From: midrange-l-bounces@xxxxxxxxxxxx > [mailto:midrange-l-bounces@xxxxxxxxxxxx]On Behalf Of Goodbar, > Loyd (ETS > - Water Valley) > Sent: Friday, February 18, 2005 4:00 PM > To: Midrange Systems Technical Discussion > Subject: That one row one column file for SQL > > > It's Friday. I can't think of the name of that IBM-supplied > file you can use > in SQL that has one column and one row. Anyone remember it of > the top of > their heads? > > > > Thanks, > > Loyd > > > > Loyd Goodbar > > Senior programmer/analyst > > BorgWarner > > E/TS Water Valley > > 662-473-5713 > > > > -- > This is the Midrange Systems Technical Discussion > (MIDRANGE-L) mailing list > To post a message email: MIDRANGE-L@xxxxxxxxxxxx > To subscribe, unsubscribe, or change list options, > visit: http://lists.midrange.com/mailman/listinfo/midrange-l > or email: MIDRANGE-L-request@xxxxxxxxxxxx > Before posting, please take a moment to review the archives > at http://archive.midrange.com/midrange-l. > > -- This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list To post a message email: MIDRANGE-L@xxxxxxxxxxxx To subscribe, unsubscribe, or change list options, visit: http://lists.midrange.com/mailman/listinfo/midrange-l or email: MIDRANGE-L-request@xxxxxxxxxxxx Before posting, please take a moment to review the archives at http://archive.midrange.com/midrange-l. -- This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list To post a message email: MIDRANGE-L@xxxxxxxxxxxx To subscribe, unsubscribe, or change list options, visit: http://lists.midrange.com/mailman/listinfo/midrange-l or email: MIDRANGE-L-request@xxxxxxxxxxxx Before posting, please take a moment to review the archives at http://archive.midrange.com/midrange-l. -- This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list To post a message email: MIDRANGE-L@xxxxxxxxxxxx To subscribe, unsubscribe, or change list options, visit: http://lists.midrange.com/mailman/listinfo/midrange-l or email: MIDRANGE-L-request@xxxxxxxxxxxx Before posting, please take a moment to review the archives at http://archive.midrange.com/midrange-l. -- This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list To post a message email: MIDRANGE-L@xxxxxxxxxxxx To subscribe, unsubscribe, or change list options, visit: http://lists.midrange.com/mailman/listinfo/midrange-l or email: MIDRANGE-L-request@xxxxxxxxxxxx Before posting, please take a moment to review the archives at http://archive.midrange.com/midrange-l. -- This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list To post a message email: MIDRANGE-L@xxxxxxxxxxxx To subscribe, unsubscribe, or change list options, visit: http://lists.midrange.com/mailman/listinfo/midrange-l or email: MIDRANGE-L-request@xxxxxxxxxxxx Before posting, please take a moment to review the archives at http://archive.midrange.com/midrange-l. -- This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list To post a message email: MIDRANGE-L@xxxxxxxxxxxx To subscribe, unsubscribe, or change list options, visit: http://lists.midrange.com/mailman/listinfo/midrange-l or email: MIDRANGE-L-request@xxxxxxxxxxxx Before posting, please take a moment to review the archives at http://archive.midrange.com/midrange-l. -- This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list To post a message email: MIDRANGE-L@xxxxxxxxxxxx To subscribe, unsubscribe, or change list options, visit: http://lists.midrange.com/mailman/listinfo/midrange-l or email: MIDRANGE-L-request@xxxxxxxxxxxx Before posting, please take a moment to review the archives at http://archive.midrange.com/midrange-l. -- This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list To post a message email: MIDRANGE-L@xxxxxxxxxxxx To subscribe, unsubscribe, or change list options, visit: http://lists.midrange.com/mailman/listinfo/midrange-l or email: MIDRANGE-L-request@xxxxxxxxxxxx Before posting, please take a moment to review the archives at http://archive.midrange.com/midrange-l. -- This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list To post a message email: MIDRANGE-L@xxxxxxxxxxxx To subscribe, unsubscribe, or change list options, visit: http://lists.midrange.com/mailman/listinfo/midrange-l or email: MIDRANGE-L-request@xxxxxxxxxxxx Before posting, please take a moment to review the archives at http://archive.midrange.com/midrange-l. -- This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list To post a message email: MIDRANGE-L@xxxxxxxxxxxx To subscribe, unsubscribe, or change list options, visit: http://lists.midrange.com/mailman/listinfo/midrange-l or email: MIDRANGE-L-request@xxxxxxxxxxxx Before posting, please take a moment to review the archives at http://archive.midrange.com/midrange-l.
As an Amazon Associate we earn from qualifying purchases.
This mailing list archive is Copyright 1997-2024 by midrange.com and David Gibbs as a compilation work. Use of the archive is restricted to research of a business or technical nature. Any other uses are prohibited. Full details are available on our policy page. If you have questions about this, please contact [javascript protected email address].
Operating expenses for this site are earned using the Amazon Associate program and Google Adsense.