SQL

SQL Server SELECT into existing table

by

This morning I needed to add a single record to an existing table in database1 from an existing table in database2. Here’s how I was able to do it:

INSERT INTO database1.dbo.tablename
     SELECT *  
     FROM 
          database2.dbo.tablename
     WHERE 
          ID='4d08aeb0bedd01452dfef3eabc2816dcc75533c8'

Please note that the databases use a SHA-1 Hash for the ID field. If you use an IDENTITY field in SQL you’d have to take some extra steps to briefly allow inserting an IDENTITY field.

ColdFusion 9 SELECT IN query using ormExecuteQuery()

by

For some reason this took me some effort to figure out. Maybe others will find it useful.


CategoryList = '1,2,3';
Categories = ormExecuteQuery("from Category where Id IN (:IdList)",{IdList=ListToArray(CategoryList)});

If you know a better way please drop me a comment. I posted this in the Adobe Coldfusion forum for a few days but didn’t get anything.

Oracle equivalent of SQL Server SELECT TOP (SELECT TOP n)

by

I had to do a small update on a site for work with an Oracle back end.  I don’t work with Oracle so I ran into a little snag.  I needed to find the Oracle equivalent of SQL Server’s “SELECT TOP”.  So, here it is:

MS SQL

SELECT TOP 10
field1, field2
FROM
tablename
ORDER BY
field1

ORACLE

SELECT *
FROM
(SELECT
field1, field2
FROM
tablename
ORDER BY field1)
WHERE
ROWNUM <= 10

Saving to a database with ColdFusion using jQuery

by

A commenter asked me how to extend a previous jQuery post to perform the database save via ColdFusion.  It is REALLY easy (and please keep in mind this is meant to be a SIMPLE example, not production code).  From the jQuery side my example only required the following code to ship the data off to ColdFusion:

function fSave(){
you=$("#you").val();
// Try to save
$.get("adduser.cfm?"+"you="+you , function(data){
// Result
alert(data);
window.location=location.href;
});
}

The adduser.cfm page then validates and inserts the data and returns a success or failure message.

See it here | Get it here.

CF quickie: cfqueryparam with SQL’s LIKE operator

by

If you are using cfqueryparam to build a SQL statement from a search form and need to use the SQL’s LIKE operator here is how it’s done in the most basic way:

<cfquery name="qData" datasource="myDsn">
SELECT DISTINCT last_name
FROM person
WHERE last_name LIKE <cfqueryparam cfsqltype="cf_sql_varchar" value="#FORM.last_name#%">
ORDER BY last_name
</cfquery>

The magic is all in the % sign at the end of the cfqueryparam value attribute.

ColdFusion Dying… Again

by

So I took some database training with Global Knowledge last week.¬† My office paid for the training.¬† Apparently that “back end” training may be all for naught though since my front end programming language is on life support.

Today’s Global Knowledge newsletter letter informed me (in very cheeky fashion) that ColdFusion is # 5 on their “Dying Technology” list:

“If any of these skills are your main expertise, perhaps it’s time to retrain.”

Here’s the whole article: http://www.globalknowledge.com/training/generic.asp?pageid=2347&country=United+States

The notion of ColdFusion being dead has been debated into the ground so I’m not going to belabor it.

IMHO, ColdFusion is the best thing that ever happened to me from an IT standpoint.¬† If you want to get things done quickly, easily and reliably use it.¬† If you are thinking of ColdFusion along with COBOL, Netware, Flannel Shirts and “Grunge” Bands From Seattle, and¬† you might want step outside your house more.¬† It’s not the mid 90’s anymore.

A few SQL queries

by

I had to do some basic analysis of email addresses at my company the other day.  Here are a few useful SQL queries.

Show 10 records with the most data in field ’email’ (10 longest email addresses)

SELECT TOP 10 email, LEN(email) AS fieldLength
FROM person
ORDER BY fieldLength DESC

Count all records with field ’email’ over 20 chars

SELECT COUNT(*) AS emailOver20
FROM person
WHERE LEN(email) > 20

Display the average length of data (in characters) of field ’email’

SELECT AVG(LEN(email)) AS emailAvgLength
FROM person

CFGRID DateFormat

by

For some reason when you have a datetime field in a cfgrid column it displays in a long format like this: Nov 17 2006 01:00:00

Fig 1 (Below) A date displayed in cfgrid

CFGRID DateFormat Before

If we work a little SQL magic (using “CONVERT”) in the cfquery though we can do the formatting we want, which is: 11/17/2006. Refer to the “styles” listed in Figure 3 to see what formatting is available.

Coldfusion Query:

SELECT note_id,note_text,
CONVERT(varchar,note_date,101) as note_date
FROM tbl_notes
ORDER BY note_date

Fig 2 (Below) A date displayed in cfgrid after using CONVERT in cfquery

CFGRID DateFormat After

Fig 3 (Below) A helpful list of “styles” to use with the SQL CONVERT function
Style ID Style Type
0 or 100 mon dd yyyy hh:miAM (or PM)
101 mm/dd/yy
102 yy.mm.dd
103 dd/mm/yy
104 dd.mm.yy
105 dd-mm-yy
106 dd mon yy
107 Mon dd, yy
108 hh:mm:ss
9 or 109 mon dd yyyy hh:mi:ss:mmmAM (or PM)
110 mm-dd-yy
111 yy/mm/dd
112 yymmdd
13 or 113 dd mon yyyy hh:mm:ss:mmm(24h)
114 hh:mi:ss:mmm(24h)
20 or 120 yyyy-mm-dd hh:mi:ss(24h)
21 or 121 yyyy-mm-dd hh:mi:ss.mmm(24h)
126 yyyy-mm-dd Thh:mm:ss.mmm(no spaces)
130 dd mon yyyy hh:mi:ss:mmmAM
131 dd/mm/yy hh:mi:ss:mmmAM

Prepend existing data in SQL

by

Disclaimer: This tip will probably be really basic for most SQL folks.

The task: The company I work for has a web application that does some basic tracking of grants. The system feeding information to this application just had an across the board change to the numbering scheme of the grants. All grants must have a prefix of “999-“. Therefore, any grant not beginning with “999-” must be updated. Example: a grant with the current number 8789966 needs to be 999-8789966.

The solution: The SQL below does two things. It updates all the grants to prepend the 999- prefix while also skipping any grants that are already correctly prefixed.


UPDATE tbl_grants
SET grant_no = '999-' + grant_no
WHERE grant_no NOT LIKE '999-%'