Link to home
Create AccountLog in
Avatar of Gerhardpet
GerhardpetFlag for Canada

asked on

Need help with a query using an if statement

I have this query

SELECT formatMoney(SUM(araging_cur_val)) AS f_current,
formatMoney(SUM(araging_thirty_val)) AS f_thirty,
formatMoney(SUM(araging_sixty_val)) AS f_sixty,
formatMoney(SUM(araging_ninety_val)) AS f_ninety,
formatMoney(SUM(araging_plus_val)) AS f_plus,
formatMoney(SUM(araging_total_val)) AS f_total
FROM araging(<? value("asofdate") ?>, true, false)
WHERE ((abs(araging_aropen_amount) > 0)
AND (araging_cust_id = <? value("cust_id") ?>));

I need help with the following

if f_thirty or f_sixty or f_ninety or f_total > 0

then SELECT image_data
FROM image
WHERE ((image_name='logo'));

Can someone help me put this together in to one query?
Avatar of Altin Bardhi
Altin Bardhi
Flag of United Kingdom of Great Britain and Northern Ireland image

if (  (f_thirty || f_sixty || f_ninety || f_total)  > 0  ){
SELECT image_data
FROM image
WHERE ((image_name='logo'));
}
Or Try

if (  (f_thirty  > 0 ) || (f_sixty  > 0 ) || (f_ninety  > 0 ) ||( f_total  > 0)  ){
SELECT image_data
FROM image
WHERE ((image_name='logo'));
}
Avatar of earth man2
I'm not sure what your araging function returns but you could use the
CASE WHEN THEN END contruct
see http://www.postgresql.org/docs/9.2/static/functions-conditional.html
section 9,17.1 of PostgreSQL Documentation.

SELECT CASE WHEN
( SUM(araging_thirty_val) > 0 ) OR
( SUM(araging_sixty_val ) > 0 ) OR
( SUM(araging_ninety_val) > 0 ) OR
( SUM(araging_total_val)  > 0 ) THEN select image_data from image where image_name='logo'
END
FROM araging(<? value("asofdate") ?>, true, false)
WHERE ((abs(araging_aropen_amount) > 0)
AND (araging_cust_id = <? value("cust_id") ?>));
Avatar of Gerhardpet

ASKER

earthman2,
I will try your suggestion...the one from albacom does not work.

The araging function returns values for outstanding balances on customers

Current, 30+ overdue, 60+ overdue, 90+ overdue, Total due
earthman2,
I've been trying to make your suggestion work but I don't know how to put the query I already have together with what you suggested.

The one that I have was already in the report.
Avatar of OnALearningCurve
OnALearningCurve

Hi  Gerhardpet,

are you looking for something like the example below?


SELECT formatMoney(SUM(araging_cur_val)) AS f_current,
formatMoney(SUM(araging_thirty_val)) AS f_thirty,
formatMoney(SUM(araging_sixty_val)) AS f_sixty,
formatMoney(SUM(araging_ninety_val)) AS f_ninety,
formatMoney(SUM(araging_plus_val)) AS f_plus,
formatMoney(SUM(araging_total_val)) AS f_total,
MAX(CASE WHEN (SUM(araging_thirty_val) > 0 OR SUM(araging_sixty_val) > 0 OR SUM(araging_ninety_val) > 0 OR SUM(araging_total_val) > 0) THEN image.image_data END)AS image_data
FROM araging(<? value("asofdate") ?>, true, false)
LEFT JOIN image ON araging.araging_cust_id = image.cust_id
WHERE ((abs(araging_aropen_amount) > 0)
AND (araging_cust_id = <? value("cust_id") ?>))


I have guessed on the fields used to link the two tables so it may require a little tinkering to get it to work.

hope this helps,

Mark.
there is nothing that links the image table to the araging table.

That is the part I don't know how to do

In the report I can display an image if a certain condition is met and the image name is coming from the image table
I can also phrase the query in a different...something like this

SELECT public_aropen.aropen_duedate AS due_date, public_aropen.aropen_cust_id
FROM (public_aropen INNER JOIN public_custinfo ON public_aropen.aropen_cust_id = public_custinfo.cust_id) INNER JOIN public_terms ON public_aropen.aropen_terms_id = public_terms.terms_id
WHERE (((public_aropen.aropen_open)="TRUE"))
GROUP BY public_aropen.aropen_duedate, public_aropen.aropen_cust_id
HAVING (([public_aropen]![aropen_duedate]>[public_terms]![terms_duedays]));
you can't use the query you currently have as I suspect it casts number to character then you want to compare it as bigger than 0.  I have morphed the query into one that should be equivalent.
what is the error given when you execute the query...

SELECT CASE WHEN
( SUM(araging_thirty_val) > 0 ) OR
( SUM(araging_sixty_val ) > 0 ) OR
( SUM(araging_ninety_val) > 0 ) OR
( SUM(araging_total_val)  > 0 ) THEN (select image_data from image where image_name='logo')
END
FROM araging(<? value("asofdate") ?>, true, false)
WHERE ((abs(araging_aropen_amount) > 0)
AND (araging_cust_id = <? value("cust_id") ?>));

It is difficult to see what you are trying to acheive.  A report that displays an image using SQL ?  There should be some programming constructs in the reporting tool to do the if.....
ASKER CERTIFIED SOLUTION
Avatar of Gerhardpet
Gerhardpet
Flag of Canada image

Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
It is found a solution to this with help from an expert on xTuple

Here is the query to make it work

SELECT image.image_data AS past_due
FROM   image
WHERE  image.image_name = 'past_due'
       AND (SELECT Min(aropen.aropen_duedate)
            FROM   aropen
            WHERE  aropen.aropen_cust_id = <? value("cust_id") ?> ) < CURRENT_DATE
I will consult with an expert on this on the reporting software