Translate <>'' to sql_oracle

Question

Im rewriting some code from sql to sql_oracle but i cant find a sollution for <>''.

!= '' will not work.

If i can translate <>'' it should work. i already googled alot but couldnt find any awnsers.. im not that experienced in coding so maybe some one here can help me.

This the sql code

    SQL_genericp2.Insert(
            "from   tsdsmd  trip                            "
            "       left outer join                         "
            "       (                                       "
            "       select  tsroma.* from tsroma            "
            "       where   tsroma.tstohn <> ''             "
            "       )       leg                             "
            "       on      trip.dosvlg = leg.tsrido        "
            "       left outer join dosier shipment         "
            "       on      leg.dosvlg = shipment.dosvlg    "
            "       left outer join                         "
            "       (                                       "
            "       select  * from tsdsco                   "
            "       where   laloin = 1 and volgnr = 1       "
            "       )       tsdsco1                         "
            "       on      leg.dosvlg = tsdsco1.dosvlg     "
            "       left outer join                         "
            "       (                                       "
            "       select  * from tsdsco                   "
            "       where   laloin = 2 and volgnr = 1       "
            "       )       tsdsco2                         "
            "       on      leg.dosvlg = tsdsco2.dosvlg     "
            ,1);
    </i>

And this is the sql_orcale code:

    SQL_Oracle_genericp2.Insert(
            "from                                                       "
            "        \"dkf_tsdsmd\"                                                             trip        "
            "                                                       "
            "        left outer join                                            "
            "        (select  * from \"dkf_tsroma\" where \"tstohn\" != '')                     leg     "
            "        on      trip.\"dosvlg\" = leg.\"tsrido\"                               "
            "                                                       "
            "   left outer join                                             "
            "        \"dkf_tsdsmd\"                                                                 shipment    "
            "        on      leg.\"dosvlg\" = shipment.\"dosvlg\"                               "
            "                                                       "
            "   left outer join                                             "
            "   (                                                   "
            "   select                                                  "
            "       \"fileNumber\"                                  \"dosvlg\", "
            "       max(case when \"addressRole\" = 0 then \"relationSearchname\"   else null end)  \"zoek0\",  "
            "       max(case when \"addressRole\" = 0 then \"nameLine1\"        else null end)  \"tsnam10\",    "
            "       max(case when \"addressRole\" = 0 then \"streetLine1\"      else null end)  \"tsadr10\",    "
            "       max(case when \"addressRole\" = 0 then \"countryCode\"      else null end)  \"land0\",  "
            "       max(case when \"addressRole\" = 0 then \"placeName\"        else null end)  \"tscity0\",    "
            "       max(case when \"addressRole\" = 0 then \"postcode\"     else null end)  \"postun0\",    "
            "                                                       "
            "       max(case when \"addressRole\" = 1 then \"relationSearchname\"   else null end)  \"zoek1\",  "
            "       max(case when \"addressRole\" = 1 then \"nameLine1\"        else null end)  \"tsnam11\",    "
            "       max(case when \"addressRole\" = 1 then \"streetLine1\"      else null end)  \"tsadr11\",    "
            "       max(case when \"addressRole\" = 1 then \"countryCode\"      else null end)  \"land1\",  "
            "       max(case when \"addressRole\" = 1 then \"placeName\"        else null end)  \"tscity1\",    "
            "       max(case when \"addressRole\" = 1 then \"postcode\"     else null end)  \"postun1\",    "
            "                                                       "
            "       max(case when \"addressRole\" = 3 then \"relationSearchname\"   else null end)  \"zoek3\",  "
            "       max(case when \"addressRole\" = 3 then \"nameLine1\"        else null end)  \"tsnam13\",    "
            "       max(case when \"addressRole\" = 3 then \"streetLine1\"      else null end)  \"tsadr13\",    "
            "       max(case when \"addressRole\" = 3 then \"countryCode\"      else null end)  \"land3\",  "
            "       max(case when \"addressRole\" = 3 then \"placeName\"        else null end)  \"tscity3\",    "
            "       max(case when \"addressRole\" = 3 then \"postcode\"     else null end)  \"postun3\",    "
            "                                                       "
            "       max(case when \"addressRole\" = 4 then \"relationSearchname\"   else null end)  \"zoek4\",  "
            "       max(case when \"addressRole\" = 4 then \"nameLine1\"        else null end)  \"tsnam14\",    "
            "       max(case when \"addressRole\" = 4 then \"streetLine1\"      else null end)  \"tsadr14\",    "
            "       max(case when \"addressRole\" = 4 then \"countryCode\"      else null end)  \"land4\",  "
            "       max(case when \"addressRole\" = 4 then \"placeName\"        else null end)  \"tscity4\",    "
            "       max(case when \"addressRole\" = 4 then \"postcode\"     else null end)  \"postun4\" "
            "   from                                                    "
            "       \"dkf_v_cw_fileAddress\"                                    "
            "   group by                                                "
            "       \"fileNumber\"                                          "
            "   )                                           tsdnaw      "
            "   on  leg.\"dosvlg\" = tsdnaw.\"dosvlg\"                              "
            "                                                       "
            "   left outer join                                             "
            "   (                                                   "
            "   select                                                  "
            "       \"dkf_tsdsco\".\"dosvlg\"                           \"dosvlg\", "
            "       max(case when \"laloin\" = 1 and \"volgnr\" = 1 then \"tsfrti\" else null end)  \"tsfrti1\",    "
            "       max(case when \"laloin\" = 1 and \"volgnr\" = 1 then \"tsttim\" else null end)  \"tsttim1\",    "
            "       max(case when \"laloin\" = 2 and \"volgnr\" = 1 then \"tsfrti\" else null end)  \"tsfrti2\",    "
            "       max(case when \"laloin\" = 2 and \"volgnr\" = 1 then \"tsttim\" else null end)  \"tsttim2\" "
            "   from                                                    "
            "       \"dkf_tsdsco\"                                          "
            "   group by                                                "
            "       \"dosvlg\"                                          "
            "   )                                           tsdsco      "
            "       on  leg.\"dosvlg\" = tsdsco.\"dosvlg\"                              "
            ,1);

Answer 1

tsroma.tstohn <> '' 

Oracle treats empty strings as NULL. So in this case, you have to write it as

tsroma.tstohn IS NOT NULL

You also need to think about if empty strings becoming NULL is a problem for your data model.