RE: [sqr-users] Debugging a Lookup Table

[James Womeldorf <jwomeldo@fastenal.com>]

Hi Denise,

To see what is being loaded into your lookup try this.  (Oracle again)  I've
never tried it across databases but I've used the ROWNUM technique to parse
through the data in a lookup.
   
load-lookup
      name=departments
      TABLE='(SELECT ROWNUM, division_number, department FROM
department@smarttime)'
      KEY=ROWNUM
      return_value=division_number||department
      rows=9999

let $key = '1'
lookup departments $key $return

Regarding the return_value I'd try
return_value='to_char(division_number,''09999'')||department'
although I have not tested it.
   
Jim

-----Original Message-----
From: White, Denise [mailto:DEWhite@vicr.com]
Sent: Friday, August 01, 2003 11: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

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