Avatar billede kim1a Ekspert
03. februar 2020 - 19:03 Der er 2 kommentarer og
1 løsning

Antal af kolonner giver fejl?

Antal kolonner i DB udtræk
Kære eksperter

Jeg har overtaget noget kode som jeg gerne vil lave en smule om på. Jeg vil gerne indsætte flere kolonner i det udtræk som skabes. Det lykkes op til et vist punkt (antal kolonner er 44, når jeg vil indsætte den 45. kolonne så meldes fejl uanset hvilken rækkefølge jeg sætter dem i).

Har nogen været ude for lignende? Er der en begrænsning? Det skal sige jeg er ikke SQL skarp, men mere VBA.

Det jeg er nervøs for er om der måske er noget i den yderligere kode der skaber  problemet.

Min kode ser sådan ud:
SET
@Konto_start = 10000

SET
@Konto_slut = 99998

SELECT
CONVERT(INT,G_L_E.[Entry No_]) AS Løbenr,
CONVERT(INT,G_L_E.[G_L Account No_]) As Kontonr,
G_L_E.[Posting Date] AS Bogføringsdato,
G_L_E.[Document Type] As Bilagstype,
G_L_E.[Document No_] AS Bilagsnr,
G_L_E.Description AS Beskrivelse,
--næste linje betyder blot tom kolonne med navn
'' as placeholder1BalAccountNo,
G_L_E.[Amount] AS Beløb,
'' as placeholder2Amount,
'' as placeholder3SourceCode,

Det er så disse PlaceholderX jeg laver mange af.

Under alle disse står der noget i retning af dette, og jeg overvejede om table ID = 17 har en konsekvens:

-- DEBITOR
(SELECT L_E_D.[Entry No_], L_E_D.[Dimension Value Code] AS DEBITOR
FROM
[DynamicsNAV501].[dbo].[NetDesign A_S$Ledger Entry Dimension] L_E_D
WHERE
L_E_D.[Entry No_] IN (SELECT G_L_E.[Entry No_]
FROM
[DynamicsNAV501].[dbo].[NetDesign A_S$G_L Entry] G_L_E
WHERE
G_L_E.[G_L Account No_] BETWEEN @Konto_start AND @Konto_slut AND G_L_E.[Posting Date] BETWEEN @Primo AND @Ultimo)
AND L_E_D.[Dimension Code] LIKE 'DEBITOR' AND L_E_D.[Table ID] = 17) DEBITOR
ON
G_L_E.[Entry No_] = DEBITOR.[Entry No_]
LEFT OUTER JOIN

Er der nogen som har forstand. Jeg sender også gerne koden i fuld længde hvis det giver mening, men man kan jo ikke genskabe/trække.
Avatar billede a1a1 Novice
03. februar 2020 - 22:20 #1
en ide... måske det er fordi din LIKE giver mere end et resultat?
LIKE 'DEBITOR' AND L_E_D.[Table ID] = 17) DEBITOR
Avatar billede a1a1 Novice
03. februar 2020 - 22:29 #2
men det er nu også noget "sjov" kode, hvorfor:
SELECT
CONVERT(INT,G_L_E.[Entry No_]) AS Løbenr,
CONVERT(INT,G_L_E.[G_L Account No_]) As Kontonr,
G_L_E.[Document Type] As Bilagstype,
G_L_E.[Document No_] AS Bilagsnr,
G_L_E.Description AS Beskrivelse,
--næste linje betyder blot tom kolonne med navn
'' as placeholder1BalAccountNo,
G_L_E.[Amount] AS Beløb,

det er jo det samme der bliver "valgt"....??? :)

prøv at skiv hele koden
Avatar billede kim1a Ekspert
04. februar 2020 - 09:21 #3
Det er som sagt ikke mig der har skrevet koden, og er ikke skarp på hvordan eller hvorfor. Data i Navision har dog mange dimensioner, så det er noget med at have bygget så det kan trækkes fra de forskellige tabeller.

Hele koden:
"DECLARE
@Primo AS DATETIME

DECLARE
@Ultimo AS DATETIME

DECLARE
@Konto_start AS INT

DECLARE
@Konto_slut AS INT

SET
@Primo = CONVERT(DATETIME,'20200129')

SET
@Ultimo = CONVERT(DATETIME,'20200131')"

"SET
@Konto_start = 10000

SET
@Konto_slut = 99998

