SQL issues comparing Long field to sysdate


 
Thread Tools Search this Thread
Top Forums Programming SQL issues comparing Long field to sysdate
# 1  
Old 02-26-2016
Wrench SQL issues comparing Long field to sysdate

I am having hard time with this sql:

Code:
select partition_name, high_value
    FROM user_tab_partitions
   WHERE table_name = 'WNP_TPRESPONSE_INSTANCE'
    and to_char(high_value) <= to_char(sysdate - 15, 'yyyymm')
;

I get an error: ORA-00932: inconsistent datatypes: expected CHAR got LONG


high_value is defined as long and here is a sample data:

PARTITION_NAME HIGH_VALUE
CURRENT_P '000001'
P_201009 '201010'
P_201010 '201011'


How to do i do this conversion !
# 2  
Old 02-29-2016
One technique is to use PL/SQL, which converts LONG to VARCHAR2 during cursor fetches:

Code:
SQL> 
SQL> --
SQL> select partition_name, high_value
  2    from user_tab_partitions
  3   where table_name = 'WNP_TPRESPONSE_INSTANCE';

PARTITION_NAME    HIGH_VALUE
--------------- ----------
CURRENT_P    '000001'
P_201009    '201010'
P_201010    '201011'

3 rows selected.

SQL> 
SQL> -- Anonymous PL/SQL block
SQL> begin
  2       for rec in (select partition_name, high_value
  3                 from user_tab_partitions
  4                where table_name = 'WNP_TPRESPONSE_INSTANCE'
  5              )
  6       loop
  7           if to_char(rec.high_value) <= to_char(sysdate - 15, 'yyyymm')
  8           then
  9           dbms_output.put_line(rec.partition_name ||' '|| rec.high_value);
 10           end if;
 11       end loop;
 12  end;
 13  /
CURRENT_P '000001'
P_201009 '201010'
P_201010 '201011'

PL/SQL procedure successfully completed.

SQL> 
SQL>

---------- Post updated at 01:10 PM ---------- Previous update was at 01:09 AM ----------

Another technique is to use Oracle's supplied package "dbms_xmlgen" for XML processing. Like PL/SQL, this package converts LONG data to VARCHAR2 as well.

First, check if you have access to the package by executing this sqlplus command:
Code:
desc dbms_xmlgen

It should list down all the public procedures and functions in this package.
Then, pass your query to the "getxmltype()" function of this package to convert the output into a canonical XML document.

Code:
SQL>
SQL> --
SQL> SELECT DBMS_XMLGEN.GETXMLTYPE('SELECT partition_name, high_value
  2                                   FROM user_tab_partitions
  3                                  WHERE table_name = ''WNP_TPRESPONSE_INSTANCE''') AS xml
  4    FROM dual;

XML
--------------------------------------------------------------------------------
<ROWSET>
 <ROW>
  <PARTITION_NAME>CURRENT_P</PARTITION_NAME>
  <HIGH_VALUE>'000001'</HIGH_VALUE>
 </ROW>
 <ROW>
  <PARTITION_NAME>P_201009</PARTITION_NAME>
  <HIGH_VALUE>'201010'</HIGH_VALUE>
 </ROW>
 <ROW>
  <PARTITION_NAME>P_201010</PARTITION_NAME>
  <HIGH_VALUE>'201011'</HIGH_VALUE>
 </ROW>
</ROWSET>

1 row selected.

SQL>
SQL>

From the output, it can be seen that:
(a) The outermost tags are <ROWSET> and </ROWSET>, and they are always returned.
(b) Each row of data is then sandwiched within <ROW> and </ROW> tags.
(c) Each column heading (partition_name, high_value in your case) has its own opening and closing tag.
(d) The actual data shows up as the content between the relevant column tags.

This is the canonical XML output. You can then use the "extractValue()" function to convert this XML format to relational aka "rows-and-columns" format.

Code:
SQL>
SQL> --
SQL> WITH xml AS (
  2      SELECT XMLTYPE(DBMS_XMLGEN.GETXML('SELECT partition_name, high_value
  3                                           FROM user_tab_partitions
  4                                          WHERE table_name = ''WNP_TPRESPONSE_INSTANCE'''
  5                                       )
  6                    ) AS xml
  7        FROM dual
  8      ),
  9  parsed_xml AS (
 10      SELECT extractValue(xs.object_value, '/ROW/PARTITION_NAME') AS partition_name,
 11             extractValue(xs.object_value, '/ROW/HIGH_VALUE')     AS high_value
 12        FROM xml x,
 13             TABLE(XMLSEQUENCE(EXTRACT(x.xml, '/ROWSET/ROW'))) xs
 14      )
 15  SELECT *
 16    FROM parsed_xml
 17   WHERE TO_CHAR(high_value) <= TO_CHAR(SYSDATE - 15, 'YYYYMM')
 18  ;

