sql query in sql2005

Question

0.00/5 (No votes)

See more:

SQL-Server-2005

can any one say what does this 0 and one 1 does here near the GPS_DATETIME and also whether the values get inserted

SQL

 insert into @temp          
   SELECT REGISTRATIONNO,GPS_DATETIME,SPEED,          
   CASE WHEN BASESTATIONNAME IS NULL THEN LOCATION ELSE BASESTATIONNAME END AS LOCATION,          
   LAT_METER,LON_METER,ODOMETER       
   FROM GPSDATA_HISTORY          
   WHERE          
   GPS_DATETIME BETWEEN @v_from_date AND @v_to_date          
   and registrationno=@v_reg_no          
             
           
    union           
 select REGISTRATIONNO,GPS_DATETIME,SPEED,          
  LOCATION,          
   LAT_METER,LON_METER,ODOMETER from (SELECT TOP 1 REGISTRATIONNO,GPS_DATETIME,1 as speed,          
   CASE WHEN BASESTATIONNAME IS NULL THEN LOCATION ELSE BASESTATIONNAME END AS LOCATION,          
   LAT_METER,LON_METER,ODOMETER          
   FROM GPSDATA_HISTORY          
   WHERE GPS_DATETIME BETWEEN @v_from_date AND @v_to_date          
   and registrationno=@v_reg_no order by gps_Datetime desc)b      



    
union           
 select REGISTRATIONNO,GPS_DATETIME,SPEED,          
  LOCATION,          
   LAT_METER,LON_METER,ODOMETER from (SELECT TOP 1 REGISTRATIONNO,GPS_DATETIME,0 as speed,          
   CASE WHEN BASESTATIONNAME IS NULL THEN LOCATION ELSE BASESTATIONNAME END AS LOCATION,          
   LAT_METER,LON_METER,ODOMETER          
   FROM GPSDATA_HISTORY          
   WHERE GPS_DATETIME BETWEEN @v_from_date AND @v_to_date          
   and registrationno=@v_reg_no order by gps_Datetime desc)c

Posted 3-Nov-11 18:27pm

ashok_89

Updated 3-Nov-11 19:43pm

P.Salini

v4

Add a Solution

2 solutions

Solution 1

Select statements usually get their data from columns and you can change the column name to something more readable by using the as key word, sometimes your column values are computed and not directly from the table itself.

This is the case with the 0, and it means show a column in the output and put the value zero in it.

Posted 3-Nov-11 18:54pm

Mehdi Gholam

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Amir Mahfoozi · Accepted Answer · 2011-11-03T18:57:00

Zero would be the data in third column which is named speed.

May be they are going to do some more action on the speed field and it should start from zero :-?

If you see further operations on this SQL you may find out why they need a zero as the initial value for speed.

Edit : after you provided more SQL statements :

It is because they UNIONed 3 tables and they need to have equal number of fields and datatypes.

But something that is very strange is that the second and third query return only on record with the same data and it is the last happened event in GPS_DATETIME table which happened between @v_from_date AND @v_to_date
But the second table has the zero speed and the third SQL result have the speed of 1.

So you have always this in output
R1
R2
.
.
.
RN
RN(speed=0)
RN(speed=1)
*if the output of the first SQL was sorted by GPS_DATETIME

So it seems that you need to track the SQL result and see where its used and how they used the last two records :-?

Hope this helps.