Skip to main content
Announcements
See what Drew Clarke has to say about the Qlik Talend Cloud launch! READ THE BLOG
cancel
Showing results for 
Search instead for 
Did you mean: 
tmumaw
Specialist II
Specialist II

Script Logic

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])

1 Solution

Accepted Solutions
tmumaw
Specialist II
Specialist II
Author

I'm so sorry I did not get back to you sooner.  I finally figured it out.  I have attached a copy of my entire script just in case it happens to anyone else.  Thanks for all your help and time.

View solution in original post

21 Replies
vishsaggi
Champion III
Champion III

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
Specialist II
Specialist II
Author

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
Specialist II
Specialist II
Author

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

tmumaw
Specialist II
Specialist II
Author

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
Champion III
Champion III

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

tmumaw
Specialist II
Specialist II
Author

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
Champion III
Champion III

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
Specialist II
Specialist II
Author

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
Specialist II
Specialist II
Author

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;