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);
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.