DSXchange

Just to say if it's possible to create a cursor with datastage.

I must migrate this cursor sqlserver on datastage.

DECLARE CURSOR_JO_NUM CURSOR LOCAL
FOR
select a.JO_NUM,convert(varchar,a.CBMARQ)
FROM dbo.F_ECRITUREC a
WHERE CONVERT(varchar,a.EC_DATE,112) = convert(varchar,DateAdd(D, -1,getdate()),112);

OPEN CURSOR_JO_NUM

FETCH CURSOR_JO_NUM INTO @TYPE_JO_NUM,@CBMARQ
WHILE @@FETCH_STATUS=0
BEGIN
--PREMIERE INTEGRATION (VENTE, DEBOURS ou AVOIR)
IF (@TYPE_JO_NUM like '%VENTE%' or @TYPE_JO_NUM like '%DEBOUR%' or @TYPE_JO_NUM like '%AVOIR%')
BEGIN
INSERT INTO dbo.Results


Thank's

Nick

Hi Nick,
Welcome to Dsxchange!!!

You may need to say the purpose of cursor as well. Do you have more to come in the Body part of the cursor? If it is just to insert into the a table with a some condition, you can acheive this with simple quer with decode logic and where class.

A DataStage job that selects and inserts will, of itself, employ cursors. You don't have to code them. Just provide the SELECT statement in your "source" or "extract" stage, and the INSERT statement in your "target" stage.

ok thank's it's ok.

I have more 2 questions for you.

i have this :
convert(varchar,a.EC_DATE,12)+'SA'+b.D_CODEREGION+replicate('0',5-LEN(convert(varchar,a.CBMARQ)))+convert(varchar,a.CBMARQ)

What is the solution with datastage to simulate the function REPLICATE ?

i have this :
WHERE CONVERT(varchar,a.EC_DATE,112) = convert(varchar,DateAdd(D, -1,getdate()),112);

What is the solution with datastage to have the date of yesterday ?

Thank's

Nick

And REPLICATE does what, exactly? If you are padding the output field with zeroes, use the FMT function for that.

There are various date related functions in DataStage, check Date() in the online help as one example. From what I recall, it returns the system date in internal format, which means you can subtract 1 from it to get 'yesterday'. Then use OConv() to get it back into whatever 'output' or external format you need.

CONVERT you won't need most of the time, it's all automagic under the covers.

thank's for your response but i have a problem.

I can't use the funtion date() in a ODBC stage for my selection in my clause where .

Hi,
Date() is a function to get the current date in datastage. It is not used in SQL query for sql server. I think we have to use "getdate()" as for sql server. I suggest you to go through server job .pdf file to know more on functions .

incognico wrote:thank's for your response but i have a problem.

I can't use the funtion date() in a ODBC stage for my selection in my clause where .

incognico wrote:I can't use the funtion date() in a ODBC stage for my selection in my clause where.

That's true - you got the 'with DataStage' answer that I thought you were looking for. Nothing changes in your SQL query just because you are running it in a DataStage job, everything still works the same there.

For another questions pls start new stances as it reduces the efficiency of search mechanism.

thanks