Monday, February 2, 2015

Crystal Reports : What you need to know about SQL Expression Fields

I'll be honest, I rarely use these. But they can be really powerful on a MSSQL back-end. The first thing you need to know is that they allow you to submit TSQL directly to the server.

So, if I was after the Year value of a date field, the Crystal Formula would look like this;

YEAR({TABLE.DATEFIELD})

...where the SQL Expression would use native Transact SQL, like this;

DATEPART(yyyy, TABLE.DATEFIELD)

The big difference is how these two recordsets are generated. If I plug this into the Select Expert using Crystal Formulas, ALL records are downloaded from the server, and THEN the formula is processed. If you use the SQL Expression, it becomes a part of the Crystal SQL Statement, and only SELECTs the appropriate rows.

But it wouldn't work with, say, Access, or Interbase. The SQL Expressions are server-specific. What works with MSSQL may need a different syntax for Oracle or mySQL. But if you're reporting against millions of rows, this could dramatically reduce refresh times.

I've also seen it used in hyper-specialized environments to call Custom SQL Functions, as the SQL Expression is just a code-delivery system. Any exposed SQL object, including three-part qualifiers, should be allowed. I.e. DATABASE.DBO.TABLE.

2 comments:

Anonymous said...

Given that the sit back and watch trade is constantly on the flourish, you can get alot more new developments that can be to be brought in at present just like the promotional of that been refined as a result of unique longines replica. Much more a man or woman would most likely include the obstacle what exactly that swiss-quality-watch significant difference in between prime quality cartier replica in addition to honest sit back and watch? From this context, that shopkeeper contains stated to usa the fact that the very important certainly is the distinctive chanel replica handbags. That move belonging to the classic sit back and watch often is the classic brought in. Nevertheless reliable tag heuer replica store is simply not for instance the classic sit back and watch. In the several other, the additional resources in the hublot replica in addition to honest classic sit back and watch is definitely the equivalent.

林磊 said...

2016-2-20 leilei
air max 90
nike cortez shoes
michael kors outlet
canada goose jackets
toms shoes
christian louboutin outlet
adidas wings
michael kors outlet
ray ban sunglasses
louis vuitton handbags
coach factory outlet online
louis vuitton outlet stores
michael kors outlet
michael kors handbags
jordan 3 retro
nike air max 90
cheap toms
ralph lauren
nike store
nike blazers uk
true religion outlet
coach factory outlet
true religion outlet
timberland boots
ugg boots
cheap jordans
true religion jeans
kate spade outlet
coach outlet online
kate spade handbags
prada handbags
polo ralph lauren
louis vuitton outlet
louboutin pas cher
true religion jeans
nike roshe run
jordan retro 13
jordan shoes
jordan pas cher
michael kors outlet