SELECT
CONVERT(INT,G_L_E.[Entry No_]) AS Løbenr,
CONVERT(INT,G_L_E.[G_L Account No_]) As Kontonr,
G_L_E.[Posting Date] AS Bogføringsdato,
G_L_E.[Document Type] As Bilagstype,
G_L_E.[Document No_] AS Bilagsnr,
G_L_E.Description AS Beskrivelse,
'' as placeholder1BalAccountNo,
G_L_E.[Amount] AS Beløb,
'' as placeholder2Amount,
'' as placeholder3SourceCode,
'' as placeholder4SystemCreatedEntry,
'' as placeholder5PriorYearEntry,
'' as placeholder6JobNo,
'' as placeholder7Quantity,
'' as placeholder8VATAmount,
'' as placeholder9BusinessUnitCode,
'' as placeholder10JournalBatchName,
'' as placeholder11ReasonCode,
'' as placeholder12GenPostingType,
'' as placeholder13GenBusPostingGroup,
'' as placeholder14GenProdPostingGroup,
'' as placeholder15BalAccountType,
'' as placeholder16TransactionNo,
'' as placeholder17DebitAmount,
'' as placeholder18CreditAmount,
Getdate() as placeholder19Documentdate,
'' as placeholder20ExternalDocumentNo,
'' as placeholder21SourceType,
DEBITOR.DEBITOR,
OMSART.OMSART,
PRODUCENT.PRODUCENT,
PRODUKT01.PRODUKT01,
PRODUKT.PRODUKT,
AFDELING.AFDELING,
DEBKAT.DEBKAT,
KONCERN.KONCERN,
SKIHANDEL.SKIHANDEL,
SKIVARE.SKIVARE,
PROJEKT.PROJEKT,
REMEDY.REMEDY,
Cus.Name as debitornavn

FROM
[DynamicsNAV501].[dbo].[NetDesign A_S$G_L Entry] G_L_E
LEFT OUTER JOIN


-- AFDELING
(SELECT L_E_D.[Entry No_], L_E_D.[Dimension Value Code] AS AFDELING
FROM
[DynamicsNAV501].[dbo].[NetDesign A_S$Ledger Entry Dimension] L_E_D
WHERE
L_E_D.[Entry No_] IN (SELECT G_L_E.[Entry No_]
FROM
[DynamicsNAV501].[dbo].[NetDesign A_S$G_L Entry] G_L_E
WHERE
G_L_E.[G_L Account No_] BETWEEN @Konto_start AND @Konto_slut AND G_L_E.[Posting Date] BETWEEN @Primo AND @Ultimo) AND L_E_D.[Dimension Code] LIKE 'AFDELING' AND L_E_D.[Table ID] = 17) AFDELING
ON
G_L_E.[Entry No_] = AFDELING.[Entry No_]
LEFT OUTER JOIN

-- DEBITOR
(SELECT L_E_D.[Entry No_], L_E_D.[Dimension Value Code] AS DEBITOR
FROM
[DynamicsNAV501].[dbo].[NetDesign A_S$Ledger Entry Dimension] L_E_D
WHERE
L_E_D.[Entry No_] IN (SELECT G_L_E.[Entry No_]
FROM
[DynamicsNAV501].[dbo].[NetDesign A_S$G_L Entry] G_L_E
WHERE
G_L_E.[G_L Account No_] BETWEEN @Konto_start AND @Konto_slut AND G_L_E.[Posting Date] BETWEEN @Primo AND @Ultimo)
AND L_E_D.[Dimension Code] LIKE 'DEBITOR' AND L_E_D.[Table ID] = 17) DEBITOR
ON
G_L_E.[Entry No_] = DEBITOR.[Entry No_]
LEFT OUTER JOIN

-- DEBKAT
(SELECT L_E_D.[Entry No_], L_E_D.[Dimension Value Code] AS DEBKAT
FROM
[DynamicsNAV501].[dbo].[NetDesign A_S$Ledger Entry Dimension] L_E_D
WHERE
L_E_D.[Entry No_] IN (SELECT G_L_E.[Entry No_]
FROM
[DynamicsNAV501].[dbo].[NetDesign A_S$G_L Entry] G_L_E
WHERE
G_L_E.[G_L Account No_] BETWEEN @Konto_start AND @Konto_slut AND G_L_E.[Posting Date] BETWEEN @Primo AND @Ultimo)
AND L_E_D.[Dimension Code] LIKE 'DEBKAT' AND L_E_D.[Table ID] = 17) DEBKAT
ON
G_L_E.[Entry No_] = DEBKAT.[Entry No_]
LEFT OUTER JOIN

