No Column name was specified for column 4 of 'l'

Question

I am getting this error with the following code. I also get the error for column 3 of 'c'

Here is a snapshot of the errors I am getting with this code.

I have updated my SQL below. This is as of 6/5/14

USE SYNLIVE 

SELECT 
 INLOC.Itemkey, l.Description, INLOC.Location, INLOC.Qtyonhand, 
   sum(l.POqtyRemn) [POqtyRemn], 
   SUM(c.Qtyord) AS [COqtyOrd], h.Statusflg
FROM 
   INLOC INLOC

INNER JOIN 
 (SELECT 
   POLIN.Itemkey, POLIN.Description, POLIN.Location, POLIN.Pono,
   SUM(POLIN.Qtyremn) AS [POqtyRemn] 
FROM 
   POLIN POLIN 

GROUP BY 
   POLIN.Itemkey, POLIN.Description, POLIN.Location, POLIN.Pono) l ON INLOC.Itemkey = l.Itemkey
 INNER JOIN 
   (SELECT 
   POHDR.Statusflg, POHDR.Pono
FROM 
   POHDR POHDR 
   WHERE POHDR.Statusflg = 'NEW' OR POHDR.Statusflg = 'OPEN'
GROUP BY 
    POHDR.Statusflg, POHDR.Pono) poh ON l.Pono = poh.Pono
JOIN 
   OELIN c ON INLOC.Itemkey = c.Itemkey


INNER JOIN 
  (SELECT 
    OEHDR.Statusflg, OEHDR.Ordno
FROM
    OEHDR 
    WHERE OEHDR.Statusflg = 'NEW' OR OEHDR.Statusflg = 'OPEN'
GROUP BY
    OEHDR.Statusflg, OEHDR.Ordno) h ON c.Ordno = h.Ordno


WHERE 

 ((INLOC.Location = 'SPL') AND (l.POqtyRemn > 0)) OR ((INLOC.Location = 'SPL') AND (c.Qtyord > 0))
GROUP BY 
 INLOC.Itemkey, l.Description, INLOC.Location, h.Statusflg, inloc.Qtyonhand
    /* Add other fields that you are pulling -- you must group by all fields (or have a calc on them .. i.e. Sum(field) */
ORDER BY INLOC.Itemkey

What server software are you using? Looks like valid SQL to me. — D Stanley, Jun 04 '14 at 14:37
Is the query you use, exactly this one? Or have you changed anything? — ypercubeᵀᴹ, Jun 04 '14 at 14:47
I changed the name of the database and used 'X' in it's place here. Everything else stayed the same. — the_brianb, Jun 04 '14 at 14:51
you should download the free SQL Server 2008 Tools from Microsoft's website... it'll make all your testing much easier... then copy and paste my sql into SQL Server Management Studio (SSMS)... This will give you much better error messages.. and allow you to highlight and run portions of the SQL Statement(s). — Danimal111, Jun 06 '14 at 15:11

score 3 · Answer 1 · answered Jun 04 '14 at 15:11

3

Looks like this issue with later versions of office.

Namely that the Aliases are confusing MS Query. Try replacing your

SUM(somefield) as SomeAlias

with

SUM(somefield) as [SomeAlias]

answered Jun 04 '14 at 15:11

Steve Homer

3,852
2
22
41

Excellent point Steve... that is going to cause a lot of pain for a lot of developers (myself included)... thanks for the heads up. – Danimal111 Jun 04 '14 at 15:15
No doubt. SQL Server itself is pretty robust, but a lot of the support tools leave much to be desired. – Steve Homer Jun 04 '14 at 15:34

Danimal111 · Accepted Answer · 2014-06-06T14:52:06.513

1

Update: I see what you're after now... woah... you have to group by the first 3 fields and sum the last 3 fields in your first select statement... I updated the sql below.

Note the change to the final "group by". It has to include all fields... otherwise... run the individual select statements by themselves and make sure they are valid... seems ok other than the "group by".

As for your error... run the select statement on it's on that creates the L table. Based on that error, I believe that's giving you trouble... also add [] square brackets around the word "Description" as it's a keyword... shouldn't hurt you... but something is and that might be a good place to start. Once you have this statement working... and fix the group by... let us know what happens.

 SELECT 
           POLIN.Itemkey, POLIN.Description, POLIN.Location, 
           SUM(POLIN.Qtyremn) AS [POqtyRemn] 
        FROM 
           X.dbo.POLIN POLIN 
        GROUP BY 
           Itemkey, Description, Location

-- After a few more tweaks -- I flatlined it... so you no longer need the group by... I also added the where clauses to the individual subqueries (the inner join select statements).

