Script Logic

[tmumaw]

Just wondering if this will work?

Thanks

[ZTRPSTTEMP]:
SQL Select *
FROM ZTRPST
WHERE Right([ZORDER],10) in (Select VBELN from VBAK)
;

[ZTRPST]:  // Time Reports - Spread Time Posted
Load
[ZTICKNO]   as [Ticket],
Left([ZOBJECT],8)  as EmpID,
Right([ZORDER],10) & [ZACTIVITY] as [%ContractItemKey],
Right([ZORDER],10)  as [Contract],
[ZACTIVITY]   as [Contrac Line],
[ZSTATUS]   as [Status], 
[ZWDATE]   as [Date],
ZHOURS   as [Z_Hours],
ceil(ZHOURS, .01)  as [Hours],
round(ZHOURS, .01) as [Hours_Round],
floor(ZHOURS, .01) as [Hours_Floor]
Resident ZTRPSTTEMP
WHERE exists(Right([ZORDER],10), [VBELN])
; 

[vishsaggi]

This error does not need to be QV error. There is something we need to look into from SQL Side. Do you have a SQL connection before running your SQL Script inside QV.

I want to check few things here.

1. Will the SQL Script runs in your SQL editor?

2. Can you comment out the Resident Load script whole script i mean and just run your SQL script?

Please let me know.

[tmumaw]

I tried this and it works....

[ZTRPSTTEMP]:
SQL Select *
FROM ZTRPST
// WHERE substr([ZORDER],3,10) in (Select VBELN from VBAK)
WHERE ZWDATE >= '$(vPostYear)' and ZEMPEQP = 'P' and
(ZSTATUS = 'C' or ZSTATUS = 'I')
;
exit SCRIPT;

[vishsaggi]

Ok, does that work for you?

[tmumaw]

This works:

[ZTRPSTTEMP]:
SQL Select *
FROM ZTRPST
WHERE ZWDATE >= '$(vPostYear)' and ZEMPEQP = 'P' and
(ZSTATUS = 'C' or ZSTATUS = 'I')
;
exit SCRIPT;

I need this one to work:

[ZTRPSTTEMP]:
SQL Select *
FROM ZTRPST
WHERE substr([ZORDER],3,10) in (Select VBELN from VBAK)
;
exit SCRIPT;

[vishsaggi]

May be you can try this? Change accordingly.

VBAK:

SQL

Select VBELN from VBAK

;

[ZTRPSTTEMP]:

SQL Select *, Right([ZORDER],10) AS ZorderID

FROM ZTRPST;

[ZTRPST]:  // Time Reports - Spread Time Posted

Load

ZorderID,

[ZTICKNO]   as [Ticket],

Left([ZOBJECT],8)  as EmpID,

Right([ZORDER],10) & [ZACTIVITY] as [%ContractItemKey],

Right([ZORDER],10)  as [Contract],

[ZACTIVITY]   as [Contrac Line],

[ZSTATUS]   as [Status], 

[ZWDATE]   as [Date],

ZHOURS   as [Z_Hours],

ceil(ZHOURS, .01)  as [Hours],

round(ZHOURS, .01) as [Hours_Round],

floor(ZHOURS, .01) as [Hours_Floor]

Resident ZTRPSTTEMP

WHERE ZWDATE >= '$(vPostYear)' and ZEMPEQP = 'P' and

(ZSTATUS = 'C' or ZSTATUS = 'I')

AND Exists(ZorderID, VBELN)

;

Drop Table [ZTRPSTTEMP];

STORE * FROM [ZTRPST] into QVD\ZTRPST.QVD

;

Exit Script;

[tmumaw]

Here is my entire script.  I have already extracted VBAK.

[vishsaggi]

OK try this in your script and let me know if you getting any error:

VBAKTemp:

LOAD *

FROM QVD\VBAK.QVD(qvd)

;

[ZTRPSTTEMP]:

SQL Select *, Right([ZORDER],10) AS ZorderID

FROM ZTRPST;

[ZTRPST]:  // Time Reports - Spread Time Posted

Load

ZorderID,

[ZTICKNO]   as [Ticket],

Left([ZOBJECT],8)  as EmpID,

Right([ZORDER],10) & [ZACTIVITY] as [%ContractItemKey],

Right([ZORDER],10)  as [Contract],

[ZACTIVITY]   as [Contrac Line],

[ZSTATUS]   as [Status],

[ZWDATE]   as [Date],

ZHOURS   as [Z_Hours],

ceil(ZHOURS, .01)  as [Hours],

round(ZHOURS, .01) as [Hours_Round],

floor(ZHOURS, .01) as [Hours_Floor]

Resident ZTRPSTTEMP

WHERE ZWDATE >= '$(vPostYear)' and ZEMPEQP = 'P' and

(ZSTATUS = 'C' or ZSTATUS = 'I')

AND Exists(ZorderID, VBELN)

// Or use below for Exists

// AND Exists(Right([ZORDER], 10), VBELN)

;

Drop Table [ZTRPSTTEMP];

STORE * FROM [ZTRPST] into QVD\ZTRPST.QVD

;

Exit Script;

[tmumaw]

This part of the script runs fine however I get nothing out and I know there is data there.

[VBAKX]: 
Load
KUNNR & VKORG & VTWEG AS %KEY_KNVV,
[VBELN], 
[VBELN] as [%ContractDocument_Key],
Right([VBELN],8) as [Contract_ID], 
[VKORG] as [%SalesOrganisation_Key],
[ERNAM] as [Created by],
[ERDAT] as [Contract Create Date],
[VTWEG] as [%DistributionChannel_Key],
[VBTYP] as [Doc Cat],
[AUART] as [Sales Document Type],
[KUNNR] as [Ship To],
[VKBUR] as [SalesOffice_Key],
[VKGRP] as [%SalesGroup_Key],
[AUDAT] as [Document Date],
[NETWR] as [Contract Net Value],
[WAERK] as [Doc Curr],
[GUEBG] as [Valid Frm Date],
year(GUEBG) as Valid_Year,
[GUEEN] as [Valid To Date],
[PS_PSP_PNR] as [WBS Element],
[ZZWBS],
If(Mid(ZZWBS,1,1) = 'W',1,0) as WBS_SW
; 

SQL Select * from VBAK where (AUART = 'ZRRC' or AUART = 'ZBPC' or AUART = 'ZRPS' or AUART = 'ZBCS') //and (ZZWBS <> '00000000' or ZZWBS <> ' ')
  and GUEEN >= '$(vYearEnd)'
;
//Store VBAK into QVD\VBAKX.QVD
;

NoConcatenate
VBAK:
Load *
Resident VBAKX
Where WBS_SW = '1'
; 
Store VBAK into QVD\VBAK.QVD
;
Drop table VBAKX;
[ZTRPSTTemp]:  // Time Reports - Spread Time Posted
Load
[ZTICKNO]   as [Ticket],
Right([ZORDER],10) & [ZACTIVITY] as [%ContractItemKey],
Right([ZORDER],10)  as [%Contract],
[ZACTIVITY]   as [Contract Line],
[ZSTATUS]   as [Status], 
[ZWDATE]   as [Date],
ZHOURS   as [Z_Hours],
ceil(ZHOURS, .01)  as [Hours],
round(ZHOURS, .01) as [Hours_Round],
floor(ZHOURS, .01) as [Hours_Floor]
;
SQL Select ZTICKNO ZORDER ZWDATE ZHOURS ZACTIVITY ZSTATUS
from ZTRPST where ZWDATE >= '$(vPostYear)' and ZEMPEQP = 'P' and
(ZSTATUS = 'C' or ZSTATUS = 'I')
;

[ZTRPST]: 
Load *
RESIDENT ZTRPSTTemp
WHERE exists([VBELN], [%Contract])
;
STORE * FROM [ZTRPSTTemp] INTO QVD\ZTRPST.QVD;

Drop Table ZTRPSTTemp
;

exit SCRIPT;

[vishsaggi]

Do not use exists and just load the data and see if you can see the same data values for VBELN and %Contract fields?

Your below SQL statement do not have commas for your fields. May be this is taken care in your actual script file? Just wanted to check.

SQL Select ZTICKNO ZORDER ZWDATE ZHOURS ZACTIVITY ZSTATUS
from ZTRPST where ZWDATE >= '$(vPostYear)' and ZEMPEQP = 'P' and
(ZSTATUS = 'C' or ZSTATUS = 'I')
;

[tmumaw]

I commented out the exists statement and it runs fine with correct join.