I am trying to convert a sql statment that has an inner query:

select distinct spriden_id,spriden_last_name,spriden_first_name,
rrrareq_trst_code
from spriden,rrrareq
where spriden_change_ind is null
and spriden_pidm=rrrareq_pidm
and rrrareq_aidy_code='&aid_year'
and rrrareq_treq_code='&treq_code'
and rrrareq_trst_code not in ('S','W')
AND SPRIDEN_ID IN
(SELECT SPRIDEN_ID
FROM spriden,rrrareq
WHERE spriden_change_ind IS NULL
AND spriden_pidm=rrrareq_pidm
AND rrrareq_aidy_code = '&aid_year'
AND rrrareq_treq_code='ADMIT'
AND rrrareq_trst_code NOT IN ('C'))
order by spriden_last_name,spriden_first_name

My problem is that I cant' figure out how to look IN the hold file taht I created with the list of ID's. All of my documentation talks about looking in an actual file, however it doesn't tell me the syntax for looking in a hold file. Also, I am figuring that the column headers would have to be turned off as well when creating the hold file, and I haven't figured that out yet either (but I haven't looked much).

The method I am using seems to complicated for something this simple....creating several hold files and joining them together. Is there a simpler way of doing this?

(P.S> I am only asking about looking in a hold file....not writing the whole report)

Brian

I had a similar issue if I am reading your question correctly. You might want to look at this link:
https://forums.informationbuilders.com/eve/forums/a/tpc/...1057331/m/2871008671

I had to make sure all the values in the hold file were surrounded by quotes as well as you will see below. I have this working flawlessly now thank to a few on here!

ENGINE SQLMSS SET DEFAULT_CONNECTION ohlewwap0055
SQL SQLMSS PREPARE SQLOUT FOR
SELECT
UPPER(UserID) USERID,
DateTime,
ROWID
FROM vLM_producer_potential
WHERE DateTime > DATEADD(dd,-9,getdate())
AND POTENTIAL = '1'
;
END
TABLE FILE SQLOUT
BY USERID
BY DateTime
BY ROWID
ON TABLE HOLD AS LMS-POTENTIAL FORMAT FOCUS INDEX ROWID
END
-RUN
DEFINE FILE LMS-POTENTIAL
ROW3/A15V= '''' | ROWID | '''';
END
TABLE FILE LMS-POTENTIAL
PRINT ROW3
ON TABLE HOLD AS TEMP1 FORMAT ALPHA
END
-RUN
DEFINE FILE S_CONTACT
ROWSIEB/A15V='''' | S_CONTACT.S_CONTACT.ROW_ID | '''';
END
TABLE FILE S_CONTACT
BY S_CONTACT.S_CONTACT.ROW_ID
WHERE ROWSIEB IN FILE TEMP1;
END




Thanks Neuro, but I don't get the s_contact part. I omitted that part, and just created a define that puts it in quotes....and I still get the infamous format error in decode......

TABLE FILE RRRAREQ
BY RRRAREQ_PIDM
WHERE RRRAREQ_AIDY_CODE EQ '&AID_YEAR';
WHERE RRRAREQ_TREQ_CODE EQ 'ADMIT';
WHERE RRRAREQ_TRST_CODE NE 'C';
ON TABLE SET PAGE-NUM OFF
ON TABLE NOTOTAL
ON TABLE HOLD AS RRRAREQ_TEMP FORMAT FOCUS
END
DEFINE FILE RRRAREQ_TEMP
ROW3/A15V='''' | RRRAREQ_PIDM | '''';
END
TABLE FILE RRRAREQ_TEMP
PRINT ROW3
ON TABLE HOLD AS RRRAREQ_ADMIT FORMAT ALPHA
END
TABLE FILE RRRAREQ
BY RRRAREQ_PIDM
WHERE RRRAREQ_AIDY_CODE EQ '&AID_YEAR';
WHERE RRRAREQ_TREQ_CODE EQ 'SELSER';
WHERE NOT RRRAREQ_TRST_CODE IN ('S','W');
WHERE RRRAREQ_PIDM IN FILE RRRAREQ_ADMIT;
ON TABLE SET PAGE-NUM OFF
ON TABLE NOTOTAL
END

Brian

I think I knwo your problem. It is with the Master File:

Here was mine before and after(The problem is the "V":

THIS IS FROM MY LAST POST ON THE LINK I SENT YOU!
Ok, I finally figured it out, all of your information helped me:

In the Master File I changed the following:


FIELDNAME=ROW_ID, ALIAS=ROW_ID, USAGE=A15V, ACTUAL=A15V, $


TO


FIELDNAME=ROW_ID, ALIAS=ROW_ID, USAGE=A15, ACTUAL=A15V, $


WORKS PERFECTLY NOW!!! Smiler I am not liking the "V"

I'm not so sure I can change the master file. I've tried several formats, but I always come up with that error.

Also, take out the V in:


DEFINE FILE RRRAREQ_TEMP
ROW3/A15V='''' | RRRAREQ_PIDM | '''';
END


For some reason, when you try to do the IN FILE, the V will not match up with any of the values. So leave it as just "A15". Also, make sure that your Master file for:

TABLE FILE RRRAREQ field you are is using a similar format like I have above.

Yeah, I did that. In the master file, the Usage is P9 and the actual is P5.

But P's dont seem to work with the quotes. So I am on A20 now. Still gives error.

Brian

What does the output file look like with the PCHOLD, there should be nothing outside the single quotes.


TABLE FILE RRRAREQ
BY RRRAREQ_PIDM
WHERE RRRAREQ_AIDY_CODE EQ '&AID_YEAR';
WHERE RRRAREQ_TREQ_CODE EQ 'ADMIT';
WHERE RRRAREQ_TRST_CODE NE 'C';
ON TABLE SET PAGE-NUM OFF
ON TABLE NOTOTAL
ON TABLE HOLD AS RRRAREQ_TEMP FORMAT FOCUS
END
DEFINE FILE RRRAREQ_TEMP
ROW3/A15='''' | RRRAREQ_PIDM | '''';
END
TABLE FILE RRRAREQ_TEMP
PRINT ROW3
ON TABLE PCHOLD AS RRRAREQ_ADMIT FORMAT ALPHA
END
-RUN
-EXIT
TABLE FILE RRRAREQ
BY RRRAREQ_PIDM
WHERE RRRAREQ_AIDY_CODE EQ '&AID_YEAR';
WHERE RRRAREQ_TREQ_CODE EQ 'SELSER';
WHERE NOT RRRAREQ_TRST_CODE IN ('S','W');
WHERE RRRAREQ_PIDM IN FILE RRRAREQ_ADMIT;
ON TABLE SET PAGE-NUM OFF
ON TABLE NOTOTAL
END



Try this.


DEFINE FILE RRRAREQ
ROW3/A15='''' | RRRAREQ_PIDM | '''';
END

TABLE FILE RRRAREQ_TEMP
PRINT ROW3
BY ROW3 NOPRINT
WHERE RRRAREQ_AIDY_CODE EQ '&AID_YEAR';
WHERE RRRAREQ_TREQ_CODE EQ 'ADMIT';
WHERE RRRAREQ_TRST_CODE NE 'C';
ON TABLE HOLD AS RRRAREQ_ADMIT
END

TABLE FILE RRRAREQ
BY RRRAREQ_PIDM
WHERE RRRAREQ_AIDY_CODE EQ '&AID_YEAR';
WHERE RRRAREQ_TREQ_CODE EQ 'SELSER';
WHERE NOT RRRAREQ_TRST_CODE IN ('S','W');
WHERE RRRAREQ_PIDM IN FILE RRRAREQ_ADMIT;
ON TABLE SET PAGE-NUM OFF
ON TABLE NOTOTAL
END

quote:

DEFINE FILE RRRAREQ
ROW3/A15='''' | RRRAREQ_PIDM | '''';
END

I looked at my data in a pchold file....and it looks like garbage....ascii boxes...etc. There are no pidms (id's) in the file at all. If I get rid of the define, the numbers look fine, yet I still get errors when trying to use the IN FILE command.

I wouldn't think this should be this hard.

Brian

This message has been edited. Last edited by: funsutton,

This was what it looked like with the define:

' 0?'
' 8_'
' G'
' b'
' ?o'
' ?o'
' "?'
' 1'
' ?'
' ?/'
' F'
' c?'
' ??'
' ?O'
' ?_'
' `?'
' q?'
' '
' 3O'
' #?'

Brian,

Copy this example into a fex and run it to see the operation.

The important bit is to have the file in ALPHA format as opposed to binary as you appear to have. If you use SAVE then the default is ALPHA so you don't need to add the FORAMT ALPHA part. Also, you don't need a master file so using HOLD FORMAT ALPHA isn't required. Finally you need the file on the reporting server so do not use PCHOLD.

TABLE FILE GGSALES
BY DOLLARS
WHERE DATE FROM '19960101' TO '19971231'
  AND DOLLARS FROM 1800 TO 1899
   ON TABLE SAVE AS MYLOOKUP
END
-RUN

TABLE FILE GGSALES
PRINT ST
BY REGION
WHERE DOLLARS IN FILE MYLOOKUP
END

Good luck

T

Thanks Tony. Unfortunately, running your code doesn't work for me, as we dont have those tables. Plus, I don't have write access to the Data Servers. I work under the section in the Developer Studio called Managed Reporting.

But, applying what you wrote to mine, it looks like this:

-DEFAULT &AID_YEAR = '0607'

TABLE FILE RRRAREQ
BY RRRAREQ_PIDM
WHERE RRRAREQ_AIDY_CODE EQ '&AID_YEAR'
WHERE RRRAREQ_TREQ_CODE EQ 'ADMIT'
WHERE RRRAREQ_TRST_CODE NE 'C'
ON TABLE SAVE AS MYLOOKUP
END
-RUN


TABLE FILE RRRAREQ
BY RRRAREQ_PIDM
WHERE RRRAREQ_AIDY_CODE EQ '&AID_YEAR'
WHERE RRRAREQ_TREQ_CODE EQ 'SELSER'
WHERE NOT RRRAREQ_TRST_CODE IN ('S','W')
WHERE RRRAREQ_PIDM IN FILE MYLOOKUP
END
  

And I get different output with an error:

 
0 NUMBER OF RECORDS IN TABLE=    16750  LINES=  16750
 ALPHANUMERIC RECORD NAMED  MYLOOKUP
 0 FIELDNAME                         ALIAS         FORMAT        LENGTH
 RRRAREQ_PIDM                      RRRAREQ_PIDM  P9              9
 RRRAREQ_PIDM                      RRRAREQ_PIDM  P9              9
 TOTAL                                                          18
 0 ERROR AT OR NEAR LINE     18  IN PROCEDURE ADHOCRQ FOCEXEC *
 (FOC553) A COMPUTATIONAL EXPRESSION IS TOO LARGE
 (FOC009) INCOMPLETE REQUEST STATEMENT

I've tried looking up that error message, however I haven't found anything.

Brian

I get that same message when the hold file that I am looking IN is too large. Does anyone know the actual size limit for the hold file?

Use the WF help.

Look up the WHERE phrase and then Search on Reading Selection Values From a File

That gives you the answer to your error.

You should only have one value per line in your file also - look at the examples they show in the help.

That should put you on the right track.

I almost always use the ALPHA format so I can see the actual values to help with debugging etc.

Brian,

Use SET HOLDLIST = PRINTONLY to get the SVAE file to only contain one entry per row. That should help a little with the size, which I believe is 32768 chars? or something like that.

T

Well, it looks like I am going to have to abort on this method based on what I know now, because I am giving the file at least 16,000 rows....at roughly 6-8 characters per row. That's way more than 32,000 characters. And the alpha files are probably close to 180kb (when I looked at them on my pc).

I can always do multiple reports with a couple of joins to make this work for me. Just was trying to learn an easier method.

Brian

Just so there's no confusion about size limits - this is from the WF Help:

For IF, the total of all files can be up to 32,767 literals, including new line and other formatting characters. Lower limits apply to fixed sequential and other non-relational data sources.

For WHERE, the file can be approximately 16,000 bytes. If the file is too large, an error message displays.

Brian,

As your data appears to eminate from a SQL source, then why not stick to SQL passthru anyway?

T

They have sql passthru disabled here. The way we have it set up is that if I have access to the sql passthru, then I have access to every table in oracle; which bypasses webfocus permissions.

Sometimes it would be easier to have it on...but this forces me to investigate webfocus ways to do it, which does make me learn.

Brian