|
I didn't understand
|
|
|
|
|
in Front End Like C#
Sqlcommand cmd=new Sqlcommand()
SqlTransaction myTrans;
myTrans = myConnection.BeginTransaction();
cmd.Transaction = myTrans;
try
{
myTrans.Commit();
}
catch(Exeception ex)
{
myTrans.Rollback();
}
|
|
|
|
|
I am having the below secnario.
Table 1
Col1 Col2 Col3
10 20 30
Table 2
Code Name
1 Col1
2 Col2
3 Col3
Expected Output:
1 Col1 10
2 Col2 20
3 Col3 30
How to write the SQL script (SQL server 2005) for this? Thanks in advance.
Jey
|
|
|
|
|
Are you sure about the values in Table 1 ?
From your description, there is one row with three columns where col1 contains a value of 10, col2 contains a value of 20 and col3 contains a value of 30.
This is not a typical relational database method for storing data. You may want to re-check your homework assignment.
BTW: Most people won't help you with your homework.
|
|
|
|
|
Hi,
Thanks for your reply.
I do aware of it. But my requirement is similar like this.
I needs to get all the column names from the Table 1 & lookup on Table 2 and get the Code of that respective Column name.
Let me explain: In table 1 there is a column called 'LoadFactor' & its value is 80
LoadFactor
80
Table 2
Code Name
1 LoadFactor
Table 3
Code LSL USL
1 50 100
My Required Output:
Code Name 'Actual Value' LSL USL
1 LoadFactor 80 50 100
Now. have u got it? This is a simple example that explains my requirement. I do have 500 similar columns with few input tables.
Jey
|
|
|
|
|
I think you want to access system tables that will allow you to get the names of columns for a given table.
For example, the following will give you a listing of all of the columns for "myTable1". You could then expand this query to join to your other tables.
Use myDatabase
GO
select so.name,sc.name
from sysobjects so, syscolumns sc
where so.xtype = 'U'
and so.name = 'myTable1'
and so.id = sc.id
Tell me if this helps you.
|
|
|
|
|
Thank you Mujica. Definately It helps me... Thanks a lot.
Please find my last comment for the complete solution.
Jey
|
|
|
|
|
Your table data and structure doesn't seems to me good enought logical.
What if Table1 have different values? What if Table1 contain these values :
Col1 Col2 Col3
10 20 30
100 200 300
1000 2000 3000
10000 20000 30000
and so on.
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, Thanks for your valuable reply.
Please assume that I am always select one row from Table 1.
Please refer my previous thread for the detailed requirement.
Jey
|
|
|
|
|
SQL Server 2005 introduced an UNPIVOT operator that will do what you're looking for.
SELECT T2.CODE, T1.COLS,T1.INDICOLVALS
FROM
(
SELECT INDICOLVALS,COLS
FROM
(SELECT Col1, Col2, Col3 FROM Table1) P
UNPIVOT
(INDICOLVALS FOR COLS IN (Col1, Col2, Col3)) AS U
) AS T1
INNER JOIN TABLE2 T2
ON T1.INDICOLVALS = 10*T2.CODE
Vote me please
Niladri Biswas
|
|
|
|
|
Great Work I am really thankful for your effort spent on this & guidance
I have used this logic for my case. BUT, the JOIN is the highlighting one Sorry, this is for fun.
ON T1.INDICOLVALS = 10*T2.CODE
declare @cols nvarchar(2000)
declare @sql nvarchar(4000)
set @cols='Col1, Col2, Col3'
set @query='SELECT T2.CODE,
T1.COLS,
T1.INDICOLVALS
FROM
(
SELECT INDICOLVALS,COLS FROM
(SELECT Col1, Col2, Col3 FROM Table1) P
UNPIVOT
(INDICOLVALS FOR COLS IN ('+ @cols +')
) AS U
) AS T1
INNER JOIN TABLE2 T2
ON T1.COLS = T2.[Name]'
--print @query
execute(@query)
As the list of columns may change in future, we have used the below script to get the list of columns and using the CURSOR, it is stored in the @cols variable with comma seperated format.
Select name from syscolumns where id=object_id('Table 1')
Once Again, Thank You very much.
Jey
modified on Wednesday, June 17, 2009 11:03 AM
|
|
|
|
|
Niladri Biswas
|
|
|
|
|
I have the folowing stored procedure in my DB. It does not error out. It says it completes successfully. But it doe not update any data. I know I have good matched data for the join part.
USE [sde]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: <Author,,Name>
-- Create date: <Create Date,,>
-- Description: <Description,,>
-- =============================================
ALTER PROCEDURE [domain\me].[GIStoCRW]
AS
BEGIN
SET NOCOUNT ON;
update gis.crw_gis_data set block = t2.suff from gis.crw_gis_data t1 INNER JOIN gis.adata t2 on t1.ppi = t2.ppi
END
exec [domain\me].[GIStoCRW]
Any ideas?
thanks
|
|
|
|
|
Are you sure that the update statement updates any rows ?
Maybe there are no rows where t1.ppi = t2.ppi
Start with that.
|
|
|
|
|
Thanks,
It turns out that I was missing a GO statement in the SP. I though I had posted that fix here.
|
|
|
|
|
Nice to know you've got it solved.
|
|
|
|
|
Respected,
Greetings
I have problem to get search result query result
I have database with carMaster,AmenityMST & carAmm which is transactional or derived table.
CarMST
carCode carNumber
1 MH12-KO1212
2 MH42-jk1234
AmenityMST
ammCode ammName
1 mobile charger
2 video coach
3 AC
carAmm
carCode ammCode
1 1
1 3
2 1
2 2
Now I want, when user check the amenities to search 1 & 2 that time, he should get only 1 result i.e. 2
I used
"select carCode from carAmm wjhere ammCode in (1,2)" this sqlquery
but it returns me both cars, even car have not 'video coach'
Please help me to solve this problem
|
|
|
|
|
Your question is very unclear...
Explain it properly otherwise, nothing cannot be figured out.
One advice, when asking question , frame it in such a way that anybody can understand what you are actually meaning.
Remember, it is not you who will be reading the question, rather others who can understand by looking into the problem statement. there are no other 3rd party medium for this communication.
Niladri Biswas
|
|
|
|
|
thanx for advice.
I have car entry screen, in which user can insert a car details such as number, color, model etc. & amenities (select multiple for 1 car).
Now search screen, when user selects aminities from list., I want to show only those cars who have all selected amminities.
|
|
|
|
|
If I have clearly understood your problem, you want to display only those cars which have got all the amenities.
I created 3 tables as what you specified.
Here I am depicting the junction table( tblCarAmnesty ) which is having 2 columns viz. CarCode & AmmCode [ both of type int ]
The values are as under
CarCode AmmCode
1 1
1 2
1 3
2 1
2 2
2 3
3 1
3 2
4 1
5 2
5 3
6 1
6 2
6 3
As per your requirement, Only car code 1,2 and 6 will only appear.
Here is the query
SELECT C.CARCODE
FROM (
SELECT A.CARCODE,A.AMMCODE
FROM (SELECT CARCODE,AMMCODE FROM dbo.tblCarAmnesty
WHERE AMMCODE = 1) A
INNER JOIN
(SELECT CARCODE,AMMCODE FROM dbo.tblCarAmnesty
WHERE AMMCODE = 2) B
ON A.CARCODE = B.CARCODE) C
INNER JOIN
(SELECT CARCODE,AMMCODE FROM dbo.tblCarAmnesty
WHERE AMMCODE = 3) D
ON C.CARCODE = D.CARCODE
And here is the output
CARCODE
1
2
6
Let me know in case of any concerns
Hope this helps.
Niladri Biswas
modified on Friday, June 26, 2009 12:27 AM
|
|
|
|
|
Respected Niladri,
Thanx for the reply.
Yes it is working correctly.
But,
I have N' number of amminities.
User can select 1 or more than aminity for search a car, & result should be, all the cars, who have 'ALL SELECTED' amminities only
Means, as per your dataentry,
If user is searching car, which having amminities 2 & 3, he should get result as '1,2,5,6'
If user is searching car, which having amminities 1 & 2, he should get result as '1,2,3,6'
If user is searching car, which having amminities 1,2,3, he should get result as '1,2,6'
Hope now I can clearely describe the problem
|
|
|
|
|
Hi Kripa, sorry for answering late.
I was extremely busy with my project work. That's the only reason I answered late to your first question.
Anyway, here is the answer
For Case I & II
If user is searching car, which having amminities 2 & 3, he should get result as '1,2,5,6'
If user is searching car, which having amminities 1 & 2, he should get result as '1,2,3,6'
Solution 1:
SELECT CarCode
FROM tblCarAmnesty
WHERE AmmCode in (1,2)
GROUP BY CarCode
HAVING COUNT(*) = 2
Solution 2
SELECT A.CarCode
FROM tblCarAmnesty A, tblCarAmnesty B
WHERE A.CarCode = B.CarCode
AND A.AmmCode = 1
AND B.AmmCode = 2
--------------------------------------------------------------------------
For Case III
If user is searching car, which having amminities 1,2,3, he should get result as '1,2,6'
Solution 1:
SELECT CarCode
FROM tblCarAmnesty
WHERE AmmCode in (1,2,3)
GROUP BY CarCode
HAVING COUNT(*) = 3
Solution 2
SELECT A.CarCode
FROM tblCarAmnesty A, tblCarAmnesty B,tblCarAmnesty C
WHERE
A.CarCode = B.CarCode
AND B.CarCode = C.CarCode
AND A.AmmCode = 1
AND B.AmmCode = 2
AND C.AmmCode = 3
N.B.~ As you can generalized that the count must match the number of AmmCode you are looking for for the Solution I category.
So what I suggest is that, make the Count of Ammcode as dynamic(i.e. a variable) as well as the parameter in the IN clause.
For Solution II category, I suggest better you make a dynamic SQL Query.
Hope this helps.
Niladri Biswas
modified on Sunday, June 28, 2009 1:19 AM
|
|
|
|
|
Thanx very very much
Really It's working
|
|
|
|
|
Niladri Biswas
|
|
|
|
|
when i want to Clear my record in SQL server 2005 i used this code:
USE [Test]
GO
DELETE FROM TblAuditTrial;
DELETE FROM TblAuditTrialGroup;
DELETE FROM TblGrossProfit
DELETE FROM TblMenu
DELETE FROM TblMenuCategory
DELETE FROM TblMenuSubCategory
DELETE FROM TblOrderDetail
DELETE FROM TblOrderStatus
DELETE FROM TblPayment
DELETE FROM TblPaymentDetail
DELETE FROM dbo.TblProject
DELETE FROM dbo.TblRefund
DELETE FROM dbo.TblReport
DELETE FROM dbo.TblReportGroup
DELETE FROM dbo.TblStockOutGross
DELETE FROM dbo.TblUnit
DELETE FROM dbo.TblUser
DELETE FROM dbo.TblUserGroup
DELETE FROM dbo.TblUserGroupPermission
DELETE FROM dbo.TblUtilities
DELETE FROM dbo.TblVoidOrder
DELETE FROM dbo.TblVoidStockIn
DELETE FROM dbo.TblVoidStockOut
DELETE FROM dbo.TlkpNext
DELETE FROM dbo.TlkpPermissionCategory
DELETE FROM dbo.TrelBranchUser
DELETE FROM dbo.TlkpPermission
Go
It's is not easy for me, cos i must drop down alot ..
Can any one give me some easy way? i'm a new Learner, thanks..
|
|
|
|
|