Using derived tables and subqueries

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Using derived tables and subqueries

Author

Topic

mcinvalek
Starting Member

5 Posts

Posted - 2014-06-02 : 11:50:47

I have a large table that I was forced to bring in via text tab delimited. Unfortunately, I do not have another option at this time. The table is 400k records and 600+ columns. The first step is to cast some numeric columns and then I need to sum a few of those columns and do some case statements on them as well. It is a pain to do this all in separate steps and I believe that I can use derived tables to do this in 1 query

The SQL below will fail cause I am using cast and then trying to sum on the newly casted column and because I am trying to exclude (in the where clause) based off of the newly created col3. If I break this down into separate steps I will have hundreds of lines of code cause I have to bring in the other 595 or so columns.

SELECT
CLAIMS1 = CAST(REPLACE(REPLACE(REPLACE([COL1],'(','-'),')',''),'$','') AS MONEY)
,CLAIMS2 = CAST(REPLACE(REPLACE(REPLACE([COL2],'(','-'),')',''),'$','') AS MONEY)
,TOTL_CLAIMS = COL1 + COL2
,COL3, COL4, COL5, COL6 --AND A FEW HUNDRED MORE COLS

FROM TABLE1
WHERE COL3 >= 1

Is there a way that I can execute this all in one sql statement? Perhaps with a combination of derived tables as well as subqueries?

SELECT * FROM
(((
SELECT
CLAIMS1 = CAST(REPLACE(REPLACE(REPLACE([COL1],'(','-'),')',''),'$','') AS MONEY)
,CLAIMS2 = CAST(REPLACE(REPLACE(REPLACE([COL2],'(','-'),')',''),'$','') AS MONEY)
)

SELECT
(
,TOTL_CLAIMS = COL1 + COL2
)

FROM TABLE 1
WHERE COL3 >= 1

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2014-06-02 : 12:31:17

I don't understand what you are trying to do, so I'm not sure I can help. Maybe try inserting into a temp table doing the necessary casts? We might be able to help you better if you if you show us some sample data and expected output. This might be a simple thing, but it sounds like you are limited because of the way SQL does it's evaluations.

If you are not familiar with how to post your sample data, here are some link to help you out:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2014-06-02 : 12:58:20

It's hard to know exactly what you want, but here's a sample of how you might use CROSS APPLY to your advantage:


select t2.CLAIMS1, t2.CLAIMS2, t3.TOTL_CLAIMS
from table1 t1
cross apply (
	select CLAIMS1 = CAST(REPLACE(REPLACE(REPLACE([COL1],'(','-'),')',''),'$','') AS MONEY) 
	      ,CLAIMS2 = CAST(REPLACE(REPLACE(REPLACE([COL2],'(','-'),')',''),'$','') AS MONEY)
	) t2
cross apply (select TOTL_CLAIMS = CLAIMS1 + CLAIMS2) t3

ScottPletcher
Aged Yak Warrior

550 Posts

Posted - 2014-06-02 : 16:41:07

I suggest an indexed view. First, create a view WITH SCHEMABINDING with all the base columns you need, with any REPLACE or other column changes as needed, but without trying to do any expressions/computations on columns yet.

Then create a unique clustered index on that view (the first index on a view must be a unique, clustered index). That view can contain columns from expressions, such as:
"COL1 + COL2 AS TOTL_CLAIMS".

Finally, write your query against the clustered index.

If you need help with this, just let me know.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources