Databases
StackOverflow2013
Postsdbo
POSpace character that persists in varchar2 in Oracle 11g for display in SSRS 2008
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POSpace character that persists in varchar2 in Oracle 11g for display in SSRS 2008
    primarykey
    data
    text
    <p>Is there a space or other non-printing character I can insert to a varchar2 column that will not be trimmed away and result in a NULL?</p> <p>I just want to insert a blank into the column so it displays nothing on an SSRS 2008 report. The column is part of a PK so it cannot be NULL. Of course using <code>''</code> doesn't work as this is seen as NULL in Oracle, and <code>' '</code> doesn't work because it's varchar2 and trimmed to be NULL.</p> <p>So is there a literal value I can insert instead that will display as nothing in SSRS but also can be inserted to a non-nullable varchar2 column in Oracle 11g?</p> <p>Thinking about it for a moment, I guess something like a tab character could do the job. But I look forward to your suggestions.</p> <p><strong>UPDATE</strong></p> <p>Whoops. Guess where the trimming behavior came from? My own RTRIM! Sorry about that. Let's just say I was mislead by my inexperience with Oracle and my frustration over this leading me to determine the error was in the product rather than in my query. But hey, it's not exactly a simple query.</p> <pre><code>INSERT INTO WeeklyInvoice (GUID, Mo, VendorName, CostCenter, WkNum, Amt) SELECT ExecID, Mo, VendorName, CostCenter, WkNum, Amt FROM ( WITH CostCenters AS ( SELECT REGEXP_SUBSTR(CostCenterList, '[^,]+', 1, LEVEL) CostCenter FROM DUAL CONNECT BY LEVEL &lt;= Length(CostCenterList) - Length(Replace(CostCenterList, ',', '')) + 1 ), Invoices AS ( SELECT TRUNC(I.Invoice_Dte, 'MM') Mo, (TRUNC(I.Invoice_Dte, 'W') - TRUNC(I.Invoice_Dte, 'MM')) / 7 + 1 WkNum, I.Vendor, V.Vendor_VName, RTrim(D.Dis_Acct_Unit) CostCenter, D.To_Base_Amt FROM CostCenters CC CROSS JOIN prod.IcCompany C INNER JOIN prod.ApDistrib D ON C.Company = D.Company AND D.Dis_Acct_Unit = CC.CostCenter INNER JOIN prod.ApInvoice I ON D.Invoice = I.Invoice AND D.Vendor = I.Vendor AND D.Suffix = I.Suffix AND D.Company = I.Company INNER JOIN prod.ApVenMast V ON I.Vendor = V.Vendor WHERE D.Cancel_Seq = 0 AND I.Cancel_Seq = 0 AND I.Invoice_Dte &gt;= ADD_MONTHS(FromDate, -2) AND I.Invoice_Dte &lt; ToDate ), Months AS ( SELECT ADD_MONTHS(FromDate, LEVEL - 1) Mo FROM DUAL CONNECT BY LEVEL &lt;= MONTHS_BETWEEN(ToDate, ADD_MONTHS(FromDate, -2)) ), Names AS ( SELECT DISTINCT I.Mo, I.Vendor, I.Vendor_VName, I.CostCenter FROM Invoices I UNION ALL SELECT M.Mo, '0', 'No Paid Invoices', ' ' FROM Months M WHERE NOT EXISTS ( SELECT I.* FROM Invoices I WHERE I.Mo = M.Mo ) ), Weeks AS ( SELECT LEVEL WkNum FROM DUAL CONNECT BY LEVEL &lt;= 5 ) SELECT N.Mo, N.Vendor_VName VendorName, N.CostCenter, W.WkNum, Sum(I.To_Base_Amt) Amt FROM Names N INNER JOIN Weeks W ON W.WkNum &lt; 5 OR EXTRACT (MONTH FROM (N.Mo + 28)) = EXTRACT (MONTH FROM N.Mo) LEFT JOIN Invoices I ON N.CostCenter = I.CostCenter AND N.Vendor = I.Vendor AND N.Mo = I.Mo AND W.WkNum = I.WkNum GROUP BY N.Mo, N.Vendor_VName, N.CostCenter, W.WkNum ) X; </code></pre>
    singulars
    1. PO
      singulars
      1. PTAnswer
    1. This table or related slice is empty.
    1. PTQuestion
    1. USErikE
    1. USErikE
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PO
      singulars
      1. PTAnswer
    1. VO
      singulars
      1. POSpace character that persists in varchar2 in Oracle 11g for display in SSRS 2008
      1. This table or related slice is empty.
      1. VTUpMod
    2. VO
      singulars
      1. POSpace character that persists in varchar2 in Oracle 11g for display in SSRS 2008
      1. This table or related slice is empty.
      1. VTUpMod
    1. COSince the column is a PK and cannot be null, why do you want to insert a blank into it? Either the data model is not correct or it's just a presentation issue that can be handled on the report side.
      singulars
      1. POSpace character that persists in varchar2 in Oracle 11g for display in SSRS 2008
      1. USEddie Awad
    2. COSince the report uses a matrix (that is, it pivots the data), when an entire time period of data is null, the report would display nothing at all. This is not desirable. Instead, I need a row for every week column that will be pivoted in order to display "No paid invoices" for that period with all 4 or 5 weeks. Thus I am UNION ALL SELECTing a "seed row" to use when there are no invoices at all. In that seed row, there is no account unit. So it is properly blank in this case, not null. The table is not intended for persistent storage but is just a temp table for the report.
      singulars
      1. POSpace character that persists in varchar2 in Oracle 11g for display in SSRS 2008
      1. USErikE
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload