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]

You are already checking ZORDER values IN VBELN in your SQL Script. Why you want to check if exists in your resident load which you are loading from your SQL. Just trying to understand your requirement. IS VBELN coming from our SQL Script? Are you getting any errors when you run this resident load? Can you share some sample? Usually Exists will check if the data from 2nd table exists in the data from left table.

[tmumaw]

Here is the error message I get from the first step:  Can I not do the Right command?

QVX_UNEXPECTED_END_OF_DATA: SQL_ERROR

Comma without preceding colon (after OPEN ?).

ZTRPSTTEMP]:

SQL Select *

FROM ZTRPST

WHERE Right([ZORDER],10) in (Select VBELN from VBAK)

[tmumaw]

Never mind my last error.  I forgot the left bracket on my table name.  DAH

[tmumaw]

Same message:

QVX_UNEXPECTED_END_OF_DATA: SQL_ERROR

Comma without preceding colon (after OPEN ?).

[ZTRPSTTEMP]:

SQL Select *

FROM ZTRPST

    WHERE Right([ZORDER],10) in (Select VBELN from VBAK)

[vishsaggi]

Does this script works in your SQL environment? Can you try and let us know.

[tmumaw]

I think I may have found it.  The table I am trying to compare against has a contract ID as a length of 10 where the other has a length of 12.  I changed the right function to a substr function giving it a starting position and length.  We will see how this works.  Thanks for your help I will keep you posted.

[vishsaggi]

And also try to use VBELN in your select list if not that Field will not be loaded and used in your resident load.

[tmumaw]

Error: QVX_UNEXPECTED_END_OF_DATA: SQL_ERROR

2018-01-25 12:09:03      Comma without preceding colon (after OPEN ?).

2018-01-25 12:51:28      Execution Failed

[tmumaw]

Sorry forgot to add this:

[ZTRPSTTEMP]:
SQL Select *
FROM ZTRPST
WHERE substr([ZORDER],3,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 ZWDATE >= '$(vPostYear)' and ZEMPEQP = 'P' and
(ZSTATUS = 'C' or ZSTATUS = 'I')
;

STORE * FROM [ZTRPSTTEMP] into QVD\ZTRPST.QVD
;
Exit Script;