Databases
StackOverflow2013
Postsdbo
POOracle SQL weird issue with my below query
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POOracle SQL weird issue with my below query
    primarykey
    data
    text
    <p>I have a view name RULE_READOUT_VIEW which has varchar columns named 'READOUTTYPE' and 'CUSTOMERNUMBER'.</p> <p>When I run the following count query I get only one row as output. </p> <pre><code>SELECT count(1) FROM RULE_READOUT_VIEW WHERE READOUTTYPE = '5' AND CUSTOMERNUMBER = 'NSMPF'; </code></pre> <p>However if run this other query I get a different result and this time count is 11 rows which is what is the expected result.</p> <pre><code>SELECT count(1) FROM RULE_READOUT_VIEW WHERE to_char(READOUTTYPE) = '5' AND CUSTOMERNUMBER = 'NSMPF'; </code></pre> <p>I am not sure why the first query is not working where as only if I force a to_char on the column I get the correct answer. I searched for answers but cannot find any thing satisfactory. Can some one please explain why this behaviour?</p> <p>I ran the below query to check the length of each and every row too</p> <pre><code>SELECT READOUTTYPE, TO_CHAR(READOUTTYPE), LENGTH(READOUTTYPE), LENGTHB(READOUTTYPE) FROM teoss.RULE_READOUT_VIEW WHERE to_char(READOUTTYPE) = '5' and CUSTOMERNUMBER = 'NSMPF'; </code></pre> <p>I got 11 rows as output and all of the columns have length 1.</p> <pre><code>5 5 1 1 5 5 1 1 5 5 1 1 5 5 1 1 5 5 1 1 5 5 1 1 5 5 1 1 5 5 1 1 5 5 1 1 5 5 1 1 5 5 1 1 </code></pre> <p>So the columns do no have any addtional whitespaces as well.</p> <p>View DDL:</p> <pre><code>SELECT --ro.creation_date, ro.POINT_OF_TIME_DAY AS pointOfReadoutDay, ro.POINT_OF_TIME_HOUR AS pointOfReadoutHour, ro.POINT_OF_TIME_MINUTE AS pointOfReadoutMinute, rot.NAME AS readoutType, cp.IDENTIFICATION AS customerNumber, mp.METER_POINT_NUMBER AS meterPointNumber, ro.ORDER_REFERENCE AS readoutOrderNumber, mtrefstat.NAME AS meterStatus, mt.ADDRESS AS address, mt.SERIAL_NUMBER AS serialNumber, mt.UPPER_SERIAL_NUMBER AS upperSerialNumber, servrefstat.NAME AS serverStatus, mus.MAC AS hostname, mserv.IP_ADDRESS AS ipAddress , mt.device_id AS deviceID, GET_ACCESSCONNECTION_TYPE(mus.device_id) AS ACCESSCONNECTIONTYPE, FUNC_LIST_OBIS_NAME(ro.order_read_out_id) AS refObisList, mt.METER_NUMBER AS meterNumber FROM ORDER_READ_OUT ro, REF_METER_READ_OUT_TYPE rot, COMPANY cp, METER_POINT mp, METER mt, MAP_DEVICE_STATUS mtstat, REF_STATUS mtrefstat, DEVICE servdev, MUS mus, METERING_SERVER mserv, MAP_DEVICE_STATUS servstat, REF_STATUS servrefstat, MAP_ORD_RD_OUT_CNTRCT_MT_PT mocm, MAP_CONTRACT_METER_POINT mcm, CONTRACT co WHERE -- order readout relation has to be valid mocm .ORDER_READ_OUT_ID = ro.ORDER_READ_OUT_ID AND ro.REF_METER_READ_OUT_TYPE_ID = rot.REF_METER_READ_OUT_TYPE_ID AND ro.ACTIVATION_DATE &lt;= SYSTIMESTAMP AND (ro.DEACTIVATION_DATE IS NULL OR ro.DEACTIVATION_DATE &gt; SYSTIMESTAMP) -- contract relation has to be valid ... AND mocm.MAP_CONTRACT_METER_POINT_ID = mcm.MAP_CONTRACT_METER_POINT_ID AND mcm.CONTRACT_ID = co.CONTRACT_ID AND co.COMPANY_ID = cp.COMPANY_ID AND ((co.BEGIN IS NULL AND mcm.BEGIN &lt;= SYSTIMESTAMP) OR (co.BEGIN &lt;= SYSTIMESTAMP AND mcm.BEGIN &lt;= SYSTIMESTAMP AND mcm.begin &gt;= co.begin)) AND (mcm.end IS NULL OR mcm.end &gt;= SYSTIMESTAMP) AND ((co.END IS NULL) OR (co.END &gt;= SYSTIMESTAMP AND ((mcm.end IS NULL) OR (mcm.end &lt;= co.end)))) -- meter point relation has to be valid ... AND mcm.METER_POINT_ID = mp.METER_POINT_ID AND mp.METER_POINT_ID = mt.METER_POINT_ID AND mt.DEVICE_ID = mtstat.DEVICE_ID AND mtstat.REF_STATUS_ID = mtrefstat.REF_STATUS_ID AND (mtrefstat.name = 'active' OR mtrefstat.name = 'toBeRemoved') AND mtstat.STATUS_DATE = (SELECT MAX(mds.status_date) FROM Map_device_status mds WHERE mds.device_id = mtstat.DEVICE_ID ) -- mus/metering-server relation has to be valid AND servdev.DEVICE_ID = (SELECT FUNC_FIND_SERVER_DEV_ID(mt.DEVICE_ID) FROM DUAL ) AND servdev.DEVICE_ID = mus.DEVICE_ID(+) AND servdev.DEVICE_ID = mserv.DEVICE_ID(+) AND servdev.DEVICE_ID = servstat.DEVICE_ID AND servstat.REF_STATUS_ID = servrefstat.REF_STATUS_ID AND (servrefstat.name = 'active' OR servrefstat.name = 'toBeRemoved') AND servstat.STATUS_DATE = (SELECT MAX(mds.status_date) FROM Map_device_status mds WHERE mds.device_id = servstat.DEVICE_ID ) AND ro.CREATION_DATE = (SELECT MAX(t9.CREATION_DATE) FROM ORDER_READ_OUT t9 WHERE t9.point_of_time_day = ro.point_of_time_day AND t9.point_of_time_hour = ro.point_of_time_hour AND t9.point_of_time_minute = ro.point_of_time_minute AND t9.ref_meter_read_out_type_id = ro.ref_meter_read_out_type_id AND t9.ORDER_READ_OUT_ID IN (SELECT t5.ORDER_READ_OUT_ID FROM map_ord_rd_out_cntrct_mt_pt t5 WHERE t5.MAP_CONTRACT_METER_POINT_ID = mcm.MAP_CONTRACT_METER_POINT_ID )); </code></pre> <p>Plans snippet: <code>explain plan for SELECT count(1) FROM RULE_READOUT_VIEW WHERE READOUTTYPE = '5' AND CUSTOMERNUMBER = 'NSMPF';</code></p> <p>| 19 | TABLE ACCESS BY INDEX ROWID| REF_METER_READ_OUT_TYPE | 1 | 7 | 1 |<br> | 20 | INDEX UNIQUE SCAN | UQ_REF_METER_READ_OUT_TY_NAME | 1 | | 0 </p> <pre><code> explain plan for SELECT count(1) FROM RULE_READOUT_VIEW WHERE to_char(READOUTTYPE) = '5' AND CUSTOMERNUMBER = 'NSMPF'; </code></pre> <p>| 14 | INDEX FAST FULL SCAN | PK_METERREADTYPE | 1 | 7 | 1 |<br> | 15 | INDEX FAST FULL SCAN | UQ_REF_METER_READ_OUT_TY_NAME | 1 | 7 | 1 | </p>
    singulars
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PTQuestion
    1. USthiyaga
    1. USthiyaga
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PO
      singulars
      1. PTAnswer
    2. PO
      singulars
      1. PTAnswer
    1. VO
      singulars
      1. POOracle SQL weird issue with my below query
      1. This table or related slice is empty.
      1. VTUpMod
    2. VO
      singulars
      1. POOracle SQL weird issue with my below query
      1. This table or related slice is empty.
      1. VTUpMod
    1. COMake sure your readouttype column is clean, i.e. no entries with whitespace in them.
      singulars
      1. POOracle SQL weird issue with my below query
      1. USMat
    2. COI checked the column values. i donot see any addtional white spaces before or after. Also if i run below query too i get 11 rows SELECT count(1) FROM RULE_READOUT_VIEW WHERE READOUTTYPE like '5' AND CUSTOMERNUMBER = 'NSMPF'. The problem seems to be only if i use the equality operator alone.
      singulars
      1. POOracle SQL weird issue with my below query
      1. USthiyaga
    3. COHow did you check? Both your "working" queries indicate that your readout column doesn't contain only a '5' but also has some other whitespace-like stuff.
      singulars
      1. POOracle SQL weird issue with my below query
      1. USMat
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload