Make a casewhen statement including an alias in the same query

1 reply [Last post]
Thu, 25 Feb 2016 - 3:58 PM
Cabid
Offline
Last seen: 13 weeks 1 day ago
Title: ★
Joined: 25 Feb 2016
Posts: 2
Im working on a storage db and want to make a query that informs if an item is low in stock. I have made this i MS Access but have problems here in Base as I just started with this. In Access I did this in design mode

In Stock:[Total in] – [Total out]
Low in Stock:IIF

While in Base

“Total in” – “Total out”
Alias: In Stock

But when Im trying to make the casewhen statement it cant find the “In stock” alias field.
When I make a query based on that query then the alias becomes the valid fieldname and the casewhen works, but I dont want to have multible querys just becouse it needs multible steps. In Access it worked fine to have all those steps in a single query, so how do I make it in Base?
I also had trouble when I tried the >= function. > worked fine but not >=

/Cabid

Top
Fri, 26 Feb 2016 - 5:19 PM
#1
F3K Total
Offline
Last seen: 2 weeks 6 hours ago
Title: ★★★★★
Joined: 14 Oct 2013
Posts: 201
Make a casewhen statement including an alias in the same query
Something like this 

SELECT 
    "FK_ID_P", 
    STOCK, 
    CASEWHEN(STOCK <=10,'LOW STOCK','OK') LS
FROM(
    SELECT 
        X."FK_ID_P",
        TOTAL_IN - TOTAL_OUT AS STOCK
    FROM 
        (SELECT "FK_ID_P",  SUM("AMOUNT") TOTAL_IN FROM "T_IN" GROUP BY "FK_ID_P") X 
    LEFT JOIN 
        (SELECT "FK_ID_P", SUM("AMOUNT") TOTAL_OUT FROM "T_OUT" group BY "FK_ID_P") Y 
    ON
        X."FK_ID_P" = Y."FK_ID_P" )
order by
    "FK_ID_P"

?

R

AttachmentSize
Stock.odb 40.95 KB
Top

Comment viewing options

Select your preferred way to display the comments and click "Save settings" to activate your changes.