Select data from AS400 table with Member

[BandOQ4b]

I am going through a linked server from my desktop to a AS400 using an ODBC connection To properly connect to a AS400 table with a single member I must use the following

SELECT *
FROM AS400_A.S1018328.NMLIB.NMPTSPL

I need to access a table with multiple members.
Several sources suggested the following to get to the proper member. Member in ()

SELECT *
FROM AS400_A.S1018328.NMLIB.NMPTSPL(STPICK)

However, I get this error

Msg 4122, Level 16, State 1, Line 5
Remote table-valued function calls are not allowed.

 

[SkipVought]

hi,

for instance...

SELECT *
FROM AS400_A.S1018328.NMLIB.NMPTSPL
where member='SkipVought'

assuming that there is a field named member

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[BandOQ4b]

There is no field labeled as such or anything similar.

 

[SkipVought]

well then, what field contains the member value that you would be looking for?

Please do not make this a Twenty Questions dragged out ordeal!

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[BandOQ4b]

To avoid the 20 questions below is the names of all the fields as listed in System i Navigator.

column name Data Type Length Nullable Default Val Description Alt Heading

TDEL CHARACTER 1 No ' ' DELETE CODE DEL
TFLAG CHARACTER 1 No ' ' DC OR VENDOR ORDER DC/VEND
TCORP DECIMAL 3,0 No 0 SBU SBU
TSTR DECIMAL 5,0 No 0 STORE # STORE
TORD# DECIMAL 5,0 No 0 ORDER # ORD#
TSTYL DECIMAL 4,0 No 0 ITEM # PART 1 ITM1
TKOLR DECIMAL 2,0 No 0 ITEM # PART 2 ITM2
TSIZE DECIMAL 3,0 No 0 ITEM # PART 3 ITM3
TDIV DECIMAL 3,0 No 0 DIVISION DIV
TVNUM DECIMAL 4,0 No 0 VENDOR VENDOR
TDPT DECIMAL 3,0 No 0 DEPT DPT
TMAJ DECIMAL 3,0 No 0 MAJOR CLASS MAJ
TSUB DECIMAL 3,0 No 0 SUB CLASS SUB
TSKU DECIMAL 8,0 No 0 SKU SKU
TCOST DECIMAL 7,2 No 0 UNIT COST COST
TCPCK CHARACTER 2 No ' ' COST PACK COST PACK
TPACK DECIMAL 5,0 No 0 PACK QTY PACK
TLOC1 CHARACTER 2 No ' ' PICK LOCATION-SECTION LOC1
TLOC2 CHARACTER 2 No ' ' PICK LOCATION-SLOT LOC2
TTRDTE DECIMAL 8,0 No 0 TRANSACTION DATE AS YYYYMMDD TRANS YMD
TQORD DECIMAL 9,0 No 0 QTY ORDERED QTY ORD
TSHDTE DECIMAL 8,0 No 0 SHIP DATE AS YYYYMMDD SHIP YMD
TQSHP DECIMAL 9,0 No 0 QTY SHIPPED QTY SHIP
TCOMNT CHARACTER 60 No ' ' COMMENTS COMMENT
TFLD1 CHARACTER 1 No ' ' EXTRA FIELD 1 FLD1
TFLD2 CHARACTER 1 No ' ' EXTRA FIELD 2 FLD2
TFLD3 CHARACTER 1 No ' ' EXTRA FIELD 3 FLD3
TFLD4 DECIMAL 5,0 No 0 EXTRA FIELD 4 FLD4
TFLD5 DECIMAL 7,0 No 0 EXTRA FIELD 5 FLD5

 

[fredericofonseca]

try this

http://www.itjungle.com/guruo/mgo021302-story02.html

create alias mylib/pay2001
for mylib/payhist(pay2001)

create alias mylib/pay2000
for mylib/payhist(pay2000)

create alias mylib/pay1999
for mylib/payhist(pay1999)

Now you can access the individual members from any system using select commands like these:

select * from pay1999
select * from pay2000
insert into pay2001 (somecolumn) values('some value')

and thread513-919227

Regards

Frederico Fonseca
SysSoft Integrated Ltd

http://www.syssoft-int.com

FAQ219-2884
FAQ181-2886

 

[SkipVought]

The question(3) stands.

WHAT FIELD & VALUE are you looking for???????????

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[BandOQ4b]

Frederico, I found that, but it requires permissions on the AS400 that I don't think I have.

Skip, for this conversation, I will use Alt. Headings for field names.
dc/vend
ord#
itm1
vendor
sku
loc1
loc2
transYMD
qty ord
ship ymd
qty ship

I may need additional fields later. All values from those fields the current month plus the previous 2 calendar months going back from the current date. (Oct., Sept. and Aug.)

 

[SkipVought]

BandOQ4b,

In the words of The Captain, "What we've got here, is a failure to communicate!"

Good luck!

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]