|
|
|
SSC Journeyman
      
Group: General Forum Members
Last Login: Wednesday, May 09, 2012 4:31 AM
Points: 89,
Visits: 231
|
|
Hi Gurus,
I have 2 parts of a script PART ONE --- DELETE FROM [ReorderS] GO INSERT INTO [ReorderS] SELECT ITEM,NULL FROM [INV_ITEM_MAS] GO UPDATE [ReorderS] SET Quantity = [PO_Qty] FROM [PO ITEM INTERFACE] INNER JOIN [INV_ITEM_MAS] ON [PO ITEM INTERFACE].[PO_Item]= [ReorderS].ITEM
Part TWO -- 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
PART TWO SHOULD EXECUTE AFTER PART ONE IS COMPLETE AND ALSO ONLY WHEN SELECT COUNT(*) FROM [ReorderS] > 0
HOW DO I DO THIS?THANKS IN ADVANCE.
|
|
|
|
|
SSCertifiable
       
Group: General Forum Members
Last Login: Today @ 5:55 AM
Points: 5,101,
Visits: 20,197
|
|
|
|
|
|
SSC Veteran
      
Group: General Forum Members
Last Login: Monday, October 15, 2012 7:17 AM
Points: 201,
Visits: 378
|
|
There are several ways, but I would create 2-step job using your 2 scripts and put something like
if (select count(*) from FROM [ReorderS]) > 0 in the beginning of second step.
|
|
|
|
|
SSChampion
        
Group: General Forum Members
Last Login: Today @ 6:23 AM
Points: 11,607,
Visits: 27,653
|
|
also note you cannot have a GO statement inside your IF statements BEGIN /END block...
your example for part one would not be valid, it would have to be like this:
IF EXISTS(SELECT 1 FROM [ReorderS]) --what is your condition? BEGIN DELETE FROM [ReorderS]
INSERT INTO [ReorderS] SELECT ITEM,NULL FROM [INV_ITEM_MAS]
UPDATE [ReorderS] SET Quantity = [PO_Qty] FROM [PO ITEM INTERFACE] INNER JOIN [INV_ITEM_MAS] ON [PO ITEM INTERFACE].[PO_Item]= [ReorderS].ITEM END
Lowell
--There is no spoon, and there's no default ORDER BY in sql server either. Actually, Common Sense is so rare, it should be considered a Superpower. --my son
|
|
|
|
|
SSC Journeyman
      
Group: General Forum Members
Last Login: Wednesday, May 09, 2012 4:31 AM
Points: 89,
Visits: 231
|
|
Hi Gurus,
I am not able to make much progress.If I have the below
if (select count(*) from FROM [ReorderS] where Item is not null) > 0
I get error incorrect systax near > and also what will be in the ELSE part?
|
|
|
|
|
SSChampion
        
Group: General Forum Members
Last Login: Today @ 6:23 AM
Points: 11,607,
Visits: 27,653
|
|
if you need the actual count, instead of just checking for orders, try this way:
declare @TheCount int select @TheCount = count(*) FROM [ReorderS] where Item is not null if @TheCount> 0 BEGIN --do stuff END ELSE BEGIN --do other stuff END
alternatively, you can use EXISTS:
if EXISTS(SELECT * FROM [ReorderS] where Item is not null) BEGIN --do stuff END ELSE BEGIN --do other stuff END
Lowell
--There is no spoon, and there's no default ORDER BY in sql server either. Actually, Common Sense is so rare, it should be considered a Superpower. --my son
|
|
|
|
|
SSC Journeyman
      
Group: General Forum Members
Last Login: Wednesday, May 09, 2012 4:31 AM
Points: 89,
Visits: 231
|
|
hi lowell,thanks for the help.2 questions apart from SELECT * FROM [ReorderS] where Item is not null there is one more count.so it is acually SELECT * FROM [ReorderS] where Item is not null OR SELECT * FROM [AnotherTable] where Item is not null IF either of these is not null then it should execute and THERE is NO ELSE part.That is if either of these is count is not there then there is nothing to execute.so how do I modify.Thanks in advance.
|
|
|
|
|
SSC Veteran
      
Group: General Forum Members
Last Login: Monday, October 15, 2012 7:17 AM
Points: 201,
Visits: 378
|
|
mathewspsimon (9/22/2009) hi lowell,thanks for the help.2 questions apart from SELECT * FROM [ReorderS] where Item is not null there is one more count.so it is acually SELECT * FROM [ReorderS] where Item is not null OR SELECT * FROM [AnotherTable] where Item is not null IF either of these is not null then it should execute and THERE is NO ELSE part.That is if either of these is count is not there then there is nothing to execute.so how do I modify.Thanks in advance.
mathewspsimon,
"ELSE" is not a mandatory part, and we don't need this part in your case.
|
|
|
|