PDA

View Full Version : Change positive number to negative for some items in a field



kariaheart
2011-10-26, 11:31 AM
Hi, have googled for a solution with no success so hoping to get help. Using Microsoft query through odbc connection have a table such as:

Item Type Qty
1 82 10
2 71 5
3 71 2

Qty values for Type 71 should be negative amounts. How can I amend the query to have the Type 71 display negative amounts while retaining the type 82's as positive amounts.

Thanks and kind regards

Ken Puls
2011-10-26, 05:33 PM
Hi there, and welcome to the forum.

Can you go into Microsoft Query, click the SQL button, and paste the SQL query here for us? Easier to edit when we see what you already have.

Surround it with code tags like this:
Your query here and it will format nicely for reading too. :)

kariaheart
2011-10-27, 12:13 AM
Hi Ken,

Thanks for this. Have not grasped the instructions about wraping in code. The IM_TYPE field is the one that has the codes 71 and 82 etc where 71 represents sales and comes through as a positive amount whereas want this to show as negative. 82 is item receipts into inventory.


SELECT Inventory_Movement_File.IM_ITMNO, Inventory_Movement_File.IM_TYPE, Inventory_Movement_File.IM_DATE,Inventory_Movement_File.IM_QTY,
FROM OAUSER.Inventory_Movement_File Inventory_Movement_File
WHERE (Inventory_Movement_File.IM_DATE>=? And Inventory_Movement_File.IM_DATE<=?) AND (Inventory_Movement_File.IM_QTY<>0)

Ken Puls
2011-10-27, 07:08 AM
Try this:


SELECT Inventory_Movement_File.IM_ITMNO, Inventory_Movement_File.IM_TYPE, Inventory_Movement_File.IM_DATE,
Inventory_Movement_File.IM_QTY*IIF(Inventory_Movement_File.IM_TYPE='82',1,-1)
FROM OAUSER.Inventory_Movement_File Inventory_Movement_File
WHERE (Inventory_Movement_File.IM_DATE>=? And Inventory_Movement_File.IM_DATE<=?)
AND (Inventory_Movement_File.IM_QTY<>0)

kariaheart
2011-10-28, 02:57 AM
Hi Ken,

I get this error message " Syntax error in SQL statement. syntax error line 1 at or after token <IM_TYPE>". Only now am I dipping my toes in SQL so have no idea what the error message is. Grateful for further help.

Ken Puls
2011-10-28, 04:02 AM
Can you try removing the single quotes around 82, and if that doesn't work, but in full quotes?

I'm pretty sure that's where the error is, and the resolution will depend on the data type in that field. (Text string or value.)

kariaheart
2011-10-28, 04:48 AM
Changed the = sign to ==, remembered seeing this in some obscured document, this got rid of the syntax error. Now error is incorrect column expression. Tried with and without quotes around the 82 figure to no avail.

kariaheart
2011-10-28, 04:54 AM
Hi Ken,
Data type is unsigned char. More searching for me now to understand this.

Ken Puls
2011-10-28, 05:07 AM
I would think that unsigned char is a text field on some kind, but every database language is a little different. I'm sure you've noticed that the only part I added is multiplying the QTY by this:

IIF(Inventory_Movement_File.IM_TYPE='82',1,-1)

I know Access uses IIF, but I'm not sure about your database... you could try IF instead.

Do you happen to know what language the database is programmed in? MySQL, PervasiveSQL, MSSQL, Oracle, etc...?

kariaheart
2011-10-28, 06:15 AM
Hi Ken,

I know that Database vendor going to sql in next version. Not certain,but i think current database written in c+ or c++.

Ken Puls
2011-10-28, 06:24 AM
Can you share what program it is? (Who is the vendor?) That may help...

kariaheart
2011-10-28, 07:04 AM
Micronet Systems Australia
To get the currency balance on a debtor report the following print logic is used. This may give some insight to the programming used.
Print Logic "" != CURNO(5)

Accumulate if DTRN_EXCH == CURNO(5)

Ken Puls
2011-10-28, 07:11 AM
Unfortunately, not really.

I'm seeing that there's a couple of options. You could email them and ask or you could use your original query, then run an Excel formula down to convert it in the next column. Copy, pastespecial --> values and you can delete the original column. Honestly, that may be the fastest way.

kariaheart
2011-10-28, 10:54 AM
Many thanks, Ken for all assistance given and for entertaining my request in the first place. Already I have learnt a fair bit through your replies. Have read your powerpivot blogs as well so thank you again. Have a great weekend

gsnidow
2011-12-29, 06:28 PM
kariaheart

It may be a little late here, but what the heck... Why not just use a plain old CASE statement. I know for a fact the below syntax works in SQL Server, DB2, and Oracle, but I would think it should work in any database supporing ANSI standard SQL. It does not work in Access, and Excel might not like it either.



SELECT
Inventory_Movement_File.IM_ITMNO,
Inventory_Movement_File.IM_TYPE,
Inventory_Movement_File.IM_DATE,
CASE WHEN Inventory_Movement_File.IM_TYPE = '71'
THEN Inventory_Movement_File.IM_QTY*-1
ELSE Inventory_Movement_File.IM_QTY AS IM_QTY
END
FROM OAUSER.Inventory_Movement_File Inventory_Movement_File
WHERE (Inventory_Movement_File.IM_DATE>=? And Inventory_Movement_File.IM_DATE<=?)
AND (Inventory_Movement_File.IM_QTY<>0)


The only thing you might need to do is convert Inventory_Movement_File.IM_QTY to integer if it is indeed stored as...GASP...char.

Greg