RE: [sqr-users] Debugging a Lookup Table

["White, Denise" <DEWhite@vicr.com>]

Rick,

I tried what you suggested (thanks for the tip on how to see the actual SQL 
from the load-lookup command), and it was what I expected.  There was a 
DISTINCT that I hadn't included when trying to duplicate in my query tool, but 
when I included it, I got the same results.

Steve and clark suggested that I might be retrieving trailing spaces.  I have 
confimed that this isn't so.  Thanks for the suggestion, though!

I went down a blind alley when I found that I couldn't log in to the PeopleSoft 
database in Toad and retrieve data from the other database using the database 
link.  However, this turned out to be an apparent limitation of Toad related to 
the fact that the two databases are on different releases of Oracle.  The 
database link retrieved data correctly in SQL*Plus, and I also added a 
BEGIN-SELECT in my program that successfully retrieved and displayed the same 
data I am trying to retrieve in the load-lookup.  The frustrating thing here is 
the complete inability to see exactly what is in the lookup table.  I am using 
another lookup table from another database and it works perfectly.  The main 
difference is that I am not using a concatenated key in the other one, but I 
searched the archives before I coded that, and Ray had proven that you can use 
a concatenated key.

I guess maybe it's time to give up and rewrite the program to use arrays.  I 
hate to admit defeat, though, especially when I don't understand why!

Thanks!

Denise M. White
Sr. Software Engineer
Vicor 

> -----Original Message-----
> From: Lynds,Rick [mailto:rlynds@mwdh2o.com]
> Sent: Friday, August 01, 2003 12:38 PM
> To: White, Denise; SQR Users Group (E-mail)
> Subject: RE: [sqr-users] Debugging a Lookup Table
> 
> 
> Denise,
> 
> Have you used a "-s" in your parameters and reviewed the 
> actual generated
> SQL code for the lookup table (I believe that it will be near 
> the end of the
> log)?  At least then you would have some idea of what SQR is 
> doing for you.
> 
> Rick
> 
> -----Original Message-----
> From: White, Denise [mailto:DEWhite@vicr.com]
> Sent: Friday, August 01, 2003 9:29 AM
> To: Lynds,Rick; Steve.Alexander@ci.sj.ca.us; SQR Users Group (E-mail)
> Subject: RE: [sqr-users] Debugging a Lookup Table
> 
> 
> Steve and Rick
> 
> Thank you for responding so quickly!  I tried your suggestion 
> and no luck.
> I had seen in a reference manual where they had enclosed the 
> value in quotes
> (I think this was in reference to a return_value), but 
> assumed that was
> because they had a space in the value (they were 
> concatenating two fields
> together, with a comma and a space between).  I know that the 
> rule for the
> where clause is that you need quotes if there is a space in 
> the value, but
> otherwise you don't.  I believe the same rule applies here.
> 
> Thanks anyway!
> 
> Denise M. White
> Sr. Software Engineer
> Vicor 
> 
> > -----Original Message-----
> > From: Lynds,Rick [mailto:rlynds@mwdh2o.com]
> > Sent: Friday, August 01, 2003 12:15 PM
> > To: White, Denise
> > Subject: FW: [sqr-users] Debugging a Lookup Table
> > 
> > 
> > Looking at Steve's message, I didn't see if he CC'd you, so I 
> > thought I
> > would pass it along. I believe he's got it right.
> > 
> > Rick
> > 
> > -----Original Message-----
> > From: Alexander, Steve [mailto:Steve.Alexander@ci.sj.ca.us]
> > Sent: Friday, August 01, 2003 9:16 AM
> > To: 'sqr-users@sqrug.org'
> > Subject: RE: [sqr-users] Debugging a Lookup Table
> > 
> > 
> > When your key or return_value have multiple fields or a 
> > formula, you have to
> > put them in quotes.  And, of course, any quotes within those 
> > quotes have to
> > be two consecutive quotes.  So:
> > 
> > key='to_char(division_number,''09999'')||department'
> > 
> > 
> > -----Original Message-----
> > From: White, Denise [mailto:DEWhite@vicr.com]
> > Sent: Friday, August 01, 2003 9:04 AM
> > To: SQR Users Group (E-mail)
> > Subject: [sqr-users] Debugging a Lookup Table
> > 
> > 
> > Hi,
> > 
> > I am having a problem with a lookup table.  I have to 
> access data from
> > another database, so I want to just read it once, up front, 
> > and create a
> > lookup table.  It looks to me like everything should work, 
> > but it's not - I
> > am not getting any values returned to my lookup statement.  I 
> > do not believe
> > that there is any way to actually see or display what has 
> > been loaded into
> > the lookup table - is that correct?  Here are the details:
> > 
> >    load-lookup
> >       name=departments
> >       table=department@smarttime
> >       key=to_char(division_number,'09999')||department
> >       return_value=department
> >       rows=9999
> > .....
> >             let $key = $division_number||$home_department
> >             display $key
> >             lookup departments $key $st_dept
> >             display $st_dept
> >             if isnull($st_dept)
> >                do Insert-Department
> >                end-if
> > 
> > It is trying to do Insert-Department every time.  If I use a 
> > query tool and
> > retrieve:
> > 
> > select to_char(division_number,'09999')||department from department;
> > 
> > this should give me what is loaded into the key values of the 
> > lookup table.
> > One of the values is: 
> >  
> > 0000160100
> > 
> > The displays from the logic where I try to retrieve the 
> > lookup value on my
> > first selected record show: 
> > 
> > 0000160100 
> > 
> > for the display of $key, and this is followed by a null line 
> > where I display
> > $st_dept.  The keys look identical to me, but I have no way 
> > of telling that
> > it was actually stored in the lookup table, so I can't figure 
> > out why it's
> > not finding the match.
> > 
> > I get this in digest mode, so if you would please copy me on 
> > any reply at
> > dewhite@vicr.com, I would appreciate it.  This is holding up 
> > what I wanted
> > to accomplish today, but I don't want to have to back down 
> > and rewrite it to
> > use arrays instead of lookup tables!
> > 
> > BTW, when the program does the load-lookup, it does display 
> > the correct
> > number of rows that have been loaded, and I also confirmed 
> > that if I place a
> > syntax error in the key clause of the load-lookup, it will 
> abend on an
> > error, so it is accepting the to_char() function as valid 
> > syntax.  I am
> > using SQR 4.3.2.
> > 
> > TIA,
> > 
> > Denise M. White
> > Sr. Software Engineer
> > Vicor 
> > 
> > _______________________________________________
> > sqr-users mailing list
> > sqr-users@sqrug.org
> > http://www.sqrug.org/mailman/listinfo/sqr-users
> > 
> > _______________________________________________
> > sqr-users mailing list
> > sqr-users@sqrug.org
> > http://www.sqrug.org/mailman/listinfo/sqr-users
> > 
> 

_______________________________________________
sqr-users mailing list
sqr-users@sqrug.org
http://www.sqrug.org/mailman/listinfo/sqr-users