Apply Condition by using two columns in Dynamic Query

[VIJSQL01]

Hi,

I am writing a dynamic query where in i need to put data exclusion criteria by using two columns from a table.

The two columns which i am using are 'FLOP_PRICE_AMT' and 'REO_PRICE_AMT'. The condition goes like this, if FLOP_PRICE_AMT is less than 90% of REO_PRICE_AMT, then that record(entire row) would be eliminated from data retrieval.

Here you for my query,

select cbsa_name,sum(1) from hpi3.swap_analysis where cbsa_name like '%ABERD%'
AND Variables_of_subtotals='First Listing Date AFTER REO Take back'
AND SALE_AMOUNT_BEFORE_FCL is not NULL
AND spreadsheet_FCLYear_Group in ('2008-1','2008-2')
AND sqid is not NULL
AND REO_PRICE_AMT < 10000000
AND REO_RESALE_IND = 'Y'
AND FLOP_PRICE_AMT < 0.9 * REO_PRICE_AMT(This condition is wrong. I have simply put)
group by cbsa_name

 

[djj55]

I think you got the logic backward. Try

Flop_price_amt >= (0.9 * Reo_price_amt)

notice the parentheses. This will get the value and leave out the ones you indicated would be eliminated.

djj
The Lord is my shepherd (Psalm 23) - I need someone to lead me!

 

[Akey2020]

Hail my fellow "nerdiacs" ! This was a good post to demonstrate the real art of SAS, which sometimes still alludes me. Hi all ! Long time reader, first time poster.

This was an interesting post. Question: What made the >= correct vs the original < ? I thought that you always point the sign towards the variable in question. Maybe this is an esoteric question. You definitely will get different results I didn't see that addressed in the thread of the post.

Can someone give me the straight scoop ? Thanks Much

 

[gmmastros]

Question: What made the >= correct vs the original < ? I thought that you always point the sign towards the variable in question.

No.

These are comparison operators you're talking about. Specifically:

= Equal
< Less than
> Greater than
<= Less than or equal
>= Greater than or equal to.

Suppose you had a table of people with their ShoeSize, like this:

Name ShoeSize
------------ ---------
Adam 9.5
Barry 8
Chad 10

If you wanted all the people with ShoeSize larger than Size 9...

[tt]
Select Name
From People
Where ShoeSize [!]>[/!] 9
[/tt]

If you wanted all the people with ShoeSize smaller than size 9...

[tt]
Select Name
From People
Where ShoeSize [!]<[/!] 9
[/tt]

Does this make sense?

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[VIJSQL01]

YES. All these replies have helped me a lot. Thanks for providing the solution.i used the condition "Flop_price_amt >= (0.9 * Reo_price_amt)" which worked for me.
Since i was on vacation for long time , i really coudnt look into this.

Now i am coming up with another question which is pertaining to SYbase IQ not SQL. I am not sure this is the right forum to discuss about Sybase stuff. But i thought that, if anybody have any idea on Sybase, can help me out on my problem.

The problem which i am facing is, I need to list the columns in a table and the data types defined for those columns.

As of now i am using following query,

sp_columns 'hpi3.swap_analysis'

The result says '0' rows.

It will fetch all the information, it supposed to be but no results.

Please help me on this.