Very Computer

by **zeke** » Sun, 03 Nov 2002 05:25:55

I am not a pick/d3 person so please pardon the ignorance.

I have been tasked with pulling information from my company's pick/d3 DB
into our Lotus Notes system using ODBC. Seems fairly easy except that I
cannot get the sql-create-view command to work on the CUSTOMER file.

My command looks like this:
SQL-CREATE-VIEW CUSTOMER NAME LASTNAME STREET CITY ST ZIPCODE TELEPHONE
[ADDITIONAL-PHONE#] [ORDKEY] [JOBKEY] [TRANSKEY] (cze

It starts to analyze the Customer file and fails with:
'LASTNAME' is not an attribute definition

When I enter <SORT DICT CUSTOMER>, LASTNAME shows up in the list as
attribute 20 as TYP L. I removed LASTNAME from the statement and tried
to run it again, and it came up with the same error for STREET. So I
stripped the command down to:

SQL-CREATE-VIEW CUSTOMER CITY

and it worked just fine. So, I cannot tell what the difference is
between the NAME/CITY attributes and the LASTNAME/STREET attributes that
lets this command succeed. None of these fields are MV and are just
simple text.

Any tips or ideas on what to look for to get this to work would be
greatly appreciated.

Thanks,
Jon

--
Posted via http://dbforums.com

by **zeke** » Sun, 03 Nov 2002 07:39:17

I did this command:

SQL-CREATE-VIEW CUSTOMER

and it creats a couple of tables with ALL of the attributes. When I look
at the table, the column values are:

N_2, NAME, N_4, N_5 .... N_14, CITY, N_17 ...

As it turns out, I ran the following command successfully:

SQL-CREATE-VIEW CUSTOMER 2 NAME 4 5 8 13 CITY 22 42 [23] [16] (czo

I still cannot figure out why I can't refer to most of the attributes by
name (which would be really nice, since now I have 3 tables with cryptic
column names!!)

--
Posted via http://dbforums.com

by **Steve** » Sun, 03 Nov 2002 13:18:28

Quote:
> I did this command:

> SQL-CREATE-VIEW CUSTOMER

> and it creats a couple of tables with ALL of the attributes. When I look
> at the table, the column values are:

> N_2, NAME, N_4, N_5 .... N_14, CITY, N_17 ...

> As it turns out, I ran the following command successfully:

> SQL-CREATE-VIEW CUSTOMER 2 NAME 4 5 8 13 CITY 22 42 [23] [16] (czo

> I still cannot figure out why I can't refer to most of the attributes by
> name (which would be really nice, since now I have 3 tables with cryptic
> column names!!)

Try entering (at TCL):
COPY DICT CUSTOMER 2
TO: LASTNAME

I think you should then be able to use LASTNAME in your sql-create-view
command. You would then need to repeat for the other attributes. It looks
like somebody was lazy and only populated the numbered dict items.

Steve

by **Doyen Klei** » Mon, 04 Nov 2002 05:58:40

at my last job we found that we needed to keep separate dictionaries with
limited numbers of fields in a physically different dictionary file, and
only use that dictionary for ODBC. It seems that some of the items would
mess things up even if they wern't referenced. You might try creating a
CUSTOMERB file that points to the same data, but different dict with only
the items that work, adding them one at a time to figure out which one is
causing the problem.

Quote:
> I did this command:

> SQL-CREATE-VIEW CUSTOMER

> and it creats a couple of tables with ALL of the attributes. When I look
> at the table, the column values are:

> N_2, NAME, N_4, N_5 .... N_14, CITY, N_17 ...

> As it turns out, I ran the following command successfully:

> SQL-CREATE-VIEW CUSTOMER 2 NAME 4 5 8 13 CITY 22 42 [23] [16] (czo

> I still cannot figure out why I can't refer to most of the attributes by
> name (which would be really nice, since now I have 3 tables with cryptic
> column names!!)

> --
> Posted via http://dbforums.com

by **terry_la..** » Tue, 05 Nov 2002 07:48:42

> Try entering (at TCL):
> COPY DICT CUSTOMER 2
> TO: LASTNAME

WARNING! Only do this if you don't mind breaking about a dozen reports.
You have no way of knowing at this point if this is safe. LASTNAME
might be anything, all you know is that it doesn't work with SQL
commands. Your best bet is to just use "2" and move on.

Quote:> You would then need to repeat for the other attributes. It looks
> like somebody was lazy and only populated the numbered dict items.

That's assuming a lot of things that you really have no way
of knowing from the information provided.

--
Terry Layne
Portland, OR

by **Steve** » Tue, 05 Nov 2002 09:52:17

> > Try entering (at TCL):
> > COPY DICT CUSTOMER 2
> > TO: LASTNAME

> WARNING! Only do this if you don't mind breaking about a dozen reports.
> You have no way of knowing at this point if this is safe. LASTNAME
> might be anything, all you know is that it doesn't work with SQL
> commands. Your best bet is to just use "2" and move on.

> > You would then need to repeat for the other attributes. It looks
> > like somebody was lazy and only populated the numbered dict items.

> That's assuming a lot of things that you really have no way
> of knowing from the information provided.

> --
> Terry Layne
> Portland, OR

Huh? What are you on about? I didn't tell him to delete or overwrite an
existing dictionary item. It looks to me like this file does not have a
dictionary item called LASTNAME, but does have one for "2", which happens to
be the last name. If he makes a copy under the correct name, then his
sql-create-view command will be easier to read and maintain. I can't think
how this would break anything. So what have I missed?
Steve

by **terry_la..** » Tue, 05 Nov 2002 11:38:57

> Huh? What are you on about? I didn't tell him to delete or overwrite
> an existing dictionary item.

Um, yes you did. Quoting the original post:

Quote:>> When I enter <SORT DICT CUSTOMER>, LASTNAME shows up in the list as
>> attribute 20 as TYP L.

That's what I'm on about. :-)

--
Terry Layne
Portland, OR

Very Computer

sql-create-view problem ...

sql-create-view problem ...

sql-create-view problem ...

sql-create-view problem ...

sql-create-view problem ...

sql-create-view problem ...

sql-create-view problem ...

sql-create-view problem ...