Home » SQL & PL/SQL » SQL & PL/SQL » Query Problem (Not returning required result)
Query Problem (Not returning required result) [message #287386] Wed, 12 December 2007 02:29 Go to next message
musman
Messages: 147
Registered: July 2007
Location: Lahore
Senior Member

my query is ..

select 0000 as member_code,'TEST' as member_name from dual
union
select member_code,member_name from members.


but what it returns for test is.

0 TEST
...
not returning 0000.where is the problem in query.

[Updated on: Wed, 12 December 2007 02:32]

Report message to a moderator

Re: QUERY PROBLEM [message #287388 is a reply to message #287386] Wed, 12 December 2007 02:34 Go to previous messageGo to next message
Michel Cadot
Messages: 64108
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
0000 is a number, its canonical form is 0 until you give an explicit format.
SQL> select 0000 col from dual;
       COL
----------
         0

1 row selected.

SQL> select to_char(0000,'0000') col from dual;
COL
-----
 0000

1 row selected.

SQL> col col format 0000
SQL> select 0000 col from dual;
  COL
-----
 0000

1 row selected.

And so on.

Regards
Michel
Re: Query Problem (Not returning required result) [message #287390 is a reply to message #287386] Wed, 12 December 2007 02:35 Go to previous messageGo to next message
Michel Cadot
Messages: 64108
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
If you really mean four 0 then it is a string and you have to enclose it between ':
SQL> select '0000' col from dual;
COL
----
0000

1 row selected.

Regards
Michel

[Updated on: Wed, 12 December 2007 02:36]

Report message to a moderator

Re: QUERY PROBLEM [message #287391 is a reply to message #287388] Wed, 12 December 2007 02:37 Go to previous messageGo to next message
musman
Messages: 147
Registered: July 2007
Location: Lahore
Senior Member

i don`t want it as char want it as number.
i am using this query in form.
so 0000 required as number.
because list item is NUMBER.

[Updated on: Wed, 12 December 2007 02:39]

Report message to a moderator

Re: QUERY PROBLEM [message #287395 is a reply to message #287391] Wed, 12 December 2007 02:41 Go to previous messageGo to next message
Michel Cadot
Messages: 64108
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
0000 is NOT a number it is the representation of a number (0) and so is a string.
For the rest, read the examples I gave.

Regards
Michel
Re: QUERY PROBLEM [message #287397 is a reply to message #287395] Wed, 12 December 2007 02:45 Go to previous messageGo to next message
musman
Messages: 147
Registered: July 2007
Location: Lahore
Senior Member

select '0000' as member_code,'TEST' as member_name from dual
union
select member_code,member_name from members


when i write this query it givers error.
expression must have same data type as corresponding expression

as member_code data type is number in members table.
Re: Query Problem (Not returning required result) [message #287403 is a reply to message #287386] Wed, 12 December 2007 03:00 Go to previous messageGo to next message
kir_ait
Messages: 198
Registered: November 2007
Location: Bangalore,India
Senior Member

to_char(member_code)


Kiran.
Re: QUERY PROBLEM [message #287405 is a reply to message #287391] Wed, 12 December 2007 03:02 Go to previous message
Frank
Messages: 7880
Registered: March 2000
Senior Member
musman wrote on Wed, 12 December 2007 09:37

i don`t want it as char want it as number.
i am using this query in form.

Use a format mask for the field in the form instead of messing up the data you fetch from the database.
Previous Topic: Count Query in Having Clause
Next Topic: partition
Goto Forum:
  


Current Time: Mon Dec 05 04:52:47 CST 2016

Total time taken to generate the page: 0.06213 seconds