SELECT order and CASE WHEN THEN & MERGE queries

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)

SELECT order and CASE WHEN THEN & MERGE queries

Author

Topic

drivensql
Starting Member

9 Posts

Posted - 2011-01-31 : 12:43:29

TABLE1:

Account float
[Activity Date] datetime
Reference numeric(8, 0)
Cusip nvarchar(255)
Security nvarchar(255)
Contra nvarchar(255)
[D/C] nvarchar(255)
Par float
Money float
Currency nvarchar(255)
Acct float
[Account Name] nvarchar(255)
DEBIT float
CREDIT float

TABLE2:

Account float
[Activity Date] datetime
[D/C] nvarchar(255)
[Opening Balance] float
Currency nvarchar(255)
[Closing Balance] float


INSERT INTO [TABLE2]
SELECT     Account, [Activity Date], [D/C], Money AS [Opening Balance], NULL AS [Closing Balance], Currency
FROM          [TABLE1]
WHERE     ([D/C] = N'C') AND (Security = N'Opening Balance') AND (Currency = N'DOM') AND (Account = 0)

Msg 8114, Level 16, State 5, Line 1
Error converting data type nvarchar to float.

*UPDATED TITLE TO REFLECT LATEST QUESTION REPLY*

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2011-01-31 : 12:48:46

Change the position of Currency and [Closing Balance] in your select list.

No, you're never too old to Yak'n'Roll if you're too young to die.

dataguru1971
Master Smack Fu Yak Hacker

1464 Posts

Posted - 2011-01-31 : 12:55:03

You are trying to insert the Currency source column into a float column. Change the order of the select to match the structure, and/or specify the order of columns in the correct way..

[code]
INSERT INTO [TABLE2] (Account,[Activity Date],[D/C], [Opening Balance], Currency,[Closing Balance])
SELECT Account, [Activity Date], [D/C], Money AS [Opening Balance], Currency ,NULL AS [Closing Balance]
FROM [TABLE1]
WHERE ([D/C] = N'C') AND (Security = N'Opening Balance') AND (Currency = N'DOM') AND (Account = 0)
[code]

[D/C] column should be likely only 1 character ('D' or 'C' I am guessing is the values for debit/credit)

So I am guessing you imported this terrible structure from excel.

Currency ...not likely you need a 255 nvarchar field there either.

Dollar amounts should be stored as Money datatype or numeric datatype with proper precision of 2 decimal places.

Account should also not be a "float" unless account numbers have decimal places. You can likely use INT for this field's datatype.

Poor planning on your part does not constitute an emergency on my part.

drivensql
Starting Member

9 Posts

Posted - 2011-01-31 : 14:35:06

Thank you webfred, for sharing the importance of order in SELECT.

And thank you dataguru1971, for clarifying the importance of order and your datatype advice, which I will optimize.

I have a follow up IF ELSE query, that I'm not sure how to structure... I haven't found any examples of IF ELSE with SELECT and INSERTS:

[CODE]
INSERT INTO [TABLE2]
FROM [TABLE1]
IF ([D/C] = N'C') AND (Security = N'Opening Balance') AND (Currency = N'DOM') AND (Account = 0)
SELECT Account, [Activity Date], [D/C], Money AS [Opening Balance], Currency, NULL AS [Closing Balance]

ELSE IF ([D/C] = N'C') AND (Security = N'Closing Balance') AND (Currency = N'DOM') AND (Account = 0)
SELECT Account, [Activity Date], [D/C], NULL AS [Opening Balance], Currency, Money AS [Closing Balance]

ELSE IF ([D/C] = N'D') AND (Security = N'Opening Balance') AND (Currency = N'DOM') AND (Account = 0)
SELECT Account, [Activity Date], [D/C], -Money AS [Opening Balance], Currency, NULL AS [Closing Balance]

ELSE IF ([D/C] = N'D') AND (Security = N'Closing Balance') AND (Currency = N'DOM') AND (Account = 0)
SELECT Account, [Activity Date], [D/C], NULL AS [Opening Balance], Currency, -Money AS [Closing Balance]
[/CODE]

dataguru1971
Master Smack Fu Yak Hacker

1464 Posts

Posted - 2011-01-31 : 14:46:39

[code]
INSERT INTO [TABLE2]
FROM [TABLE1]
SELECT
Account,
[Activity Date],
[D/C],
[Opening Balance] =Case WHen [Security] = N'Opening Balance' and [D/C]= 'C' then [Money]
WHen [Security] = N'Opening Balance' and [D/C]= 'D' then -[Money]
else NULL END ,
Currency,
[Closing Balance] = Case WHen [Security] = N'Closing Balance' and [D/C]= 'C' then [Money]
WHen [Security] = N'Closing Balance' and [D/C]= 'D' then -[Money]
else NULL END
FROM Table1
Where [D/C] in ('D','C') and [Account] = 0
[/code]

That does what you need. YOu can change what values are being selected in one statement using Case When. You cannot use If statements in the fashion you attempted. You can do stuff like this and the code between begin and end are executed if the condition is true.
[code]
IF ([D/C] = N'C') AND (Security = N'Opening Balance') AND (Currency = N'DOM') AND (Account = 0)
BEGIN
INSERT INTO [TABLE2]
FROM [TABLE1]
SELECT Account, [Activity Date], [D/C], Money AS [Opening Balance], Currency, NULL AS [Closing Balance]
END
[/code]

