Maybe you could do it within the preceeding-part, like:
[CDPOS]: // Change document items
[TABNAME] as [Table_TABNAME],
[MANDANT] as [Client_MANDANT],
[OBJECTCLAS] as [Object_OBJECTCLAS],
[CHANGENR] as [Doc. no._CHANGENR],
[VALUE_OLD] as [Old value_VALUE_OLD],
[VALUE_NEW] as [New value_VALUE_NEW],
[CHNGIND] as [ChangeInd._CHNGIND],
[TEXT_CASE] as [Text flag_TEXT_CASE],
[UNIT_OLD] as [Unit_UNIT_OLD],
[UNIT_NEW] as [Unit_UNIT_NEW],
[CUKY_OLD] as [CUKY_CUKY_OLD],
[CUKY_NEW] as [CUKY_CUKY_NEW],
[_DATAAGING] as [__DATAAGING],
[FNAME] as [Field_FNAME],
[OBJECTID] as [Obj. value_OBJECTID],
[OBJECTID] as [Sales Doc._VBELN],
[TABKEY] as [Table Key_TABKEY],
right([TABKEY], 6) as POSNR
SQL Select FNAME TABNAME MANDANT OBJECTID OBJECTCLAS CHANGENR VALUE_OLD VALUE_NEW CHNGIND TEXT_CASE UNIT_OLD UNIT_NEW CUKY_OLD CUKY_NEW _DATAAGING TABKEY substr(TABKEY,6,6) AS POSNR from CDPOS
WHERE TABNAME = 'VBAP' AND CHNGIND = 'D';
I have no SAP backend-knowledge but I think there are like in other SQL system various string-functions to do this job. Beside searching here in the various SAP groups or more global per google you could also build a small dummy-load of one or two fields with a where-clause and/or a First /Top statement and trying which functions of left/right/mid ... are known.
For fetching the a specific number of characters from any field, directly from the left or right there are two functions.
In your case it would be:
FieldName is TABKEY and numcharacters in from the right is 6.
Hope this helps.