Lots of trouble with the editor again ..

where a.dr 0
should read where a.dr greater then less then 0
but it dropped the greather then sign and the less then sign

…. well the SQL now fails in my mind ..
add a where clause ..
where a.dr 0

something like this

Select a.id, a.eeid, a.dr, b.id, b.cr
from myfile a join myfile b
on a.eeid = b.eeid
and a.dr = b.cr
where a.dr 0
order by a.eei, a.dr

“Nobody expects the Spanish Inquisition”

You want matching records ..
records with the same eeid and dr = cr.
is that what you want?

And how did this sql fail?

Select a.id, a.eeid, a.dr, b.id, b.cr
from myfile a join myfile b
on a.eeid = b.eeid
and a.dr = b.cr
order by a.eei, a.dr

What does it do that you don’t want to do and how does it fail?

There is a product somewhere that claims to provide the capability. I might be able to track it down with a couple Google searches. But Microsoft doesn’t supply a driver other than its JDBC driver (and doesn’t implement full DRDA). Without a driver, SQL Server isn’t going to let some remote system into its database. That gets into fundamental platform security. There is AppServer4RPG at Sourceforge [ http://sourceforge.net/projects/appserver4rpg/ ] that might make it reasonable. (I need to try it someday.) — Tom

Three things make it impossible for us to guess what the problem is. First, custArr is defined as [ likeds(custTemp) ], but you dn’t show us how custTemp is defined. I’d assume it’s a parm value. Second, your READE key doesn’t match the SETLL key. Third, there’s no test to see if SETLL succeeded. Who knows how it’s going? Could be many things. — Tom

The best action is to ask the college network administrators. If they have it blocked, they’re the only who can help (if any help is possible). Anything else can be a federal crime and could end your time at that college in a hurry. — Tom

We can’t do anything about it until you show us the QM query along with the code that runs the query. — Tom

I put a basic REXX proc together overnight that should work for many journaled files. Put it into a QREXSRC source member with type REXX. Run it with STRREXPRC, or PDM member option 16, and specify [ lib/file ] for the REXX proc parameter:

/* Show basic journal entries for LIB/FILE                             */
parse arg inparm
qfile = translate( inparm )
   /* */
parse var qfile part1 ‘/’ part2
if part2 = ” then
   do
      lib = ‘*LIBL     ‘
      file = substr( part1, 1, 10 )
   end
else
   do
      lib = substr( part1, 1, 10 )
      file = substr( part2, 1, 10 )
   end
   /* */
qfile = (file)(lib)
   /* */
fd = Copies(’00′x, 1024)
   /* */
     “call QSYS/QDBRTVFD ( “,
                           ” &fd              “,
                           ” x’00000400′      “,
                           ” ‘                    ‘ “,
                           ” ‘FILD0100′       “,
                           ” &qfile           “,
                           ” ‘*FIRST    ‘     “,
                           ” ’0′              “,
                           ” ‘*LCL      ‘     “,
                           ” ‘*EXT      ‘     “,
                           ” x’0000000000000000′ “,
                        “)                   ”
   /* */
/* Extract journal info…                             */
xfd    = c2x( fd )
ofsjrn = x2d( substr( xfd, 757, 8 ) ) + 1
qjrn   = substr(  fd, ofsjrn, 20 )
jrn    = substr(  qjrn, 1, 10 )
jlib   = substr(  qjrn, 11, 10 )
   /* */
‘crtdupobj   QADSPJR4  QSYS  *file  QTEMP  MyDSPJRN4′
‘addpfm      QTEMP/MyDSPJRN4  mbr( MyMbr4 )’
   /* */
/* Display journal entries into temp file…           */
‘dspjrn      ‘strip( jlib )’/'strip( jrn ) ,
             ‘ file(( ‘strip( lib )’/'strip( file )’ )) ‘ ,
             ‘ rcvrng( *CURRENT ) ‘ ,
             ‘ enttyp( ‘ ,
                     ‘ BR DL DR PT PX UB ‘ ,
                     ‘ UP UR ‘ ,
                   ‘ ) ‘ ,
             ‘ output( *OUTFILE ) outfilfmt( *TYPE4 ) ‘ ,
             ‘ outfile( QTEMP/MYJRNOUT4 ) ‘ ,
             ‘ entdtalen( *CALC ) ‘
   /* */
address ‘*EXECSQL’ execsql ,
        ‘CREATE VIEW QTEMP/MYDSPJRN4V AS ‘ ,
        ‘ SELECT JOENTL, JOSEQN, JOCODE, ‘ ,
        ‘ JOENTT, JOTSTP, JOJOB, JOUSER, ‘ ,
        ‘ JONBR, JOPGM, JOOBJ, JOLIB, JOMBR, ‘ ,
        ‘ JOCTRR, JOFLAG, JOCCID, JOUSPF, ‘ ,
        ‘ JOSYNM, JOJID, JORCST, JOTGR, ‘ ,
        ‘ JOINCDAT, JOIGNAPY, JOMINESD, ‘ ,
        ‘ JORES, JONVI ‘ ,
        ‘ FROM QTEMP/mydspjrn4 ‘
   /* */
address ‘*EXECSQL’ execsql ,
        ‘CREATE TABLE QTEMP/MyDATA4  AS (‘ ,
           ‘select a.*, b.* from QTEMP/MyDSPJRN4V a,’ ,
            strip( lib )’/'strip( file )’ b ) WITH NO DATA’
address ‘*EXECSQL’ execsql ‘COMMIT’
   /* */
/* Do an image-copy of the displayed entries into our  */
/* fully described format…                           */
‘cpyf        fromfile( QTEMP/MYJRNOUT4 ) ‘ ,
           ‘ tofile( QTEMP/MyDATA4 ) ‘ ,
             ‘ mbropt( *REPLACE ) ‘ ,
             ‘ fmtopt( *NOCHK ) ‘
   /* */
address ‘*EXECSQL’ execsql ,
        ‘UPDATE  QTEMP/MyDATA4 ‘ ,
           ‘set JOJID = ”        ” ‘ ,
           ‘with NC’
   /* */
/* Now view the fully-described entries…             */
‘runqry      *n  QTEMP/MyDATA4 ‘
   /* */
/*CleanUp:                                                             */
address ‘*EXECSQL’ execsql ,
        ‘DROP VIEW  QTEMP/MYDSPJRN4V ‘
address ‘*EXECSQL’ execsql ‘COMMIT’
‘dltf        QTEMP/MyDSPJRN4 ‘
address ‘*EXECSQL’ execsql ,
        ‘DROP TABLE QTEMP/MyDATA4 ‘
‘dltf        QTEMP/MYJRNOUT4 ‘
   /* */
exit

The result should be a list of journal entries for the LIB/FILE that you specified. Each entry should show both the journal entry header fields and the formatted file fields.

Copy and paste the code into a text file in your home directory using Notepad. Use CPYFRMSTMF to copy from your home directory into a QREXSRC source member.

The biggest problem will probably be that this editor messes up leading and trailing apostrophes (and possibly leading & trailing double-quotes). After I paste code from here into Notepad, I find one of the messed up leading apostrophes and paste into a Find & Replace box. Then I put an apostrophe into the .Replace field and click [Replace All]. I then do the same thing for a trailing apostrophe. Once all are replaced, that’s when I save it into my IFS home directory. (Double-quotes might need the same treatment.)

After it’s in a source member and opened in SEU, any oddly translated characters usually show up on an emulator screen because they mess with display attributes. You can type over those characters with corrections in SEU, but it’s easier when I do it in Notepad first.

This REXX should work on essentially every AS/400 or iSeries or System i, regardless of whatever products you have installed, with one possible exception. There is one [CREATE TABLE AS <result set>] statement that might need a more recent OS version. I can’t recall when the <result set> option was added, but it was quite a while ago.

The proc runs on my 6.1 system and one of my V5R3 systems. It’s not sophisticated enough to handle errors, but that’s a good learning exercise for REXX coders. I could supply a generic REXX error proc if I took more time.

If anyone wants to, it can be converted to CL. If you have any kind of RUNSQL command that runs dynamic SQL, the conversion would be easy. I did this in REXX because it runs SQL natively without any added requirements (except your system has to be newer than approximately V2R1).

Tom

The iPad is *already* a “true corporate device”…whatever that means (it’s in the eyes of the beholder). I see them everywhere – in every organization I visit. Whether or not they’re ready for prime time doesn’t matter. Execs, sales professionals, marketing reps, and practically everyone else uses them and that’s what makes them relevant.

There are plenty of ways for centrally managing such devices. The real question is: When are IT professionals (CIOs included as I outlined here: http://searchwinit.techtarget.com/tip/What-to-do-when-the-CIO-gets-in-the-way-of-enterprise-IT-security!) going to step up to the plate and roll out true MDM and BYOD management/security technologies to keep mobile devices in check? It’s time to stop ignoring the elephant in the room and do what we know needs to be done as I talked about here: http://searchwinit.techtarget.com/tip/Top-10-reasons-why-you-shouldnt-ignore-mobile-device-security.

My hotmail has been changed to Outlook. When it was hotmail if I wanted to send a new email to someone in my contact list I  just typed the name in the box and it automatically came up with the email address. In Outlook, it just comes up with frequently used contacts so I have to go into the contacts list to find the email address.  This is a bit of a pain, unless I’m missing something? 

As long as there’s no way to centrally manage the iPad from a corporate perspective then it won’t become a truly corporate device.

TomLiotta-There is nothing simplistic about the set of data I have provided. The format got wacky when pasting it in, but it is a perfect sample from the population of data in the table. If a query can be written to return the matching or unmatching records in the data above, then it can be applied to all the data in my table. For person 333 there is an example of a set of records which I would consider to match, as well as a record dated later for the same amount, that the query should not return to me. There is also a second person in the sample. THe format got pasted very strangely. The records beginning with ID’s 3 and 6 have amounts that should be in the credit column.
When you first called it an academic exercise, it seemed as though you were insinuating that I was a student looking to cheat on a test. I have posted on a lot of different Access and coding sites and have not seen anyone else use that terminology.
Are you an accountant? A clearing account should theoretically have an ending balance of zero. In reality, there are items that are still “open” due to timing issues. I am trying to use Access right now to narrow down on the items that are “unmatched” ultimately. If I could write a query that returned to me matching records, then I could write another query to return to me records which are part of the original table, NOT appearing in the match query. Those would be the “leftover items” that make up the ending balance. There would still be some manually removal of records for the few instances where a debit is cleared by two credits. The majority of the records would be the true timing items making up the ending balance. WIth that said, it may be easier to write a query that returns records which are “unmatching”. For instance, a $40 debit and a $50 credit for person 333. IF there are no other $40 credits dated later and $50 debits dated earlier for this person, than these are what I would consider to be leftover “unmatched” items.
Right now we are transitioning to a new reconciliation software which will match transactions based on automatch rules. Access is only a temporary solution. You are incorrect when you say that I NEED to have more data (invoice number, etc) in each record. Not true. A recon software tool wouldn’t need that, and I understand that Access is not a recon tool. I am trying to get Access to do more than it was designed to do, temporarily.
I do have a query written in my current table that gives me a nice balance by EEID which sums to the ending balance in this clearing account, but the problem is that I don’t have aging information on the records making up this query. The query is summing up ALL transactions by person and giving me the net, and I asked it to not show me any record with a zero net. Maybe this query is what you were deeming to be “novice level”. ANyhow, I am trying to come up with a query on just unmatched records ULTIMATELY so that I can turn it into a pivot table and see the real dates associated with these unmatched items.
I think it is pretty funny that you are questioning the “real worldliness” of this example. I don’t know what world you are in, but in the real world there are inconsistencies, transitionary periods where you make due with what you have, and in the real world people reach out to others for assistance and do NOT want feedback from unrealistic people that have God-complexes. I currently have two other people on other sites who are helping me with query language as well as SQL in order to perform this recon in Access. So far it seems to be working on the “unpredictable” data getting loaded into my table each month.

Welcome to ITKE, bbarton40! Can you provide the software / program you’re using (Microsoft Excel?) so our experts can try to help you with your issue?

To see how our community works, check out our FAQ.

Welcome to ITKE, jmz! Here’s a great definition on KWH from the SearchCIO-Midmarket team that should point you in the right direction.

For more information on how our community works, check out our FAQ.

The printer is local via USB.

The OS is V6R1.

The PC is Windows 7 professional.

The printer is a thermal label printer that is not supported by IBM. It was working and now that the PC is down we are unable to configure it. I have been with this company for 3 months so I have no idea what was doen before.

I need to figure out how to set it up from scratch. Creating an outq using printer model IBM 42023 does not work with a printer session defined with the same printer model.

MFRTYPMDL IBM 42023
WSCST QWPDEFAULT

I also tried creating a remote outq, but I must not have the right values for some parameters.

It appears that you are asking about using the first and third keyfields to chain to your file using your three keyfield logical.  The system does not allow a keyfield to be skipped in the midst of other keyfields, which would be the second keyfield in your example.  Keyfields which follow the last specified keyfield can be skipped.  For example, you could specify keyfield 1, or keyfields 1 and 2, or all three but you can not specify keyfields 1 and 3, or 2 and 3.

Some of my co-workers have done it using their laptop and an HDMI cable. That is as long as both devices support them. Then it works like a dual monitor. Google hooking up a laptop to a flat screen TV. Talk to someone at your nearest electronics store, they can help you and recommend a good cable.

Some clarification is needed to be able to help you.

What exactly do you mean by “stops” ?  Does it stop working, or just stops showing results?  do you see an error message? 

Also, is it a long SQL statment or a query that returns a big number of rows? or a query that takes to long to give results?

Thank you Carlosdl. Do you know the syntax of the select statement?

Thank you Denny. I´m trying to get an ODBC driver. So, cant you tell me the syntax to select a table in another database engine? Like select * from <servername>.<instancename>.<databasename>.<tablename>Thanks in advance.