PARTITION_NAME       HIGH_VALUE
-------------------- --------------------
CURRENT_P            '000001'
P_201009             '201010'
P_201010             '201011'

3 rows selected.

SQL>
SQL>

Login or Register to Ask a Question

Previous Thread | Next Thread

10 More Discussions You Might Find Interesting

1. Shell Programming and Scripting

Shell script to run sql query having a long listing of parameters

Hi, I have a query regarding execution of a sql query having long listing of parameters ..I need to execute this query inside a shell script. The scenario is like.... Suppose I have a file abc.txt that has the card numbers..it could be in thousands.. then I need to fire a query like ... (12 Replies)
Discussion started by: vsachan
12 Replies

2. Shell Programming and Scripting

Multiple long field separators

How do I use multiple field separators in awk? I know that if I use awk -F"", both a and b will be field separators. But what if I need two field separators that both are longer than one letter? If I want the field separators to be "ab" and "cd", I will not be able to use awk -F"". The ... (2 Replies)
Discussion started by: locoroco
2 Replies

3. Shell Programming and Scripting

Issues with comparing 2 files timestamps

Hi, Im trying to write a script to first check a directory and if the filename has "ACK" in it do nothing and exit but if it has "ORD" in the filename then compare it with a dummy file created 2 minutes previous and see which one is newer Im getting a few errors which im unsure how to rectofy... (5 Replies)
Discussion started by: 02JayJay02
5 Replies

4. Shell Programming and Scripting

issues with sql inside if statement

Hi, I have problem with the following code. My IF block is not executed. And I see "syntax error near unexpected token `)'" error for line "EOF" in the stats_function(). but when I comment the IF block I don't see this error. Kindly help me with this issue. clean_function() {... (10 Replies)
Discussion started by: babom
10 Replies

5. Shell Programming and Scripting

Comparing file with Sql output.

Hi Guys, I need to compare the sql output with a column present in a file. I am able to read the column from the file and stored in a variable. Can somebody help how to store output of the below query to a variable. The database is Oracle. Select count(*) from tableName; ... (2 Replies)
Discussion started by: mac4rfree
2 Replies

6. Shell Programming and Scripting

How to use sql "sysdate" in unix?

I am use unix shell script to called an sql Script to query data in my shell program. sqlplus -S /nolog @update.sql but my script on function "sysdate" not work !! Could you tel me,How can i use function "sysdate" on unix or can replace the other function in my script to get data in system... (5 Replies)
Discussion started by: krai
5 Replies

7. Shell Programming and Scripting

Need help in Shell Script comparing todays date with Yesterday date from Sysdate

Hi, I want to compare today's date(DDMMYYYY) with yesterday(DDMMYYYY) from system date,if (today month = yesterday month) then execute alter query else do nothing. The above requirement i want in Shell script(KSH)... Can any one please help me? Double post, continued here. (0 Replies)
Discussion started by: kumarmsk1331
0 Replies

8. Shell Programming and Scripting

extract a field from a long sentence!

Hi, I want to extract the value of LENGTH column (high-lighted in red) from a file which will have several lines as shown below: <INPUT VAR1 ="" DATATYPE ="number(p,s)" VAR2 ="" VAR3 ="3" VAR4="0" VAR5 ="ELEMITEM" VAR6 ="NO" VAR7 ="NOT A KEY" VAR8 ="17" LEVEL ="0" NAME ="UNIX" NULLABLE... (4 Replies)
Discussion started by: dips_ag
4 Replies

9. Shell Programming and Scripting

Field comparing in files

Guys trying to compare field in two files. For Ex: demo.txt 23.33.4.2 hostname 3.2.4.2 hostname12 demo1.txt 3.3.3.3 hostname23 45.23.23.23 hostname 323 I would like to compare the ips b/w these two files.any... (2 Replies)
Discussion started by: coolkid
2 Replies

10. Shell Programming and Scripting

Subtract 100 from first field in long list? Simple manipulation?

It sounds so easy to do. I have a file thats laid out like this.. number text text text text (etc about 15 times with various text fields) I want to take the first field, "number", subtract 100 from it, and then put it back in the file. a simple little manipulation of the first field in... (4 Replies)
Discussion started by: LordJezo
4 Replies
Login or Register to Ask a Question