Replacing Join Operation In XML
Feb 2, 2007
is it possible to replace join type ( for eg. nested loop with hash
join and so on) in xml plan...
we will fst take plan in xml format ( show xml plan ) and then we will
replace join with other one .. and then execute this plane .. to see
the effect....
for this we need to understand the way join information get stored in
xml ....and then replace...
for any extra info we can put garbage .. which will be filled by
actual value while execution...
so my question is : is it possible...( i think it is very much
possible)
and if yes then guide me... from where i can get these join
format .. so that i can replace...
or just running query on some dataset for both join type and thn
comparing the way the get stored .. is sufficient to convert...
thankx
View 1 Replies
ADVERTISEMENT
Jan 18, 2008
Dear All,
is there anyway to replace right outer join? coz,i'm trying to create an index on indexed view. i'm getting error, that too because of outer join. how can i replace this?
Vinod
Even you learn 1%, Learn it with 100% confidence.
View 7 Replies
View Related
Sep 6, 2005
Hi All,I'm confused by how to replace a SELECT statement in a SQL statementwith a specific value. The table I'm working on is a list of words (acolumn called "word") with an index int pointing to the sentence theycome from (a column called "regret"). I also have a table of stop words(called "GenericStopWords") that contains the words I do not want toconsider. That table has a single column called "word".I started off using a SQL function (called "GETALLWORDS") whichreturned the words of a string. This is how I used it:INSERT INTO WordsSELECT wrds.WORD, 1 FROMGETALLWORDS('I could be bounded in a nutshell and count myself a kingof infinite space', default) AS wrdsLEFT OUTER JOINGenericStopWordsON wrds.WORD = GenericStopWords.wordWHERE GenericStopWords.word IS NULLThis statement inserts every word in the sentence that is not a stopword. So, for example, ('bounded', 1) is added but ('could', 1) is notadded.I've now decided to move the function that breaks a string into wordsout of the SQL layer of my application and into the JavaScript whichruns under ASP on the web server. So now I want to call somethinganalogous to the above SQL statement, but word by word. What I'd likeis something like:INSERT INTO WordsSELECT 'bounded', 1 FROM ???LEFT OUTER JOINGenericStopWordsON 'bounded' = GenericStopWords.wordWHERE GenericStopWords.word IS NULLDoes that make sense? I want a SQL statement that will insert the tuple('bounded', 1) into the table Words if (and only if) 'bounded' does notappear in the table GenericStopWords. It's easy to say procedurally, Icannot see how to write it in a relational style in SQL.Thanks in advance for any help you can give.Cheers,Tim.
View 3 Replies
View Related
Apr 10, 2008
Hi all,
I have run into a problem that I am stuck on. I have 3 tables lets call them a,b, and c. What I want to do is left outer join a with b on a common value, then replace any null values in this result with a default value, and then left outer join table c on the previously joined table (on the columns that I just replaced certain values on).
please help!
Thanks
View 4 Replies
View Related
May 29, 2008
E.g.
SELECT *
FROM TestOne T1
WHERE
T1.ID1 IN (SELECT ID1 FROM TestTwo)
GO
SELECT T1.*
FROM TestOne T1
INNER JOIN TestTwo T2 ON T1.ID1 = T2.ID1
Which one among them is better?
I have seen contradictory explanations on the web and hence pasting a query here...
Personally I had an experience where IN I felt was in the end costly compared to INNER JOIN... was I correct in such a deduction?
Thanks,
Pratap.
View 6 Replies
View Related
Nov 20, 2013
I'm using SQL Server 2012 Analysis services in Tabular mode and connected to Oracle Database and while importing, I'm getting below error after importing some rows.
OLE DB or ODBC error: Accessor is not a parameter accessor.. The current operation was cancelled because another operation in the transaction failed.
View 1 Replies
View Related
Apr 30, 2008
Hello
Can any one tell me the difference between Cross Join, inner join and outer join in laymans language
by just taking examples of two tables such as Customers and Customer Addresses
Thank You
View 1 Replies
View Related
Aug 28, 2000
Hi, I'm trying to conver an Access database to SQL 7, but I can't find a easy way to replicate the aggregate function FIRST in SQL, can anyone give me some advise on how to "fake" the FIRST agg function in SQL7? Thanks
View 1 Replies
View Related
Aug 13, 2004
I am incorporating a perl script loading data into my SQL Server. If I receive a message with a single backslash I know to replace it with a double backslash \. But what if it is a " double quote what do I need to do to get it to appear as is?
Thanks
View 2 Replies
View Related
Nov 20, 2013
I want to replace the value in a column with the content from listbox.
I have wrote the code like,
Dim i as Integer
dim sql as string
For i = 0 to Customerslist1.ListCount-1
sql = "UPDATE master (gstl) VALUES(" +chrb(34)+Customerslist1.Cell(i,8)+chrb(34)+")"
app.Gudangstock.SQLExecute(sql)
app.Gudangstock.commit
next
msgbox"insert ok"
UpdateCustomerList1
but no error found and the sql command is not executing.
View 5 Replies
View Related
Aug 31, 2005
Nevil Mascarenhas writes "Hi SQL team,
I am just a beginer in SQL
Here is sample output of a SQL query
Alarm No Site Name Startdate Starttime
7767 ABC 20-08-05 00:00
7765 XYZ 20-08-05 00:00
7762 ASD
5453 QWE 22-08-05 01:00
In this above example, I would like to replace blank fields in Startdate and Starttime coloumn with XXX
Output required is
Alarm No Site Name Startdate Starttime
7767 ABC 20-08-05 00:00
7765 XYZ 20-08-05 00:00
7762 ASD XXX XXX
5453 QWE 22-08-05 01:00
Can you please guide me."
View 1 Replies
View Related
Nov 18, 2005
Hi i have 2 cols
col1 col2
A1 21
A2 22
A3 21
A4 23
I want to create a report such that i should see all values of col1 but in col2 i want to show only value of 21 and the remaining should be zero.
So basically all the other values except 21 should be zero in the report but should not be updated in the database.
So the report should look like
col1 col2
A1 21
A2 0
A3 21
A4 0
can you please tell me how to do that
Thanks
vic
Vicky
View 10 Replies
View Related
Jul 20, 2005
Hi,Is it possible to do from one script? We have a set of user's tables like"tbl%". We can get this list very easy using this script:SELECT name FROM sysobjects WHERE xtype = 'U' AND name LIKE 'tbl%' ORDER BYname;We need to change some column names if these names are in a special listthat we have. What can we do? Use FOR EACH ROW? Or what?So, I need to get a column list for each table and check if every columnname is equal to one of the names from the list and then if YES replace itby something or add some symbol to this name. Terrible or possible?Regards,Dmitri
View 4 Replies
View Related
Jul 30, 2007
Hi
I have a table with column type as ntext. I need to modify the column value. I wanted to replace a given characterstring with another one in this column. Any assistance on this is highly appreciated.
Thanks!
Santhosh
View 1 Replies
View Related
Jan 15, 2008
Hi all,
We are trying to migrate a SQL Server 2005 database/schema onto SQL CE. The original SQL Server 2005 schema uses views. And VIEWs are not supported on SQL CE. What can we use as an alternative?
Here are the option I can think of:
Use Tables instead of views : We are currently using this approach. But the disadvantage is that data remains on the DB. We need to clean and reuse the table everytimeCan we use a DataSet or DataTable instead? Any sample code available?
Can we replace them by some in-memory data structure?
View 3 Replies
View Related
Nov 24, 2006
Hi all
I need to remove all the ' in a table and i am having some problems with the script, any help would be great
Thanks
Richard
View 1 Replies
View Related
Jan 21, 2008
Hi,
I would like to replace all instances of a code that starts with 'GEM' with the word 'Perfect'', and all instances of the code that starts with 'GOLD' replaced with 'Imperfect'.
For example, if I have a table named STONES, and a field within the table STONES.Code contained codes such as 'GEMPART4000', 'GEMPART5000', 'GOLDSIDE2300' and 'GOLDSIDE3000', I want to return the first two codes as the words 'Perfect' for each and the last two codes with the word 'Imperfect' for each.
Similarly, how would it be written so I could select a middle part of the code to use as the trigger to replace the code with something different, e.g. if I wanted to use the 'PART' from the first two codes?
I have discovered that the REPLACE and CASE functions don't work with % (so I am looking for any code that starts with what I stipulate but can end with anything (or using it on either side of the middle part of the code)).
Any help would really be appreciated please.
View 5 Replies
View Related
Dec 3, 2007
Hi Guys,
I am faced with a problem that is giving me headaches. I have t1, t2 and t3. I t1 I have students that have a reference to t2 which are the schools they belong to. The problem arises when I see that there is redundancy in t2. There more records for the same schools. This was posible in giving the same school (postcode and name make it the same school) a different ID. In t3 I removed these redundant schools by using fuzzy grouping.
My problem is I want to ensure that the students are put using the schools from t3 instead of t2. So what I need to do is is to replace the redundant id from t2 with the correct id from t3.
Please help. Thans in advance
View 3 Replies
View Related
Jul 20, 2007
I am trying to clean up an ugly query that's based on trying to find items that exist in one table but not the other.
My tables are like this:
ITEMSitemID,itemName,itemDescriptionetc...
ORDERITEMSOrderLineID,OrderID,itemID
ORDERSOrderID,OrderCompleted
Currently my query looks something like this:
Select Items.* from ITEMS where not exists (select 1 from ORDERITEMS inner join ORDERS on ORDERITEMS.OrderID = ORDERS.OrderID where ORDERITEMS.itemID=ITEMS.itemID and (ORDERS.OrderCompleted=1))
So this query is looking for ITEMS what don't have a corresponding entry in the ORDERITEMS table. As I understand it this is pretty inefficient as it is going to be executing the sub query in the Not Exists statement for each entry in the ITEMS table. Is the preferred method to do something along the lines of somehow making the sub query into a derived table and doing a left or right join?
Thanks for reading!
Ryan
View 7 Replies
View Related
Aug 2, 2007
Hi, below is the query that i use.update user set user_description=' "+ userDesc +" ' where user_id = 1;userDesc is the value taken from a textbox and is supposed to be used with the update query.userDesc = "kad'nsad'kasnd'nak";The quotes in userDesc is affecting the update. Is there anyway to replace quotes with ' using stringbuilder?
View 1 Replies
View Related
Apr 1, 2004
Hi, I have the following query.
sql = "select firstname AS Expr1, lastname AS Expr2, status AS Expr3 from person order by lastname"
Status is either 0,1,2 or 3
How can I use the query to create "a temp Alias" for the query, so that there is a "temp Alias" AS Expr4, AS Expr5, AS Expr6 and AS Expr7.
So if status = 0 then Expr4 = "true" else false
So if status = 1 then Expr5 = "true" else false
So if status = 2 then Expr6 = "true" else false
So if status = 3 then Expr7 = "true" else false
So when the reader reads Expr4 its either true or false.
Is this possible i a query?
View 1 Replies
View Related
Dec 24, 2005
In an *existing* database, how would you remove the City column in the
Employees table, and put in the CityID key column from the Cities table
?
View 2 Replies
View Related
Jan 10, 2002
Hi,
how can I remove newline characters and tabs from a char field in sql.
We are directing the results from a query to an excel and having the newlines in one of the columns is messing up the whole output.
thanks
Zoey
View 1 Replies
View Related
Mar 1, 2001
I am thrawn at the deep end
I have been given the task of moving certain tables (that have PK, FK Constraints) from an existing Database (A) to form another Database (B) but making sure all the references to other tables in the original DB (A) are not affected. I was talled that triggers can solve this but I am not sure where to start. The Question is what I am looking for to replace, and how ??
Please help !!!!!!
Saad
View 3 Replies
View Related
Sep 4, 2005
Need to update data from one table with data from another table.
I'm very, very new to MS SQL Server
I have the following two tables.
First table
tblEmp
Field - empno
Second table
tbltemp
Field - oldempno
Field - newempno
tblemp.empno = tbltemp.oldemno
I need to replace the data in tblEmp.empno with tbltemp.newempno where tblemp.empno = tbltemp.oldempo.
Thank you.
View 1 Replies
View Related
Jun 26, 2001
Hi, and tanks for taking time to read this question. I am still a novice at SQL (Server 2000)programming.
Is there a way to replace the contents of a column of a table derivied from SELECT and WHERE statements?
For example,
SELECT * FROM table1
WHERE column1 = 'text'
and replace 'text' with say, 'newtext'
Any help would be appreciated!
Regards,
Chandran
View 5 Replies
View Related
Dec 1, 2004
I need to change the first digit (0) in a number, like 05678494 and replace it with 0046 so it would look like 00465678494 when it is done.
I have tried to use the REPLACE function without any success.
Can anyone help me?
View 4 Replies
View Related
Sep 18, 2007
I have a instance with many databases in it.
due to company/product name change,
I want to search for a string "xyz" in
database name,
table name,
column name,
stored procedure name
content of all stored procedures
and replace all of them with "abc" without affecting the databases an application.
Can u please help me with step by step guidance?
muralidaran r
View 14 Replies
View Related
Dec 2, 2013
I have a SQL table with 5000 rows in it. Some of the other SQL columns have different values but if I wanted to swap say 500 of the rows would it be something like
UPDATE Coupins
SET Name='test1,test2,test3'
WHERE Name='test4,test5,test6';
I need basically to swap the Name value of these without affecting any other values.
View 4 Replies
View Related
Feb 11, 2014
I've got a sproc that we are migrating from SS2K that uses the reserved keyword 'Pivot'. Best way to replace it. I think it is renameable until the select from TempDB (which is mysterious as I can't see a table in tempDB by that name!!) as below...
CREATE PROCEDURE [dbo].[sp_CreateXtab]
@select varchar(8000),
@sumfunc varchar(100),
@pivot varchar(100),
@table varchar(100),
@SQLOutput varchar(8000) OUTPUT
[code]....
View 3 Replies
View Related
Aug 23, 2005
Hi friends
I have a database where i need to replace with a new version of the same one.
Can anyone suggest the best possible and reliable way of doing that
regards
Vic
Vicky
View 3 Replies
View Related
May 17, 2006
Hello. We have a list of 1000+ sku numbers. 90% of them start with u. I need to replace the u with T.
Concerns:
It has to only replace the first letter, if there are other "u"s in the sku, they need to remain.
Not all skus start with u, those that don't should remain the same.
I have searched and searched because I'm sure someone has requested the exact same thing but I can't find anything that is identical.
I have this so far but I know its not correct:
set sku = replace(left(sku,1),'u',right(rtrim(sku),1)+
'T')
Can someone help lead me down the right path?
Thanks in advance!!!
View 4 Replies
View Related
Dec 17, 2007
I'm the assistant to the assistants assistant and have no SQL base at all but have to change dates in our SQL database. I've got the script for how to do an update/replace for non date fields and have been successful at that. Unfortunately when I try to use the script to change date fields it does not work. It goes through the process and acts like it is working (even asks me if I want to update the records) but when I look in the database the dates have not changed. Below is the script that I am using, any help you can give me will help me keep my job (and pay for the beer I'm sure to need after work today) Thanks in advance for your help. I am trying to change the date '30/08/2007' to '28/08/2008'.
My Script:
update Main_table set valuedate=replace(valuedate,'30/08/2007','28/08/2008')
where country='FR'
and valuedate='30/08/2007'
View 4 Replies
View Related
