I have this sql query

SELECT cast(dmh.DOC_NO as varchar(35)) AS DOCUMENT_ID, cast(dmh.DOC_TYPE as varchar(30)) AS DOC_TYPE_ID, dmh.CREATE_DATE as DOC_DATE, dmh.LOCATION as LOCATION, dmh.LOC_TYPE as LOC_TYPE, 'DOC_MERCH' as DOC_SOURCE, 
          (SELECT lcompany_fiscal_num
           FROM (SELECT lcompany_fiscal_num
                 FROM NB_IM_LOC_RIM_START locr, NB_LEGAL_COMPANY legalc
                    WHERE locr.LOC_TYPE = dmh.LOC_TYPE
                        AND locr.LOCATION = dmh.LOCATION
                        AND locr.LOC_START_DATE <= dmh.CREATE_DATE
                        AND legalc.LCOMPANY_CODE = locr.COMPANY
                        ORDER BY locr.LOC_START_DATE desc)
                        WHERE rownum = 1) as COMPANY_ID, 'N' as IS_PRINTED, 'N' as IS_MIGRATED
    FROM NB_DOC_MERCH_HEADER dmh
    WHERE dmh.DOC_TYPE IN ('VFT','NDB','VND','CVFT','VTD','VNC','NCI','NNC','CVNC', 'VFI')
          AND dmh.CREATE_DATE BETWEEN ADD_MONTHS(get_vdate, - (12 * 10)) and get_vdate
          AND dmh.DOCUMENT_ID_SMIS is null;

This query returns me this type of results:

As you can see company_id in some cases are null, and i don't want that to happen

What i want to do is: when attrib LOC_TYPE = 'S' i want to do one query and when LOC_TYPE = 'W' i want to do another query, in order to solve the null question.

Is that possible to do with the NVL function? If so, how can i integrate that in my code?

CodePudding user response：

Main select clause:

select cast(...) as document_id, cast(...) as document_type_id, ...

When you come to company_id, instead of the scalar subquery you have now, write the following case expression:

select cast(...) as document_id, cast(...) as document_type_id, ...
...
, case loc_type when 'S' then (the scalar subquery for that case)
                when 'W' then (the scalar subquery for that case)
  end as company_id,
....

You may have more cases for loc_type (use in the same manner), and you can also have a "default" case preceded by the else keyword. Note the end keyword before the column name (alias); it is part of the case expression syntax, and it is mandatory.

I don't see a need for nvl since you said you need one thing for case 'S' and another thing for case 'W'. That's not how nvl works - you would use that if you want "something" in all cases, but when that "something" is null you want "something else".