June 14, 2009 at 5:57 am
I have a table as follows:
CREATE TABLE [dbo].[ReorderS]([Item_Code] [nvarchar](50) NULL,
[Quantity] [int] NULL ) ON [PRIMARY]
-- Tables values are
insert into [dbo].[ReorderS]([Item_Code],[Quantity]) values ('GN00001',20)
insert into [dbo].[ReorderS]([Item_Code],[Quantity]) values ('GN00002',15)
insert into [dbo].[ReorderS]([Item_Code],[Quantity]) values ('GN0011',50)
WHEN I RUN THE BELOW SCRIPT
DECLARE @val4 varchar(20),@val1 varchar(20),@val2 varchar(50),@val3 varchar(50),@Sql varchar(8000)
SELECT @val1 ='SQLSRV',@val2=[Item_Code],@val3=[Quantity],@val4=CONVERT(VARCHAR(10), GETDATE(), 101)
FROM [ReorderS]
SET @Sql='Begin XXOH_INV_INTERFACE_PKG.insert_trx(''' + @val4 + ''', ''' + @val1 + ''' , '''+@val2 + ''','+@val3+'); End;'
print @Sql
--- I get the output as
Begin XXOH_INV_INTERFACE_PKG.insert_trx('06/14/2009', 'SQLSRV' , 'GN0011',110); End;
THE OUT PUT I WANT IS AS FOLLOWS --
Begin XXOH_INV_INTERFACE_PKG.insert_trx('06/14/2009', 'SQLSRV' , 'GN0011',50); End;
0
Begin XXOH_INV_INTERFACE_PKG.insert_trx('06/14/2009', 'SQLSRV' , 'GN00002',15); End;
1
Begin XXOH_INV_INTERFACE_PKG.insert_trx('06/14/2009', 'SQLSRV' , 'GN00001',20); End;
DEAR GURUS PLEASE SOME HELP WITH THIS.
June 14, 2009 at 6:09 am
I'm not sure if this is the most efficient code but perhaps does your work
select 'Begin XXOH_INV_INTERFACE_PKG.insert_trx(' +''''+ convert(varchar,getdate(),101) + ''''+
',' +''''+'SQLSRV' + '''' +',' + '''' + item_code +'''' +','+ cast(quantity as varchar) + ');END;'
from [dbo].[ReorderS]
OUTPUT
-------------------
Begin XXOH_INV_INTERFACE_PKG.insert_trx('06/14/2009','SQLSRV','GN00001',20);END;
Begin XXOH_INV_INTERFACE_PKG.insert_trx('06/14/2009','SQLSRV','GN00002',15);END;
Begin XXOH_INV_INTERFACE_PKG.insert_trx('06/14/2009','SQLSRV','GN0011',50);END;
June 14, 2009 at 6:29 am
Hi Sir,
That works as far as the output is concerned but I need to use dynamic sql since i am using this to execute a statement to update in the Oracle database.
June 14, 2009 at 7:33 am
using cursors might not be the best option but still using cursors in this example.
let us know if it's fine.
DECLARE @val4 varchar(20),@val1 varchar(20),@val2 varchar(50),@val3 varchar(50),@Sql varchar(8000);
set @val1='SQLSRV'
set @val4=CONVERT(VARCHAR(10), GETDATE(), 101)
Declare sample_cur cursor for
SELECT Item_Code,Quantity
FROM ReorderS
OPEN sample_cur
Fetch next from sample_cur into @val2, @val3
While (@@fetch_status-1)
BEGIN
SET @Sql='Begin XXOH_INV_INTERFACE_PKG.insert_trx(''' + @val4 + ''', ''' + @val1 + ''' , '''+@val2 + ''','+@val3+'); End;'
print @Sql
Fetch next from sample_cur into @val2, @val3
END
Close sample_cur
Deallocate sample_cur
June 14, 2009 at 8:40 am
thank you sir.it worked
June 14, 2009 at 9:09 am
🙂
June 15, 2009 at 3:01 am
hello sir,there seems to be one issue with this.the quantity column in the table ReorderS is a number.Using this way I find that value is in some case getting rounded off like 100.29 becomes 100.3 and 121.08 becomes 121.10>Is there some way to avoid this.
June 15, 2009 at 3:34 am
The table script u gave in ur first post defines Quantity as Integer.
I modified the column by running
alter table reorderS alter column Quantity decimal(10,2), you can increase the precision (here 2) or use float data type as well...
I also updated the quantity column
update reorders set quantity=quantity*6.123
and the same cursor displays records perfectly.
June 15, 2009 at 4:20 am
sir,that was a dummy table for this cae.I dont know what is the reason,is it becuase there are 1000 rows?the output of the cursor shows as I mentioned as 121.1 instead of 121.08.And this is not happening for all only an few.Is there a way to prevent this?
June 15, 2009 at 4:26 am
mathewspsimon (6/15/2009)
sir,that was a dummy table for this cae.I dont know what is the reason,is it becuase there are 1000 rows?the output of the cursor shows as I mentioned as 121.1 instead of 121.08.And this is not happening for all only an few.Is there a way to prevent this?
I just wrote that in my previous post. did u try that?
-----------------------Try this if ur base table can accept decimal numbers in quantity col---
DECLARE @val4 varchar(20),@val1 varchar(20),@val2 varchar(50), @val3 decimal(10,2), @Sql varchar(8000);
set @val1='SQLSRV'
set @val4=CONVERT(VARCHAR(10), GETDATE(), 101)
Declare sample_cur cursor for
SELECT Item_Code,Quantity
FROM ReorderS
OPEN sample_cur
Fetch next from sample_cur into @val2, @val3
While (@@fetch_status-1)
BEGIN
SET @Sql='Begin XXOH_INV_INTERFACE_PKG.insert_trx(''' + @val4 + ''', ''' + @val1 + ''' , '''+@val2 + ''','+cast(@val3 as varchar)+'); End;'
print @Sql
Fetch next from sample_cur into @val2, @val3
END
Close sample_cur
Deallocate sample_cur
June 15, 2009 at 4:54 am
sir,
that didnt work.what I did was like this
''' + str(@val2 ,21, 2)+ ''' and it worked
June 15, 2009 at 5:05 am
am glad u got it work!!
but still few things not clear...
@val2 is used to store Item_code in the cursor but u faced problem with quantity column??
June 15, 2009 at 5:19 am
sorry that was a typo mistake.thanks any for the help
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply