|
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.
|
|
|
|
|
No problem
|
|
|
|
|
Hello,
I am administering an ASP.NET ecommerce website that runs on top of an old legacy Oracle 8i database. I administer the database using TOAD. I have recently attempted to add a new set of flags to the master product table in order to make a set of labels appear for certain products.
The 8i database pulls information from its own store of product info, and combines it with pricing information drawn from an outside Oracle system. The final "master product" table is the result of a Materialized View (MV) or Snapshot, and this table is what the website looks at using a stored procedure when it populates a page template with product info. The process works like so:
1. PRODUCTS_TABLE plus MASTER_PRICE_TABLE are "pulled from" by the
2. Materialized View/Snapshot Script, which combines teh "downstream" data into a final table called
3. MV_MASTER_PRODUCTS (table resulting from MV)
4. MV_MASTER_PRODUCTS is called by a Proc, and this info is used to create a dynamic webpage.
Recently I added several new fields to the tables "downstream" and "upstream" of the MV/snapshot that builds the final master product table. I discovered, however, that my copy of TOAD doesn't want to let me edit the MV script itself in order to add commands to pull data up from these new fields. Although the lower levels of the database are still OK, the final MV_MASTER_PRODUCTS table (to which I added fields) is now empty, and when I attempt to refresh the MV/snapshot to populate this table, nothing happens (no error message, just no activity). Because this top-level tabel is now empty, my website can't pull data from it.
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"?
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?
3. One final thing: I made a duplicate of the final MV_MASTER_PRODUCTS table which is still populated with the last data refresh. This is, I believe, just a regular table with no connection to a snapshot, so I don't expect it would have anything to do with MV_MASTER_PRODUCTS failing to refresh/repopulate, but I thought I should mention it just in case.
Thank you for any advice!
|
|
|
|
|
Basically materialized view is just a view (stored SQL statement), but it's been materialized to a table (predefined or not).
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"?
I don't think so. A view can contain any portion of the original table(s).
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?
I'm not sure about TOAD, but if you modified the script (one way or another) did you execute it. I don't believe that TOAD executes it automatically. However, I haven't used TOAD very much.
What happens if you describe the materialized view (desc MV_MASTER_PRODUCTS), do you see the columns added? If not, it could be an indication that the view hasn't been changed.
Both altering and creating a new one are supported. If it would be a privilege issue you would get a clear message that you don't have the privileges.
Try taking the script from TOAD and then modify and execute it (using TOAD's SQL editor or SQL*Plus). Perhaps you would get a message describing what's wrong.
|
|
|
|
|
Hi all,
I need to relocate the datbase to another drive on my server and i need to make sure that all permissions and rules, diagrams are moved also to the new location. I can relocate either by copying and pasting mdf and ldf to new location and then attach or by backup and restore. I need to keep the same database name.
Do these above 2 ways guarantee me relocating with keeping all my permissions?
thanks
|
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
