Using logical expression inside a column

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

General SQL Server Forums

New to SQL Server Programming

Using logical expression inside a column

Author

Topic

siba94
Starting Member

3 Posts

Posted - 2007-05-16 : 03:49:25

Hi Experts,

I am new to this forum and SQL usage.

I have a database with following table fields as

Refernce - Varchar field
D_C - Varchar field (containing either D or C) and
Amount - Integer

Now i want to create a View & split the Amount column into 2 basd on
split this Amount into 2 columns as below:

Refernce - Varchar field
D_C - Varchar field (containing either D or C) and
Debit Amount - Integer (where D_C is D)
Credit Amount - Integer (where D_C is C)

Regards
SEkar

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2007-05-16 : 03:57:19

[code]CREATE VIEW SOMEVIEW
AS
SELECT
D_C,
CASE WHEN D_C = 'D' THEN AMOUNT ELSE 0 END AS DEBIT_AMOUNT,
CASE WHEN D_C = 'C' THEN AMOUNT ELSE 0 END AS CREDIT_AMOUNT
FROM TABLE[/code]

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

siba94
Starting Member

3 Posts

Posted - 2007-05-16 : 04:17:29

quote:
Originally posted by harsh_athalye

CREATE VIEW SOMEVIEW
AS
SELECT
D_C,
CASE WHEN D_C = 'D' THEN AMOUNT ELSE 0 END AS DEBIT_AMOUNT,
CASE WHEN D_C = 'C' THEN AMOUNT ELSE 0 END AS CREDIT_AMOUNT
FROM TABLE

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

THANKS A LOT, THAT WORKED BUT I JUST HAVE ANOTHER SMALL QUERY, IN CASE OF DEBITS THE AMOUNT IS STORED AS NEGATIVE, HOW DO I CONVERT THAT TO POSITIVE ?

THANKS

Vijaykumar_Patil
Posting Yak Master

121 Posts

Posted - 2007-05-16 : 04:31:42

Multiply by -1 ..only for Debits.

CREATE VIEW SOMEVIEW
AS
SELECT
D_C,
CASE WHEN D_C = 'D' THEN (-1 * AMOUNT) ELSE 0 END AS DEBIT_AMOUNT,
CASE WHEN D_C = 'C' THEN AMOUNT ELSE 0 END AS CREDIT_AMOUNT
FROM TABLE

Necessity is the mother of all inventions!

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-05-16 : 05:00:34

[code]
SELECT CASE WHEN (D_C = 'D' AND AMOUNT > 0)
OR (D_C = 'C' AND AMOUNT < 0)
THEN ABS(AMOUNT)
ELSE 0
END AS DEBIT_AMOUNT,
CASE WHEN (D_C = 'C' AND AMOUNT > 0)
OR (D_C = 'D' AND AMOUNT < 0)
THEN ABS(AMOUNT)
ELSE 0
END AS CREDIT_AMOUNT
FROM TABLE
[/code]

Vijaykumar_Patil
Posting Yak Master

121 Posts

Posted - 2007-05-16 : 05:22:41

khtan .. that is a better one.

Necessity is the mother of all inventions!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources