|
That wasn't to hard to solve.
But I'm not going to tell you how. And this is why:
Pyramid schemes are illegal in a larger part of the world, and I'm not being part of it!
Alternatively this is a school assignment, where I'm not simply going to tell you the answer. Because I'm might have to work with you in the future, and if you got your degree without actually having understood what you were taught, you're going to be a dead weight for your workmates.
So you'll have to do the work yourself, but I'll assume you're a student and will give you a few pointers on how to get there.
Drop that silly tbl prefix, you're not selling furniture and it's not following standards (ISO-11179).
Drop the temp table, it's a last resort and shows that you're thinking procedurally instead of setbased.
So if you think setbased instead: get two CTEs, one containing the nodes having left children and one having containing the nodes with right children. Now you only need to join those CTEs three times to get the nodes that should get payed $1500.
Politicians are always realistically manoeuvering for the next election. They are obsolete as fundamental problem-solvers.
Buckminster Fuller
|
|
|
|
|
Jörgen Andersson wrote: Pyramid schemes are illegal in a larger part of the world, and I'm not being part of it!
Good call.
The OP lists the US as where they are are from and in many jurisdictions in the US a pyramid scheme can lead to a felony conviction.
|
|
|
|
|
I'm trying to write a complex stored procedure that will filter my GridView. Right now I'm using a If statement but I rather to a way with that and some how run the procedure depending on what I type into my text box. Instead of having:
@SearchBy = 'Username' OR
@SearchBy = 'State' OR
@SearchBy = 'AreaNumber'
Also
WHERE
Username LIKE '%' + @SearchVal + '%'
OR State LIKE '%' + @SearchVal + '%'
OR AreaNumber LIKE '%' + @SearchVal + '%'
Is there a way to do something like:
These don't work
@SearchBy = ''
@SearchBy = '' LIKE '%' + @SearchVal + %
This is my code and hopefully it will clear up what I'm trying to say above:
ALTER PROCEDURE [dbo].[SearchEmpRecords_Sp2]
@SearchBy varchar(50) = ISNull,
@SearchVal varchar(50) = ISNull
AS
BEGIN
DECLARE @sql NVARCHAR(1000)
IF
@SearchBy = 'Username'or
@SearchBy = 'State' or
@SearchBy = 'AreaNumber'
BEGIN
SELECT
EmployeeID,
Username,
State,
AreaNumber
FROM Employees
WHERE
Username LIKE '%' + @SearchVal + '%'
OR State LIKE '%' + @SearchVal + '%'
OR AreaNumber LIKE '%' + @SearchVal + '%'
END
ELSE
BEGIN
SELECT
EmployeeID,
Username,
State,
AreaNumber
FROM Employees
END
END
EXECUTE sp_executesql @sql
Also the Username, State, AreaNumber are in my aspx code. They are in a dropdown list and part of a SelectedItem routine. I don't know if you needed to know that but I put it in here just in case.
|
|
|
|
|
The only way to do what you want is using dynamic sql, basically build a string and execute it. A better soluton isto pass in 3 discreet variables and test each one for content.
Where ((Isnull(@UserName,'') == '' OR Table.UserName like @Username)
and ((Isnull(@Sate,'') == '' OR Table.State like @State)
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
hi,
i am a beginner in database...i have two tables like
eid ename pid and second table like pid salary
where i need to get the name of employee who took maximum salary in April month..
please suggest
|
|
|
|
|
Try something like this :
SELECT s.salary, e.ename FROM employees e INNER JOIN salaries s ON s.pid = e.pid HAVING s.salary = MAX(s.salary)
This assumes the first table is called employees and the second table is called salaries. Substitute real names as appropriate.
Hope this helps
When I was a coder, we worked on algorithms. Today, we memorize APIs for countless libraries — those libraries have the algorithms - Eric Allman
|
|
|
|
|
can i know hoe we can know that maximum salary taken by employee in april month please?
|
|
|
|
|
I will have to leave that up to you to figure out. Mycroft has given you a link to an excellent resource for learning SQL. You will be better off by trying to work it out yourself.
When I was a coder, we worked on algorithms. Today, we memorize APIs for countless libraries — those libraries have the algorithms - Eric Allman
|
|
|
|
|
Spanner, while Wayne has given you the answer I suggest you do NOT use a forum as your primary learning tool. Read up on queries using TSQL, there is plenty of doco and examples available. This will insure you spend less time struggling with really basic, easy to learn stuff (like this question) and more time being productive.
Here is an example of what you can find to help you Visual Representation of SQL Joins[^]
Oh and as a bonus you will not piss off the forum by asking endless very simple questions.&lt;/pre&gt;</pre></pre>
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
First of all, you need a field in the second table to capture date of salary.
|
|
|
|
|
hi to all
how can i optimiza this query
id dont want use select top(1) in nested select
thanks in advance
|
|
|
|
|
On a first look: too many subqueries.
Have a look here: How To: Optimize SQL Queries[^].
I would suggest you to ask Google with sentence: "query performance".
|
|
|
|
|
Maciej Los wrote: too many subqueries.
I try to avoid subqueries; JOINs tend to work better in many situations.
|
|
|
|
|
Consider changing Top(1) for Max([Time]), but with a proper indexing you shouldn't notice a to big difference.
Politicians are always realistically manoeuvering for the next election. They are obsolete as fundamental problem-solvers.
Buckminster Fuller
|
|
|
|
|
I've built a RDL with SRSS 2008 (actually it's migrated from 2005 by opening and saving in SRSS 2008)
There's a group which its visibility is controlled by input parameters. (Static) rows inside the group sets RepeatOnNewPage as True and KeepWithGroup attribute is set to "Before" as footer also.
I found that the group keeps repeating on each page just only missing in the second last page.
I have tried to set the RepeatOnNewPage be either True or False for the group but the result still be same (missing the footer in 2nd last page).
I am not sure why this happening and hope if some advise can be provided. Thanks very much.
Source can be provided as request.
|
|
|
|
|
|
Marked as spam.
Bastard Programmer from Hell
If you can't read my code, try converting it here[^]
|
|
|
|
|
Greetings,
below command works fine. but everytime when i want to update another quess and buildingpoint .i need to change quess_2 ,q2buildingpoint.So i write all 12 ques and 12 builgpoints .So my question is i want output like this with stored procedures.Can anyone please suggest or modify my stored procedure.
output
exec 2,5,5 ---this out put effects in survey table (cid,ques_5 ,q5buildpoint)
exec 3,5,5 ---this out put effects in survey table (cid,ques_5 ,q5buildpoint)
UPDATE survey SET
ques_1 = (select response from surveyanswers t2 where id=@response),q1buildpoint=(select response from surveyanswers t2 where id=@buildingpoint)
from survey c
inner join surveyanswers s
on s.id = c.cid
where c.cid = @ID
End
can i write procedure like this .it thows errors .can anyone please suggset something on this below procedure.How can i add if else conditions
ALTER PROCEDURE [dbo].[UpdateSurvey]
-- Add the parameters for the stored procedure here
@ID int,@response nvarchar(50),@buildingpoint nvarchar(50)
AS
BEGIN
if(@ID=1) then
Begin
UPDATE survey SET
ques_1 = (select response from surveyanswers t2 where id=@response),q1buildpoint=(select response from surveyanswers t2 where id=@buildingpoint)
from survey c
inner join surveyanswers s
on s.id = c.cid
where c.cid = @ID
End
Else If(@ID=2) then
Begin
UPDATE survey SET
ques_2a = (select response from surveyanswers t2 where id=@response),q2abuildpoint=(select buildingpoint from surveyanswers t2 where id=@buildingpoint)
from survey c
inner join surveyanswers s
on s.id = c.cid
where c.cid = @ID
End
Else If(@ID=3) then
Begin
UPDATE survey SET
ques_2b = (select response from surveyanswers t2 where id=@response),q2bbuildpoint=(select buildingpoint from surveyanswers t2 where id=@buildingpoint)
from survey c
inner join surveyanswers s
on s.id = c.cid
where c.cid = @ID
Else If(@ID=4) then
Begin
UPDATE survey SET
ques_2c = (select response from surveyanswers t2 where id=@response),q2cbuildpoint=(select buildingpoint from surveyanswers t2 where id=@buildingpoint)
from survey c
inner join surveyanswers s
on s.id = c.cid
where c.cid = @ID
Else If(@ID=3) then
Begin
UPDATE survey SET
ques_3 = (select response from surveyanswers t2 where id=@response),q3buildpoint=(select buildingpoint from surveyanswers t2 where id=@buildingpoint)
from survey c
inner join surveyanswers s
on s.id = c.cid
where c.cid = @ID
Else If(@ID=3)then
Begin
UPDATE survey SET
ques_4 = (select response from surveyanswers t2 where id=@response),q4buildpoint=(select buildingpoint from surveyanswers t2 where id=@buildingpoint)
from survey c
inner join surveyanswers s
on s.id = c.cid
where c.cid = @ID
Else If(@ID=3) then
Begin
UPDATE survey SET
ques_5 = (select response from surveyanswers t2 where id=@response),q5buildpoint=(select buildingpoint from surveyanswers t2 where id=@buildingpoint)
from survey c
inner join surveyanswers s
on s.id = c.cid
where c.cid = @ID
Else If(@ID=3) then
Begin
UPDATE survey SET
ques_6 = (select response from surveyanswers t2 where id=@response),q6buildpoint=(select buildingpoint from surveyanswers t2 where id=@buildingpoint)
from survey c
inner join surveyanswers s
on s.id = c.cid
where c.cid = @ID
Else If(@ID=3) then
Begin
UPDATE survey SET
ques_7 = (select response from surveyanswers t2 where id=@response),q7buildpoint=(select buildingpoint from surveyanswers t2 where id=@buildingpoint)
from survey c
inner join surveyanswers s
on s.id = c.cid
where c.cid = @ID
Else If(@ID=3)then
Begin
UPDATE survey SET
ques_8 = (select response from surveyanswers t2 where id=@response),q8buildpoint=(select buildingpoint from surveyanswers t2 where id=@buildingpoint)
from survey c
inner join surveyanswers s
on s.id = c.cid
where c.cid = @ID
Else If(@ID=3)then
Begin
UPDATE survey SET
ques_9 = (select response from surveyanswers t2 where id=@response),q9buildpoint=(select buildingpoint from surveyanswers t2 where id=@buildingpoint)
from survey c
inner join surveyanswers s
on s.id = c.cid
where c.cid = @ID
Else If(@ID=3)then
Begin
UPDATE survey SET
ques_10 = (select response from surveyanswers t2 where id=@response),q10buildpoint=(select buildingpoint from surveyanswers t2 where id=@buildingpoint)
from survey c
inner join surveyanswers s
on s.id = c.cid
where c.cid = @ID
Else If(@ID=3)then
Begin
UPDATE survey SET
ques_11 = (select response from surveyanswers t2 where id=@response),q11buildpoint=(select buildingpoint from surveyanswers t2 where id=@buildingpoint)
from survey c
inner join surveyanswers s
on s.id = c.cid
where c.cid = @ID
End
Else If(@ID=3) then
Begin
UPDATE survey SET
ques_12= (select response from surveyanswers t2 where id=@response),q12buildpoint=(select buildingpoint from surveyanswers t2 where id=@buildingpoint)
from survey c
inner join surveyanswers s
on s.id = c.cid
where c.cid = @ID
End
End
End
------
--exec UpdateSurvey 10,19,19
--select * from survey
--select * from surveyanswers
Errors
Msg 156, Level 15, State 1, Procedure UpdateSurvey, Line 58
Incorrect syntax near the keyword 'then'.
Msg 156, Level 15, State 1, Procedure UpdateSurvey, Line 71
Incorrect syntax near the keyword 'Else'.
Msg 156, Level 15, State 1, Procedure UpdateSurvey, Line 71
Incorrect syntax near the keyword 'then'.
Msg 156, Level 15, State 1, Procedure UpdateSurvey, Line 82
Incorrect syntax near the keyword 'Else'.
Msg 156, Level 15, State 1, Procedure UpdateSurvey, Line 82
Incorrect syntax near the keyword 'then'.
Msg 156, Level 15, State 1, Procedure UpdateSurvey, Line 93
Incorrect syntax near the keyword 'Else'.
Msg 156, Level 15, State 1, Procedure UpdateSurvey, Line 93
Incorrect syntax near the keyword 'then'.
|
|
|
|
|
"Then" is not part of the SQL-IF construction. See here[^].
If the error says "incorrect syntax", then there's an error in parsing the command; it means that the computer doesn't understand what you want. Easiest solution to those errors is to verify the syntax (the way the command is written) using MSDN.
Bastard Programmer from Hell
If you can't read my code, try converting it here[^]
|
|
|
|
|
ALTER PROCEDURE [dbo].[UpdateSurvey]
@ID int,@response nvarchar(50),@buildingpoint nvarchar(50),@ID1 int
AS
BEGIN
SET NOCOUNT ON;
if(@ID1=1)
Begin
UPDATE survey SET
ques_1 = (select response from surveyanswers t2 where id=@response),q1buildpoint=(select buildingpoint from surveyanswers t2 where id=@buildingpoint)
from survey c
inner join surveyanswers s
on s.id = c.cid
where c.cid = @ID
End
Else If(@ID1=2)
Begin
UPDATE survey SET
ques_2a = (select response from surveyanswers t2 where id=@response),q2abuildpoint=(select buildingpoint from surveyanswers t2 where id=@buildingpoint)
from survey c
inner join surveyanswers s
on s.id = c.cid
where c.cid = @ID
End
Else If(@ID1=3)
Begin
UPDATE survey SET
ques_2b = (select response from surveyanswers t2 where id=@response),q2bbuildpoint=(select buildingpoint from surveyanswers t2 where id=@buildingpoint)
from survey c
inner join surveyanswers s
on s.id = c.cid
where c.cid = @ID
END
Else If(@ID1=4)
Begin
UPDATE survey SET
ques_2c = (select response from surveyanswers t2 where id=@response),q2cbuildpoint=(select buildingpoint from surveyanswers t2 where id=@buildingpoint)
from survey c
inner join surveyanswers s
on s.id = c.cid
where c.cid = @ID
END
Else If(@ID1=5)
Begin
UPDATE survey SET
ques_3 = (select response from surveyanswers t2 where id=@response),q3buildpoint=(select buildingpoint from surveyanswers t2 where id=@buildingpoint)
from survey c
inner join surveyanswers s
on s.id = c.cid
where c.cid = @ID
END
Else If(@ID1=6)
Begin
UPDATE survey SET
ques_4 = (select response from surveyanswers t2 where id=@response),q4buildpoint=(select buildingpoint from surveyanswers t2 where id=@buildingpoint)
from survey c
inner join surveyanswers s
on s.id = c.cid
where c.cid = @ID
END
Else If(@ID1=7)
Begin
UPDATE survey SET
ques_5 = (select response from surveyanswers t2 where id=@response),q5buildpoint=(select buildingpoint from surveyanswers t2 where id=@buildingpoint)
from survey c
inner join surveyanswers s
on s.id = c.cid
where c.cid = @ID
END
Else If(@ID1=8)
Begin
UPDATE survey SET
ques_6 = (select response from surveyanswers t2 where id=@response),q6buildpoint=(select buildingpoint from surveyanswers t2 where id=@buildingpoint)
from survey c
inner join surveyanswers s
on s.id = c.cid
where c.cid = @ID
END
Else If(@ID1=9)
Begin
UPDATE survey SET
ques_7 = (select response from surveyanswers t2 where id=@response),q7buildpoint=(select buildingpoint from surveyanswers t2 where id=@buildingpoint)
from survey c
inner join surveyanswers s
on s.id = c.cid
where c.cid = @ID
END
Else If(@ID1=10)
Begin
UPDATE survey SET
ques_8 = (select response from surveyanswers t2 where id=@response),q8buildpoint=(select buildingpoint from surveyanswers t2 where id=@buildingpoint)
from survey c
inner join surveyanswers s
on s.id = c.cid
where c.cid = @ID
output
exec UpdateSurvey 5,5,5,2(id,response,buildingpoint,@ID1=2)
but instead of updating @ID1=2 ,it updates @ID1=5 .BUT I want to update @ID1=2 query .Is there any solution for this
|
|
|
|
|
"Debugging"; as far is I can see, it'd select the "Else If(@ID1=5)", and execute that query. Replace those queries with a "print" statement, and see if it switches to the right one.
|
|
|
|
|
Hi,
Well, this is kind of embarrassing, i always used the GUI for altering the database tables in sql server 2005. Now, in sql server 2008, I need to write sql statements to do this.
I need to alter the existing column, and set it to not null with default value 0.
I got the not null part working, get error on default value.
alter table dbo.mytable
alter column [VendorLevelId] INT Not NULL Default 0
Msg 156, Level 15, State 1, Line 2
Incorrect syntax near the keyword 'Default'.
|
|
|
|
|
Well, that's odd! BOL[^] says you should be able to use alter column ... set default x to set the default value, but it doesn't seem to work.
Try using three statements:
ALTER TABLE dbo.mytable
ADD CONSTRAINT DF_mytable_VendorLevelId DEFAULT (0) FOR VendorLevelId;
UPDATE dbo.mytable
SET VendorLevelId = 0
WHERE VendorLevelId Is Null;
ALTER TABLE dbo.mytable
ALTER COLUMN VendorLevelId int NOT NULL;
"These people looked deep within my soul and assigned me a number based on the order in which I joined."
- Homer
|
|
|
|
|
Here's an example from BOL (2k8):
CREATE TABLE dbo.doc_exy (column_a INT ) ;
GO
INSERT INTO dbo.doc_exy (column_a) VALUES (10) ;
GO
Straightforwardly:
SELECT * FROM dbo.doc_exy
Gets:
10
Now, doing the operation appears to suggest that no default value is ever needed:
ALTER TABLE dbo.doc_exy ALTER COLUMN column_a DECIMAL (5, 2);
GO
SELECT * FROM dbo.doc_exy
Shows what I mean:
10.00
DROP TABLE dbo.doc_exy ;
GO
|
|
|
|
|
Two of my OLAP databases disappeared in the last few days. Thankfully I had a backup.
However, I would want to investigate the root cause. How do I proceed?
|
|
|
|
|