|
there is 2 table named table 1 and table 2 in the db
where both table have a comman coloumn named employee_code.
let table1 conatain emplyee_code-1,2,6,8,9
and table2 contain employye_coce-2,4,6,7,8
now i want a sql statement which will give me the out put as follows
i want to check(let say employee code1) wather a particular employee_code
present in table 1 and not present in table2 .if it run succesfully it should give massage emplyee code exists else
doesn't exists.
means if i select emp_code 1 then the message would be "emplyee code exists"
i select emp_code 2 then the message would be "emplyee code doesnt exists"
|
|
|
|
|
Use the EXISTS[^] operator to check for the existence of rows with a particular employee code in table 2.
Paul Marfleet
"No, his mind is not for rent
To any God or government"
Tom Sawyer - Rush
|
|
|
|
|
Hi,
Master Table
SMID(int) Descr(varchar)
43 A
47 B
208 C
502 D
549 E
Child Table
Descr_SMID(varchar)
208
502,549,43,47
502,549
Now I want result like this using count.
A (1)
B (1)
C (1)
D (2)
E (2)
kiran banker
|
|
|
|
|
I don't follow the table layout, but it looks like you need something like
select sum(count), letter from tbl group by letter
where letter is the column that gives you A/B/C and count is the column you want to combine. use count instead of sum if you're counting columns and not combining their values
Christian Graus - Microsoft MVP - C++
"also I don't think "TranslateOneToTwoBillion OneHundredAndFortySevenMillion FourHundredAndEightyThreeThousand SixHundredAndFortySeven()" is a very good choice for a function name" - SpacixOne ( offering help to someone who really needed it ) ( spaces added for the benefit of people running at < 1280x1024 )
|
|
|
|
|
banker_kiran wrote: Child Table
Descr_SMID(varchar)
208
502,549,43,47
502,549
Your child table violates one of the principles of 1st normal form. Values for each column-row intersection should be atomic. You should consider splitting these values out, otherwise it will be very difficult to query the data efficiently.
Paul Marfleet
"No, his mind is not for rent
To any God or government"
Tom Sawyer - Rush
|
|
|
|
|
pmarfleet wrote: Your child table violates one of the principles of 1st normal form
Yes, it certainly does.
"The clue train passed his station without stopping." - John Simmons / outlaw programmer
|
|
|
|
|
Hi all,
I have a dataset which has 3 tables. They have relation by ds.Relations.Add(...)
Now I want a filteration like a master table i.e. ds.tables(0) should be filtered by one column like ds.tables(0).Select("Site_ID = 5")
(which will return datarow array now) and I want its children rows should be filtered appropriately.
Instead of table.Select(...) is there any other way?
Thank You
|
|
|
|
|
You could create a DataView-object for the DataTable via DataView dv = new DataView(ds.Tables[0]);. Then set the RowFilter with this line
dv.RowFilter = "Site_ID = 5";
|
|
|
|
|
i creating function
when used execute statment in function i have error:
Invalid use of 'EXECUTE' within a function.
-- modified at 3:43 Saturday 1st December, 2007
|
|
|
|
|
Rami Said Abd Alhalim wrote: Invalid use of 'EXECUTE' within a function.
Sounds like you need to use the execute command correctly.
"The clue train passed his station without stopping." - John Simmons / outlaw programmer
"Real programmers just throw a bunch of 1s and 0s at the computer to see what sticks" - Pete O'Hanlon
|
|
|
|
|
Hi all,
Here iam getting RoleId and iam storing it in a variable @str2.Now
i want to use this variable data as a column name in third query i.e
@str3.Please help me on this.
alter procedure LogProc
(
@UserId varchar(500),
@Password varchar(500)
)
as
Begin
declare @str1 varchar(500)
declare @str2 varchar(500)
declare @str3 varchar(500)
select @str1=AstId from UserTable where UserId=@UserId and Password = @Password;
select @str2=RoleID from AstProf where AstId =@str1 ;
select @str3=MenuId from MenuTable where @str2='y';
select @str3;
End
exec LogProc 'veeresh','veeresh'
i want to join this group
|
|
|
|
|
you cannot pass the Column dynamically unless using Dynamic SQL.
Now the question is that how to use dynamic sql
declare a variable assign it TSQL with any dynamic values
execute it .
________________
Tehmina Qureshi
****************
|
|
|
|
|
Hi Sir,
Iam new to sql.Can you tel me how to use dynamic sql in my query.
Veeresh
i want to join this group
|
|
|
|
|
you may declare and use dynamic sql like this;
CREATE PROCEDURE GetRecords ( @DynamicColumnName varchar(100) ) AS
DECLARE @dynamicsql varchar(255)
SELECT @dynamicsql = 'SELECT Column1, Column2, Column3 ' +
'FROM TableName ORDER BY ' + @DynamicColumnName
EXEC(@dynamicsql)
Hope this will help.
________________
Tehmina Qureshi
****************
|
|
|
|
|
Dear Sir,
Thanks for your help.But it's not comming here.Can you tel me how to use
that in this query.Here is my query
alter procedure LogProc
(
@UserId varchar(500),
@Password varchar(500)
)
as
Begin
declare @str1 varchar(500)
declare @str2 varchar(500)
declare @str3 varchar(500)
declare @str4 varchar(500)
select @str1=AstId from UserTable where UserId=@UserId and Password = @Password;
select @str2=RoleID from AstProf where AstId =@str1 ;
set @str4=@str2;
select @str3=MenuId from MenuTable where @str4='y';
exec(@str4);
select @str3;
End
exec LogProc 'veeresh','veeresh'
But its giving error Like this
(1 row(s) affected)
Server: Msg 2812, Level 16, State 62, Line 1
Could not find stored procedure 'R1'.
i want to join this group
|
|
|
|
|
Veer,
you did not fully understood the concept of dynamic sql.
so 1st you have to understand what the dynamic sql is;
Dynamic SQL is actually the complete sql statement, including all the necessory clauses
required for executing the sql statemnt like as "select * from tablename"
so if u hav a variable declared for dynamic sql u must assign a complete statement in it.
here in ur sp u did wrong.
Example:Declaration of dynamic sql
Declare @DynamicSql varchar(2000)
set @DynamicSql = 'SELECT * FROM MyTable'
now you may execute the like this
exec(@DynamicSql)
You may do aything which you want to with Dynamic SQL.
I did not understand what you want to select in you stored procedure (as i dont hav enough time to understand the code coz im at my job. May be after few time i'll correct your code and post it back to u.)
If you have got an idea about Dynamic SQL you can do whatever you want.
Still hav problem? You are welcome to query.
Thanks
________________
Tehmina Qureshi
****************
|
|
|
|
|
Hi,
Thank you very much sir.I wil follow the rules.
Veeresh.
i want to join this group
|
|
|
|
|
hi IsrarMuhammadQ
i have error to execute dynamic sql in function
|
|
|
|
|
Send the code and error msg
i'll try to figure it out
Can u describe me in words what you want to do?
just describe it clearly i'll try to make you the actual understanding about what u are asking.
________________
Tehmina Qureshi
We can have facts without thinking but we cannot have thinking without facts.
****************
|
|
|
|
|
this example :
--------------------------------------
CREATE FUNCTION test_fn
(
@x int
)
RETURNS
@table TABLE
(
c1 int
)
AS
BEGIN
EXEC('select * from @table')
RETURN
END
-----------------------------
Msg 443, Level 16, State 2, Procedure _fn__Get_All_References, Line 35
Invalid use of 'EXECUTE' within a function.
|
|
|
|
|
Hi,
Dont make it complicated.
just try this in Northwind database.
Create PROCEDURE test_GetRecords ( @DynamicColumnName varchar(100) ) AS
DECLARE @dynamicsql varchar(255)
SELECT @dynamicsql = 'SELECT * ' +
'FROM [Categories] ORDER BY ' + @DynamicColumnName + ' desc'
EXEC(@dynamicsql)
exec test_GetRecords '[CategoryName]'
________________
Tehmina Qureshi
We can have facts without thinking but we cannot have thinking without facts.
****************
|
|
|
|
|
i want use dynamic sql in function not in stored procedure
|
|
|
|
|
Oops, sorry - Basically, no, you cannot
use dynamic SQL in a function, and you probably shouldn't use it anyway.
Writing to a temporary table is prohibited by functions.
________________
Tehmina Qureshi
We can have facts without thinking but we cannot have thinking without facts.
****************
|
|
|
|
|
ok Thanks
where r u from?
|
|
|
|
|
I'm busy designing a little sales database, and I would like to include a natural key in addition to my favourite surrogate (identity) keys. My question here is about auto-populating the natural key.
I would like to, on creation of a new customer for example, default the natural key (CustNo) to the surrogate key (custId). However, in a databound application, when I insert new records, the surrogate key assigned by ADO.NET changes. Here I would like to use a trigger to only change the natural key if it hasn't been assigned explicitly. This would be easy if I left CustNo null on the client, and used an insert trigger, but I want to default the CustNo to custId on the client, so it is never null. Should I maybe leave it null until explicitly assigned, but show the value of custId for CustNo when CustNo is null? Is there any other, neater way to do this?
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
