Database

Nostrom0 wrote:

Aha, you are right - there are some duplicate part numbers in TEST_CPSC_BOOL

UPDATE  WEB_PRODUCT_TEST_COPY  p
SET     (	
	CONTAINS_MARBLE_FLG, 	
	IS_MARBLE_FLG, 	
	CONTAINS_SMALL_BALL_FLG, 	
	IS_SMALL_BALL_FLG, 	
	SMALL_PARTS_FLG, 	
	BALLOON_FLG	
	)
 =(SELECT  	
	 CONTAINS_MARBLE_FLG, 	
	 IS_MARBLE_FLG, 	
	 CONTAINS_SMALL_BALL_FLG, 	
	 IS_SMALL_BALL_FLG, 	
	 SMALL_PARTS_FLG, 	
	 BALLOON_FLG
 FROM    TEST_CPSC_BOOL a1
 WHERE   PART_NUM = p.PART_NO
 AND     NOT EXISTS (SELECT 1
                     FROM TEST_CPSC_BOOL a2
                     WHERE a1.ROWID    < a2.ROWID
                     AND   a1.PART_NUM = a2.PART_NUM));

Nostrom0 wrote:

Wicked! That worked perfectly

Nostrom0 wrote:

If it goes wrong I'll be back tomorrow asking how to turn NULLs into 0's

Nostrom0 wrote:

Thanks again, Mika!

update table_new t1
  set t1.flag1 = ( select t2.flag1 from table_old t2 where t2.part_num = t1.part_num )
     ,t1.flag2 = ( select t2.flag2 from table_old t2 where t2.part_num = t1.part_num )
     ,t1.flag3 = ( select t2.flag3 from table_old t2 where t2.part_num = t1.part_num )
;

 WITH GetTopRecord AS (
    SELECT     TOP (1) RecordID
FROM         [16500Record]
WHERE     (Completed = 'false') AND (CheckedOut = 'false')
 )

 SELECT * FROM [16500Record]
 WHERE [16500Record].RecordID = GetTopRecord.RecordID

Sunset Towers wrote:

WITH GetTopRecord AS (
SELECT TOP (1) RecordID
FROM [16500Record]
WHERE (Completed = 'false') AND (CheckedOut = 'false')
)
SELECT * FROM [16500Record]
WHERE [16500Record].RecordID = GetTopRecord.RecordID

SELECT * 
FROM [16500Record]
WHERE [16500Record].RecordID = (
                   SELECT TOP (1) RecordID
                   FROM   [16500Record]
                   WHERE  (Completed = 'false')
                   AND (CheckedOut = 'false')
                   ORDER BY ???)

WITH GetTopRecord (id) AS (
        SELECT   TOP (1) RecordID
       	FROM     [16500Record]        
 	WHERE    (Completed = 'false') 
        AND      (CheckedOut = 'false')
        ORDER BY RecordID
)	     
SELECT * FROM [16500Record], GetTopRecord 
WHERE [16500Record].RecordID = GetTopRecord.id

Declare @RecordIDToUpdate int
Set @RecordIDToUpdate = 
 (
      SELECT     TOP (1) RecordID
      FROM         [{0}]
      WHERE     (Completed = 'false') AND (CheckedOut = 'false')
 )

 UPDATE [{0}] 
      SET CheckedOut = 'true', CheckedOutBy = '{1}', CheckedOutDateTime = {2}
      WHERE (RecordID = @RecordIDToUpdate)

Syed Mehroz Alam wrote:

Looks like you are expecting the CTE to behave like a single scalar value, however, your defined CTE actually returns a result set with one row and one column

Syed Mehroz Alam wrote:

A correct approach would be to use a variable instead

Mika Wendelius wrote:

Why is that necessary?

CREATE FUNCTION [dbo].[ReturnNumber](@Parametri VARCHAR(8000))<br />
returns  varchar(8000)<br />
as begin<br />
<br />
declare @i as int<br />
set @i=0<br />
<br />
declare @resultNumber as varchar(8000)<br />
<br />
while(@i<=len(@Parametri))<br />
begin<br />
	set @i=@i+1<br />
	<br />
	if(select isnumeric(substring(@Parametri,@i,1)))=1<br />
	begin<br />
	set @resultNumber =@resultNumber+substring(@Parametri,@i,1)	<br />
	end<br />
<br />
end<br />
<br />
return @resultNumer<br />
END

select dbo.ReturnNumber('AA432k')

select dbo.ReturnNumber(varcharcolumn) from test

.<br />
.<br />
.<br />
declare @resultNumer as varchar(8000)<br />
set @resultNumer =''<br />
.<br />
.<br />
.

CREATE FUNCTION [dbo].[fn_DelimitedSplit] 
(
@TextToSplit VARCHAR(8000), @Delimiter VARCHAR(255)
)
RETURNS @SplitKeyword TABLE (Keyword VARCHAR(8000),seq int identity)
AS
BEGIN
 DECLARE @Word VARCHAR(255)

 WHILE (CHARINDEX(@Delimiter, @TextToSplit, 1)>0)
 BEGIN
  SET @Word = SUBSTRING(@TextToSplit, 1 , CHARINDEX(@Delimiter, @TextToSplit, 1) - 1)
  SET @TextToSplit = SUBSTRING(@TextToSplit, CHARINDEX(@Delimiter, @TextToSplit, 1) + 1, LEN(@TextToSplit))
  INSERT INTO @SplitKeyword(Keyword) VALUES(@Word)
 END
 
 INSERT INTO @SplitKeyword(Keyword) VALUES(@TextToSplit)
 return
END

Declare @Code vatrchar(50)
set @Code = 'a_b_c'
select Keyword,seq from dbo.fn_DelimitedSplit(@Code,'_')

CREATE TABLE [dbo].[Table_1](
	[RecordID] [bigint] IDENTITY(1,1) NOT NULL,
	[Disposition] [nvarchar](50) NULL,
	[First] [nvarchar](50) NULL,
	[Middle] [nvarchar](50) NULL,
	[Last] [nvarchar](50) NULL,
	[Suffix] [nvarchar](5) NULL,
	[Address1] [nvarchar](100) NULL,
	[Address2] [nvarchar](100) NULL,
	[City] [nvarchar](50) NULL,
	[State] [nvarchar](2) NULL,
	[ZipCode] [nvarchar](10) NULL,
	[FileName] [nvarchar](100) NULL,
	[CheckedOut] [nvarchar](5) DEFAULT ('false') NULL,
	[CheckedOutDateTime] [datetime] NULL,
	[Completed] [nvarchar](5) DEFAULT ('false') NULL,
	[ReturnedFailed] [nvarchar](5) DEFAULT ('false') NULL,
	[ImportedRecord] [text] NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO

Nostrom0 wrote:

1. Is this problem caused by the fact that the PRODUCTS_TABLE and/or the MV_MASTER_PRODUCTS tables both now have additional fields that are not referenced in the MV/Snapshot script itself, and these changes are causing the script to "stall"?

Nostrom0 wrote:

2. Are MV scripts not meant to be edited, or is this just a problem with TOAD, or with my permissions? i.e., am I supposed to create a new MV to replace the old one instead of editing the existing script?