Skip to main content

[DSP / Transform] Invalid Column with use of ORDER BY

Caroline St.John
  • Updated

Issue

Processing a Target Report produces an 'Invalid Column' error even though the column exists in the SQL View. 

Resolution

Until DSP 6.5, remove the ORDER BY clause from the report view and register the ORDER BY column/s manually in the Report Order By setting on the Vertical View of the Transform report.

Why is this Happening?

This is related to your use of ORDER BY in the report view.  The best method to use here is to remove the ORDER BY from the view and then populate the Report Order By setting on the vertical of the Report registration in Transform.  Here are some notes posted to a similar ticket:

1. The error is unrelated to whether or not the ORDER BY column in the report view is aliased.  I can produce it with a regular column as well.  This is simply a bug with reports that contain ORDER BY clauses.  It has been reported and is resolved in the DSP 6.5 release (DSP-5449 - Order By clauses cause Reports to fail in Transform).

2. The reason that the DSP-5449 bug was not resolved sooner is that there is little point in having an ORDER BY clause in a view.  An ORDER BY clause will only affect the query within the design mode of the view.  Once you run SELECT * FROM tv<report>_Sel, the ordering is gone.

3. The purpose of the Report Order By setting on the vertical of the report record in Transform is to sort the report records in the Excel file generated when the report is 'Executed'.  The Report Order By setting does not affect the ordering of records when clicking 'View Report'.  This is simply displaying the results of SELECT * FROM tv<report>_Sel.  By the way, I have confirmed that an aliased field can be entered for the Report Order By setting and the Excel file is sorted appropriately.

4. In DSP 6.5, the fix to the DSP-5449 bug will allow you to have an ORDER BY clause in your view.  But this will basically just serve to be an alternate way of populating/maintaining the Report Order By setting in Transform, because nothing is gained by having an ORDER BY clause in a view (outside of when you're designing it).



Related articles