Hi All,

I have a requirement to get all store numbers where a car is available.
There is a Str_status column in the table which is a VARCHAR2(10), where the first position of the character string denotes store_id = 1, 2nd character denotes store_id = 2,....10th character denotes store_id = 10. 'A' is available and ' ' is not available.

  

 CAR          BEG_I     END_I       STR_STATUS
 ----------   -----     -----       -----------
 Jaguar          1        10        'AAA AA A  '
 Jaguar         11        20        'A  AA AAAA'
 Toyota          1        10        'A   A  A A'

My Output should be:

 

  CAR          Store_Id
  --------     ----------
  Jaguar             1
  Jaguar             2
  Jaguar             3
  Jaguar             5
  Jaguar             6
  Jaguar             8
  Jaguar            11
  Jaguar            14
  Jaguar            15
  Jaguar            17
  Jaguar            18
  Jaguar            19
  Jaguar            20
  Toyota             1
  Toyota             5
  Toyota             8
  Toyota            10

 

How can I get that?
Please help

Thanks


Note: In above example I have shown it as 10 characters string. But my acutal table contains 1000 character string. and my primary key has 1000+ items

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

nsk110483,

The only I could think of was to use DM and loop through the string values you have:

TABLE FILE SYSTABLE
SUM
COMPUTE CAR/A16 = '';
COMPUTE BEG_I/I3 = 0;
COMPUTE END_I/I3 = 0;
COMPUTE STR_STATUS/A10 = '';
BY NAME NOPRINT
ON TABLE SET HOLDLIST PRINTONLY
ON TABLE SET XRETRIEVAL OFF
ON TABLE HOLD AS HLDFILE FORMAT ALPHA
END
-RUN
-*------------------------------------------------------------------------------
-WRITE HLDFILE Jaguar 1 10AAA AA A
-WRITE HLDFILE Jaguar 11 20A AA AAAA
-WRITE HLDFILE Toyota 1 10A A A A
-*------------------------------------------------------------------------------
TABLE FILE SYSTABLE
SUM
COMPUTE CAR/A16 = '';
COMPUTE Store_Id/I3 = 0;
BY NAME NOPRINT
ON TABLE SET HOLDLIST PRINTONLY
ON TABLE SET XRETRIEVAL OFF
ON TABLE HOLD AS NEWFILE FORMAT ALPHA
END
-RUN

TABLE FILE HLDFILE
PRINT *
ON TABLE HOLD AS HLDFILE FORMAT ALPHA
END
-RUN
-*------------------------------------------------------------------------------
-SET &CNT = 0;
-SET &IORETURN = 0;
-*-------------------------------------------------------------------
-* First Loop
-REPEAT END1LOOP WHILE (&IORETURN EQ 0)

-READ HLDFILE NOCLOSE &CAR.A16 &BI.A3 &EI.A3 &STAT.A10

-SET &NBI = &BI -1;
-SET &CNT = &CNT + 1;
-IF &CNT GT &LINES THEN GOTO END1LOOP;
-*-------------------------------------------------------------------
-* Second Loop
-REPEAT END2LOOP FOR &CT FROM 1 TO 10

-SET &XSTR1 = SUBSTR( 10, &STAT.QUOTEDSTRING, &CT, &CT , 1, 'A1');

-SET &XBI = IF ( &XSTR1 EQ '' ) THEN 0 ELSE &NBI + &CT;

-WRITE NEWFILE &CAR &XBI

-END2LOOP
-*-------------------------------------------------------------------
-END1LOOP
-CLOSE HLDFILE
-CLOSE NEWFILE
-*------------------------------------------------------------------------------

TABLE FILE NEWFILE
PRINT *
WHERE Store_Id NE 0
END

This message has been edited. Last edited by: Mary Watermann,

Look up the macgiver technique. It's perfect for this problem.

I tried to use MacGyver, but without luck. If someone can post an MacGyver example to solve this question, please do so; I'll appriciate it.

But on the other hand, I think it can be solved with TABLE. The only amper that needs to set in this example is
the length of the field containing all those A's (see -SET &STR_STATUS_LENGTH = xxx);

Not sure what the response would be with this on 1000+ record-set with a A1000 field for STR_STATUS.

(btw, it would be quite interesting to compare the runtimes between this and a MacGyver solution).

-* -------------------------------------------------------------------------------------
-* Create MASTER / DATA files
-* -------------------------------------------------------------------------------------
APP FI HLDFILE DISK hldfile.mas
-RUN
-WRITE HLDFILE SEGMENT=HLDFILE, SEGTYPE=S0, $
-WRITE HLDFILE  FIELDNAME=CAR        ,E01 ,A16   ,ACTUAL=A16 ,$
-WRITE HLDFILE  FIELDNAME=BEG_I      ,E02 ,I3    ,ACTUAL=A03 ,$
-WRITE HLDFILE  FIELDNAME=END_I      ,E03 ,I3    ,ACTUAL=A03 ,$
-WRITE HLDFILE  FIELDNAME=STR_STATUS ,E04 ,A10   ,ACTUAL=A10 ,$

APP FI HLDFILE DISK hldfile.ftm
-RUN
-WRITE HLDFILE Jaguar            1 10AAA AA A
-WRITE HLDFILE Jaguar           11 20A  AA AAAA
-WRITE HLDFILE Toyota            1 10A   A  A A
-* -------------------------------------------------------------------------------------


-* --- Length of field STR_STATUS
-SET &STR_STATUS_LENGTH = 10;


FILEDEF HLDTOTAL DISK HLDTOTAL.FTM (APPEND
-RUN


-REPEAT :LB_LOOP FOR &I FROM 1 TO &STR_STATUS_LENGTH;
DEFINE FILE HLDFILE
  FLAG/A1 = SUBSTR(&STR_STATUS_LENGTH, STR_STATUS, &I, &I, 1, 'A1');
END

TABLE FILE HLDFILE
  PRINT
    CAR
    COMPUTE STORE_ID/I5 = (&I + BEG_I) - 1;
  WHERE FLAG EQ 'A' ;
  ON TABLE HOLD AS HLDTOTAL
  ON TABLE SET HOLDLIST PRINTONLY
END
-:LB_LOOP

TABLE FILE HLDTOTAL
  BY CAR
  BY STORE_ID
END

FreSte,

I got thinking about this again using the MacGyver technique. I did get this to work.

  
TABLE FILE SYSTABLE
SUM
    COMPUTE SP1/A1          = '';
    COMPUTE CAR/A16         = '';
    COMPUTE BEG_I/I3        = 0;
    COMPUTE END_I/I3        = 0;
    COMPUTE STR_STATUS/A10  = '';
BY NAME NOPRINT
ON TABLE SET HOLDLIST PRINTONLY
ON TABLE SET XRETRIEVAL OFF
ON TABLE HOLD AS HLDFILE FORMAT ALPHA
END
-RUN
-*------------------------------------------------------------------------------
-WRITE HLDFILE  Jaguar            1 10AAA AA A
-WRITE HLDFILE  Jaguar           11 20A  AA AAAA
-WRITE HLDFILE  Toyota            1 10A   A  A A
-*------------------------------------------------------------------------------
TABLE FILE SYSTABLE
SUM
    COMPUTE CAR/A16     = '';
    COMPUTE Store_Id/I3 = 0;
BY NAME NOPRINT
ON TABLE SET HOLDLIST PRINTONLY
ON TABLE SET XRETRIEVAL OFF
ON TABLE HOLD AS NEWFILE FORMAT ALPHA
END
-RUN

TABLE FILE HLDFILE
PRINT *
ON TABLE HOLD AS HLDFILE FORMAT ALPHA
END
-RUN
-*------------------------------------------------------------------------------
FILEDEF FSEQ DISK fseq.mas
-RUN

-WRITE FSEQ  
-WRITE FSEQ FILE=FSEQ,SUFFIX=FIX,REMARKS='McGuyver Technique'
-WRITE FSEQ SEGNAME=SEG1
-WRITE FSEQ FIELD=BLANK,,A1,A1,$
-WRITE FSEQ SEGNAME=SEG2,OCCURS=10,PARENT=SEG1
-WRITE FSEQ FIELD=CHAR1,,A1,A1,$
-WRITE FSEQ FIELD=FOCLIST,ORDER,I4,I4,$

JOIN SP1 IN HLDFILE TO BLANK IN FSEQ AS J1
-RUN
DEFINE FILE HLDFILE
BEGI/I5     = ((BEG_I - 1) + FOCLIST);
STOREID/A1  = SUBSTR( 10, STR_STATUS, FOCLIST, FOCLIST , 1, 'A1');
STORE_ID/I5 = IF ( STOREID EQ '' ) THEN 0 ELSE BEGI;
END

TABLE FILE HLDFILE
PRINT
CAR
STORE_ID
WHERE STORE_ID NE 0
END
-RUN

This message has been edited. Last edited by: Mary Watermann,

Mary,

Thanks for the post, but if I copy your code and run it I get a 0-record answer set.
Am I missing something ?

This message has been edited. Last edited by: <FreSte>,

Can you just use OCCURS in the master file ?

If the data source does not like it, then HOLD the data , and use your own master.

e.g.

DEFINE FILE CAR
 CNTR/I9 WITH BODY = LAST CNTR + 1 ;
 BEG_I/I9 WITH BODY = IF CAR NE LAST CAR THEN 1 ELSE LAST BEG_I + 10 ;
 END_I/I9 WITH BODY = BEG_I + 9 ;
 STR_STATUS/A10 = DECODE CNTR(1 'AAA AA A  '
                              2 'A  AA AAAA'
                              3 'A   A  A A'
                           ELSE ' ') ;
END
TABLE  FILE CAR
 PRINT BEG_I
       END_I
       STR_STATUS
 BY    CAR
 BY    BODY NOPRINT
 WHERE CAR IN ('JAGUAR','TOYOTA')

 ON    TABLE SAVE AS TMP_DATA
END

-RUN

-* Write out a master to read the BSB_ACC list
EX -LINES 10 EDAPUT MASTER,TMP_DATA,CV,FILE
FILENAME=TMP_DATA, SUFFIX=FIX,$
SEGNAME=TMP_DATA, $
  FIELD=CAR        ,ALIAS=  ,A16 ,A16 ,$
  FIELD=BEG_I      ,ALIAS=  ,I9  ,A9  ,$
  FIELD=END_I      ,ALIAS=  ,I9  ,A9  ,$
SEGNAME=BYTEDATA,OCCURS=10,PARENT=TMP_DATA
  FIELD=CHAR1      ,ALIAS=  ,A1  ,A1  ,$
  FIELD=CHARNO     ,ORDER   ,I4  ,I4  ,$
DEFINE Store_Id/I9 = BEG_I + CHARNO - 1 ;

-RUN

TABLE FILE TMP_DATA
 PRINT CAR
       Store_Id
 WHERE CHAR1 NE ' '
END

Thanks Waz, things are more clear now.