-- KONCERN
(SELECT L_E_D.[Entry No_], L_E_D.[Dimension Value Code] AS KONCERN
FROM
[DynamicsNAV501].[dbo].[NetDesign A_S$Ledger Entry Dimension] L_E_D
WHERE
L_E_D.[Entry No_] IN (SELECT G_L_E.[Entry No_]
FROM
[DynamicsNAV501].[dbo].[NetDesign A_S$G_L Entry] G_L_E
WHERE
G_L_E.[G_L Account No_] BETWEEN @Konto_start AND @Konto_slut AND G_L_E.[Posting Date] BETWEEN @Primo AND @Ultimo)
AND L_E_D.[Dimension Code] LIKE 'KONCERN' AND L_E_D.[Table ID] = 17) KONCERN
ON
G_L_E.[Entry No_] = KONCERN.[Entry No_]
LEFT OUTER JOIN

-- OMSART
(SELECT
  L_E_D.[Entry No_], L_E_D.[Dimension Value Code] AS OMSART
FROM
  [DynamicsNAV501].[dbo].[NetDesign A_S$Ledger Entry Dimension] L_E_D
WHERE
  L_E_D.[Entry No_] IN (SELECT
  G_L_E.[Entry No_]
  FROM
  [DynamicsNAV501].[dbo].[NetDesign A_S$G_L Entry] G_L_E
  WHERE
  G_L_E.[G_L Account No_] BETWEEN @Konto_start AND @Konto_slut AND G_L_E.[Posting Date] BETWEEN @Primo AND @Ultimo)
  AND L_E_D.[Dimension Code] LIKE 'OMSART' AND L_E_D.[Table ID] = 17) OMSART
ON
G_L_E.[Entry No_] = OMSART.[Entry No_]
LEFT OUTER JOIN

-- PRODUCENT
(SELECT
  L_E_D.[Entry No_], L_E_D.[Dimension Value Code] AS PRODUCENT
FROM
  [DynamicsNAV501].[dbo].[NetDesign A_S$Ledger Entry Dimension] L_E_D
WHERE
  L_E_D.[Entry No_] IN (SELECT
  G_L_E.[Entry No_]
  FROM
  [DynamicsNAV501].[dbo].[NetDesign A_S$G_L Entry] G_L_E
  WHERE
  G_L_E.[G_L Account No_] BETWEEN @Konto_start AND @Konto_slut AND G_L_E.[Posting Date] BETWEEN @Primo AND @Ultimo)
  AND L_E_D.[Dimension Code] LIKE 'PRODUCENT' AND L_E_D.[Table ID] = 17) PRODUCENT
ON
G_L_E.[Entry No_] = PRODUCENT.[Entry No_]
LEFT OUTER JOIN

-- PRODUKT
(SELECT
  L_E_D.[Entry No_], L_E_D.[Dimension Value Code] AS PRODUKT
FROM
  [DynamicsNAV501].[dbo].[NetDesign A_S$Ledger Entry Dimension] L_E_D
WHERE
  L_E_D.[Entry No_] IN (SELECT
  G_L_E.[Entry No_]
  FROM
  [DynamicsNAV501].[dbo].[NetDesign A_S$G_L Entry] G_L_E
  WHERE
  G_L_E.[G_L Account No_] BETWEEN @Konto_start AND @Konto_slut AND G_L_E.[Posting Date] BETWEEN @Primo AND @Ultimo)
  AND L_E_D.[Dimension Code] LIKE 'PRODUKT' AND L_E_D.[Table ID] = 17) PRODUKT
ON
G_L_E.[Entry No_] = PRODUKT.[Entry No_]
LEFT OUTER JOIN
-- PRODUKT01
(SELECT
  L_E_D.[Entry No_], L_E_D.[Dimension Value Code] AS PRODUKT01
FROM
  [DynamicsNAV501].[dbo].[NetDesign A_S$Ledger Entry Dimension] L_E_D
WHERE
  L_E_D.[Entry No_] IN (SELECT
  G_L_E.[Entry No_]
  FROM
  [DynamicsNAV501].[dbo].[NetDesign A_S$G_L Entry] G_L_E
  WHERE
  G_L_E.[G_L Account No_] BETWEEN @Konto_start AND @Konto_slut AND G_L_E.[Posting Date] BETWEEN @Primo AND @Ultimo)
  AND L_E_D.[Dimension Code] LIKE 'PRODUKT01' AND L_E_D.[Table ID] = 17) PRODUKT01
