Adding A default value in a column in a declare statment

Hi Can anybody help me.

I have this querey at the minute but when I attempt to put the default value of IND in the Sales Rep Code field I get an error Invalid column name, if I try and put 'IND' AS [Sales Rep Code] I get an incorrect syntax error.

DECLARE @.Query nVarchar(1000)
SET @.Query = N'SELECT NULL AS [GEO UNIT], NULL AS [PMC Invoice Date], IND AS [Sales Rep Code] FROM '+ 'Test' + RIGHT(DATEPART(yy, GETDATE()), 2) + '_' + CASE WHEN DATEPART(m, GETDATE()) IN ('11', '12', '1')
THEN 'Q1' ELSE CASE WHEN DATEPART(m, GETDATE()) IN ('2', '3', '4') THEN 'Q2' ELSE CASE WHEN DATEPART(m, GETDATE()) IN ('5', '6', '7')
THEN 'Q3' ELSE CASE WHEN DATEPART(m, GETDATE()) IN ('8', '9', '10') THEN 'Q4' END END END END + '.dbo.all_data' EXECUTE sp_executesql @.Query, N'@.level tinyint', @.level = 35

Can anybody please help on this.

ThanksHi Can anybody help me.

I have this querey at the minute but when I attempt to put the default value of IND in the Sales Rep Code field I get an error Invalid column name, if I try and put 'IND' AS [Sales Rep Code] I get an incorrect syntax error.

DECLARE @.Query nVarchar(1000)
SET @.Query = N'SELECT NULL AS [GEO UNIT], NULL AS [PMC Invoice Date], IND AS [Sales Rep Code] FROM '+ 'Test' + RIGHT(DATEPART(yy, GETDATE()), 2) + '_' + CASE WHEN DATEPART(m, GETDATE()) IN ('11', '12', '1')
THEN 'Q1' ELSE CASE WHEN DATEPART(m, GETDATE()) IN ('2', '3', '4') THEN 'Q2' ELSE CASE WHEN DATEPART(m, GETDATE()) IN ('5', '6', '7')
THEN 'Q3' ELSE CASE WHEN DATEPART(m, GETDATE()) IN ('8', '9', '10') THEN 'Q4' END END END END + '.dbo.all_data' EXECUTE sp_executesql @.Query, N'@.level tinyint', @.level = 35

Can anybody please help on this.

Thanks

would help us if u could post the ddl for the table|||what is the dataytpe of [Sales Rep Code]?
Off the cuff, it looks like your IND value is supposed to be a string.
Your example would be OK if you use 'IND' as [Sales Rep Code] - with the single quotes around IND.|||hi,

The datatype for SalesRep code is not defined as its in a view and I am creating the column [Sales Rep Code] with the default value of IND,

e.g

Select 'IND' AS [Sales Rep Code] from test would create a [Sales Rep Code] populated by IND for each row.

I have tried the 'IND' and it appears that it is incorrect syntax, this is beacuse it is closing the string value of the declare statment at the fist ' but I dont have a way around this.

Any Ideas|||Hi thanks for everyones help finally got it working but I still have one problem in that it is over 4000 carachters long and the @.test has a maximum of 4000 carachters is there any way around this.

Thanks

Add Cases to Select Statment

I need to add some cases to the select statment for cpeorderstatus:

Here is my Select statement:

"SELECT O.ORDERID, C.FIRSTNAME, C.LASTNAME, O.CLIENTORDERID AS CRMORDERID, TO_CHAR(O.ORDERDATE, 'YYYYMMDD')
AS CPEORDERDATE, TO_CHAR(O.SHIPDATE, 'YYYYMMDD') AS SHIPDATE, O.TRACKINGNBR AS TRACKINGNUMBER, O.SHIPNAME AS CARRIER,
OI.ITEM AS CPEORDERTYPE, OI.QTY,
O.STATUS AS CPEORDERSTATUS, OSN.ORD_SERIAL_NO AS SERIALNUMBER, C.BTN AS BTN, C.FIRSTNAME AS FIRST, C.LASTNAME AS LAST,
C.SHIPADDR1 AS ADDRESSLINE1, C.SHIPADDR2 AS ADDRESSLINE2, C.CITY AS CITY, C.STATE AS STATE, C.ZIP AS ZIP, TO_CHAR(R.ISSUEDATE,
'YYYYMMDD') AS ISSUEDATE, R.RMA_ID AS RMANUMBER, R.RMA_REASON AS REASON, TO_CHAR(R.RETURNDATE, 'YYYYMMDD') AS RETURNDATE
FROM SELF.ORDERS O, SELF.CUSTOMER C, SELF.ORDERITEM OI, SELF.ORD_SERIAL_NUMBER OSN, SELF.RMA R
WHERE O.CUSTID = C.CUSTID AND O.ORDERID = OI.ORDERID AND O.ORDERID = OSN.ORDER_ID (+) AND O.ORDERID = R.ORDER_ID (+) AND
(C.CUSTID IN (SELECT C.CUSTID FROM SELF.CUSTOMER C WHERE C.BTN='{0}')) ORDER BY O.ORDERDATE DESC"

I need to add multiple cases to cpeorderstatus, five different cases. Cane anyonye HELP

This is really unclear. It looks like you're selecting

O.STATUS AS CPEORDERSTATUS

but you're not doing anything with it other than returning it. Why not simply adding 5 different values to whatever inserting into the orders table?