|
By assuming your field is of varchar...
case when
(TABLE_A.Field_1 + TABLE_A.Field_2) = (TABLE_B.Field_1 + TABLE_B.Field_2)
then 'Match'
else 'Unmatch' end
i guess this will help you .
Reasons are not Important but Results are Important.
http://www.sql4professional.blogspot.com
Swati Tripathi
|
|
|
|
|
hi again
I found following query
SELECT T1.C1,T1.C2,
CASE WHEN (SELECT COUNT(*) FROM TABLE_2 T2 WHERE T1.C1 = T2.C1 AND T1.C2 = T2.C2 ) = 0
THEN 'Unmatch' ELSE 'Match'
END
AS Result
FROM TABLE_1 T1
Still
i am not able to retrieve fields from Table_2 which are not present in Table_1
The Stifler
--
Bugs can neither be created nor be removed from software by a developer. They can only be converted from one form to another. The total number of bugs in the software always remain constant.
|
|
|
|
|
HI pls try below code
Select
C1,
C2,
C3,
C4,
case when (C1 + C2) = (C3 + C4) then 'Match'
else 'Unmatch' end as Result
From
(
Select
Row,
Max(C1) as C1,
Max(C2) as C2,
Max(C3) as C3 ,
Max(C4) as C4
From
(
Select
ROW_NUMBER()
OVER (ORDER BY TABLE_A.Field_1) AS Row,
TABLE_A.Field_1 as C1
,TABLE_A.Field_2 as C2
,'0' as C3
,'0' as C4
From
TABLE_A
Union ALL
Select
ROW_NUMBER()
OVER (ORDER BY TABLE_B.Field_1) AS Row,
'0' as C1
,'0' as C2
,TABLE_B.Field_1 as C3
,TABLE_B.Field_2 as C4
From
TABLE_B
)
as OuterTable
group by Row
) as FinalTable
n yes let me know which sql version u r using because in sql 2008 we have
merge statement too...
i hope this will help you ..
Reasons are not Important but Results are Important.
http://www.sql4professional.blogspot.com
Swati Tripathi
|
|
|
|
|
You have 2 options here. Assume one of the two tables is the "master" and left join the other:
select t1.field1,t1.field2,
t2.field1,t2.field2,
CASE
WHEN t2.field1 IS NOT NULL THEN 'Match'
ELSE 'Unmatch'
END
FROM Table1 t1
LEFT JOIN table2 t2
ON t1.field1=t2.field1 AND t1.field2=t2.field2
or, cross join all of t1 against t2 to find matches.
select t1.field1,t1.field2,
t2.field1,t2.field2,
CASE
WHEN t1.field1=t2.field1 AND t1.field2=t2.field2 THEN 'Match'
ELSE 'Unmatch'
END
FROM Table1 t1
CROSS JOIN table2 t2
Im sure one of those 2 approaches will give you the result you're after.
|
|
|
|
|
Hi,
What is Datawarehousing ..?
Thanks
Richa
|
|
|
|
|
|
And if that fails: http://tinyurl.com/2wbzbr3[^]
CQ de W5ALT
Walt Fair, Jr., P. E.
Comport Computing
Specializing in Technical Engineering Software
|
|
|
|
|
Hello Everybody,
I need a help.My question is---"How can we call a SLEEP Function in VBSCRIPT"
Please do response me..I, shall be, thankful to u all..
Thanks
Richa
|
|
|
|
|
WScript.Sleep(2000)
Why did you post this in SQL forum?????
Die Energie der Welt ist konstant. Die Entropie der Welt strebt einem Maximum zu.
|
|
|
|
|
I have a TexBox and it's value stored in a database.
but whwe the entered value like ' , ' or ' ; '
there is some error occurred ,so how I can solve this problem.
|
|
|
|
|
that should not happen.
assuming your DB field is nvarchar, it should be able to hold whatever text you enter.
what is your database?
are you using SQLParameter?
maybe best show us your relevant code.
Luc Pattyn [Forum Guidelines] [Why QA sucks] [My Articles]
I only read formatted code with indentation, so please use PRE tags for code snippets.
I'm not participating in frackin' Q&A, so if you want my opinion, ask away in a real forum (or on my profile page).
|
|
|
|
|
OK,
protected void Button1_Click(object sender, EventArgs e)
{
SqlConnection con = new SqlConnection();
con.ConnectionString = ConfigurationManager.ConnectionStrings["AdNetConnectionString"].ToString();
Session["CampaignName"]= TexBox1.Text.ToString();
string sql;
sql = "insert into AdCampaign (CampaignName)values( ' " + Session["CampaignName"] + " ');
SqlCommand cmd = new SqlCommand(sql, con);
con.Open();
cmd.ExecuteNonQuery();
con.Close();
}
|
|
|
|
|
I still don't know what your database is.
I see you are not using SQLParameter; it would almost certainly solve your problem. Read up on it, and use it; it will keep a lot of problems away, SQL injection vulnerability being one of them.
I would not move the data from the textbox through the session array into the SQL command; no need for such a detour. No need for ToString() either, the content of a TextBox is always a string.
Luc Pattyn [Forum Guidelines] [Why QA sucks] [My Articles]
I only read formatted code with indentation, so please use PRE tags for code snippets.
I'm not participating in frackin' Q&A, so if you want my opinion, ask away in a real forum (or on my profile page).
|
|
|
|
|
How to use a parameterized query, using your code as an example:
sql = "insert into AdCampaign (CampaignName) values(@myParameter)";
SqlCommand cmd = new SqlCommand(sql, con);
cmd.Parameters.AddWithValue("myParameter", Session["CampaignName"]);
I.e. in your sql statement, no values are shown, but only the parameters; with SQL Server, parameter names use to start with an @. After having created the command object, you add all of your parameters with their respective values.
|
|
|
|
|
ok ,
my code is:
sql_Store_Sessions = "insert into AdCampaign (AccountID,AdType,SiteCategory,Language,Gender,Age,Keyword,MaxBudget,DailyBudget,CampaignName,AdHeadline,AdText,DisplayUrl,TargetUrl,BannerImg,ImgName,Date,Status)values(" + Session["SelectIdFromTableAccount"] + ",'" + Session["adType"] + "','" + Session["Category"] + "','" + Session["Language"] + "','" + Session["Gender"] + "','" + Session["Age"] + "',@AdvKeyword," + Session["MaxBudget"] + "," + Session["Daily_Budget"] + ",@CampaignName,@AdHeadLine,@AdText,@DisplayUrl,@DestinationUrl,@Banner_Img,@Img_Name,'" + DateTime.Now + "','Paused')";
SqlCommand cmd_Store_Sessions = new SqlCommand(sql_Store_Sessions, con);
cmd_Store_Sessions.Parameters.AddWithValue("CampaignName", Session["CampaignName"]);
cmd_Store_Sessions.Parameters.AddWithValue("AdHeadLine", Session["AdHeadLine"]);
cmd_Store_Sessions.Parameters.AddWithValue("AdText", Session["AdText"]);
cmd_Store_Sessions.Parameters.AddWithValue("DisplayUrl", Session["DisplayUrl"]);
cmd_Store_Sessions.Parameters.AddWithValue("DestinationUrl", Session["DestinationUrl"]);
cmd_Store_Sessions.Parameters.AddWithValue("Banner_Img", Session["Banner_Img"]);
cmd_Store_Sessions.Parameters.AddWithValue("Img_Name", Session["Img_Name"]);
con.Open();
cmd_Store_Sessions.ExecuteNonQuery();
con.Close();
but there is an error :
The parameterized query '(@AdvKeyword nvarchar(2),@CampaignName nvarchar(2),@AdHeadLine n' expects the parameter '@Banner_Img', which was not supplied
|
|
|
|
|
I think that happens when the value is null. C# null and database null are different. If you want to insert a null into the database you need to use DBNull.Value.
|
|
|
|
|
isn't that yet another reason for using SQLParameter?
Luc Pattyn [Forum Guidelines] [Why QA sucks] [My Articles]
I only read formatted code with indentation, so please use PRE tags for code snippets.
I'm not participating in frackin' Q&A, so if you want my opinion, ask away in a real forum (or on my profile page).
|
|
|
|
|
|
I always prefer of using storedProcedure with I/O parama instant of hard coded sql statement.
Thanks
Md. Marufuzzaman
I will not say I have failed 1000 times; I will say that I have discovered 1000 ways that can cause failure – Thomas Edison.
|
|
|
|
|
SQL Server 2005
Database sizes 25-30Gb
There are only two sites running the software at the moment.
I've recently joined a very small new company, and taken on several existing software products. I'm very concerned about the sizes of their customers databases. I've checked that the log file has not grown excessivly which was not the case, after that i have looked through all the tables in the database and found that 95% of the storage is in two tables. Checking the storage allocation for both those tables i have found that each has around 25% of the sorage being used for data, and 75% for indexing. This seemed very wrong to me, can any one recomend a way of re indexing the tables so that the storage being used is lower. If its just a case of removing all the indexes and starting again is there any thing i should be aware of.
Thanks in advance for any replies. If more specific information is needed please let me know.
|
|
|
|
|
Sometimes the reality is indexing needs more space than the data itself, so 75% for indexing may not be out of line. You can simply backup the database and restore it (to a different location) so the database table and indexes are re-created. That way you can find out if the indexing really needs that much space or the original database needs compacting. Chances are if the indexing part of the original database needs compacting, the data part also needs compacting. The ratio may change but indexing still needs more space than the data itself.
|
|
|
|
|
this database has been freshly restored from a backup so i guess from what you have said that would rule out the compacting being required.
To give some more of an idea of the issue the tables that are being affected are used to store financial transactions, one table contains 7.7 million rows, the storage cost is 3.9gb for the data, and 16.4gb for the indexing. This amount of data is resulting in very slow processing of even simple select queries, and my thoughts are that to resolve the problem some kind of archiving needs to be implemented.
This is probably a dumb question but do the data types being indexed have a direct affect on the storage space required for the indexing. i.e. would indexing a column of the type guid be significantly more storage intensive than an int column.
Sorry if the question is overly simple, i've worked with sql databases in the past and never seen storage requirements even close to this.
|
|
|
|
|
The data types being indexed does not have impact on the size used to store the indexes. What you described indicates even more indexing is needed for the database table since some simple "select" queries are very slow. You may need to remove some indexes not being used by the program and add some new indexes based on the queries frequently conducted. When the needed index does not exist, "select" queries on such large tables will be very slow. I was once amazed by how much performance gains was brought by adding proper indexes when I was working on a large database table (similar to your case). So it may worth a try to get a close look at the database to find out what you need for it.
|
|
|
|
|
|
thank you, i'll have a thorough read through this asap.
|
|
|
|
|