asked on

Oracle SQL formatting in a query

I am using PL SQL (Oracle) to run a have a query and when I copy the results into an Excel spreadsheet the "clm_no" ( looks like this "1.7282E+14" instead of the correct format "172820014002563". I tried using "CAST(a.clm_no AS VARCHAR2(50)) as Claim_Number", but it did work for all of the fields.

Is there a way to make sure they clm_no's will copy correctly to the spreadsheet? Below is the code:

select distinct cin, lname, fname, dob, bill_type, net_amt as Net_Amount, srv_from_date, srv_thru_date, clm82_payee_id as Payee_ID, clm82_chk_date as Check_Date,
clm82_chk_num as Check_Number,
npi, project_code, clm_no AS Claim_Number, patient_control as Patient_Control_Number, ppg, prv00_prv_name as PPG_Name
from phdp_claims a left join phdp_evr b on substr(b.clm_no,4) = clm_no
left join cn114261.dhcs_update dhs ON substr(dhs.ccn, 4) = clm_no
where dhs.ccn is null
and npi in ('xxxxxxxxxx')
and srv_thru_date <= '31-dec-2017'
order by cin;

ASKER CERTIFIED SOLUTION

Kelvin Sparks

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Russ Suter

You will need to change the format of the column in Excel to "Text" BEFORE you paste the values. Otherwise, Excel reads the value as numeric and follows its general formatting rules. To change the format:

1. Right click on the column and select "Format Cells..." from the context menu
2. In the first tab, confusingly named "Number", select "Text" from the Category list box and click OK.
3. Now you can paste your values into Excel and the full numeric value will be preserved as a text object.

Scott Palmer

ASKER

After further examination some fields are numeric and some are alpha-numeric and it is the numeric fields that have the problem when copying into Excel. I can format the Excel column as text and it copies fine, but I would like to not have to do that as I have to remember to do that and remembering minutia like that is not my forte.

So is there a format option that will force the field into a "text" value?

Russ Suter

If I recall correctly you can prepend the value with a single apostrophe (') character before pasting. This queues Excel to treat it as a text value rather than numeric. Other than that you really don't have any options. This is how Excel behaves and there are no options to change that behavior.

Scott Palmer

ASKER

Thanks for your help.

Scott