Home | Overview | Browser | Access | Login | Cookbook

SQL queries - notes and tips

• Image cut-out links
• Uploading and using a file as #userTable

select top 10 ra,dec,framesetID,hapermag3 from lasSource where hapermag3 > 0
or
select top 10 ra,dec,multiframeID,apermag3 from atlasDetection where apermag3 > 0

Another way of achieving a similar result can be combined with TOPCAT. This method requires you to build a column URL as part of the query eg

select top 10 cast ('http://osa.roe.ac.uk:8080/osa/GetImage?mode=show&database='+ db_name()+'&ra='+ CONVERT(varchar(17), cast(ra as decimal (17,6)) )+ '&dec='+CONVERT(varchar(17), cast(dec as decimal (17,5)))+ '&fsid='+convert(varchar(18),framesetid)as varchar(192)) as url,ra,dec from atlassource

Again the table you are querying needs to have the columns RA,Dec and framesetID/multiframeID. If querying detection tables rather than source tables i.e using multiframeID then it would be

select top 10 cast ('http://osa.roe.ac.uk:8080/osa/GetImage?mode=show&database='+ db_name()+'&ra='+ CONVERT(varchar(17), cast(ra as decimal (17,6)) )+ '&dec='+CONVERT(varchar(17), cast(dec as decimal (17,5)))+ '&mfid='+convert(varchar(18),multiframeid)as varchar(192)) as url,ra,dec from atlasDetection

The format that the query should be written to should be FITS or VOTable. Once loaded into TOPCAT click on the Activation Action button in the main window. Then check View URL as Web Page and select Web Page Locataion Column as URL and Browser Type as system browser the click OK. Now when viewing the table in TOPCAT if you click on a row the cut-out(s) should be displayed in your default browser.

Uploading and using a file as #userTable
The enhanced version of the SQL form allows a file to be uploaded as a temporary table, called #userTable, and used in the query. If column names are given in the file then the same names are used where possible in #userTable (some names might be reserved SQL keywords). If no column names are present then the columns are labelled col1 ... colN.
This functionality is useful if you have a long list of ID numbers eg multiframeIDs, sourceIDs that you wish to use in you query. For example if a file contained a column of sourceIDs then it could be uploaded and used with

select s.ra,s.dec,s.framesetid from atlasSource as s, #userTable as u where s.sourceID=u.sourceID

The temp table #userTable only lasts for the lifetime of the query.