<%@ Page Language="c#" %>
<%@ Register TagPrefix="uc1" TagName="Trailer" Src="Trailer.ascx" %>
<%@ Import Namespace="System.IO" %>
<%@ Import Namespace="System.Web" %>
<%@ Import Namespace="edu.jhu.Cas.Util" %>
<%@ Register TagPrefix="uc2" TagName="Header" Src="top.ascx" %>
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN" >
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
    <style type="text/css" media="screen"><!--
#righty {
	position:absolute;
	top: 95;
	left:182px;
	width:640;
	text-align:left;
	
}
#lefty{
	position: absolute;
	left: 3px;
	top: 95;
	width:158px;
}
--></style>
    <link href="cjobs.css" rel="stylesheet" />
    <title>CasJobs Advanced Queries</title>
</head>
<body>
    <uc2:Header ID="Header" runat="server" />
    <div id="lefty">
        <h4>
            Help</h4>
        <table cellpadding="3">
            <tr>
                <td>
                    <img  src="img/helppic.jpg"></td>
            </tr>
            <tr>
                <td>
                    <a href="#var">Variables</a></td>
            </tr>
            <tr>
                <td>
                    <a href="#enum">Enumerator column</a></td>
            </tr>
            <tr>
                <td>
                    <a href="#comp">Compound Queries</a></td>
            </tr>
            <tr>
                <td>
                    <a href="#crossapply">Nearest Neighbor Search (Cross-ID)</a></td>
            </tr>
            <tr>
                <td>
                    <a href="#pics">Thumbnails </a>
                </td>
            </tr>
            <tr>
                <td>
                    <a href="#nbor">Neighbors Search (old)</a></td>
            </tr>
            <tr>
                <td>
                    <a href="#cursors">Using Cursors</a></td>
            </tr>
        </table>
    </div>

    <div  id="righty">
	<ol>
	<li>
	<a name="var"></a>
        <h3 name="var">Using variables in your query</h3>
        <p>
 	    You can declare variables in your query buffer and use them in your query using the SQL
            <b>declare</b> statement.  All variable names must be preceded by the '@' character.  An
            example of a query using variables is shown below (courtesy of Dimitri Pourbaix).  
        </p>
        <p class="code">
declare @BRIGHT bigint      set @BRIGHT=dbo.fPhotoFlags('BRIGHT')	<br>
declare @EDGE bigint      set @EDGE=dbo.fPhotoFlags('EDGE')		<br>
declare @SATURATED bigint set @SATURATED=dbo.fPhotoFlags('SATURATED')	<br>
declare @NODEBLEND bigint set @NODEBLEND=dbo.fPhotoFlags('NODEBLEND')	<br>
-- <br>
declare @bad_flags bigint set						<br>
@bad_flags=(@SATURATED|@BRIGHT|@EDGE|@NODEBLEND)			<br>
<br>
select run,rerun,camcol,field,obj,colc,rowc,parentID,nChild,ra,dec,	<br>
&nbsp;&nbsp;&nbsp; extinction_r,psfMag_u,psfMag_g,psfMag_r,psfMag_i,psfMag_z, 		<br>
&nbsp;&nbsp;&nbsp; psfMagErr_u,psfMagErr_g,psfMagErr_r,psfMagErr_i,psfMagErr_z,rowv,	<br>
&nbsp;&nbsp;&nbsp; colv,rowvErr,colvErr,rowc_u,colc_u,rowc_g,colc_g,rowc_r,colc_r,	<br>
&nbsp;&nbsp;&nbsp; rowc_i,colc_i,rowc_z,colc_z,offsetRa_u,offsetDec_u,offsetRa_g,	<br>
&nbsp;&nbsp;&nbsp; offsetDec_g,offsetRa_r,offsetDec_r,offsetRa_i,offsetDec_i,		<br>
&nbsp;&nbsp;&nbsp; offsetRa_z,offsetDec_z						<br>
into MyDB.CID										<br>
from Star										<br>
where (flags & @bad_flags) = 0 and nChild=0						<br>
&nbsp;&nbsp;&nbsp;       and psfMag_u>0 and psfMag_u<21 and psfMag_g>0 and psfMag_g<21	<br>
&nbsp;&nbsp;&nbsp;       and abs(psfMagErr_u)<=0.1 and abs(psfMagErr_r)<=0.1		<br>
&nbsp;&nbsp;&nbsp;       and abs(psfMagErr_g)<=0.05 and abs(psfMagErr_i)<=0.05		<br>
&nbsp;&nbsp;&nbsp;       and abs(psfMagErr_z)<=0.05					<br>
&nbsp;&nbsp;&nbsp;       and sqrt((offsetRa_u-offsetRa_z)*(offsetRa_u-offsetRa_z)	<br>
&nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp;        *cos(dec*0.01745)*cos(dec*0.01745)		<br>
&nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp;        +(offsetDec_u-offsetDec_z)*(offsetDec_u-offsetDec_z))>=0.5
	</p>
	<hr>
	</li>

	<li>
	<a name="enum"></a>
        <h3 name="enum">Adding an INT enumerator column to your MyDB table</h3>
	<p>You can add an int enumerator easily as follows, by making a new table to copy your 
	   existing table into along with the new enumerator column:
	<p class="code">
create table table2 ( <br>
&nbsp;&nbsp;&nbsp;   int_id int identity(1,1), <br>
&nbsp;&nbsp;&nbsp;   ra float, <br>
&nbsp;&nbsp;&nbsp;   dec float, <br>
&nbsp;&nbsp;&nbsp;   objid bigint <br>
) <br>
insert table2( ra,dec,objid)  <br>
select ra,dec,objid <br>
from table1
</p>
	<p>You shd run this in the MyDB context.  <b>table2</b> will then have an <i>int_id</i> field
	filled with consecutive integers.

	</li>

	<li>
	<a name="comp"></a>
        <h3 name="comp">Compound Queries with GO statement</h3>
        <p>
            You can submit compound queries in the query buffer, i.e., more than one query
            in the buffer, separating individual queries with the GO statement.  The GO statement tells the server
            to immediately execute that query before going on to the next one.  This allows, for example, a table
            to be created or deleted before running a query that writes data to it.  
	</p>
	<p>
	    A simple example of a compound query is shown below.  It must be run in the MYDB context because it
	    is not possible to drop a table remotely in another database, so the drop table cannot be run in the
	    non-MYDB context.
	</p>
	<p class="code">
	    drop table mytable_2<br>
	    go<br><br>

	    select top 10 objid, ra, dec into mytable_2<br>
	    from dr5.photoobj			-- remotely gets data from DB mapped to DR5 context<br>
	    go<br>
	</p>

	</li>
	<li>
	<a name="crossapply"></a>
	<h3 name="crossapply">Nearest Neighbor Search (Cross-ID): Table-valued Functions without
	    Cursors </h3>
	<p>
	    This is an example of using a table-valued function
	    (functions that return a table of values rather than a
	    single scalar value) in a
	    query and applying its results to a query on another table.
	    In this case we do a nearest neighbor search, and we use the
            <b>CROSS APPLY</b> and <b>OUTER APPLY</b> SQL constructs.
	    You can use the same prescription to apply the results of
	    other table-valued functions.  
	</p>
	<p>
	    Assume that you have already created or imported a MyDB
	    table called MyRaDecPairs that contains a list of ra,dec
	    pairs for the locations that you want to find neighbors.  
            The table also has an "id" as the first column that is
            either <a href="#enum">an enumerator</a> or some other unique id for each point.
	    The first version of the nearest neighbor query returns
            only the matching SDSS objects, whereas the second version
            (using the OUTER APPLY instead of CROSS APPLY) also returns
            non-matches.
	</p>
	<p>Version 1:</p>
	<p class="code">
SELECT <br>
&nbsp;&nbsp;   m.id, m.ra AS ra1, m.dec AS dec1, <br>
&nbsp;&nbsp;   n.objid, n.distance, <br>
&nbsp;&nbsp;   o.ra AS ra2, o.dec AS dec2 <br>
FROM MyDB.MyRaDecPairs AS m <br>
&nbsp;&nbsp;  CROSS APPLY dbo.fGetNearestObjEq( m.ra, m.dec, 0.5) AS n <br>
&nbsp;&nbsp;  JOIN PhotoObj AS o ON n.objid=o.objid <br>
	</p>
	<p>Version 2:</p>
	<p class="code">
SELECT <br>
&nbsp;&nbsp;   m.id, m.ra AS ra1, m.dec AS dec1, <br>
&nbsp;&nbsp;   n.objid, n.distance, <br>
&nbsp;&nbsp;   o.ra AS ra2, o.dec AS dec2 <br>
FROM MyDB.MyRaDecPairs AS m <br>
&nbsp;&nbsp;  OUTER APPLY dbo.fGetNearestObjEq( m.ra, m.dec, 0.5) AS n <br>
&nbsp;&nbsp;  LEFT JOIN PhotoObj AS o ON n.objid=o.objid <br>
	</p>
	<p>The second version will give output of the form:</p>
	<p class="code">
