September 10, 2011 at 1:07 am
hi there,
I have this sql code
select
patient.dbpatcnt as id,
patient.dbpatfirstname as fname,
patient.dbpatlastname as lname,
phone.dbphonenumber as phnNum,
phone.dbphonetypeid as phnTypeID
FROM PATIENT
inner join lnkphone on lnkphone.dbkeycnt = patient.dbpatcnt
inner join phone on phone.dbphoneid = lnkphone.dbphoneid
where patient.dbstatusid <> 13
and phone.dbphonetypeid in (5,9)
order by patient.dbpatcnt
which produces a table with columns -
[id] [fname] [lname] [phnNum] [phnTypeID]
...as you'd expect.
Now within the statement I wish to create another column and check the phnTypeID. If it is 5 then put phnNum into the [phnNum] column and an empty string into and if the phnTypeID is 9 put an empty string in [phnNum] and phnNum into .
[id] [fname] [lname] [phnNum] [phnTypeID]
Is this all possible within one sql statement and is it the most efficient way of doing this?
thanks
September 10, 2011 at 1:17 am
I do not see any DDL?
Could you include some with sample data?
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
September 10, 2011 at 1:26 am
mattech06 (9/10/2011)
Now within the statement I wish to create another column and check the phnTypeID. If it is 5 then put phnNum into the [phnNum] column and an empty string into and if the phnTypeID is 9 put an empty string in [phnNum] and phnNum into .
[id] [fname] [lname] [phnNum] [phnTypeID]
Is this all possible within one sql statement and is it the most efficient way of doing this?
You can;t assign values using a check constraint to assign values so you are going to have t write a trigger.
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
September 10, 2011 at 2:19 am
what would the trigger contain welshcorgi?
September 10, 2011 at 2:28 am
Is this all possible within one sql statement and is it the most efficient way of doing this?
It depends what you're looking for: if it's only a SELECT statement, then you could use the CASE function.
If you want to create a physical column on one of the underlying tables, you'll have to do it separately.
If you want to create a new table based on the output of the SELECT statement, you could use the INTO clause.
September 10, 2011 at 2:46 am
Is this going to b a one shot deal or will it be ongoing, i.e a user enters a record you get this behavior?
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
September 10, 2011 at 5:35 am
The script will be run daily and the output 'table' is sent to a web server.
I'm actually using sqllite with php for the script but the client's database uses sql server 2005. I'm just testing the sql in sql server first. Which may complicate matters if I venture into triggers, I may be able to make use of views tho..i just wanted to see if I could do it all in one sql statement really.
So i do physically want an extra column adding to the output 'table' yes.
September 10, 2011 at 6:01 am
I tend to do go with Physical Columns and I would script out the entire table so that you have the indexes, etc.
After Scripting the Tabe Out ALTER it to add the new column. Then add any Primary Keys and constraints that you need.
I would also write an INSERT INTO for it is a lot Cleaner.
If you try and perform an INSERT INTO on subsequent times you will get an error.:-)
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
September 10, 2011 at 8:35 am
If you realy mean just add another column in the output of the select statement, then you can go with Lutz' fiorst option - use a case function. this is the code you would have:
select
patient.dbpatcnt as id,
patient.dbpatfirstname as fname,
patient.dbpatlastname as lname,
case phone.dbphonetypeid when 5 then phone.dbphonenumber else '' end as phnNum,
case phone.dbphonetypeid when 9 then '' else dbphonenumber end as ,
phone.dbphonetypeid as phnTypeID
FROM PATIENT
inner join lnkphone on lnkphone.dbkeycnt = patient.dbpatcnt
inner join phone on phone.dbphoneid = lnkphone.dbphoneid
where patient.dbstatusid <> 13
and phone.dbphonetypeid in (5,9)
order by patient.dbpatcnt
It seemed pretty clear to me from whatyou wrote that that was what you intended to say you wanted, but as others have interpreted it differently maybe it is not.
Tom
September 10, 2011 at 8:44 am
I can appreciate what you want to do in this situation but what is the long term plan.
If you could include your business requirements then you would probably get better advice.
Find out what they are doing and get a justification. PLEASE!
It is easy for someone to say that something should be performer a certain was but if you do not have good requirements you are guessing in the wind. It might resolve the immediate problem but will it be a long term solution.
I have not head a lot of feedback from but a lot of assumptions have have made by others as to your final objective.
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
September 10, 2011 at 8:59 am
mattech06 (9/10/2011)
The script will be run daily and the output 'table' is sent to a web server.I'm actually using sqllite with php for the script but the client's database uses sql server 2005. I'm just testing the sql in sql server first. Which may complicate matters if I venture into triggers, I may be able to make use of views tho..i just wanted to see if I could do it all in one sql statement really.
So i do physically want an extra column adding to the output 'table' yes.
Int that case you want to covert your Statement to a INSERT INTO . Not into a SELECT INTO which is no big daeal. 🙂
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
September 10, 2011 at 9:06 am
Is this a Select Statement manipulation or do we need a physical Store?
It depends on the requirement and what objective you are trying to accomplish.'
I tend to store information in tables for it may be needed in the future.
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
September 10, 2011 at 9:12 am
It is rare that some ask what is your objective.
Why are you trying to do this this way?
What do you want your table structure to look like?
I have seen a lot of elaborate code.
You can provide someone with some great code but you may not be doing them a favor.
Get the structure down first.
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
September 11, 2011 at 7:22 am
Hi Tom, thanks for your help...it's almost what I'm hoping for.
When I run my original sql I get ...
idfname lnamephnNum phnTypeID
5Graham Atkinsonpatientne@test.com9
5Graham Atkinson07762 540001 5
7Rebecca Baileypatienttwo@test.com9
7Rebecca Bailey07762 540002 5
8Zoe Bell patientthree@test.com9
8Zoe Bell 07762 540003 5
9Christopher Bent07791800000 5
243Rachel McLaughlin07773700000 5
Now when I run your sql I get ...
idfname lnamephnNum email phnTypeID
5Graham Atkinson 9
5Graham Atkinson07762 54000107762 5400015
7Rebecca Bailey 9
7Rebecca Bailey07762 54000207762 5400025
8Zoe Bell 9
8Zoe Bell 07762 54000307762 5400035
9Christopher Bent07791800000077918000005
243Rachel McLaughlin07773700000077737000005
425Vanessa Haves 9
So, firstly, I'm looking to get one row for each patient/id and secondly it appears if the person does have phnTypeID 9 (in other words an email address) it's not getting included (looks like the empty string is instead)
so for the first guy i'd hope to see just one row
idfname lnamephnNum email phnTypeID
5Graham Atkinson07762 540001 patientne@test.com ??
Ah, just realised that won't work if I'm wanting the phnTypeID as well duh 😉
So if I drop wanting the phnTypeID as part of the output table, I'm looking for something like this..
idfname lnamephnNum email
5Graham Atkinson07762 540001 patientne@test.com
243Rachel McLaughlin07773700000
Thanks for the help/questions as well Welsh..I hope this makes it cleared for you.
September 11, 2011 at 7:41 am
oh fun and games with the table formatting. It appears I can't delete the above post and when I try and edit it there's isn't a repost option? I'm sure it's me..well anyway, off to get some food then I'll come back to it but if you can understand my 'condensed' table displays enough to help then that'd be great, thanks.
Viewing 15 posts - 1 through 15 (of 22 total)
You must be logged in to reply to this topic. Login to reply