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
– jarlh, Commented Sep 13, 2017 at 10:20
Possible duplicate of Adding a new empty column to sql select query — A. A. Sebastian
– A. A. Sebastian, Commented Sep 13, 2017 at 11:08

Guido Leenders · Accepted Answer · 2017-09-13 10:34:30Z

1

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.

answered Sep 13, 2017 at 10:34

Guido Leenders

4,2832 gold badges25 silver badges44 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Guido Leenders Over a year ago

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.

Collectives™ on Stack Overflow

How to insert a blank column in a SQL query?

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related