Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Register Log in
  • Congratulations Mike Lewis on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

File Advice : Ext SQL or LF on 400 2

Status
Not open for further replies.
JamieCairns

JamieCairns

Programmer
Apr 2, 2003
23
0
0
US
I need to create an ODBC link to some shipping software, and have to use the data from 4 or 5 different physical files on the 400. I also have to update several of the fields - possibly in more than one PF.
My idea is to create an LF Join file on the 400 rather than a complicated SQL statement on the PC, so that the SQL from the external s/w is as simple and as quick to run as possible.
Any holes in my thinking ?
If not, anyone willing to give me a lesson in creating Join LF's ?

Oh yeah - we're using a dinosaur 400 - V4R5.

Jamie
 
Sort by date Sort by votes
Personally, I would rather do the SQL all on the PC side. Then you can optimize your SQL via faq317-3546 and get optimal speed. Using this technique, I could print a 60 page report in under 5 seconds joining about 3 tables.

iSeriesCodePoet
iSeries Programmer/Lawson Software Administrator
[pc2]
See my progress to converting to linux.
 
Upvote 0 Downvote
I agree. using SQL any time over a LF is the most optimal way to do things. It's far more stable also in my experience

___________________________________________________________________
onpnt2.gif
[sub]
The answer to your ??'s may be closer then you think.
Check out Tek-Tips knowledge bank by clicking the FAQ link at the top of the page[/sub]
 
Upvote 0 Downvote
question sounds familiar. have you posted this prior.

as far as complex SQL statements go if your SQL isn't string then STRSQL with the SQL tool makes things easy. just use your best friend the F4 prompt to get everything built. as SQL is so standardized with little to no syntax adjustments cross platformed, there should be issues

___________________________________________________________________
onpnt2.gif
[sub]
The answer to your ??'s may be closer then you think.
Check out Tek-Tips knowledge bank by clicking the FAQ link at the top of the page[/sub]
 
Upvote 0 Downvote
iSeriesCodePoet: ...doing a bit more research after my last post, I was leaning in the sql direction as well...
I need to join 2 files to begin with - it'd be easier for me to draw a picture...

Data "trigger" (coming from scanned barcode) Order# (not a 400 trigger, just the datum that sets this whole process going...)

So, I select File ORDERLIB/ORDHEAD by Order#, and this file has : CUST# and CUSTSEQ, which are the key to the CUSTADDR file. I need to create an SQL statement to pull these 2 files together, and omit anything not in COMPANY "63" or older than 07/01/2003. I've tried a dozen different ways, but keep getting syntax when I try to add the second file. Is this something someone could code in a minute or less, so I could see one that works, and figure out where I lost it... ?

TIA, Jamie

(Poet: I like the LINUX blog, I'm about to start the same "path" myself...)
 
Upvote 0 Downvote
Code: 
SELECT *
  FROM FILE1 F1, FILE2 F2
    WHERE F1.FIELD1 = F2.FIELD1
          F1.COMPANY <> MYCOMPANY
          F1.DATE <> MYDATEFORMAT

This is a real basic SQL stmt. I hope this helps.

iSeriesCodePoet
iSeries Programmer/Lawson Software Administrator
[pc2]
See my progress to converting to linux.
 
Upvote 0 Downvote
That's it - I was forgetting the F1, F2 alias thingy

Thanks muchly
Jamie
 
Upvote 0 Downvote
File Advice : Episode 2

(barely RPG related, but an extension of yesterdays question)

Ran into a slight snag - this'd be so easy on the 400, but I'm unsure how to do it in SQL - the author(s) of our ERP pkg decided to put the postal code (equiv to US ZIP) in 2 5-digit fields, so our 6 digit code is broken up to 3+3 in the 2 fields. Can I create a &quot;temp&quot; field in my SQL lookup that allows me to treat these as a single concatenated field ?

Jamie
 
Upvote 0 Downvote
I'm not able to check at this time but you can check to see if the CONCAT Function is available in the 400's SQL.

eg:
SELECT CONCAT (ZIP1, ZIP2)
FROM table

reference

___________________________________________________________________
onpnt2.gif
[sub]
The answer to your ??'s may be closer then you think.
Check out Tek-Tips knowledge bank by clicking the FAQ link at the top of the page[/sub]
 
Upvote 0 Downvote
works as
SELECT CONCAT (str, str) FROM TTester

however I think you'll run into a problem with the data type. you can't concat on integers

so the work around I tried after remembering that is
select concat (str, varchar(int)) FROM TTester

worked like a charm

___________________________________________________________________
onpnt2.gif
[sub]
The answer to your ??'s may be closer then you think.
Check out Tek-Tips knowledge bank by clicking the FAQ link at the top of the page[/sub]
 
Upvote 0 Downvote
that was a example of a string to integer

just varchar both if they are zoned

___________________________________________________________________
onpnt2.gif
[sub]
The answer to your ??'s may be closer then you think.
Check out Tek-Tips knowledge bank by clicking the FAQ link at the top of the page[/sub]
 
Upvote 0 Downvote
You guys have been so helpful so far, I thought I'd press my luck and ask one more thing...

Thanks to your advice, my sql does work. It's slow, but I'm looking at iSeriesCodePoet's link for optimization tips...

However, when I run the SQL live in the shipper application, I'm pulling back EBCDIC instead of the text I expected - did I miss a switch or setting somewhere ?

Jamie
 
Upvote 0 Downvote
How are you connecting to the 400? Through Java? Through the iSeries Access (or Client Access) ODBC driver? I am not familiar with the latter but with Java I don't think you have to do anything with the JT400 toolkit.

iSeriesCodePoet
iSeries Programmer/Lawson Software Administrator
[pc2]
See my progress to converting to linux.
 
Upvote 0 Downvote
Status
Not open for further replies.

Similar threads

Rounak
  • Locked
  • Question
Can you use a Variable in (Library/File) in this format
Replies
1
Views
187
Summer_Moon
Summer_Moon
PeteJohnston
  • Locked
  • Question
SQL error running Python
Replies
0
Views
128
PeteJohnston
PeteJohnston
twes3
  • Locked
  • Question
White font on Black blackground AS400 printer file
Replies
1
Views
163
twes3
twes3
Ju5
  • Locked
  • Question
Data Architecture on the iSeries
Replies
0
Views
123
Ju5
Ju5
nschroeder
  • Locked
  • Question
Printing to variable file names
Replies
2
Views
141
nschroeder
nschroeder

Part and Inventory Search

Sponsor

Back
Top