Without the table schema, I can't guarantee I have all the syntax correct, but take a look... also as I mentioned... make sure you run the subqueries and compare them to the totals... for the ItemKey's (do a spot check) and the overall totals...

SELECT 
 INLOC.Itemkey, l.Description, INLOC.Location, INLOC.Qtyonhand, 
   l.POqtyRemn, 
   c.Qtyord, h.Statusflg
FROM 
  (
      select Itemkey, sum(QtyOnHand) [QtyOnHand] 
      from  INLOC 
      where Location = 'SPL'
      group by ItemKey 
  ) INLOC

INNER JOIN 
    (
        SELECT   Itemkey, Description, Location, Pono,SUM(Qtyremn) AS [POqtyRemn] 
        FROM POLIN 
        GROUP BY Itemkey, Description, Location, Pono
        having SUM(Qtyremn) > 0 --This will only return an ItemKey if it has remaining Qty on the PO [POQtyRemn]
    ) l ON INLOC.Itemkey = l.Itemkey

INNER JOIN 
   (
        SELECT Statusflg, Pono
        FROM POHDR 
        WHERE Statusflg = 'NEW' OR Statusflg = 'OPEN'
        GROUP BY 
            Statusflg, Pono
    ) poh ON l.Pono = poh.Pono
JOIN 
       OELIN c ON INLOC.Itemkey = c.Itemkey
INNER JOIN 
    (
        SELECT Statusflg, Ordno
        FROM OEHDR 
        WHERE Statusflg = 'NEW' OR Statusflg = 'OPEN'
        GROUP BY Statusflg, Ordno
    ) h ON c.Ordno = h.Ordno
WHERE 
    (l.POqtyRemn > 0) OR (c.Qtyord > 0)
ORDER BY INLOC.Itemkey

edited Jun 06 '14 at 14:52

answered Jun 04 '14 at 14:45

Danimal111

1,976
25
31

can you run the code that created the "L" table ... does that work correctly? – Danimal111 Jun 04 '14 at 14:59
Change the group by in the second select also ---SELECT OELIN.Itemkey, OELIN.Location, SUM(OELIN.Qtyord) AS COqtyOrd FROM X.dbo.OELIN GROUP BY OELIN.Itemkey, OELIN.Location – Danimal111 Jun 04 '14 at 15:00
A just corrected a few more bits of the sql... try it from my answer and see what happens... – Danimal111 Jun 04 '14 at 15:04
I tried just the L table query as suggested and it works just fine. Still not getting anything from the big query though. – the_brianb Jun 04 '14 at 15:50
Troubleshoot this way... run each statement on it's own.... then start to recreate the large statement by adding in one sql join clause at a time... don't eat the whole elephant! Do that and post any errors and your new sql. – Danimal111 Jun 04 '14 at 15:57
So I got rid of the errors by running the query in Microsoft SQL Server Management Studio. The problem is, now I have duplicate values again like I mentioned here. [link]http://stackoverflow.com/questions/24025675/sum-function-is-multiplied-by-4 – the_brianb Jun 04 '14 at 17:47
I answered your other question also.... if you try the SQL it should eliminate all dups... let me know if you have any questions... you're very close to perfect! ... Once you understand how to use the group by with sum().... the rest will be much easier and go much faster, I PROMISE!!! – Danimal111 Jun 05 '14 at 14:48
I appreciate all your answers as they have gotten me closer each time. For some reason, the quantities that are summed are all x times the size they should be, with x being the number of rows that were coming up for each item before. I feel that I am sooo close to this. – the_brianb Jun 05 '14 at 19:19
The answer you gave is correct, but I realized that my issue was a foreign key issue and stems from my lack of understanding of our database. Thanks for your help. My SQL statement was also assuming that for every Itemkey there would be a corresponding Purchase Order (POLIN). This isn't always the case and I realized it wasn't displaying those items. Is there a way to return a zero if there is no PO data available? – the_brianb Jun 05 '14 at 20:35
Word to the wise... do the math and check your numbers... also.. you have the same where clause on both sides of the OR (at the very end).... take out one... or check to see if they meant to have 2 separate conditions... make sure that the totals in each table match the overall totals.. I'm still not convinced we have the "final" answer. But Good WORK! – Danimal111 Jun 05 '14 at 20:40
I updated my SQL above. I also did catch the same WHERE statement. Thanks for pointing that out. – the_brianb Jun 05 '14 at 20:43

score 0 · Answer 3 · answered Feb 22 '18 at 08:15

0

I had to add ALIAS-es to all columns and then it worked.

Example:

replace

INLOC.Location

with

INLOC.Location AS Location

answered Feb 22 '18 at 08:15

Tadej

553
7
16

No Column name was specified for column 4 of 'l'

3 Answers3