QV 11 with SAP Conn 5.60 SUBSELECT problem

[avastani]

Has anyone gotten this error?

/QTQVC/OPEN_STREAM failed after 00:00:00 Key = SQL_ERROR (ID:00 Type:E Number:001 not a valid comparison operator .)

I get this error on the following statement

SQL Select MEINS MBLNR MJAHR MATNR WERKS BUKRS BWART AUFNR AUFPS WAERS EBELN BELNR KOSTL FKBER PS_PSP_PNR PRCTR KZBWS GJAHR ANLN1 ANLN2 MENGE BUZEI MWSKZ DMBTR SGTXT ERFMG ERFME BPMNG BPRME EBELP ZEKKN VPRSV VPTNR NPLNR AUFPL APLZL EMATN RSNUM EMLIF RSPOS SAKTO KZEAR BSTME BSTMG LFBNR LFPOS SMBLN SMBLP SJAHR VKWRT XWSBR BELUM BUZUM EXBWR BNBTR LFBJA UMMAB DABRBZ XBEAU QINSPST URZEI J_1AGIRUPD VKMWS KDAUF KDEIN KDPOS INSMK SOBKZ KUNNR LGORT LIFNR VKWRA ZEILE  from MSEG WHERE MBLNR MJAHR IN (SELECT MBLNR MJAHR  FROM MKPF WHERE BUDAT GE $(vMaxPostingDate))

;

MJAHR and MBLNR exist in both MKPF and MSEG but I am unable to use it. When I remove, it works fine but I need the Year as well so that I am only fetching data for the year(s) I need. Any assistance greatly appreciated. Thanks.

[Ian_Crosland]

Hi

The SUBSELECT is designed for cluster tables, MSEG can be either transparent or cluster depending on SAP version so worth checking as the syntax would need to use SQL SUBSELECT not SQL Select.  The following will return only Posting Dates specified from MKPF and pass this filter onto MSEG based on MBLNR so no requirement to add MJAHR in your select statement

SET vMaxPostingDate='19941115';


SQLSelect MEINS MBLNR MJAHR MATNR WERKS BUKRS BWART AUFNR AUFPS WAERS EBELN BELNR KOSTL FKBER PS_PSP_PNR PRCTR KZBWS GJAHR ANLN1 ANLN2 MENGE BUZEI MWSKZ DMBTR SGTXT ERFMG ERFME BPMNG BPRME EBELP ZEKKN VPRSV VPTNR NPLNR AUFPL APLZL EMATN RSNUM EMLIF RSPOS SAKTO KZEAR BSTME BSTMG LFBNR LFPOS SMBLN SMBLP SJAHR VKWRT XWSBR BELUM BUZUM EXBWR BNBTR LFBJA UMMAB DABRBZ XBEAU QINSPST URZEI J_1AGIRUPD VKMWS KDAUF KDEIN KDPOS INSMK SOBKZ KUNNR LGORT LIFNR VKWRA ZEILE
from MSEG WHERE MBLNR IN (SELECT MBLNR FROM MKPF WHERE BUDAT = '$(vMaxPostingDate)');

If you want to filter on speciifc years the statement would be changed to:

FROM MSEG
where MBLNR IN (SELECT MBLNR FROM MKPF WHERE MJAHR = '1994')