March 23, 2018 at 4:06 am
select
case when a.generatedrank=1
then (select a.journal, a.invoicenumber)
else (select a.journal, a.period, a.invoicedate)end
from
(select
dense_rank () over (order by debinvjour.invoicenumber) as row,
row_number () over (partition by debinvjour.invoicenumber order by ledtrans.accountnumber) as generatedrank,
case when ledtrans.numberseries='vk' then 'v01'
when ledtrans.numberseries='vkcn' then 'v02'
else ledtrans.numberseries end AS journal,
When I run this query I get error :"Only one expression can be specified in the select list when the subquery is not introduced with EXISTS"
When I only select one column I have no error.
case
when a.generatedrank=1
then (select a.journal)
else (select a.journal) end
from
The problem is I need output with different number of columns.
I already tried this : case when a.generatedrank=1 then c1 else c2
case when a.generatedrank=1 then c3 else c4
case when a.generatedrank=1 then c5 end
but then I have 'NULL'-column at the end of row 3 instead of nothing
March 23, 2018 at 4:31 am
You can't have different numbers of columns in different rows.
Can you post the whole query?
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
March 23, 2018 at 4:55 am
Hereby the complete query.
What I need is a script to transfer the records of my invoices to a new software.
The format of the CSV input-file is :
row 1 invoice header : journal, invoicenumber, rownumber, customeraccountnumber, amountmst, vatcode, vatamount
row 2 to ... invoice detail : journal, invoicenumber, ledgeraccountnumber, customervat, period, invoicedate, invoiceduedate, salesnumber, amountmst, exchangecode
row 2 can return multiple times depending on the ledgeraccountnumber
For each new invoice, the rownumber increases.
I have al the fields I need in my query, but I don't know how to obtain the above output.
select
case when a.generatedrank=1
then (select a.journal, a.invoicenumber)
else (select a.journal, a.period, a.invoicedate)end
from
(select
dense_rank () over (order by debinvjour.invoicenumber) as row,
row_number () over (partition by debinvjour.invoicenumber order by ledtrans.accountnumber) as generatedrank,
case when ledtrans.numberseries='vk' then 'v01'
when ledtrans.numberseries='vkcn' then 'v02'
else ledtrans.numberseries end AS journal,
case when (ledtrans.accountnumber =400000 OR ledtrans.accountnumber=400001) then debinvjour.invoiceaccount else ledtrans.accountnumber end AS accountnumber,
convert (varchar(50),cast(ledtrans.amountmst as money),1) AS amountmst,
convert (varchar(50),cast(ledtrans.vatamount as money),1) AS vatamount,
case when ledtrans.vatcode='vbtwex' then 'EX'
when ledtrans.vatcode='vbtwic' then 'CM'
when ledtrans.vatcode='vtwicd' then 'VCMD'
when ledtrans.vatcode='vbtw03' then '21'
else ledtrans.vatcode end AS vatcode,
case when ledtrans.vatcode='vbtwex' then '06'
when ledtrans.vatcode='vbtwic' then '04'
when ledtrans.vatcode='vtwicd' then '04'
when ledtrans.vatcode='vbtw03' then '01'
else '00' end AS customervat,
convert (numeric,debinvjour.invoicenumber) AS invoicenumber,
convert (varchar(10),debinvjour.invoicedate,111) AS invoicedate,
convert (VARCHAR(10),debinvjour.invoiceduedate,111)AS invoiceduedate,
right (convert (varchar(7),debinvjour.invoicedate,111),7) AS period,
debinvjour.salesnumber AS salesnumber,
case when debinvjour.exchangecode is null then debinvjour.exchangecode else 'eur' end AS exchangecode
from debinvjour,ledtrans
where (ledtrans.numberseries='vk' or ledtrans.numberseries='vkcn') and (debinvjour.invoicedate between convert(datetime,'2018-02-05 00:00:00',102)
AND CONVERT(datetime,'2018-02-09 00:00:00',102)) and (debinvjour.ledgervoucher = ledtrans.voucher) and (ledtrans.accountnumber not between 410000 and 499999))
a
March 23, 2018 at 5:19 am
Thanks.
-- remove "noise" - anything which isn't required as output, or for the query to function
-- use proper JOINs
SELECT
--case
--when generatedrank=1
--then (select journal, invoicenumber)
--else (select journal, period, invoicedate)
--end,
generatedrank,
journal,
invoicenumber = CASE WHEN generatedrank = 1 THEN invoicenumber ELSE NULL END,
[period] = CASE WHEN generatedrank = 1 THEN NULL ELSE [period] END,
invoicedate = CASE WHEN generatedrank = 1 THEN NULL ELSE invoicedate END
FROM (
SELECT
row_number () over (partition by debinvjour.invoicenumber order by ledtrans.accountnumber) as generatedrank, -- ##
case
when ledtrans.numberseries='vk' then 'v01'
when ledtrans.numberseries='vkcn' then 'v02'
else ledtrans.numberseries end AS journal, -- ##
convert(numeric,debinvjour.invoicenumber) AS invoicenumber, -- ##
convert(varchar(10),debinvjour.invoicedate,111) AS invoicedate, -- ##
right(convert(varchar(7),debinvjour.invoicedate,111),7) AS [period] -- ##
FROM debinvjour
INNER JOIN ledtrans
ON debinvjour.ledgervoucher = ledtrans.voucher
WHERE ledtrans.numberseries IN ('vk','vkcn')
AND (debinvjour.invoicedate between convert(datetime,'2018-02-05 00:00:00',102) AND CONVERT(datetime,'2018-02-09 00:00:00',102))
AND (ledtrans.accountnumber not between 410000 and 499999)
) a
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
March 23, 2018 at 9:58 am
Having spent a little time on this, I reckon the following code snippet should help. You will need to use appropriate string conversions and NULL checking so that the columns concatenate nicely.
SELECT
[Row] = ROW_NUMBER() OVER(ORDER BY invoicenumber),
[generatedrank] = 0,
debinvjour.ledgervoucher,
[TextLine] = journal + ',' + invoicenumber + ',' + rownumber + ',' + customeraccountnumber + ',' + amountmst + ',' + vatcode + ',' + vatamount
INTO #debinvjour
FROM debinvjour
WHERE (debinvjour.invoicedate BETWEEN convert(datetime,'2018-02-05 00:00:00',102) AND CONVERT(datetime,'2018-02-09 00:00:00',102))
SELECT
[Row],
[generatedrank] = ROW_NUMBER() OVER(PARTITION BY debinvjour.invoicenumber ORDER BY ledtrans.accountnumber),
[TextLine] = journal + ',' + invoicenumber + ',' + ledgeraccountnumber + ',' + customervat + ',' + [period] + ',' + invoicedate + ',' + invoiceduedate + ',' + salesnumber + ',' + amountmst + ',' + exchangecode
INTO #ledtrans
FROM #debinvjour
INNER JOIN ledtrans
ON debinvjour.ledgervoucher = ledtrans.voucher
WHERE (ledtrans.numberseries='vk' or ledtrans.numberseries='vkcn')
and (ledtrans.accountnumber not between 410000 and 499999)
SELECT [Row], [generatedrank], [TextLine]
FROM (
SELECT [Row], [generatedrank], [TextLine] FROM #debinvjour
UNION ALL
SELECT [Row], [generatedrank], [TextLine] FROM #ledtrans
) d
ORDER BY [Row], [generatedrank]
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply