Subselect query not working in SAP

[#1]

Hi All,

I am trying to apply incremental load on a SAP table named CDPOS table using CDHDR & subselect keyword.

The script is as follows :-

let vDate = date(Today()-1,'YYYYMMDD');

CDPOS:

SQL SUBSELECT  * from CDPOS

where OBJECTCLAS OBJECTID CHANGENR IN (SELECT OBJECTCLAS OBJECTID CHANGENR FROM CDHDR where UDATE = '$(vDate)');

But it fails and the QV log file shows an error - "

QVX_UNEXPECTED_END_OF_DATA: Fetch aborted, due to an unexpected exception. Key = JOB_CANCELLED (ID:00 Type:E Number:001 Job aborted, check log for Job /QTQVC/READ_DATA in Job Overview (transaction SM37))

10/15/2014 12:39:24 PM:       Error: Custom read failed "

Hence i checked the sap log which shows me the error - " Subselect with one table is not possible"

The query does contain two different tables.

PFA the snapshot of the same.

Note:-  All the other SAP extractors are working fine.

Can anybody please help me ...... there are very less posts on subselect and not helpful to me.

Thanks in Advance

Sarang M. Mehta

[Anonymous]

I think you need to correct one minor issue: - Insert blank character between the brackets

Try this:

CDPOS:

SQL SUBSELECT  * from CDPOS

where OBJECTCLAS OBJECTID CHANGENR IN ( SELECT OBJECTCLAS OBJECTID CHANGENR FROM CDHDR where UDATE = '$(vDate)' );

[ValeriyaBartosh]

The same problem for me.  If I insert blank character between the brackets I have another problem, see below

[Hakan_Ronningberg]

Hi,

I think you need a blank character in front of the last parenthesis:

...<='20191231' )

Regards,
Håkan

[geam1988]

Hallo Mehta,

Is your problem Solved? I also want to load CDPOS table from SAP. I am getting error message 'Custom read failed'

LET vLoadDaysFrom = Date(Today()-50, 'YYYYMMDD');

SUB mw_qualifyFields (vqTable)

     let vTest = NoOfFields('KEKO');
     for q = 1 to NoOfFields('$(vqTable)')
    
           let vqFromFieldName = FieldName($(q), '$(vqTable)');
           let vqToFieldName = '$(vqTable).' & '$(vqFromFieldName)';
    
           IF Left('$(vqFromFieldName)', 1) <> '%' then
    
                RENAME Field [$(vqFromFieldName)] to [$(vqToFieldName)];
    
           end if
    
     next
    
     let q = Null();
     let vqFromFieldName = Null();
     let vqToFieldName = Null();

END SUB

CDHDR:
SQL SELECT CHANGENR UDATE FROM CDHDR WHERE OBJECTCLAS GE 'MATERIAL' AND UDATE GE '$(vLoadDaysFrom)' ;

CDPOS:
SQL SELECT OBJECTCLAS CHANGENR TABNAME TABKEY FNAME VALUE_NEW VALUE_OLD
FROM CDPOS WHERE OBJECTCLAS GE 'MATERIAL' AND CHANGENR IN (SELECT CHANGENR FROM CDHDR WHERE UDATE GE '$(vLoadDaysFrom)' );

CALL mw_qualifyFields ('CDPOS'); 

STORE CDPOS into $(QVDPath)\CDPOS.QVD;

CALL ZeitStempel ('CDPOS');

DROP TABLE CDPOS;
DROP TABLE CDHDR;

exit script ;

Thanks Kazi

[Hakan_Ronningberg]

Hi Kazi,

You need to use the SUBSELECT syntax for your second select. The reason is that CDPOS is a cluster table in some SAP basis versions.

https://help.qlik.com/en-US/connectors/Subsystems/SAP_Connectors_Help/Content/Connectors_SAP/Clients...

Replace SELECT with SUBSELECT like:

CDPOS:
SQL SUBSELECT OBJECTCLAS CHANGENR TABNAME TABKEY FNAME VALUE_NEW VALUE_OLD
FROM CDPOS WHERE OBJECTCLAS GE 'MATERIAL' AND CHANGENR IN ( SELECT CHANGENR FROM CDHDR WHERE UDATE GE '$(vLoadDaysFrom)'  );

Note that I added a white space after the first parenthesis and in front of the last parenthesis.

Regards,
Håkan