id      ra1     dec1    objid   distance        ra2                     dec2<br>
1       180     -0.5    1237648720693888083     0.188067537809051	180.000328568273        -0.496882808306584<br>
2       170     -0.15   1237648721226367920     0.257270246781359	169.999215045568        -0.15421537671495<br>
3       90      -20.5   null    null            null                    null<br>
4       110     30.5    1237673738862461148     0.157046405351005	110.001118266903        30.5024336440048<br>
5       300     -80.5   null    null            null                    null<br>
6       230     -70.5   null    null            null                    null
	</p>
	</li>
	<li>
	<hr>
        <a name="pics"></a>
        <h3>
            Putting Thumbnails in your table</h3>
        Since casjobs is all on the web you may link up the image tools to colums in your
        table. The following query adds thumbnail links to the objects in specobjall .
        <p class="code">
            select top 10 s.specobjid, s.z as sdssz ,s.ra,s.dec,<br>
            '&lt;a href=http://cas.sdss.org/dr3/en/tools/chart/navi.asp?ra='+<br>
            cast(s.ra as varchar(10))+<br>
            '&dec='+cast( s.dec as varchar(10)) +<br>
            '>'+<br>
            '&lt;img src="http://skyservice.pha.jhu.edu/dr8/ImgCutout/getjpeg.aspx?ra='<br>
            +cast(s.ra as varchar(15))+<br>
            '&dec='+cast(s.dec as varchar(15))+<br>
            '&scale=0.40&width=120&height=120&opt="/> </a>'<br>
            as pic
            <br>
            from specphotoall s
        </p>
	</li>
	<li>
        <a name="nbor"></a>
        <h3>On Neighbors Search</h3>
	    <b>NOTE: This is an older way to do neighbors search, see
	    the <a href="#crossapply">Nearest Neighbor Search example above using CROSS APPLY</a>
	    for the fastest way to do this type of search.</b><br>
            The neighbors search query is a macro from the MYDB page that searches for objects
            around every object in a table, given a certain radius. It is accessable by clicking
            on any table in your MYDB with columns named 'ra' and 'dec', then clicking the search
            button. By default, it returns only the objid of each object matched. Below are
            a few examples of modifying this query to return more information.<br>  
	    <b><i>These examples must be followed as closely as possible. You can substitute your own names
	    for the MyDB tables involved, but everything else, especially the sequence, contexts
	    etc. must be followed exactly as described below.</i></b>
        </p>
        <h4 name="ex1">
            Fixed Radius Search</h4>
        <p>
            This example shows how to do a fixed radius (same search radius for all objects) neighbors search 
	    and add extra columns to the result.</p>
        <p>
            a) First, create a new table in your MYDB (select context MYDB and run a command like
            the following), in this example it is called <b>MyTable_34</b>, but you can call it whatever
            you want:</p>
        <p class="code">
            CREATE TABLE MyTable_34 (<br />
            &nbsp;objid bigint,</br> &nbsp;ra float,<br />
            &nbsp;dec float,<br />
            &nbsp;search_id int,</br> &nbsp;matched_id bigint,<br />
            &nbsp;z real<br />
            );</br>
        </p>
        <p>
            Note that this table includes the extra spec column "z". Add whatever spec columns
            you want to the end of this table.
        </p>
        <p>
            b) Then get the neighbor query by running the neighbor search with your upload file
            and copying the query in the Query window. Paste that query in a new query buffer
            in context DR3. Then modify it as in the following example:
        </p>
        <p class="code">
            CREATE TABLE #UPLOAD(</br> &nbsp;up_ra FLOAT,</br> &nbsp;up_dec FLOAT,</br> &nbsp;up_id
            int</br> )</br> INSERT INTO #UPLOAD</br> SELECT RA AS UP_RA,DEC AS UP_DEC,search_id
            AS UP_ID</br> FROM MYDB.MyTable_32</br> </br> CREATE TABLE #tmp (</br> &nbsp; up_id
            int,</br> &nbsp; objid bigint</br> )</br> </br> INSERT INTO #tmp</br> EXEC spgetneighbors
            1</br> INSERT INTO MYDB.MyTable_34</br> select a.*,t.objid as matched_id, s.z <br>from
            #tmp t <br>&nbsp;&nbsp;JOIN MYDB.MyTable_32 a ON t.up_id = a.search_id
	    <br>&nbsp;&nbsp;JOIN specobj s ON s.bestobjid=t.objid</br>
        </p>
        <p>
            Note that the MyTable name has been manually set to MyTable_34, added "s.z" to the
            select list (again, add more columns here if you need), "specobj s" to the from,
            and "and s.bestobjid=t.objid" to the where. You need to run this query in the DR3
            context using the Submit button (wont work with Quick).
        </p>
        <h4 name="ex2">
            Variable Radius Search</h4>
        <p>
            Here is a way to do a proximity search in casjobs on a list of ra,dec pairs with
            variable search radius. You should have an ra,dec table (called <b>xrayradii</b> in this 
	    example, but you can call it what you want). For spGetNeighborsRadius, you have to add 
	    another column up_rad to the #upload table.  Your ra,dec MyDB table needs to have at least the
	    columns that are in the first SELECT statement in b) below (<i>ra,dec,xrayradius</i> and 
	    <i>cluster</i> in this example).
            Note that this requires that <i>xrayradii.cluster</i> is an <b>int</b> id that identifies the
            cluster.
        </p>
        <p>
            a) Create the table to hold the results:</p>
        <p class="code">
            CREATE TABLE MyTable_45 (<br>
            &nbsp;ra float,<br>
            &nbsp;dec float,<br>
            &nbsp;rad float,<br>
            &nbsp;cluster int,<br>
            &nbsp;objid bigint,<br>
            );
        </p>
        <p>
            b) Run the neighbors search for variable radius, saving results into table created
            in a):</p>
        <p class="code">
            CREATE TABLE #UPLOAD(<br>
            &nbsp;up_ra FLOAT,<br>
            &nbsp;up_dec FLOAT,<br>
            &nbsp;up_rad FLOAT,<br>
            &nbsp;up_id int<br>
            )<br>
            INSERT INTO #UPLOAD<br>
            SELECT ra AS UP_RA,<br>
            &nbsp;dec AS UP_DEC,<br>
            &nbsp;xrayradius as UP_RAD,<br>
            &nbsp;cluster AS UP_ID<br>
            FROM MYDB.XrayRadii<br>
            <br>
            CREATE TABLE #tmp (<br>
            &nbsp; up_id int,<br>
            &nbsp; objid bigint<br>
            )<br>
            INSERT INTO #tmp<br>
            EXEC spGetNeighborsRadius<br>
            <br>
            INSERT INTO MYDB.MyTable_45<br>
            SELECT a.*, t.objid<br>
            FROM #tmp t <br>
	    &nbsp;&nbsp;JOIN MYDB.XrayRadii a ON t.up_id = a.cluster<br>
        <h4 name="ex3">
            Nearest Neighbor Search</h4>
        <p>
	    Look at the <a href="#crossapply">Nearest Neighbor Search
	    </a> example above to see how to do a nearest
            neighbor search, i.e. to fetch only the one nearest match
	    for each position in the input list. 
	<hr>
	</li>
	<li>
            <a name="cursors"></a>
            <h3 name="cursors">
                Cursors and Table-valued Functions</h3>
	    <b>NOTE: This is the old way to work with table-valued
	    functions, see the <a href="#crossapply">example above using CROSS APPLY</a> for
	    the new (and much faster) way to do this.</b><br>
            In order to use variables with table-valued functions like fGetNearestObjEq, you need to
	    define a <b>cursor</b> and call the function from within the cursor body.  A cursor is
	    basically a loop on each row that matches a given SELECT statement.  Here MyTable_71 is created
            in a separate casjobs query in the MYDB context as:<br>
        </p>
        <p class="code">
            CREATE MyTable_71 (<br>
            regionid bigint not null,<br>
            type varchar(16) not null<br>
            )
        </p>
        <p>
            MyTable_70 contains ra,dec columns and can be imported or created with another casjobs query, 
	    to get the ra,dec from phototag, for example.  You can even use a table in the non-MyDB
	    context instead of a MyDB table, i.e., you can define the cursor directly on the DR5 phototag
	    table, for instance. fGetNearestObjEq is a table-valued function that returns certain properties 
	    of the nearest object to the given ra,dec as a table.  Finally, you run your cursor query in the
	    DR5 context:
        </p>
        <p class="code">
            declare @ra float, @dec float;<br>
            <br>
            DECLARE my_cursor cursor read_only<br>
            FOR<br>
		SELECT ra,dec FROM MYDB.MyTable_70<br>
	    -- this could be something like:<br>
	    -- &nbsp;&nbsp; "SELECT TOP 100 ra,dec FROM Star", for instance<br>
            OPEN my_cursor<br>
            --<br>
            WHILE(1=1)<br>
            BEGIN<br>
            &nbsp;&nbsp;FETCH NEXT from my_cursor into @ra, @dec<br>
            &nbsp;&nbsp;IF (@@fetch_status &lt; 0) break<br>
            &nbsp;&nbsp;INSERT MYDB.MyTable_71<br>
            &nbsp;&nbsp;SELECT * FROM<br>
            dbo.fGetNearestObjEq(@ra,@dec,1.5)<br>
END<br>
--------------------<br>
-- close the cursor<br>
--------------------<br> 
CLOSE my_cursor<br>
            DEALLOCATE my_cursor
        </p>
        <p>
            You will need to run this in the long queue (with the <b>Submit</b> button, not <b>Quick</b>).
        </p>
	<hr>
	</li>
	</ol>
        <uc1:Trailer ID="Trailer1" runat="server" cvsTag="$Name$" cvsRev="$Revision: 151075 $">
        </uc1:Trailer>
    </div>
</body>
</html>