ON
G_L_E.[Entry No_] = PRODUKT01.[Entry No_]
LEFT OUTER JOIN
-- PROJEKT
(SELECT
  L_E_D.[Entry No_], L_E_D.[Dimension Value Code] AS PROJEKT
FROM
  [DynamicsNAV501].[dbo].[NetDesign A_S$Ledger Entry Dimension] L_E_D
WHERE
  L_E_D.[Entry No_] IN (SELECT
  G_L_E.[Entry No_]
  FROM
  [DynamicsNAV501].[dbo].[NetDesign A_S$G_L Entry] G_L_E
  WHERE
  G_L_E.[G_L Account No_] BETWEEN @Konto_start AND @Konto_slut AND G_L_E.[Posting Date] BETWEEN @Primo AND @Ultimo)
  AND L_E_D.[Dimension Code] LIKE 'PROJEKT' AND L_E_D.[Table ID] = 17) PROJEKT
ON
G_L_E.[Entry No_] = PROJEKT.[Entry No_]
LEFT OUTER JOIN
-- REMEDY
(SELECT
  L_E_D.[Entry No_], L_E_D.[Dimension Value Code] AS REMEDY
FROM
  [DynamicsNAV501].[dbo].[NetDesign A_S$Ledger Entry Dimension] L_E_D
WHERE
  L_E_D.[Entry No_] IN (SELECT
  G_L_E.[Entry No_]
  FROM
  [DynamicsNAV501].[dbo].[NetDesign A_S$G_L Entry] G_L_E
  WHERE
  G_L_E.[G_L Account No_] BETWEEN @Konto_start AND @Konto_slut AND G_L_E.[Posting Date] BETWEEN @Primo AND @Ultimo)
  AND L_E_D.[Dimension Code] LIKE 'REMEDY' AND L_E_D.[Table ID] = 17) REMEDY
ON
G_L_E.[Entry No_] = REMEDY.[Entry No_]
LEFT OUTER JOIN
-- SKIHANDEL
(SELECT
  L_E_D.[Entry No_], L_E_D.[Dimension Value Code] AS SKIHANDEL
FROM
  [DynamicsNAV501].[dbo].[NetDesign A_S$Ledger Entry Dimension] L_E_D
WHERE
  L_E_D.[Entry No_] IN (SELECT
  G_L_E.[Entry No_]
  FROM
  [DynamicsNAV501].[dbo].[NetDesign A_S$G_L Entry] G_L_E
  WHERE
  G_L_E.[G_L Account No_] BETWEEN @Konto_start AND @Konto_slut AND G_L_E.[Posting Date] BETWEEN @Primo AND @Ultimo)
  AND L_E_D.[Dimension Code] LIKE 'SKIHANDEL' AND L_E_D.[Table ID] = 17) SKIHANDEL
ON
G_L_E.[Entry No_] = SKIHANDEL.[Entry No_]
LEFT OUTER JOIN
-- SKIVARE
(SELECT
  L_E_D.[Entry No_], L_E_D.[Dimension Value Code] AS SKIVARE
FROM
  [DynamicsNAV501].[dbo].[NetDesign A_S$Ledger Entry Dimension] L_E_D
WHERE
  L_E_D.[Entry No_] IN (SELECT
  G_L_E.[Entry No_]
  FROM
  [DynamicsNAV501].[dbo].[NetDesign A_S$G_L Entry] G_L_E
  WHERE
  G_L_E.[G_L Account No_] BETWEEN @Konto_start AND @Konto_slut AND G_L_E.[Posting Date] BETWEEN @Primo AND @Ultimo)
  AND L_E_D.[Dimension Code] LIKE 'SKIVARE' AND L_E_D.[Table ID] = 17) SKIVARE
ON
G_L_E.[Entry No_] = SKIVARE.[Entry No_]
LEFT OUTER JOIN
[DynamicsNAV501].[dbo].[NetDesign A_S$Customer] Cus
ON
DEBITOR.DEBITOR = Cus.No_
WHERE
G_L_E.[G_L Account No_] BETWEEN @Konto_start AND @Konto_slut AND G_L_E.[Posting Date] BETWEEN @Primo AND @Ultimo
AND G_L_E.Amount <> 0
ORDER BY
G_L_E.[Posting Date], [G_L Account No_]
"
Avatar billede Ny bruger Nybegynder

Din løsning...

Tilladte BB-code-tags: [b]fed[/b] [i]kursiv[/i] [u]understreget[/u] Web- og emailadresser omdannes automatisk til links. Der sættes "nofollow" på alle links.

Loading billede Opret Preview
Kategori
Computerworld tilbyder specialiserede kurser i database-management

Log ind eller opret profil

Hov!

For at kunne deltage på Computerworld Eksperten skal du være logget ind.

Det er heldigvis nemt at oprette en bruger: Det tager to minutter og du kan vælge at bruge enten e-mail, Facebook eller Google som login.

Du kan også logge ind via nedenstående tjenester