|
Hi ,
I had some problem with my select query.Let me explain you whole.
I had created dynamic query in my select query as i required performance.
My Select query is as below:
Select firstname,lastname,dob,ssn from person
where
(
firstname like 'abc%' and lastname like 'xyz%'
OR
firstname like 'ypx%' and lastname like 'por%'
OR
firstname like 'stup%' and lastname like 'rob%'
)
Now here i required one more field which is not available in
database table (person table).
Now that field is MatchType.Matchtype is used for
If user input of firstname and lastname is exactly matching with
database record then i have to write matchtype ='Exact Match'
if user input of first 3 character of firstname and lastname is matching
then i have to write Matchtype ='Potiential Match'
Now problem is that :
If i will write subquery like
Select firstname,lastname,dob,ssn,
(
Select
//some login which return Match type
) as Matchtype
from person
where
(
firstname like 'abc%' and lastname like 'xyz%'
OR
firstname like 'ypx%' and lastname like 'por%'
OR
firstname like 'stup%' and lastname like 'rob%'
)
If i can't write query like that becoz subquery should return only one
value.
so can somebody help me out here.??
If you have any question then let me know without hesitation.
Thankx a lot in advance.
regd,
montu3377
|
|
|
|
|
I'm not sure if I totally understand your requirements but would the following work.
Select
firstname
,lastname
,dob
,ssn
,case when (firstName = 'abc' and lastname ='xyz') or (firstname ='ypx' and lastname ='por') or (firstname ='stup' and lastname ='rob') then 'Exact Match'
else 'Potential Match'
end as [MatchType]
from person
where
(
firstname like 'abc%' and lastname like 'xyz%'
OR
firstname like 'ypx%' and lastname like 'por%'
OR
firstname like 'stup%' and lastname like 'rob%'
)
|
|
|
|
|
I need to migrate about a dozen databases from SQL Server 2005 running on (ComputerAboutToBarf01) to (ServerAboutToSaveTheDay01). Do I have to manually backup then restore each database or is there a utility out there that will do this for me? I tried it in the Management Studio but don't seem to see any point-and-click way to do it all at once.
|
|
|
|
|
Have you looked into using any stored procedures?
|
|
|
|
|
Pls anybody help me.
i hve to update a feild( say rcvd) in receivables table from taking the feild(amount) from allocation table. i give the query like this"
update receivables,allocation set rcvd=rcvd+amount where
receivables.no=allocation.no "
i want to know whether we can give 2 table name in update query if so then the syntax when i give the above i am getting error.pls help me its urgent
-- modified at 4:31 Saturday 20th January, 2007
|
|
|
|
|
|
try ...
update r
set
rcvd = r.rcvd + a.amount
from receivables r
join allocation a on a.no = r.no
|
|
|
|
|
I’m just starting to learn about database design and normalization, and from what I’ve learned so far, it seemed to me that it would be a good idea to several tables to define an “address” object.
What I mean is:
Instead of having a table called “Address” like:
__________________________
Address
• StreetAddressNumber
• StreetName
• UnitNumber
• ZipCode
• City
• County
• State
• Country
__________________________
I think it is better to have several tables such as:
__________________________
Address
• StreetAddressNumber
• StreetName
• UnitNumber
__________________________
ZipCode
• ZipCodeFiveDigit
• ZipCodeFiveDigit
__________________________
City
• Name
• County
__________________________
State
• Name
• Abbreviation
__________________________
Country
• Name
__________________________
But from what I’ve seen so far, nothing looks like this. Most of the designs I’ve seen have an “Address” entity with all the address information in the same table like the first table you see above.
Am I going too far with normalization? Is there a reason not to do what I’m doing?
Any thoughts?
Thanks in advance,
Silvio.
http://www.silvioribeiro.com
|
|
|
|
|
It is a bad idea to "over normalize". Although it increases flexiblility, it can seriously impact performance and maintainability. in your example, to get a complete "address" entity you would need a query that potentially joined all five tables (you are missing Primary keys and PK-FK relationships BTW). To add a new address you would need a potentially complex query that added new rows to possibly all 5 tables (complex because it would have to determine whether to use an existing row or add a new row). Logically, all of that information belongs to a single entity (an address) and belongs in a single table (I would not, however, break up Zip code into parts, but rather just have a single "postal code" field). You might, however, heve a separate table for "person" entities, and that would have a pk-Fk pointer to the address table, because two or more "persons" can share the same address.
Basically, don't normalize further than the 'entity' level, and keep the entities reasonably complete even at the expense of duplicate data.
|
|
|
|
|
Thanks so much Rob, your answer was very helpful as it covered everything that wasn’t clear to me. I started to learn Visual Basic about 2 years ago and found out that without knowing at least the basics of database design everything can be a lot harder.
Also, the fact that I don’t work as a programmer, and don’t have anyone to ask questions doesn’t help me at all.
I’m constantly trying to learn everything on my own. Not easy!
Thanks a lot,
Silvio.
http://www.silvioribeiro.com
|
|
|
|
|
I have a database that has the final three combined in a table called Jurisdiction. Every entry in the Address table has a reference to an entry in the Jurisdiction table, too. But it depends upon the usage. In my case I have other uses for jurisdictions, besides addresses. Things like holidays, for example.
Chris Meech
I am Canadian. [heard in a local bar]
I agree with you that my argument is useless. [Red Stateler]
Hey, I am part of a special bread, we are called smart people [Captain See Sharp]
The zen of the soapbox is hard to attain...[Jörgen Sigvardsson]
I wish I could remember what it was like to only have a short term memory.[David Kentley]
|
|
|
|
|
I have table "tasks" that is recursive--tasks can have subtasks. The table has an ID and a ParentTaskID.
Is there a way to write a query to recurse through all the subtasks of a particular task?
Thanks!
Marc
Thyme In The CountryPeople are just notoriously impossible. --DavidCrow
There's NO excuse for not commenting your code. -- John Simmons / outlaw programmer
People who say that they will refactor their code later to make it "good" don't understand refactoring, nor the art and craft of programming. -- Josh Smith
|
|
|
|
|
If I understand your problem correctly, than the only way I can think of to do this would be in your code...not in a single SQL statement. You would have to put the SQL select in a loop that would keep checking for a record until it finds one that doesn't have a parent task.
Hope this helps.
|
|
|
|
|
Marc
Is this a SQL Server 2005 table? I ask this because you can do some pretty amazing things with something called a Common Table Expression (CTE).
If not, there are ways that you can achieve this using ordinary SQL, but they aren't pretty.
Let me know which way, and I'll see if I can put an example together for you.
the last thing I want to see is some pasty-faced geek with skin so pale that it's almost translucent trying to bump parts with a partner - John Simmons / outlaw programmer
Deja View - the feeling that you've seen this post before.
|
|
|
|
|
Pete O`Hanlon wrote: Is this a SQL Server 2005 table?
Yes it is.
Pete O`Hanlon wrote: and I'll see if I can put an example together for you.
Thanks! In the meantime, I'll research CTE!
Marc
Thyme In The CountryPeople are just notoriously impossible. --DavidCrow
There's NO excuse for not commenting your code. -- John Simmons / outlaw programmer
People who say that they will refactor their code later to make it "good" don't understand refactoring, nor the art and craft of programming. -- Josh Smith
|
|
|
|
|
Without knowing the specifics of what you are trying to do, you could try something along the lines of:
WITH TaskSearch (TaskID, TaskName, TaskParent, Level)
AS
(
SELECT ID, Task, ParentTaskID, 0 AS Level
FROM Tasks WHERE ParentTaskID IS NULL
UNION ALL
SELECT ID, Task, ParentTaskID, Level + 1
FROM Tasks
INNER JOIN TaskSearch
ON TaskID = ParentTaskID
)
SELECT TaskID, TaskName, TaskParent, Level
FROM TaskSearch
Sorry - I've done this from memory so it might not be 100%.
the last thing I want to see is some pasty-faced geek with skin so pale that it's almost translucent trying to bump parts with a partner - John Simmons / outlaw programmer
Deja View - the feeling that you've seen this post before.
|
|
|
|
|
Pete O`Hanlon wrote: Sorry - I've done this from memory so it might not be 100%.
Looks great! I was fussing with the Microsoft example and fumbled with the inner join where (had the child and parent ID comparison backwards with relation to the "from" and "inner join" tables).
Thanks so much for showing me about CTE. Very cool!
Marc
Thyme In The CountryPeople are just notoriously impossible. --DavidCrow
There's NO excuse for not commenting your code. -- John Simmons / outlaw programmer
People who say that they will refactor their code later to make it "good" don't understand refactoring, nor the art and craft of programming. -- Josh Smith
|
|
|
|
|
You can write recursive stored procedures. Will that help, or do you need it to be a single query?
Jon Sagara
I controlled my laughter and simple said "No,I am very busy,so I can't write any code for you". The moment they heard this all the smiling face turned into a sad looking face and one of them farted. So i had to leave the place as soon as possible.
--Mr.Prakash
Blog | Site | Articles
|
|
|
|
|
I have a Microsoft SQL Server database that was moved. When I looked at the tables in the Server Explorer there are 2 sets of the same tables 1 set having the text '(dbo)' tacked on to the end of the table names. Why is this? Am asking because the website that connects to the database updates the tables with '(dbo)' while the admin page querys the tables without '(dbo)'. Can I delete one of the sets?
|
|
|
|
|
dbo means data base owner, in short its the user who owns the tables.if you created the tables with user name "x" the resulting table will be referenced by x.tablename
You can change the owner of an object by using stored procedure sp_changeobjectowner
dptalt wrote: Can I delete one of the sets?
Well as long as you have the right permission in your web site to access thes tables you can...
|
|
|
|
|
I did the following steps:
1. Created a report using SQL Reporting Services. (OK)
2. Deployed the project (ABC) into the ServerX. (OK)
3. Opened the SQL Reporting Services using the Report Manager on localhost of the ServerX. (OK)
4. Opened the ABC folder. Files deployed. (OK)
5. Created two subscriptions. One (Service1) for printing to the Printer. One (Service2) for saving into a file. (OK)
6. Opened the Service1. In the View Tab, after entering the Report Parameters, clicked on "View Report". (OK)
7. I expected view to show what I viewed in the Project (ABC). (NOK - It printed a wrong format. Alignment was completely different)
8. Tried to export to a TIFF file expecting to see the same view as I saw in the Project (ABC). (NOK - It printed a wrong format. Alignment was completely different)
9. Tried to export to a PDF file expecting to see the same view as I saw in the Project (ABC). (NOK - It printed a wrong format. Alignment was completely different)
10. In the subscriptions, I ran the Service1 to print the report from the SQL Reporting Services directly onto the Printer. (NOK - It printed a wrong format. Alignment was completely different)
Is this an expected issue with SQL Reporting Services which incase doesnt make any sense to use it. Or it is an issue with my code. All I assumed that I should expect from SQL Reporting Services is: if I create a Report and I preview the Report, I should get a WYSIWYG formats.
Half the way through our project, we are completely stuck up. SOS!!!
|
|
|
|
|
Hi
There are two date fields in the table and i want to select Maximum date field from two fields i m trying to query like this.
Table Fileds:
UserID Int
LastDDTxn Datetime
LastCardTxn Datetime
SELECT LAST_TRAN_DATE=
(CASE WHEN LastCardTxn>LastDDTxn THEN LastCardTxn
ELSE WHEN LastDDTxn>LastCardTxn THEN LastDDTxn
ELSE NULL
END) AS Last_Tran_Date
FROM USERHISTORY
The Error is showing that
Incorrect syntax near the keyword 'WHEN'
SHAHZAD
|
|
|
|
|
Select LAST_TRAN_DATE = Max([Date])
From(
Select [Date] = LastDDTxn
From UserHistory
Union
Select [Date] = LastCardTxn
From UserHistory
) Dates
Wout Louwers
|
|
|
|
|
Thanx! Its working fine.
Shahzad
|
|
|
|
|
SELECT LAST_TRAN_DATE =
(CASE
WHEN LastCardTxn > LastDDTxn THEN LastCardTxn
<s>ELSE</s> WHEN LastDDTxn > LastCardTxn THEN LastDDTxn
ELSE NULL
END) AS Last_Tran_Date
FROM USERHISTORYThe first, crossed-out, ELSE is incorrect - ELSE is only used for the 'default' case i.e. when no WHEN clauses match. Therefore, when the query parser encounters ELSE, it's expecting an expression, and reports that WHEN is an error.
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
