RE: That one row one column file for SQL -- MIDRANGE-L

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.