create a cursor with datastage ?
Moderators: chulett, rschirm, roy
create a cursor with datastage ?
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
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.
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.
Impossible doesn't mean 'it is not possible' actually means... 'NOBODY HAS DONE IT SO FAR'
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
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.
IBM Software Services Group
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
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![Razz :P](./images/smilies/icon_razz.gif)
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
![Razz :P](./images/smilies/icon_razz.gif)
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.![Wink :wink:](./images/smilies/icon_wink.gif)
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.
![Wink :wink:](./images/smilies/icon_wink.gif)
-craig
"You can never have too many knives" -- Logan Nine Fingers
"You can never have too many knives" -- Logan Nine Fingers
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 .
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 .
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.incognico wrote:I can't use the funtion date() in a ODBC stage for my selection in my clause where.
-craig
"You can never have too many knives" -- Logan Nine Fingers
"You can never have too many knives" -- Logan Nine Fingers
-
- Participant
- Posts: 108
- Joined: Wed Jul 28, 2004 7:15 am