Msg 116, Level 16, State 1, Line 16

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Msg 116, Level 16, State 1, Line 16

Author

Topic

cathsith9
Starting Member

6 Posts

Posted - 2012-06-25 : 20:20:39

Hi,

I am running this query on sql server 2008 and parse completely. I try to add 1 field (select the current date from one of my table) like the query in bold text below and i don't want to put the selection into the conditional clause if possible.
but when i executed the query, there was error:
"Msg 116, Level 16, State 1, Line 16
Only one expression can be specified in the select list when the subquery is not introduced with EXISTS."

This is the query :

SELECT distinct
TRXSOHDR."INVNOA", TRXSOHDR."INVREFNO", TRXSOHDR."INVTAXNO", TRXSOHDR."DONOA", TRXSOHDR."InvDate", TRXSOHDR."Loc5Code", TRXSOHDR."CustomerPO", TRXSOHDR."Deliverto", TRXSOHDR."TermDays", TRXSOHDR."SubTotal", TRXSOHDR."TotDiscount", TRXSOHDR."DiscPromo", TRXSOHDR."Tax1", TRXSOHDR."Tax2", TRXSOHDR."Tax3", TRXSOHDR."Remark", TRXSOHDR."SubTotalNet", TRXSOHDR."GrandTotal", TRXSOHDR."TotComms",
TRXSODTL."ProdCode", TRXSODTL."BarCode", TRXSODTL."Qty", TRXSODTL."UnitPrice", TRXSODTL."Disc1", TRXSODTL."Disc2", TRXSODTL."Disc3", TRXSODTL."Disc4", TRXSODTL."ProdDesc", TRXSODTL."UOM", TRXSODTL."CtnNo", TRXSODTL."Remark",
EMPLOYEEMASTER."FirstName", EMPLOYEEMASTER."MiddleName", EMPLOYEEMASTER."LastName",
CUSTOMERHEADER."CUSTOMERCODE", CUSTOMERHEADER."CUSTNAME", CUSTOMERHEADER."ADDRESS1", CUSTOMERHEADER."ADDRESS2", CUSTOMERHEADER."POSTALCODE",
LOC5."Loc5Desc",
PRODUCTCOLOR."ColorDesc",
PRODUCTMASTER."ProdDimension", PRODUCTMASTER."ProdCommCode", PRODUCTMASTER."Article",
PRODUCTSIZE."SizeDescription",
(
select trxgrvdtl.prodcode, max(trxgrvhdr.grvdate) as a from trxgrvhdr
left outer join trxgrvdtl
ON trxgrvhdr."grvnoa" = trxgrvdtl."grvnoa"
group by trxgrvdtl.prodcode
) as "Last GRV Date",
TRXGRVDtl."qty" AS "Last GRV QTY"
FROM
{ oj (((((((("TRXSOHDR" TRXSOHDR
LEFT OUTER JOIN "CUSTOMERHEADER" CUSTOMERHEADER
ON TRXSOHDR."CustomerCode" = CUSTOMERHEADER."CUSTOMERCODE")
LEFT OUTER JOIN "EMPLOYEEMASTER" EMPLOYEEMASTER
ON TRXSOHDR."SalesCode" = EMPLOYEEMASTER."SalesCode")
LEFT OUTER JOIN "TRXSODTL" TRXSODTL
ON TRXSOHDR."INVNOA" = TRXSODTL."DONOA")
LEFT OUTER JOIN "LOC5" LOC5
ON TRXSOHDR."Loc5Code" = LOC5."Loc5Code")
LEFT OUTER JOIN "PRODUCTSIZE" PRODUCTSIZE
ON TRXSODTL."SizeCode" = PRODUCTSIZE."SizeCode")
LEFT OUTER JOIN "PRODUCTCOLOR" PRODUCTCOLOR
ON TRXSODTL."ColorCode" = PRODUCTCOLOR."ColorCode")
LEFT OUTER JOIN "PRODUCTMASTER" PRODUCTMASTER
ON TRXSODTL."ProdCode" = PRODUCTMASTER."ProdCode")
LEFT OUTER JOIN "TRXGRVDTL" TRXGRVDTL
ON trxgrvdtl."prodcode"=productmaster."prodcode")
LEFT OUTER JOIN "TRXGRVHDR" TRXGRVHDR
ON TRXGRVHDR."GRVNOA" = TRXGRVDTL."GRVNOA"
}
WHERE
TRXSOHDR."INVNOA" = '00000013449'
ORDER BY
TRXSOHDR."INVNOA" ASC,
TRXSOHDR."DONOA" ASC,
TRXSODTL."CtnNo" ASC

what am i missing? anybody could give a hand please? thanks before..

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2012-06-25 : 20:33:08

You can't return two columns in one column. You are trying to put two columns into Last GRV Date, and that's not possible unless you concatenate them into one. I'm really not sure what you want here though. I think you need to join to it as a derived table instead.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

cathsith9
Starting Member

6 Posts

Posted - 2012-06-25 : 20:47:11

thanks Tara..
Sorry, I am new with this sql things.
i just want to add 2 more columns for the last grv date and the last grv qty. this query before i got from crystal report sql query and i tried to add 2 more columns that i mentioned.
i copy and edit the query in sql server 2008.

do you have any suggestion from my problem?

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2012-06-25 : 21:04:21

How do your two columns relate back to the data that is returned from the existing query? We really need to see some sample data, rows from the current result set and how you plan to fit these new two in.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

cathsith9
Starting Member

6 Posts

Posted - 2012-06-25 : 21:37:05

For example I have 5 tables:
1. TRXSOHD (InvNo, Total)
2. TRXSODTL (InvNo, ProdCode,ProdDesc, QTY, Price)
3. ProdMaster (ProdCode)
4. TRXGRVDTL (GRVNO, ProdCode, qty)
5. TRXGRVHDR (GVRNO, grvdate)

the current result set :
InvNo | Total | ProdCode | Prod Desc | qty | price |
001 | 2000 | 12345 | sample | 5 | 400

Now i plan to add another 2 columns, the grvdate from trxgrvhdr and grv qty from trxgrvdtl.
i also not sure how to connect the table and what i am doing now.. :p

cathsith9
Starting Member

6 Posts

Posted - 2012-06-25 : 22:01:37

Hi Tara,

can i just email you the image for the linking database, because it's too complicated to type 1 by 1..
maybe you will be more understand what I have been asking here.

thanks.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2012-06-25 : 22:06:38

I am not available via email for free. We need more sample data than just one row.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources