How to build a query that gets its values from a file

oracleoracle-sql-developerquery

I'm trying to build a query that uses the values in a file

select Epm,Emp_num,Emp_Addr From Dir_Rimco_Table where Emp in (Dir_Emp_Rsed.txt)

Constraints

-I can't create a new table

-I can't modify Dir_Rimco_Table

-Current Privileges

Any idea how I can do that?

Best Answer

If the restriction on creating tables includes "external tables", then you need to get management to lift that restriction. As stated, you can't expect oracle (or any rdbms product, for that matter) to just magically be able to recognize some external file as a data source and be able to process it with SQL statements. External tables is how oracle does that. If you read up on external tables in the oracle docs, you will see that it is not a table that contains data within the database, but simply a description of an external file, and that allows oracle to treat that file as if it were an actual table.

Related Solutions

Mysql – How to GROUP_CONCAT DISTINCT values in a MySQL query that gets number of records and min/max values

UPDATE Now I see your error. The inner query uses aggregation, and takes out sizeRange which is not a column you aggregate on. So you only get "samples" of that column. Strictly speaking, your query is not valid SQL but MySQL allows it given a relaxed sql_mode.

So your query is inherently erroneous. Will see if I can help fix it.

ORIGINAL answer

My guess for you would be to check the value of group_concat_max_len.

It is by default just 1024, though you typically don't really want a limit for that.

The problem might be that you are only getting partial results, where, by chance or by order of evaluation, "S" and "XL" occupy first 1024 characters or more. I see no reason why "M" or "L" would not be there -- the GROUP_CONCAT doesn't do such distinctions.

So, try out:

SET group_concat_max_len := 1000000;

And execute your query again. If this works, make sure to set said param in you MySQL configuration file.

You may find my related post useful.

Where’s the Network Alias list in Oracle

tnsnames.ora should be located in TNS_ADMIN, which is defined as ORACLE_HOME/network/admin/.

Go to a command prompt (assuming Windows from your mention of "c:\") and execute the following:

echo %TNS_ADMIN%

echo %ORACLE_HOME%

Verify the directories returned, make sure that your tnsnames.ora file is in there.

Best Answer

Related Solutions

Mysql – How to GROUP_CONCAT DISTINCT values in a MySQL query that gets number of records and min/max values

Where’s the Network Alias list in Oracle

Related Question