What needs to be done to run an SQL statement in a Variable.

Question

Post reply

What needs to be done to run an SQL statement in a Variable.

hedlund_yvonne

SSC Enthusiast

Points: 144
More actions
February 13, 2008 at 7:18 am

#119977

Error text:
The expression might contain an invalid token, an imcomplete token, or an invalid element. it might not be well-formed, or might be missing part of a required element such as a parenthesis.
My Select query:
SELECT KALKYL.ORDER_NR, KALKYL.POS_NR, KALKYL.TIDPUNKT_UPPDAT
FROM ABC.KALKYL, ABC.KALKYLSTEG_RES, ABC.KALKYLSTEG_VER, OG07.TALEVERANS_POS
WHERE ABC.KALKYL.KALKYL_ID = ABC.KALKYLSTEG_RES.KALKYL_ID AND
ABC.KALKYLSTEG_RES.KALKYLSTEG_ID = ABC.KALKYLSTEG_VER.KALKYLSTEG_ID AND
ABC.KALKYLSTEG_RES.KALKYLSTEG_VER = ABC.KALKYLSTEG_VER.KALKYLSTEG_VER AND
ABC.KALKYL.ORDER_NR = OG07.TALEVERANS_POS.ORDER_NR AND ABC.KALKYL.POS_NR = OG07.TALEVERANS_POS.POS_NR AND
(ABC.KALKYL.KALKYLTYP = 0) AND (ABC.KALKYL.STATUS_RES = 0) AND (OG07.TALEVERANS_POS.DAT_LEV >= '6/1/2003') AND
(KALKYL.TIDPUNKT_UPPDAT >= @[User::LastUpdateAbcProdCost] )
GROUP BY KALKYL.ORDER_NR, KALKYL.POS_NR, KALKYL.TIDPUNKT_UPPDAT

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Frank Bazan SSCertifiable Points: 5378 More actions · Answer 1

Hi,

It doesn't look like the string has been constructed properly. Its missing quotes and concatenation...

"SELECT KALKYL.ORDER_NR, KALKYL.POS_NR, KALKYL.TIDPUNKT_UPPDAT

FROM ABC.KALKYL, ABC.KALKYLSTEG_RES, ABC.KALKYLSTEG_VER, OG07.TALEVERANS_POS

WHERE ABC.KALKYL.KALKYL_ID = ABC.KALKYLSTEG_RES.KALKYL_ID AND

ABC.KALKYLSTEG_RES.KALKYLSTEG_ID = ABC.KALKYLSTEG_VER.KALKYLSTEG_ID AND

ABC.KALKYLSTEG_RES.KALKYLSTEG_VER = ABC.KALKYLSTEG_VER.KALKYLSTEG_VER AND

ABC.KALKYL.ORDER_NR = OG07.TALEVERANS_POS.ORDER_NR AND ABC.KALKYL.POS_NR = OG07.TALEVERANS_POS.POS_NR AND

(ABC.KALKYL.KALKYLTYP = 0) AND (ABC.KALKYL.STATUS_RES = 0) AND (OG07.TALEVERANS_POS.DAT_LEV >= '6/1/2003') AND

(KALKYL.TIDPUNKT_UPPDAT >= " + @[User::LastUpdateAbcProdCost] + ")

GROUP BY KALKYL.ORDER_NR, KALKYL.POS_NR, KALKYL.TIDPUNKT_UPPDAT"

Kindest Regards,

Frank Bazan

hedlund_yvonne SSC Enthusiast Points: 144 More actions · Answer 2

Thanks a lot. The quotes worked. I tried it once but I must have forgot something because I got the same error as earlier. But know I discovered a new problem.

The parameter that I try to compare with within the string is a date and know I get following error:

The data types "DT_WSTR" and "DT_DATE" are incompatible for binary operator "+". The operand types could not be implicitly cast into compatible types for the operation. To perform this operation, one or both operands need to be explicitly cast with a cast operator.

Any tip on how I explicitly converts a variable of datatype: date into a string to be able to use and compare in the SQL statement that is a string????

Thans for all your help this far.

Frank Bazan SSCertifiable Points: 5378 More actions · Answer 3

You're quite right,

If the variable you are concatenating isn't of the same datatype as the expression result then you need to cast it. To do this place a typecast function just before the variable.

Try this

"SELECT KALKYL.ORDER_NR, KALKYL.POS_NR, KALKYL.TIDPUNKT_UPPDAT

FROM ABC.KALKYL, ABC.KALKYLSTEG_RES, ABC.KALKYLSTEG_VER, OG07.TALEVERANS_POS

WHERE ABC.KALKYL.KALKYL_ID = ABC.KALKYLSTEG_RES.KALKYL_ID AND

ABC.KALKYLSTEG_RES.KALKYLSTEG_ID = ABC.KALKYLSTEG_VER.KALKYLSTEG_ID AND

ABC.KALKYLSTEG_RES.KALKYLSTEG_VER = ABC.KALKYLSTEG_VER.KALKYLSTEG_VER AND

ABC.KALKYL.ORDER_NR = OG07.TALEVERANS_POS.ORDER_NR AND ABC.KALKYL.POS_NR = OG07.TALEVERANS_POS.POS_NR AND

(ABC.KALKYL.KALKYLTYP = 0) AND (ABC.KALKYL.STATUS_RES = 0) AND (OG07.TALEVERANS_POS.DAT_LEV >= '6/1/2003') AND

(KALKYL.TIDPUNKT_UPPDAT >= '" + (DT_WSTR,20) @[User::LastUpdateAbcProdCost] + "')

GROUP BY KALKYL.ORDER_NR, KALKYL.POS_NR, KALKYL.TIDPUNKT_UPPDAT"

Kindest Regards,

Frank Bazan

hedlund_yvonne SSC Enthusiast Points: 144 More actions · Answer 4

THANKS A LOT. I managed to get it to work now. I used script task to convert the date varable that I had into a string. Now it´s finally working.

It would have been easier to use OLE DB command if any oracle provider supports adding on parameters but the ones that I had didn´t. So if anyone who has had the same problem as me and has found a provider that works please write a note hear and tell me what provider I need to add parameter into my SQL question in an OLE DB command component.:):)