Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Multiple Aliases for a single Column


Multiple Aliases for a single Column

Author
Message
dirtyshorellc
dirtyshorellc
Forum Newbie
Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)

Group: General Forum Members
Points: 2 Visits: 19
Hello,

I am new to SQL and have been asked to perform a query of our Dispatch database. Although I am able to pull most of the information I need I am having trouble with the following.

The following queries work seperately on their own:

SELECT unit_status_datetime AS UnitLeftSceneDateTime
FROM unit_status_hist
WHERE cad_command_code = 'EH'

SELECT unit_status_datetime AS UnitLeftSceneDateTime
FROM unit_status_hist
WHERE cad_command_code = 'AH'

Problem is I need to include these in a query that will be selecting multiple columns along with these.

Is there a way to specify multiple aliases for a single column a retrive the data I need?

Thank you for your time!
Chris Abbott
Luis Cazares
Luis Cazares
SSCrazy Eights
SSCrazy Eights (8.6K reputation)SSCrazy Eights (8.6K reputation)SSCrazy Eights (8.6K reputation)SSCrazy Eights (8.6K reputation)SSCrazy Eights (8.6K reputation)SSCrazy Eights (8.6K reputation)SSCrazy Eights (8.6K reputation)SSCrazy Eights (8.6K reputation)

Group: General Forum Members
Points: 8554 Visits: 18142
I don't understand the problem, but I believe you don't need multiple aliases.
Can you explain in more detail and give the exact problem? Or what do you mean by this:

Problem is I need to include these in a query that will be selecting multiple columns along with these.



Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?


How to post data/code on a forum to get the best help: Option 1 / Option 2
Lynn Pettis
Lynn Pettis
SSC-Insane
SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)

Group: General Forum Members
Points: 24207 Visits: 37978
dirtyshorellc (8/9/2012)
Hello,

I am new to SQL and have been asked to perform a query of our Dispatch database. Although I am able to pull most of the information I need I am having trouble with the following.

The following queries work seperately on their own:

SELECT unit_status_datetime AS UnitLeftSceneDateTime
FROM unit_status_hist
WHERE cad_command_code = 'EH'

SELECT unit_status_datetime AS UnitLeftSceneDateTime
FROM unit_status_hist
WHERE cad_command_code = 'AH'

Problem is I need to include these in a query that will be selecting multiple columns along with these.

Is there a way to specify multiple aliases for a single column a retrive the data I need?

Thank you for your time!
Chris Abbott


Not knowing how these are being used in the overall query, hard to give you a working answer.

Looking at these queries as is, the follow are equivalent:



SELECT unit_status_datetime AS UnitLeftSceneDateTime
FROM unit_status_hist
WHERE cad_command_code = 'EH' OR cad_command_code = 'AH';

SELECT unit_status_datetime AS UnitLeftSceneDateTime
FROM unit_status_hist
WHERE cad_command_code in ('EH','AH');




Perhaps if you showed us what it is you are trying to accomplish we could give you a better answer.

Cool
Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
dirtyshorellc
dirtyshorellc
Forum Newbie
Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)

Group: General Forum Members
Points: 2 Visits: 19
My appologies.

What I am doing is creating a query that will simply take certain columns from a table and export them to a csv file for export to another server. The export is for EMS calls and the data will include all of the unit status information for the incident. All of the other columns that I need to include in this query I'm able to retrieve with no issue. The problem is that I only need 2 types of data from the column I listed and I need to be able to seperate the retrived data and create a seperate column for each data type in the export file.

Here is what I have so far:

SELECT
incident_num AS IncidentNumber, incident_date AS IncidentDateTime, e911_time AS PsapCallDateTime,
rec_time AS DispatchNotifiedDateTime, disp_time AS UnitNotifiedDispatchDateTime, enr_time AS UnitEnRouteDateTime,
arv_time AS UnitArrivedSceneDateTime,
-- This is where the CAD_COMMAND_CODE for UnitLeftScene and PatientArrivedDest fields will live.


clr_time AS UnitBackInServiceDateTime, location AS StreetAddress, address AS StreetAddress2,
city_code AS City, call_type AS EmdCardNumber, disposition_code AS DestinationCode

FROM
cfs_incident_core

As you can see I am aliasing each column of the table to match the expected format for the other server.

Also I seem to have made a mistake in my first post. Each query should have a different Alias. The query for the 'AH' data should have been aliased as PatientArrivedDestDateTime. Again my apologies!

Also I'm using MS SQL SVR 2000.
dwain.c
dwain.c
SSCarpal Tunnel
SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)

Group: General Forum Members
Points: 4255 Visits: 6431
dirtyshorellc (8/9/2012)
My appologies.

What I am doing is creating a query that will simply take certain columns from a table and export them to a csv file for export to another server. The export is for EMS calls and the data will include all of the unit status information for the incident. All of the other columns that I need to include in this query I'm able to retrieve with no issue. The problem is that I only need 2 types of data from the column I listed and I need to be able to seperate the retrived data and create a seperate column for each data type in the export file.

Here is what I have so far:

SELECT
incident_num AS IncidentNumber, incident_date AS IncidentDateTime, e911_time AS PsapCallDateTime,
rec_time AS DispatchNotifiedDateTime, disp_time AS UnitNotifiedDispatchDateTime, enr_time AS UnitEnRouteDateTime,
arv_time AS UnitArrivedSceneDateTime,
-- This is where the CAD_COMMAND_CODE for UnitLeftScene and PatientArrivedDest fields will live.


clr_time AS UnitBackInServiceDateTime, location AS StreetAddress, address AS StreetAddress2,
city_code AS City, call_type AS EmdCardNumber, disposition_code AS DestinationCode

FROM
cfs_incident_core

As you can see I am aliasing each column of the table to match the expected format for the other server.

Also I seem to have made a mistake in my first post. Each query should have a different Alias. The query for the 'AH' data should have been aliased as PatientArrivedDestDateTime. Again my apologies!

Also I'm using MS SQL SVR 2000.


Perhaps something like this?


SELECT unit_status
UnitLeftSceneDateTime=MAX(CASE WHEN cad_command_code = 'EH' THEN unit_status_datetime END)
PatientArrivedDestDateTime=MAX(CASE WHEN cad_command_code = 'AH' THEN unit_status_datetime END)
FROM unit_status_hist
GROUP BY unit_status





My mantra: No loops! No CURSORs! No RBAR! Hoo-uh!

My thought question: Have you ever been told that your query runs too fast?

My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.


Need to UNPIVOT? Why not CROSS APPLY VALUES instead?
Since random numbers are too important to be left to chance, let's generate some!
Learn to understand recursive CTEs by example.
Splitting strings based on patterns can be fast!
My temporal SQL musings: Calendar Tables, an Easter SQL, Time Slots and Self-maintaining, Contiguous Effective Dates in Temporal Tables
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search