Question

SQL Query using max

  • 26 October 2022
  • 1 reply
  • 75 views
M
Rank
Userlevel 4
Badge +9

I am trying to bring back the maximum valuation number from subval_valuation_cfv for that subcontract and if a valuation doesn't exist for that subcontract, then bring back row but show valuation no as null. I only want to see one row for ever subcontract number even if a valuation doesn’t exist. Here is my query:

SELECT DISTINCT
    b.cf$_project                                            AS "Project No",
    b.sub_con_no                                             AS "Subcontract Order No",
    b.sub_con_name                                           AS "Subcontract Name",
    f.valuation_no,
    b.supplier_id                                            AS "Supplier ID",
    ifsapp.supplier_info_general_api.get_name(b.supplier_id) AS "Supplier Name",
    b.cf$_sub_purch_group                                    AS "Cost Code",
    b.cf$_sub_purch_group_desc                               AS "Cost Code Desc",
    b.pay_term_id                                            AS "Payment Terms",
    b.int_retention                                          AS "Retention %",
    f.certificate_no                                         AS "Certificate No",
    f.state                                                  AS "Certificate Status",                             
    f.cf$_payprocdate                                        AS "Contractual Payment Date",
    f.paid_date                                              AS "Date Paid"
FROM
    ifsapp.sub_contract_cfv           b
    LEFT JOIN ifsapp.subval_valuation_cfv       f ON b.sub_con_no = f.sub_con_no

WHERE
    b.state IN ( 'Active', 'Planned' )
    AND upper(b.cf$_project)= 'SO1523'

ORDER BY
    "Project No",
    "Subcontract Order No"

 

1 reply

eqbstal
Rank
Userlevel 7
Badge +21

@mlcscottonn Don't think that you will get a reply as you include subval_valuation_cfv. We have no clue what the content of that view is.

Next to this, we don't see a max() function bein used. Maybe if you have a query that seems to work as the above and then surround it with a max() function. So the above will be the subquery.

Come to think of it. Does the following give you something:

Select max(f.valuation_no)

FROM
    ifsapp.sub_contract_cfv           b
    LEFT JOIN ifsapp.subval_valuation_cfv       f ON b.sub_con_no = f.sub_con_no

WHERE
    b.state IN ( 'Active', 'Planned' )
    AND upper(b.cf$_project)= 'SO1523'

Please like this response if it helped you

Reply


Terms & ConditionsCookie settings