Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Interested in getting your voice heard by members of the Developer Marketing team at Oracle? Check out this post for AppDev or this post for AI focus group information.

HI. Doubt with "Distinct"

User553887-OracleJan 18 2007 — edited Jan 22 2007
HI,

I have a table called "tab" with 2 colums "col1" and "col2", having the values as follows.

col1 col2
1 2
2 2.

Now, I need the output report to be having col1, and col2 with those values which are distinct ( in both the colums, if seen individually). In other words, here i need output as
col1 col2
1 2 . I should not get the record " 2 2" anymore.

Im not able to achieve this using distinct. Please help. ( this is just the actual problem .My production tables and data are different though !).

Rgds,
Suman

Comments

Gaz in Oz

Well, as per I'm describing, the results looks good on :

- Toad for Oracle 11

- PL/SQL developer 11

- SQL developer

Please share you "Preferences" and show a screenshot of your sqldev setup, encoding, font, etc showing the inserts and select "looks good".

sqldev for me shows some chars ok, but for japanese, chinese, not "looks good".

What languages do you have installed in windows?

what is your locale set to?

A thought is to use powershell setup to display utf-8, and NLS_LANG set to ".UTF8", to start sqlplus

top.gun

I know there is a perception out there that you need to set the NLS_LANG to the database characterset - but this is not true!

NLS_LANG needs to be set to the characterset of the client (ie your workstation).

See the following FAQ.

nls_lang faq

Answer

Do not fight with SQL*Plus in Command Prompt to get multilingual support. BTW, what do you need it for? Command Prompt is an administrative environment with limited internationalization. It does not support the full set of Windows fonts and the fonts available do not cover all languages in your example. There is no complex script rendering for Command Prompt, which means that complex scripts, such as Arabic, Hebrew, Devanagari, or Thai will not work. Use a GUI tool instead.

If you need to run some multilingual scripts via Command Prompt, prepare the scripts in Notepad or Notepad++ and save as UTF-8. Run the script in SQL*Plus after setting NLS_LANG to .AL32UTF8, spooling the output to a spool file. View the spool file in Notepad or Notepad++ making sure it is opened as UTF-8.

Thanks,
Sergiusz

Marked as Answer by chijar · Sep 27 2020
chijar

Thanks to all of you for your responses guys.

1 - 4
Locked Post
New comments cannot be posted to this locked post.

Post Details

Locked on Feb 19 2007
Added on Jan 18 2007
18 comments
2,673 views