But you can use something like what I posted above instead as it is more efficient. Be sure to edit as needed
You should also no that when you are creating a table or typing a column and the word turns BLUE it is a keyword..you shouldn't use keywords in column names (Like money and security.

Poor planning on your part does not constitute an emergency on my part.

drivensql
Starting Member

9 Posts

Posted - 2011-01-31 : 15:24:54

Tried restarting/reconnecting to the server and still getting this error:

Msg 156, Level 15, State 1, Line 2
Incorrect syntax near the keyword 'FROM'.


INSERT INTO [TABLE2]
FROM         [TABLE1]
SELECT     
Account, [Activity Date], [D/C],  
[Opening Balance] = Case 
When [Security] = N'Opening Balance' and [D/C]= 'C' then [Money]
When [Security] = N'Opening Balance' and [D/C]= 'D' then -[Money] 
else NULL END, 
Currency, 
[Closing Balance] = Case 
When [Security] = N'Closing Balance' and [D/C]= 'C' then [Money] 
When [Security] = N'Closing Balance' and [D/C]= 'D' then -[Money] 
else NULL END
Where [D/C] in ('D','C') and [Account] = 0

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2011-01-31 : 15:41:42

INSERT INTO [TABLE2]

SELECT <columns>

FROM [TABLE1]
WHERE <these things apply>

Jim

Everyday I learn something that somebody else already knew

dataguru1971
Master Smack Fu Yak Hacker

1464 Posts

Posted - 2011-01-31 : 15:42:27

you missed my edit..I was missing the FROM Table1 below (above the WHERE)



INSERT INTO [TABLE2]
FROM         [TABLE1]
SELECT     
Account, 
[Activity Date], 
[D/C], 
 [Opening Balance] =Case WHen [Security] = N'Opening Balance' and [D/C]= 'C' then [Money]
						 WHen [Security] = N'Opening Balance' and [D/C]= 'D' then -[Money] 
					else NULL END , 
Currency, 
[Closing Balance] = Case WHen [Security] = N'Closing Balance' and [D/C]= 'C' then [Money]
						 WHen [Security] = N'Closing Balance' and [D/C]= 'D' then -[Money] 
					else NULL END
FROM Table1
Where [D/C] in ('D','C') and [Account] = 0

Poor planning on your part does not constitute an emergency on my part.

drivensql
Starting Member

9 Posts

Posted - 2011-01-31 : 16:06:22

Thank you webfred, jimf and dataguru1971! Learned a lot today :)

Was hoping to get this query to output to 1 record in the new table, but at least I can move forward with a follow up query to pull from the 2 records per balance/account.

My final query, in case this happens to help someone else:

[CODE]
INSERT INTO [TABLE2]
SELECT
Account, [Activity Date],
[Opening Balance] = CASE
WHEN [Security] = N'Opening Balance' and [D/C]= 'C' THEN [Money]
WHEN [Security] = N'Opening Balance' and [D/C]= 'D' THEN -[Money]
ELSE NULL END,
Currency,
[Closing Balance] = CASE
WHEN [Security] = N'Closing Balance' and [D/C]= 'C' THEN [Money]
WHEN [Security] = N'Closing Balance' and [D/C]= 'D' THEN -[Money]
ELSE NULL END
FROM [TABLE1]
WHERE [Account] = 0 and Currency = N'DOM' and Security LIKE N'%Balance'
[/CODE]

dataguru1971
Master Smack Fu Yak Hacker

1464 Posts

Posted - 2011-01-31 : 17:15:24

Your welcome

This
Security IN( N'Closing Balance','N'Opening_Balance')

will be faster than the LIKE condition..use equality before using a wildcard preface search

Poor planning on your part does not constitute an emergency on my part.

drivensql
Starting Member

9 Posts

Posted - 2011-02-01 : 11:26:51

Right now I have a record for Opening Balance and another record for Closing Balance in this query, with alternating nulls in the 2 records. Is MERGE the best way of combining these two records into 1? Can I combine the records via the previous query?

dataguru1971
Master Smack Fu Yak Hacker

1464 Posts

Posted - 2011-02-01 : 17:08:35

Wrap the case when inside a SUM and add a group by..this would roll the values up into one row. Maybe..without sample data I can't confirm, but something like this might handle it for you.


INSERT INTO [TABLE2]
SELECT     
Account, [Activity Date], 
[Opening Balance] =SUM( CASE 
WHEN [Security] = N'Opening Balance' and [D/C]= 'C' THEN iSNULL([Money],0)
WHEN [Security] = N'Opening Balance' and [D/C]= 'D' THEN ISNULL(-[Money],0) 
ELSE NULL END), 
Currency, 
[Closing Balance] = SUM(CASE 
WHEN [Security] = N'Closing Balance' and [D/C]= 'C' THEN iSNULL([Money],0)
WHEN [Security] = N'Closing Balance' and [D/C]= 'D' THEN ISNULL(-[Money],0)
ELSE NULL END)
FROM [TABLE1]
WHERE [Account] = 0 and Currency = N'DOM' and Security LIKE N'%Balance'
GROUP BY Account,[Activity Date],Currency

Poor planning on your part does not constitute an emergency on my part.

drivensql
Starting Member

9 Posts

Posted - 2011-02-02 : 18:33:57

Seems to work great, other than this warning:
Warning: Null value is eliminated by an aggregate or other SET operation.

dataguru1971
Master Smack Fu Yak Hacker

1464 Posts

Posted - 2011-02-02 : 18:47:18

That's not a problem.

Poor planning on your part does not constitute an emergency on my part.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources