|
Hi Mika, thank you for the answer.
I believe you are right - I do want to do an UPDATE statement, not an INSERT. Both the source and destination tables already have populate dpart number columns, and both have columns set for teh six flags. I just want to "pour" the data from the source to the destination by part number.
I have tried:
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
WHERE PART_NUM = p.PART_NO
);
...but I got an "ORA-01427: single-row subquery returns more than one row". I'm looking this up to see what I did wrong.
WEB_PRODUCT_TEST_COPY is the destination table, TEST_CPSC_BOOL is the source table, and the six columns ending in _FLG are Number fields set to 0 or 1. TEST_CPSC_BOOL uses PART_NUM for the part numbers, and WEB_PRODUCT_TEST_COPY uses PART_NO. If it makes any difference, I am using TOAD on an old 8i database...
Oh yes, I rolled back the changes I made to the MV table and the database worked again. I believe I need to just insert the info methodically step by step - i.e. if either the Proc or the MV try to reference columns that don't exist, they won't work.
|
|
|
|
|
Nostrom0 wrote: thank you for the answer
No problem.
Nostrom0 wrote: I got an "ORA-01427: single-row subquery returns more than one row".
The reason is that the subquery that selects the data to be updated on WEB_PRODUCT_TEST_COPY returns several rows, meaning that you have several PART_NUM for a single p.PART_NO. Since the update is done for each row on a row basis, there must be one exact match in TEST_CPSC_BOOL (or no matches at all)
Nostrom0 wrote: If it makes any difference, I am using TOAD on an old 8i database
That doesn't have any effect.
Nostrom0 wrote: I rolled back the changes I made to the MV table and the database worked again. I believe I need to just insert the info methodically step by step
That sounds like a good plan.
|
|
|
|
|
Aha, you are right - there are some duplicate part numbers in TEST_CPSC_BOOL. I'll have to figure out away to find and kill them, or more likely find them in the original excel sheet the data was imported form and then re-import.
|
|
|
|
|
Nostrom0 wrote: Aha, you are right - there are some duplicate part numbers in TEST_CPSC_BOOL
If they are complete duplicates, meaning all the values are the same, you could try something like this:
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));
That should select only one of the duplicates, impossible to say which one, but if all of the values are the same, it wouldn't matter.
|
|
|
|
|
Wicked! That worked perfectly. Thank you for preventing me from spending the next three hours of my life screwing around with Conditional Formatting in Excel.
WEB_PRODUCT_TEST_COPY contains more rows/products than the list represented by TEST_CPSC_BOOL. Therefore, the products that don't need to be flagged, and were not included on the TEST_CPSC_BOOL list have a row of six NULLs instead of six 0's. I am hoping this won't make a difference - that when I complete the Proc that checks these new flag columns, my webapp will just treat the NULLs as 0's, not raise the corresponding flag, and not flip out. I suppose this is shading into more of an ASP.NET question than a PL_SQL question... I'll just try it out and see what happens. If it goes wrong I'll be back tomorrow asking how to turn NULLs into 0's...
Thanks again, Mika!
|
|
|
|
|
Nostrom0 wrote: Wicked! That worked perfectly
It's a kind of magic  When you have time, have a look at ROWID column definition on SQL documentation for Oracle.
Nostrom0 wrote: If it goes wrong I'll be back tomorrow asking how to turn NULLs into 0's
Have a look at NVL function. You can use it in the update statement (in the select portion).
Nostrom0 wrote: Thanks again, Mika!
You're welcome 
|
|
|
|
|
Off the top of my head
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 )
;
Chris Meech
I am Canadian. [heard in a local bar]
In theory there is no difference between theory and practice. In practice there is. [Yogi Berra]
|
|
|
|
|
I am attempt to lock a record and at the same time retrieve that record.
I am doing this from C# and the string is created using string.format, and while I'm certain the first part of this will work with out a problem. I need to select the record that I'm updating here and I wanted to do it in one statement.
So far I have
WITH GetOneRecord AS
(
SELECT TOP (1) RecordID
FROM [{0}]
WHERE (Completed = 'false') AND (CheckedOut = 'false')
)
UPDATE [{0}]
SET CheckedOut = 'true', CheckedOutBy = '{1}', CheckedOutDateTime = {2}
WHERE (RecordID = GetOneRecord.RecordID)
{0} is a dynamic table name.
{1} is the user who is checking out the record.
{2} is the time the record was checked out.
Can I just add the following after the UPDATE clause and get just the SELECT record
below returned. I mean is there a way to hide the fact that the UPDATE record is going to try and return integer so that only the SELECT clause below is going to return data.
SELECT *
FROM [{0}]
WHERE CheckedOutBy = '{1}'
I'm wanting it to possibly return multiple rows incase something happened and a program has to be restarted or the server goes down. Then I can have the program release the older lock in another command.
|
|
|
|
|
After doing some testing, I keep coming up with an error.
WITH GetTopRecord AS (
SELECT TOP (1) RecordID
FROM [16500Record]
WHERE (Completed = 'false') AND (CheckedOut = 'false')
)
SELECT * FROM [16500Record]
WHERE [16500Record].RecordID = GetTopRecord.RecordID
This produces
Msg 4104, Level 16, State 1, Line 1
The multi-part identifier "GetTopRecord.RecordID" could not be bound.
I've done some searching, but as of yet can not figure out what other people are doing to solve the problem.
|
|
|
|
|
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
I think there are several problems in your query:
- GetTopRecords acts like a table so you should specify it's columns, like:
WITH GetTopRecord (Column1, Column2...) AS ...
- SELECT TOP (1) RecordID unless RecordID is a column this won't select anything
- unless you order your query, what would be top 1
- I think you cannot use the cte table as a column
What you could do is something like:
SELECT *
FROM [16500Record]
WHERE [16500Record].RecordID = (
SELECT TOP (1) RecordID
FROM [16500Record]
WHERE (Completed = 'false')
AND (CheckedOut = 'false')
ORDER BY ???)
Added:
If your cte returns one row (as it is in your case since you're fetching top 1), another approach to uncorrelated subquery I suggested earlier would be joining the cte result. This would be usable in select statement, but the subquery version I wrote earlier can be used in update. However, the joined version could look like:
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
modified on Tuesday, December 16, 2008 2:29 PM
|
|
|
|
|
Let me add more to supplement Mika's response:
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. A correct approach would be to use a variable instead of CTE like this:
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)
Hope that helps.
Regards,
Syed Mehroz Alam
My Blog
My Articles
Computers are incredibly fast, accurate, and stupid; humans are incredibly slow, inaccurate and brilliant; together they are powerful beyond imagination. - Albert Einstein
modified on Wednesday, December 17, 2008 12:06 AM
|
|
|
|
|
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
That's more clearly stated than what I wrote.
Syed Mehroz Alam wrote: A correct approach would be to use a variable instead
Why is that necessary?
|
|
|
|
|
Mika Wendelius wrote: Why is that necessary?
In no way it is necessary. I just wanted to present a solution very similar to his original query so I replaced CTE with a scalar variable.
My Blog
My Articles
Computers are incredibly fast, accurate, and stupid; humans are incredibly slow, inaccurate and brilliant; together they are powerful beyond imagination. - Albert Einstein
|
|
|
|
|
Ok, I thought that you saw some problems in the non-correlated subquery I suggested and used variable for this reason.
|
|
|
|
|
hi all;
I want to extract only numeric values from a column which have alphanumeric values like ABC12342M , AE2213Jk.
Thanks
Snehasish
|
|
|
|
|
You should create function which loops every character in string and check if is number or not.
I Love T-SQL
"Don't torture yourself,let the life to do it for you."
If my post helps you kindly save my time by voting my post.
|
|
|
|
|
hi;
Many thanks for reply.It must work.But It will be lengthy process as per my application.So if you give any other soln,that will be helpful.
Thanks
snehasish
|
|
|
|
|
Well here is function which return only numbers from given string parameter.
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
I Love T-SQL
"Don't torture yourself,let the life to do it for you."
If my post helps you kindly save my time by voting my post.
|
|
|
|
|
hi;
many thanks  .I can not find any wrong in your function "ReturnNumber". but when i input a varchar, for example 'AA432k', it returns a null value.
This is my query:
select dbo.ReturnNumber('AA432k')
or
select dbo.ReturnNumber(varcharcolumn) from test
what is my wrong?
snehasish
|
|
|
|
|
After declaring of variable @resultNumber then initiliaze it with empty string.
.<br />
.<br />
.<br />
declare @resultNumer as varchar(8000)<br />
set @resultNumer =''<br />
.<br />
.<br />
.
I Love T-SQL
"Don't torture yourself,let the life to do it for you."
If my post helps you kindly save my time by voting my post.
|
|
|
|
|
Hi All,
I want to split the delimiters in sql directly...
In id the values stored as 1~2
i want to split tat 1 and 2 and use as
select sid in (id)(id CONTAINS ~ SYMBOLS)
|
|
|
|
|
There is no easy way to do this as SQL doesn't have a split function built in, so you have to roll your own. Here is one way.
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
You can then use it like so:
Declare @Code vatrchar(50)
set @Code = 'a_b_c'
select Keyword,seq from dbo.fn_DelimitedSplit(@Code,'_')
Hope this helps
Bob
Ashfield Consultants Ltd
|
|
|
|
|
Still being fairly new to SQL I let SSMS write alot of the script for me when I can.
The problems is I have not had a chance to dive into the create table stuff and other aspects of SQL, but I need to get the following code down into a single Create Table Command rather than the create then alter table.
If this isn't possible, then I'll run the extra commands.
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) NULL,
[CheckedOutDateTime] [datetime] NULL,
[Completed] [nvarchar](5) NULL,
[ReturnedFailed] [nvarchar](5) NULL,
[ImportedRecord] [text] NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
ALTER TABLE [dbo].[Table_1] ADD CONSTRAINT [DF_Table_1_CheckedOut] DEFAULT ('false') FOR [CheckedOut]
GO
ALTER TABLE [dbo].[Table_1] ADD CONSTRAINT [DF_Table_1_Completed] DEFAULT ('false') FOR [Completed]
GO
ALTER TABLE [dbo].[Table_1] ADD CONSTRAINT [DF_Table_1_ReturnedFailed] DEFAULT ('false') FOR [ReturnedFailed]
|
|
|
|
|
Try to add the defaults to corresponding columns, like:
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
|
|
|
|
|
Thank I didn't know what to keep and what to throw away. Thanks.
|
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
