-2

I have a query and I am checking for a string in the where COURSE_NUMBER clause

select * from CC cc inner join AB_COURSE_ENROLL ab on cc.DCID=ab.LOCAL_ID where cc.STUDENTID=34700 and cc.COURSE_NUMBER IN (TO_CHAR(FOD1050));

The problem is that if I put FOD1050 in single quotes the query works but in my project I cannot enclose FOD1050 in single quotes or double. Is there any other way to tell the compiler that the incoming value is in string? I am using Oracle

user101
  • 139
  • 10
  • 1
    if FOD1050 is a string value, then you need to enclose it in single quotes. I'm confused as to why you say "but in my project I cannot enclose FOD1050 in single quotes or double" - why can't you? Is it because you need to be able to pass any string value into the query - i.e. via a bind variable? If so, then `and cc.course_number in (:course_number)` ought to do the trick. – Boneist Jun 16 '17 at 09:21
  • It is like ~([courses]DCID) this and ~([courses]DCID) corresponds to FOD1050 without quotes, if the add quotes '~([courses]DCID)' the expression doesn't return anything maybe it errors out – user101 Jun 16 '17 at 18:39
  • I don't understand what `~([courses]DCID)` means. Is that a value? A parameter? A variable? A column? It's not a valid identifier name, so if you're trying to refer to it as one (e.g. variable/column/etc) it needs to be enclosed in double quotes. Your question is really not clear, though - you would be far better off updating your question (or, at this point, asking a new question) and providing much more information, such as the table structure(s), sample input data and expected output. – Boneist Jun 23 '17 at 10:02

1 Answers1

0

If you need a hardcoded value, you could build it as a concatenation af single characters, identified by their ASCII code.

For example:

SQL> select chr(70) || chr(79) || chr(68) || chr(49) || chr(48) || chr(53) || chr(48) as str
  2  from dual;

STR
-------
FOD1050

To know the codes you need, DUMP can be useful:

SQL> select dump('FOD1050') from dual;

DUMP('FOD1050')
----------------------------------
Typ=96 Len=7: 70,79,68,49,48,53,48
Aleksej
  • 22,443
  • 5
  • 33
  • 38
  • Hi @Aleksej it will not be a hardcoded value, the value can be anything – user101 Jun 16 '17 at 09:22
  • So where does this value come from? Is it a variable? – Aleksej Jun 16 '17 at 09:23
  • Yes it is a variable comes from another table – user101 Jun 16 '17 at 09:25
  • I can't understand. If you have a variable, why do you need quotes? Please post a relevant part of code to clarify your need – Aleksej Jun 16 '17 at 09:36
  • It is like ~([courses]DCID) this and ~([courses]DCID) corresponds to FOD1050 without quotes, if the add quotes '~([courses]DCID)' the expression doesn't return anything maybe it errors out – user101 Jun 16 '17 at 09:39