× The internal search function is temporarily non-functional. The current search engine is no longer viable and we are researching alternatives.
As a stop gap measure, we are using Google's custom search engine service.
If you know of an easy to use, open source, search engine ... please contact support@midrange.com.


  1.  Home
  2. MIDRANGE-L
  3. January 2004

RE: Embedded Sql problem

No.  Because you have to set a default value for each statement.  Look up 
coalesce and try to imagine getting it working in there with a select *. 
Remember coalesce has to work from STRSQL, etc.

Rob Berendt
-- 
Group Dekko Services, LLC
Dept 01.073
PO Box 2000
Dock 108
6928N 400E
Kendallville, IN 46755
http://www.dekko.com





"Smith, Mike" <Mike_Smith@xxxxxxxxxxxxxxxx> 
Sent by: midrange-l-bounces@xxxxxxxxxxxx
01/20/2004 02:02 PM
Please respond to
Midrange Systems Technical Discussion <midrange-l@xxxxxxxxxxxx>


To
"Midrange Systems Technical Discussion" <midrange-l@xxxxxxxxxxxx>
cc

Fax to

Subject
RE: Embedded Sql problem






Thanks Eric. 
I have the problem solved with the null indicators now, but I will
experiment with the Coalesce. 
Can I use the Coalesce if I have a Select * SCMS, UPRM

-----Original Message-----
From: DeLong, Eric [mailto:EDeLong@xxxxxxxxxxxxxxx] 
Sent: Tuesday, January 20, 2004 1:51 PM
To: 'Midrange Systems Technical Discussion'
Subject: RE: Embedded Sql problem


Mike,

Every field you selected from UPRM (the right side of the "left outer
join") has the potential to return a null value.  The most direct way to
avoid having to deal with a null indicator would be to use
Coalesce(field,dft_value) on the fields from the UPRM file. 

Eric DeLong
Sally Beauty Company
MIS-Project Manager (BSG)
940-898-7863 or ext. 1863



-----Original Message-----
From: Smith, Mike [mailto:Mike_Smith@xxxxxxxxxxxxxxxx]
Sent: Tuesday, January 20, 2004 11:34 AM
To: Midrange Systems Technical Discussion
Subject: RE: Embedded Sql problem


Rob, 
I tried that.  However, I'm now getting the unusual message SQL0303 that
is telling me the 
 Host variable PRMDS.UPSAD not compatible. 
 SQLCOD = -000000303. 

My fetch statement now looks like 
FETCH Next From CtlCurs INTO :SMSO#,:SMSCD,:SMCRW,:UPRTE, 
:UPSAD,:SCEST, :NullIND 

I've switched locations in the list for the NullInd, because I wasn't
sure exactly where it needs to go.  No difference. 
I've tried making using host variable names.  No difference. 

I've written hundreds of programs like these(however not usually with a
specific Outer join.) and never had a problem like this. 

I have a feeling it has to do with the fact, that the record that isn't
showing up doesn't have a record with the UPRM file so that UPSAD and
UPRTE are blank.  However, I kind of thought that was what the indicator
variable was handling. 
-----Original Message-----
From: rob@xxxxxxxxx [mailto:rob@xxxxxxxxx] 
Sent: Tuesday, January 20, 2004 11:26 AM
To: Midrange Systems Technical Discussion
Subject: RE: Embedded Sql problem


Mike, in your earlier desired result you listed two columns.  Is that
all 
you really want?  If so, then drop the SELECT * and instead just select 
the two desired columns.
then it would be, (assuming that only the first column supports null 
values):
C/EXEC SQL 
C+ Fetch NEXT FROM CTLCURS Into :ColA :indA ,:ColB
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





"Smith, Mike" <Mike_Smith@xxxxxxxxxxxxxxxx> 
Sent by: midrange-l-bounces@xxxxxxxxxxxx
01/20/2004 11:07 AM
Please respond to
Midrange Systems Technical Discussion <midrange-l@xxxxxxxxxxxx>


To
"Midrange Systems Technical Discussion" <midrange-l@xxxxxxxxxxxx> cc

Fax to

Subject
RE: Embedded Sql problem






Well, I've found that I'm getting an -SQL0305 error which indicates that
I need a Indicator Variable. 

Some snippets of the code 

 * DATA STRUCTURES 
D SODS          E DS                  EXTNAME(SCMS) INZ 
D PrmDS         E DS                  EXTNAME(UPRM) INZ 
D CodDS         E DS                  EXTNAME(SOCD) INZ 

C                   Eval      SelectOne = %TRIM(SQLARY(1)) + ' ' + 
C                                         %TRIM(SQLARY(2)) + ' ' + 
C                                         %TRIM(SQLARY(3)) + ' ' + 
C                                         %TRIM(SQLARY(4)) + ' ' + 
C                                         %TRIM(SQLARY(5)) + ' ' + 
C                                         %TRIM(SQLARY(6)) + ' ' + 
C                                         %TRIM(SQLARY(7)) + ' ' + 
C                                         %TRIM(SQLARY(8)) + ' ' + 
C                                         %TRIM(SQLARY(9)) + ' ' + 
C                                         Where + ' ' + 
C                                         'Order By ' + %trim(OrderBy) 
 
C/EXEC SQL 
C+  PREPARE SEL FROM :SelectOne
C/END-EXEC 
C/EXEC SQL 
C+  Declare CTLCURS SCROLL Cursor  For SEL
C/END-EXEC 
 
 *OPEN THE CURSOR 
C/EXEC SQL 
C+  Open CTLCURS using :sodat
C/END-EXEC 


I fetch using the following 
C/EXEC SQL 
C+ Fetch NEXT FROM CTLCURS Into :SoDS ,:prmDS, :CodDS
C/END-EXEC



I can't figure out how to incorporate a indicator variable the way I
have it set up






_______________________________________________
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 thread ...
Follow-Ups:
Replies:
Follow On AppleNews
Return to Archive home page | Return to MIDRANGE.COM home page

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.