0

I am using a sql utility which integrated in Solaris 10. I want get a column in a table but it seems my sql utility not allows.

Below is my code

SELECT
    DBExternalAlarm.m_ObjectDN,
    INSTR(DBExternalAlarm.m_ObjectDN,'@@',30),
    SUBSTR(DBExternalAlarm.m_ObjectDN,1,INSTR(DBExternalAlarm.m_ObjectDN,'@@',30))
FROM DBExternalAlarm;

Result have only 2 data columns

- DBExternalAlarm.m_ObjectDN
- INSTR(DBExternalAlarm.m_ObjectDN,'@@',30)

I don't know, any body can give other way to resolve this. Can I using a varible or other function?

Tim Biegeleisen
  • 502,043
  • 27
  • 286
  • 360
Hai
  • 21
  • 4
  • So you're saying that the query ran, without error, but only returned two columns? Are you sure this is reproducible? – Tim Biegeleisen May 29 '18 at 04:52
  • Yes, when I replace INSTR(DBExternalAlarm.m_ObjectDN,'@@',30) with a number, column has data. – Hai May 29 '18 at 04:57

1 Answers1

0

My guess here is there is a third column being returned in your result set, but it is just empty string, because @@ is not being found, starting at the 30th position. To test this hypothesis, use the following query:

SELECT
    DBExternalAlarm.m_ObjectDN,
    INSTR(DBExternalAlarm.m_ObjectDN,'@@',30),
    CASE WHEN INSTR(DBExternalAlarm.m_ObjectDN,'@@',30) > 0
         THEN SUBSTR(DBExternalAlarm.m_ObjectDN, 1,
             INSTR(DBExternalAlarm.m_ObjectDN,'@@',30))
         ELSE 'Not Found' END AS result
FROM DBExternalAlarm;

I don't know your database, but in Oracle INSTR will return 0 if the string is not found. The above CASE expression checks for this and outputs Not Found in this case. If you see this everywhere, then you'll know that there are no matches.

Tim Biegeleisen
  • 502,043
  • 27
  • 286
  • 360