How to insert a blank column in a SQL query?

Question

This is the SQL code I currently use via the Invantive Control for Excel, linked with our Exact Online DB.

As you can see in the code, in front of the line of the second select case I would like to enter 4 blank columns. The way it's coded right now doesn't work of course, but it was just a try-out. :)

<pre>select  date
,       InvoiceNumber
,       AccountCode
,       AccountName
,       YourRef
,       GLAccountCode
,       GLAccountDescription
,       CostUnit
,       CostUnitDescription
,       ProjectCode
,       ProjectDescription
,       Description
,       AmountDC
,       AmountFC
,       CostCenter
,       FinancialPeriod
,       JournalDescription
,       substr(GLAccountCode, 1, 1) type
,       case
        when substr(GLAccountCode, 1, 1) = '6'
        then 'KOST'
        else 'OPBRENGST'
        end
        pl
<<HERE>>
,       case
        when substr(GLAccountCode, 1, 1) = '6'
        then AmountDC
        else 0
        end
        debet
,       case
        when substr(GLAccountCode, 1, 1) = '6'
        then 0
        else AmountDC
        end
        credit
,       AmountDC dc2
from    TransactionLines
where   FinancialYear = 2017 and JournalCode >='600'
order   by date<code>

Second part of my question: can I add excel formulas into these blank columns, via the model editor? This could be useful so everytime when synchronizing with Exact Online, these formulas are not erased, but refreshed together with the data.

`select colx, '', coly ...`. But why do you want to do this? Consider NULL instead. — jarlh, Sep 13 '17 at 10:20
Possible duplicate of [Adding a new empty column to sql select query](https://stackoverflow.com/questions/19696877/adding-a-new-empty-column-to-sql-select-query) — A. A. Sebastian, Sep 13 '17 at 11:08

score 1 · Accepted Answer · answered Sep 13 '17 at 10:34

When running on Invantive Control, you need to change both the SQL as well as make sure that your layout allows for additional columns.

First of all insert:

,     null COLUMNNAME1
,     null COLUMNNAME2
,     null COLUMNNAME3
,     null COLUMNNAME4

where you need it in the column list.

Then choose 'Refresh' with Fields tab in the model editor.

On an application of 'Synchronize' button, you will see that the columns where necessary move to the right. But...

when you have an Excel range defined in Presentation tab in the model editor for the block, you will need to resize the Excel range to accomodate the 4 new columns. For instance by changing your layout range to include the 4 new columns.

Ok, great to hear! You can remove comments that are no longer applicable by the (x) button next to it. When you have any tip for future visitors to this question (like why it didn't work the first time), it is highly appreciated when you add that tip here for instance as a comment. — Guido Leenders, Sep 13 '17 at 10:52

How to insert a blank column in a SQL query?

1 Answers1