Select Declared Variable With Case Statements

Is It Possible To Use Twice Declared. Variable Names- KILL And After Declared. Variable
is it possible to use twice declared. Variable names-
declared. Variable  and after KILL
and use the same declared. Variable 
like

DECLARE

@StartDate datetime
 
KILL @StartDate datetime (remove from memory)
use after with the same name

i have 2  big stored PROCEDURE
i need to put one after one
and psss only 1 Variable name to the second stored PROCEDURE
like this i don't get this error
 

The variable name '@Start_Date' has already been declared. Variable names must be unique within a query batch or stored procedure.

Msg 134, Level 15, State 1, Line 146

The variable name '@End_Date' has already been declared. Variable names must be unique within a query batch or stored procedure.
i use like
KILL @endDate  ??
KILL @StartDate   ??

 
TNX

View Replies !

---

Using SQL Query Columns In Select Case Statements
I am using Visual Web Developer Express 2005 as a test environment.  I have it connected to a SQL 2000 server.  I would like to use a Select Case Statement with the name of a column from a SQL Query as the Case Trigger.  Assuming the SQLDataSource is named tCOTSSoftware and the column I want to use is Type, it would look like the following in classic ASP:
Select Case tCOTSSoftware("Type")
      Case 1
         execute an SQL Update Command
     Case 2
         execute a different SQL Update Command
End Select
What would a comparable ASP.Net (Visual Basic) statement look like?  How would I access the column name used in the SQLDataSource?

View Replies !

---

Problem With Group By When Using Case Statements In The Select List.
 

I am using SQL Server 2005 and fairly new at using SQL Server.  I am having problems using a Case statements in the select list while have a group by line. The SQL will parse successfully but when I try to execute the statement I get the following error twice :

Column 'dbo.REDEMPTIONHISTORY.QUANTITY' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.
 
 

Below is the my sql statement:

SELECT dbo.DateOnly(TH.TransactionDate) AS RptDate, RH.Item,

ItemRef =

Case

when RH.Quantity < 0 then Sum(RH.Quantity)

when RH.Quantity >= 0 then Sum(0)

end

FROM dbo.RHISTORY AS RH INNER JOIN
           dbo.TRANSHISTORY AS TH ON RH.TRANSACTIONID = TH.TransactionID

WHERE (dbo.DateOnly(TH.TransactionDate) BETWEEN '10-1-2007' AND '10-5-2007')
AND       (RH.TransactionCode IN (13, 14, 15, 16))

Group by dbo.DateOnly(TH.TransactionDate), RH.Item
 
The TransHistory table contains, primary key transactionid, TransactionDate and the RHistory contains all the details about the transaction, the RHistory table is joined to the TransHistory table by foreign key TransactionID.  I am trying to get totals for same item on the same day.
 
Any help will be greatly appreciated. I am also having trouble using If..Then statements in a select list and can not fin the correct syntax to use for that.

View Replies !

---

Passing A SSIS Global Variable To A Declared Variable In A Query In SQL Task
I have a SQL Task that updates running totals on a record inserted using a Data Flow Task. The package runs without error, but the actual row does not calculate the running totals. I suspect that the inserted record is not committed until the package completes and the SQL Task is seeing the previous record as the current. Here is the code in the SQL Task:
 
DECLARE @DV INT;
SET @DV = (SELECT MAX(DateValue) FROM tblTG);
DECLARE @PV INT;
SET @PV = @DV - 1;
 
I've not been successful in passing a SSIS global variable to a declared parameter, but is it possible to do this:
 
DECLARE @DV INT;
SET @DV = ?;
DECLARE @PV INT;
SET @PV = @DV - 1;

 
I have almost 50 references to these parameters in the query so a substitution would be helpful.
 
Dan
 

View Replies !

---

How To Set The Value Of Variable In Select Case
Select Case l.GLTransactionTypeID
When 'Asset' --AND l.GLTransactionDate BETWEEN convert(datetime,'1/1/2007')AND convert(datetime,'12/31/2007') and l.GLTransactionSource=@assetID
Then Begin Set @Cost=l.GLtransactionAmount end
end from LedgerTransactions l,FixedAssets f where l.GLtransactionsource=f.assetID and l.GLtransactionsource= @assetID

View Replies !

---

Set Value For Variable In A Declared Cursor
Hi,

I have a problem on setting the value for the variable in a declared cursor. Below is my example, I have declared the cursor c1 once at the top in a stored procedure and open it many times in a loop by setting the variable @str_var to different values. It seems the variable cannot be set after the cursor declared. Please advise how can I solve this issue.

------------------------------------------------------------------------
DECLARE @str_var VARCHAR(10)
DECLARE @field_val VARCHAR(10)

DECLARE c1 CURSOR LOCAL FOR
SELECT field1 FROM tableA WHERE field1 = @str_var


WHILE (Sometime TRUE)
BEGIN

....

SET @str_var = 'set to some values, eg. ABC123, XYZ123'

OPEN c1

FETCH c1 INTO @field_val

WHILE (@@fetch_status != -1)
BEGIN

PRINT @field_val
...

FETCH c1 INTO @field_val
END

CLOSE c1

END

DEALLOCATE c1

----------------------------------------------------------------------

Thanks a lots,
Vincent

View Replies !

---

Cursor Declared With Variable In Where Clause
When I execute next query on sqlserver 6.5 nested in stored procedure I can see that 'open testCursor' selected rows using new value of @var. When I execute query on sqlserver 7.0 I can see that 'open testCursor' selected rows using value of @var before 'declare ... cursor'. Is there any way to force sqlserver 7.0 to proccess cursor like it did it before.

select @var = oldValue

declare testCursor cursor
for select someColumns
from someTable
where someColumn = @var

select @var = newValue

open testCursor

fetch next from testCursor into @someColumns

Thank's in advance.

Mirko.

View Replies !

---

Problem Returning A Declared Variable
when I run this sproc all I get out of it is "the commands completed successfully" and doesn't return the value. If anyone can point out where the error is I would really appreciate it. Thanks


Code:


Create Procedure LookupLeagueIdByUserName(@userName as varchar(40) = '') as
begin
if (@userName = '')
raiserror('LookupLeagueIdByUserName: Missing parameters', 16,1)
else
begin
Declare @leagueId int
Set @leagueId = -1

--Check if the username belong to a player
Select @leagueId = leagueId From Users u
inner join players p on p.userId = u.userId
inner join teams t on p.teamId = t.teamId
where u.userName = @userName

if (@leagueId > 0)
begin
return @leagueId
end
else
begin
--Check if the username belong to a teamUser
Select @leagueId = leagueId From Users u
inner join teamUsers tu on tu.userId = u.userId
inner join teams t on tu.teamId = t.teamId
where u.userName = @userName

if (@leagueId > 0)
begin
return @leagueId
end
else
begin
--Check if the username belong to a leagueUser
Select @leagueId = leagueId From Users u
inner join leagueUsers lu on lu.userId = u.userId
where u.userName = @userName

if (@leagueId > 0)
begin
return @leagueId
end
else
begin
--username is not in db or is an admin user
return -1
end
end
end
end
end
return
-- when I run this I get no results returned
LookupLeagueIdByUserName 'chris'

View Replies !

---

Obtaining Collation Length Of Declared Variable Within SP
Morning All,Can I have some help with this one please, I am having to make a fixed length text file based on information from the DBDeclare @EDIString varchar(MAX)Declare @RecordType varchar(2)Declare @RegistrationMark varchar(7)Declare @Model_Chassis varchar(11)Declare @LocationCode Varchar(4)Declare @MovementDate varchar(8)Declare @IMSAccountCode varchar(5)Declare @MovementType varchar(8)Declare @NotUsed1 Varchar(28)Declare @NotUsed2 varchar(7)Select @RecordType = RecordType, @RegistrationMark = RegistrationMark, @Model_Chassis = Model_And_Chassis, @LocationCode = LocationCode, @MovementDate = MovementDate, @IMSAccountCode = IMSAccountCode, @Movementtype = MovementTypeCode from Fiat_OutBoundOnce I have selected the information from the DB I need to ensure that each field is the correct length.  I therefore want to pass the variable and the length of the variable into a function to return the correct length.So if location Code = 'AB'  this needs to be four characters long so want to pass it into a function and return 'AB  'As I need to do this for 70+ variables is there an easy way to obtain the length of the collation for the variable?regardsTom

View Replies !

---

How To Call A Variable Declared In Parent Package
 

I have declared a variable XYZ in Parent package
Similarly I have declared ABC in Child package and have done the configuration.
I have assigned some value to XYZ
How to get the value in Child Package.

View Replies !

---

Using Declared Variable As Passphrase Slows Query
 

I have two tables - gift_cards and history - each related by a field called "card_number".  This field is encrypted in the history table but not in the gift_cards table.  Let's say the passphrase is 'mypassphrase'.  The following query takes about 1 second to execute with a fairly large amount of data in both tables:
 

SELECT max([history].[date_of_wash]) AS LastUse

FROM gift_cards AS gc LEFT JOIN history

ON gc.card_number=CAST(DecryptByPassPhrase('mypassphrase', HISTORY.CARD_NUMBER) AS VARCHAR(50))

GROUP BY gc.card_number

 
When I use a declared variable to contain the passphrase, the same query takes over 40 seconds.  For example,
 

declare @vchPassphrase as nvarchar(20)

select @vchPassphrase = 'mypassphrase'

SELECT max([history].[date_of_wash]) AS LastUse

FROM gift_cards AS gc LEFT JOIN history

ON gc.card_number=CAST(DecryptByPassPhrase(@vchPassphrase, HISTORY.CARD_NUMBER) AS VARCHAR(50))

GROUP BY gc.card_number

 
This query is part of a stored procedure and, for security reasons, I can't embed the passphrase in it.  Can anyone explain the discrepancy between execution times and suggest a way to make the second query execute faster?
 
Thanks,
SJonesy

View Replies !

---

Stored Procedure Using A Declared Variable In Insert Query (inline Or Using EXEC)
Hello,

I have a stored procedure where I run an insert statement.  I want to knwo if it is possible to do it using a variable for the table name (either in-line or with an EXEC statement without building a string first and executing that string.  See examples of what I am talking about in both cases below:

I want to be able to do this (with or without the EXEC) :
------------------------------------------------------------------------------------

DECLARE @NewTableNameOut  as varchar(100)


Set @NewTableNameOut  = 'TableToInsertInto'


EXEC(
                Insert Into @NewTableNameOut
                Select * From tableToSelectFrom
            )

------------------------------------------------------------------------------------

I can not do the above because it says I need to declare/set the @NewTableNameOut variable (assuming it is only looking at this for the specific insert statement and not at the variable I set earlier in the stored procedure.


I can do it like this by creating a string with the variable built into the string and then executing the string but I want to know if I can do it like I have listed above.

------------------------------------------------------------------------------------

DECLARE @NewTableNameOut  as varchar(100)


Set @NewTableNameOut  = 'TableToInsertInto'


EXEC(
                'Insert Into ' + @NewTableNameOut + ' ' +
                'Select * From tableToSelectFrom'
            )

------------------------------------------------------------------------------------



It is not an issue for my simple example above but I have some rather large queries that I am building and I want to run as described above without having to build it into a string. 

Is this possible at all?

If you need more info please let me know.

View Replies !

---

Update Statement Using Declared Variable Produces Unexpected Results On SQL Server 2005
We are getting unexpected results from the following update statement when it is executed on SQL Server 2005.
 

The strange thing is that we get duplicated values for QM_UID (although when run under SQL Server 2000 we don't get duplicated values)


Can anyone explain why this happens or suggest another way of populating this column without using a cursor or adding a temporary autoincrement column and copying the values over?


declare @NextID int;

set @NextID = 1;

update tmp set QM_UID=@NextID, @NextID = @NextID + 1;

select QM_UID, count(*) from tmp group by QM_UID having count(*) > 1 order by QM_UID


QM_UID count(*)

25 2
26 3
27 4
28 4
29 4
30 4
31 4
32 4
33 4
34 4
35 5
36 4
37 4
38 4
39 4
40 3

...

 

--- Script to replicate problem

 

-- NB: The number of rows that must be added to tmp before this problem will occur is machine dependant

--        100000 rows is sufficient on one of our servers but another (faster) server doesn't show the error

--        at 100000 rows but does at 1000000 rows.

 

-- Create a table

CREATE TABLE tmp (
    [QM_ADD_OP] [char](6) DEFAULT '' NOT NULL,
    [QM_ADD_DATE] [smalldatetime] DEFAULT ('1900-01-01') NOT NULL,
    [QM_ADD_TIME] [int] DEFAULT -1 NOT NULL,
    [QM_EDIT_OP] [char](6) DEFAULT '' NOT NULL,
    [QM_EDIT_DATE] [smalldatetime] DEFAULT ('1900-01-01') NOT NULL,
    [QM_EDIT_TIME] [int] DEFAULT -1 NOT NULL,
    [QM_LOCK_OP] [char](6) DEFAULT '' NOT NULL,
    [QM_QUOTE_JOB] [smallint] DEFAULT 0 NOT NULL,
    [QM_QUOTE_NUM] [char](12) DEFAULT '' NOT NULL,
    [QM_JOB_NUM] [char](12) DEFAULT '' NOT NULL,
    [QM_PRJ_NUM] [char](12) DEFAULT '' NOT NULL,
    [QM_NUMBER] [char](12) DEFAULT '' NOT NULL,
    [QM_REV_NUM] [char](6) DEFAULT '' NOT NULL,
    [QM_REV_DATE] [smalldatetime] DEFAULT ('1900-01-01') NOT NULL,
    [QM_REV_TIME] [int] DEFAULT -1 NOT NULL,
    [QM_REV_OPR] [char](6) DEFAULT '' NOT NULL,
    [QM_STYLE_CODE] [char](4) DEFAULT '' NOT NULL,
    [QM_REP_JOB_NUM] [char](12) DEFAULT '' NOT NULL,
    [QM_REP_COLUMN] [smallint] DEFAULT 0 NOT NULL,
    [QM_REP_PART] [char](6) DEFAULT '' NOT NULL,
    [QM_REP_MODEL] [smallint] DEFAULT 0 NOT NULL,
    [QM_REP_TYPE] [smallint] DEFAULT 0 NOT NULL,
    [QM_MODEL_QUOTE] [char](12) DEFAULT '' NOT NULL,
    [QM_RUN_NUM] [int] DEFAULT 0 NOT NULL,
    [QM_SOURCE_QUOTE] [char](12) DEFAULT '' NOT NULL,
    [QM_SOURCE_VAR] [smallint] DEFAULT 0 NOT NULL,
    [QM_SOURCE_QTY] [char](12) DEFAULT '' NOT NULL,
    [QM_SOURCE_PART] [char](6) DEFAULT '' NOT NULL,
    [QM_SOURCE_MODEL] [smallint] DEFAULT 0 NOT NULL,
    [QM_ORIG_QUOTE] [char](12) DEFAULT '' NOT NULL,
    [QM_ORIG_VAR] [smallint] DEFAULT 0 NOT NULL,
    [QM_ORIG_QTY] [char](12) DEFAULT '' NOT NULL,
    [QM_ORIG_PART] [char](6) DEFAULT '' NOT NULL,
    [QM_COPY_JOB] [char](12) DEFAULT '' NOT NULL,
    [QM_COPY_COLUMN] [smallint] DEFAULT 0 NOT NULL,
    [QM_COPY_J_PART] [char](6) DEFAULT '' NOT NULL,
    [QM_COPY_QUOTE] [char](12) DEFAULT '' NOT NULL,
    [QM_COPY_VAR] [smallint] DEFAULT 0 NOT NULL,
    [QM_COPY_QTY] [char](12) DEFAULT '' NOT NULL,
    [QM_COPY_Q_PART] [char](6) DEFAULT '' NOT NULL,
    [QM_JOINT_STATUS] [smallint] DEFAULT 0 NOT NULL,
    [QM_QUOTE_STATUS] [smallint] DEFAULT 0 NOT NULL,
    [QM_JOB_STATUS] [smallint] DEFAULT 0 NOT NULL,
    [QM_LIVE_STATUS] [smallint] DEFAULT 0 NOT NULL,
    [QM_USER_STATUS] [smallint] DEFAULT 0 NOT NULL,
    [QM_DEL_DATE] [smalldatetime] DEFAULT ('1900-01-01') NOT NULL,
    [QM_IS_CONVERTED] [smallint] DEFAULT 0 NOT NULL,
    [QM_PRINTED] [smallint] DEFAULT 0 NOT NULL,
    [QM_COPY_RATES] [smallint] DEFAULT 0 NOT NULL,
    [QM_IMPORT_UPDATE] [smallint] DEFAULT 0 NOT NULL,
    [QM_CRED_DATE] [smalldatetime] DEFAULT ('1900-01-01') NOT NULL,
    [QM_CRED_TIME] [int] DEFAULT -1 NOT NULL,
    [QM_CRED_AMT] numeric(26,8) DEFAULT 0 NOT NULL,
    [QM_CRED_OP] [char](6) DEFAULT '' NOT NULL,
    [QM_HELD] [smallint] DEFAULT 0 NOT NULL,
    [QM_PROOF] [char](12) DEFAULT '' NOT NULL,
    [QM_DELIV_METHOD] [char](12) DEFAULT '' NOT NULL,
    [QM_ART_METHOD] [char](12) DEFAULT '' NOT NULL,
    [QM_DES_TYPE] [smallint] DEFAULT 0 NOT NULL,
    [QM_REC_DATE] [smalldatetime] DEFAULT ('1900-01-01') NOT NULL,
    [QM_REC_TIME] [int] DEFAULT -1 NOT NULL,
    [QM_OWN_OP] [char](6) DEFAULT '' NOT NULL,
    [QM_RESP_DATE] [smalldatetime] DEFAULT ('1900-01-01') NOT NULL,
    [QM_RESP_TIME] [int] DEFAULT -1 NOT NULL,
    [QM_RESP_OP] [char](6) DEFAULT '' NOT NULL,
    [QM_RESP_OP_1] [char](6) DEFAULT '' NOT NULL,
    [QM_RESP_OP_2] [char](6) DEFAULT '' NOT NULL,
    [QM_RESP_OP_3] [char](6) DEFAULT '' NOT NULL,
    [QM_RESP_OP_4] [char](6) DEFAULT '' NOT NULL,
    [QM_RESP_OP_5] [char](6) DEFAULT '' NOT NULL,
    [QM_RECONTACT] [smalldatetime] DEFAULT ('1900-01-01') NOT NULL,
    [QM_REQ_FLAG] [smallint] DEFAULT 0 NOT NULL,
    [QM_ORIG_DATE] [smalldatetime] DEFAULT ('1900-01-01') NOT NULL,
    [QM_ORIG_TIME] [int] DEFAULT -1 NOT NULL,
    [QM_PREF_DATE] [smalldatetime] DEFAULT ('1900-01-01') NOT NULL,
    [QM_PREF_TIME] [int] DEFAULT -1 NOT NULL,
    [QM_LATE_DATE] [smalldatetime] DEFAULT ('1900-01-01') NOT NULL,
    [QM_LATE_TIME] [int] DEFAULT -1 NOT NULL,
    [QM_TITLE] [char](72) DEFAULT '' NOT NULL,
    [QM_DELIV_CODE] [char](12) DEFAULT '' NOT NULL,
    [QM_CLT_SPEC] [char](12) DEFAULT '' NOT NULL,
    [QM_TAX_REF] [char](22) DEFAULT '' NOT NULL,
    [QM_CONTACT] [char](36) DEFAULT '' NOT NULL,
    [QM_PHONE] [char](22) DEFAULT '' NOT NULL,
    [QM_FAX] [char](22) DEFAULT '' NOT NULL,
    [QM_ORDER] [char](20) DEFAULT '' NOT NULL,
    [QM_ORDER_CFM] [smallint] DEFAULT 0 NOT NULL,
    [QM_ORDER_REL] [char](6) DEFAULT '' NOT NULL,
    [QM_REP] [char](12) DEFAULT '' NOT NULL,
    [QM_REP_1] [char](12) DEFAULT '' NOT NULL,
    [QM_REP_2] [char](12) DEFAULT '' NOT NULL,
    [QM_REP_3] [char](12) DEFAULT '' NOT NULL,
    [QM_REP_4] [char](12) DEFAULT '' NOT NULL,
    [QM_REP_5] [char](12) DEFAULT '' NOT NULL,
    [QM_COORDINATOR] [char](12) DEFAULT '' NOT NULL,
    [QM_PRIORITY] [smallint] DEFAULT 0 NOT NULL,
    [QM_TYPE_CODE] [char](12) DEFAULT '' NOT NULL,
    [QM_GRADE] [smallint] DEFAULT 0 NOT NULL,
    [QM_FIN_SIZE_CODE] [char](12) DEFAULT '' NOT NULL,
    [QM_FIN_WID] numeric(26,8) DEFAULT 0 NOT NULL,
    [QM_FIN_LEN] numeric(26,8) DEFAULT 0 NOT NULL,
    [QM_FIN_DEP] numeric(26,8) DEFAULT 0 NOT NULL,
    [QM_FIN_GUSS] numeric(26,8) DEFAULT 0 NOT NULL,
    [QM_FIN_GSM] numeric(26,8) DEFAULT 0 NOT NULL,
    [QM_FIN_UNIT] [char](12) DEFAULT '' NOT NULL,
    [QM_ORIENT] [smallint] DEFAULT 0 NOT NULL,
    [QM_PROD_CODE] [char](22) DEFAULT '' NOT NULL,
    [QM_FIN_GOOD] [char](22) DEFAULT '' NOT NULL,
    [QM_CUST_CODE] [char](12) DEFAULT '' NOT NULL,
    [QM_CUST_CODE_1] [char](12) DEFAULT '' NOT NULL,
    [QM_CUST_CODE_2] [char](12) DEFAULT '' NOT NULL,
    [QM_CUST_PROS] [smallint] DEFAULT 0 NOT NULL,
    [QM_REQD_DATE] [smalldatetime] DEFAULT ('1900-01-01') NOT NULL,
    [QM_REQD_TIME] [int] DEFAULT -1 NOT NULL,
    [QM_FOLIO] [char](12) DEFAULT '' NOT NULL,
    [QM_FOLIO_1] [char](12) DEFAULT '' NOT NULL,
    [QM_FOLIO_2] [char](12) DEFAULT '' NOT NULL,
    [QM_PACK_QTY] numeric(26,8) DEFAULT 0 NOT NULL,
    [QM_USAGE] numeric(26,8) DEFAULT 0 NOT NULL,
    [QM_REORDER] [smalldatetime] DEFAULT ('1900-01-01') NOT NULL,
    [QM_EACH_WGT] numeric(26,8) DEFAULT 0 NOT NULL,
    [QM_WGT_UNIT] [char](12) DEFAULT '' NOT NULL,
    [QM_RFQ_DATE] [smalldatetime] DEFAULT ('1900-01-01') NOT NULL,
    [QM_RFQ_TIME] [int] DEFAULT -1 NOT NULL,
    [QM_RFQ_OPR] [char](6) DEFAULT '' NOT NULL,
    [QM_SALES_TYPE] [smallint] DEFAULT 0 NOT NULL,
    [QM_SALES_SRC] [char](12) DEFAULT '' NOT NULL,
    [QM_SALES_RSN] [char](12) DEFAULT '' NOT NULL,
    [QM_PROFILE] [char](12) DEFAULT '' NOT NULL,
    [QM_JOB_QTY] numeric(26,8) DEFAULT 0 NOT NULL,
    [QM_PREV_QTY] numeric(26,8) DEFAULT 0 NOT NULL,
    [QM_JOB_UNIT] [char](12) DEFAULT '' NOT NULL,
    [QM_PO_DATE] [smalldatetime] DEFAULT ('1900-01-01') NOT NULL,
    [QM_PO_TIME] [int] DEFAULT -1 NOT NULL,
    [QM_PO_OP] [char](6) DEFAULT '' NOT NULL,
    [QM_DLY_DATE] [smalldatetime] DEFAULT ('1900-01-01') NOT NULL,
    [QM_DLY_TIME] [int] DEFAULT -1 NOT NULL,
    [QM_QTY_DESP] numeric(26,8) DEFAULT 0 NOT NULL,
    [QM_TOTAL_DLY] [int] DEFAULT 0 NOT NULL,
    [QM_SCHED_DATE] [smalldatetime] DEFAULT ('1900-01-01') NOT NULL,
    [QM_SCHED_TIME] [int] DEFAULT -1 NOT NULL,
    [QM_CLOSE_DATE] [smalldatetime] DEFAULT ('1900-01-01') NOT NULL,
    [QM_CLOSE_TIME] [int] DEFAULT -1 NOT NULL,
    [QM_INV_NUM] [char](12) DEFAULT '' NOT NULL,
    [QM_PACK_NUM] [char](12) DEFAULT '' NOT NULL,
    [QM_DOWN_LOAD] [smallint] DEFAULT 0 NOT NULL,
    [QM_TRACK_CODE] [char](4) DEFAULT '' NOT NULL,
    [QM_TAX_TYPE] [smallint] DEFAULT 0 NOT NULL,
    [QM_TAX_CODE] [char](6) DEFAULT '' NOT NULL,
    [QM_CURR] [char](6) DEFAULT '' NOT NULL,
    [QM_EXCH_RATE] numeric(18,8) DEFAULT 0 NOT NULL,
    [QM_UNIT_QTY] numeric(26,8) DEFAULT 0 NOT NULL,
    [QM_UNIT_FLAG] [smallint] DEFAULT 0 NOT NULL,
    [QM_RUNON_QTY] numeric(26,8) DEFAULT 0 NOT NULL,
    [QM_SPEC_QTY] numeric(26,8) DEFAULT 0 NOT NULL,
    [QM_CHARGEABLE] [smallint] DEFAULT 0 NOT NULL,
    [QM_NC_REASON] [char](22) DEFAULT '' NOT NULL,
    [QM_CUST_MKUP] numeric(18,8) DEFAULT 0 NOT NULL,
    [QM_JOB_MKUP] numeric(18,8) DEFAULT 0 NOT NULL,
    [QM_BROKERAGE] numeric(18,8) DEFAULT 0 NOT NULL,
    [QM_CUST_DISC] numeric(18,8) DEFAULT 0 NOT NULL,
    [QM_INVOKED_BTNS] [int] DEFAULT 0 NOT NULL,
    [QM_IMPORTED] [smallint] DEFAULT 0 NOT NULL,
    [QM_IMPORT_RECALC] [smallint] DEFAULT 0 NOT NULL,
    [QM_IMPORT_CONVERT] [smallint] DEFAULT 0 NOT NULL,
    [QM_BRANCH] [char](6) DEFAULT '' NOT NULL,
    [QM_CODE] [char](36) DEFAULT '' NOT NULL,
    [QM_TEMPLATE] [smallint] DEFAULT 0 NOT NULL,
    [QM_REPEAT_PERIOD] [int] DEFAULT 0 NOT NULL,
    [QM_REOPEN_DATE] [smalldatetime] DEFAULT ('1900-01-01') NOT NULL,
    [QM_CAT_OPTION] [char](16) DEFAULT '' NOT NULL,
    [QM_UNIT_ID] [char](10) DEFAULT '' NOT NULL,
    [QM_PROD_BRANCH] [char](6) DEFAULT '' NOT NULL,
    [QM_UID] [int] DEFAULT 0 NOT NULL,
    [QM_AVAIL_DATE] [smalldatetime] DEFAULT ('1900-01-01') NOT NULL,
    [QM_AVAIL_TIME] [int] DEFAULT -1 NOT NULL
) ON [PRIMARY]


GO

-- Create an index on the table

CREATE unique INDEX [QM_NUMBER_ORDER] ON tmp([QM_QUOTE_JOB], [QM_NUMBER]) ON [PRIMARY]

GO

 

-- Populate the table

declare @Counter as int

SET NOCOUNT ON

set @Counter = 1

while @Counter < 100000

begin

insert into tmp (QM_ADD_TIME, QM_NUMBER) values (1,@Counter);

set @Counter = @Counter + 1

end

GO

-- Update QM_UID to a sequential value

declare @NextID int;

set @NextID = 1;

update tmp set QM_UID=@NextID, @NextID = @NextID + 1;

 

-- Find rows with a duplicate QM_UID (there should be no duplicate)

select QM_UID, count(*) from tmp group by QM_UID having count(*) > 1 order by QM_UID

--drop table tmp

 

-- output from select @@VERSION

-- Microsoft SQL Server 2005 - 9.00.3054.00 (Intel X86)   Mar 23 2007 16:28:52   Copyright (c) 1988-2005 Microsoft Corporation  Developer Edition on Windows NT 5.2 (Build 3790: Service Pack 1)

View Replies !

---

Iif And Case Statements
Hi all,I have to translate an Access query into sql. The query has thefollowing statement. I know SQL doesn't support iif, so can someone tellme how to use the case statement to get the same result?select field1,IIf(Grand_total-50>0, grand_total-50, 0) AS field2,field3Thanks.*** Sent via Developersdex http://www.developersdex.com ***Don't just participate in USENET...get rewarded for it!

View Replies !

---

Using SQL Case Statements HOW???
I'm trying to make a view that uses organization name from one tableand contact first and last name from another table. In the view Ihave a field that I want to show Organization followed by the maincontact. Problem is if the organization field or name field is NULLthen it doesn't show anything. If one field is empty I still want itto show the other field in the table.Example:Org1--ContactOrg2--ContactOrg3 (Still shows org even without a contact name)Contact (Still shows contact even without an org name)Tried using a CASE statement but didn't work.

View Replies !

---

Case Statements
Hey folks, having a problem here...

I have 2 piece of code designed to do the same thing. My problem is, i'm not getting the same results.

Code 1 where the results are correct


Code:


select Count(*) as TotalCount, Sum(DistAmt) as TotalSum
from table1
inner join table2
on table2.id = table1.id
where MailTypeID = 3
AND MailEventID = 2
and table1.IsActive = 1




code 2 - Y is correct, but Z is not, and not only is it not correct, but it is returning a number which equals more then the total rows from the table.


Code:


select
Y = sum(case when mailtypeid = 3 and maileventid = 2 and IsActive = 1 then distamt else 0 end),
Z = count(case when mailtypeid = 3 and maileventid = 2 and IsActive = 1 then 1 else 0 end)
from table1
inner join table2
on table2.id = table1.id



I have no idea what is going on.

Thanks!
Caden

View Replies !

---

Help With CASE And IF Statements
Hi,

Is there a way to use more criteria in a CASE statement other than CASE WHEN expression THEN value ELSE value END


I need to test if the count is greater than 0. If so, then perform the case statement, else return zeros. Currently there are entries where the values are blank. These blank values are causing errors in the application and unfortunately, I am not able to update these values.

So far I have the following, but I am getting an error stating "An aggregate may not appear in the WHERE clause unless it is in a subquery contained in a HAVING clause or select list, and the column being aggregated is an outer reference."

Thanks in advance!


Code:


IF @Qid = 4
SELECT @Exp as Status, COUNT(*) AS Total, @CourseID as CourseID,

(SELECT
question
FROM
tableQuestions
WHERE
qid = @Qid) AS Question,

IF COUNT(*)>0 THEN

1.0 * SUM(CASE WHEN A.Q1 > 1 THEN 1 ELSE 0 END) / COUNT(*) AS Positive,
1.0 * SUM(CASE WHEN A.Q1 = 0 THEN 1 ELSE 0 END) / COUNT(*) AS Neutral,
1.0 * SUM(CASE WHEN A.Q1 < 0 THEN 1 ELSE 0 END) / COUNT(*) AS Negative,
1.0 * SUM(CASE WHEN A.Q1 = 0 THEN 1 ELSE 0 END) / COUNT(*) AS NA
ELSE
0 AS Positive,
0 AS Neutral,
0 AS Negative,
0 AS NA

END IF

FROM
table1 A INNER JOIN table2 B ON A.SessionID = B.SessionID

WHERE
(B.CourseID = @CourseID) AND (A.SubmitDate >= @BeginDate) AND (A.SubmitDate <=@EndDate)

View Replies !

---

If Or Case Statements
Does anyone know if there is a way to look at a value in a variable and based on that value run different Data Flow Tasks?

For example, let's say I have an SSIS package that contains a variable named Client and 3 separate Data Flow Tasks.  I would like to do this:  if Client = 1 then run Data Flow 1 else if Client = 2 then run Data Flow 2 else run Data Flow 3.

Is this possible?

Thanks.

Danielle

View Replies !

---

CASE Statements
Hi is it possible to have a case statement with 2 or more constraints ?

Like we have

CASE x
WHEN 1 THEN ..
WHEN 2 THEN ..

but what if i want a constraint like :
IF (x= 0 AND y=0)
{
then...
}

can this be done in a CASE statement ?
if not , can I use IF with SQL ?

View Replies !

---

Use Of Case Statements.
hi,
I want to know how can i equate the following case statements into
one Condition? Following is my Code


(case when ws.Action_Taken_ID not in(3,17,18) then '-1' else '0' end) as istechfcr,
(case when datediff(day,dbo.Usp_Get_Date(pr.Set_Serial_Number),oh.WO_Record_Date) <= q.Product_Gurantee_Months + 6 then '-1' else '0' end) as istechfcr,
(case when substring(ltrim(rtrim(ws.Symptom)),1,1) not in('X') then '-1' else '0' end) as istechfcr,
(case when ws.Action_Taken_ID not in(8,15,21,25) then '-1' else '0' end) as istechfcr,



Thanks..

View Replies !

---

Case Statements
whats the most efficient way to do this? I have a column that contains three values. A, B, C If colVal = A add one to batch a, if B add one to batch b...ect . Then display all three values

View Replies !

---

Case Statements
I am creating a stored procedure which receives the following 4 variables. However, VacancySectorID is the only madatory variable.

@VacancySectorID int = NULL, (mandatory)
@VacancyRegionID int = NULL, optional)
@VacancyTypeID int = NULL, (optional)
@VacancyKeywords nvarchar(64) = NULL, (optional)

My objective is to dynamically build the WHERE statement which in turn will return either one or more of the variables and the required data. The following code works for a single if else but it appears that you cannot have more than one if else statement.

Anyones input would be greatly appreciated.

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[sp_Public_GetVacancyListingBySearchCriteria]
(
@VacancySectorID int = NULL,
@VacancyRegionID int = NULL,
@VacancyTypeID int = NULL,
@VacancyKeywords nvarchar(64) = NULL
)
AS
--SET NOCOUNT ON;
IF @VacancySectorID IS NULL AND @VacancyRegionID Is NULL AND @VacancyTypeID IS NULL AND @VacancyKeywords IS NULL
BEGIN
SELECTviewVacancies.*
FROMviewVacancies
WHEREVacancyActive = 1 AND VacancySectorID = @VacancySectorID
ORDER BYVacancyPosted DESC, VacancyTitle
END
ELSE
BEGIN
SELECTviewVacancies.*
FROMviewVacancies
WHEREVacancyActive = 1
ORDER BYVacancyPosted DESC, VacancyTitle
END

View Replies !

---

Nested CASE Statements
Hi All,

I'am trying to program a nested CASE statements (if this is not possible, does anyone have any alternate suggestions ?) and I'm getting syntax errors.
The statement is:

SELECT @cmdLine =
CASE @BackupType
WHEN 1 THEN Select @tmpStr =
CASE @initFlag
WHEN 1 THEN 'BACKUP Database ' + @databaseName + 'TO '+ @backupDeviceName + ' WITH INIT, NOUNLOAD, NAME = ' + @backupJobName + ' , SKIP , STATS = 10, NOFORMAT'
ELSE 'BACKUP Database ' + @databaseName + 'TO '+ @backupDeviceName + ' WITH NOINIT, NOUNLOAD, NAME = ' + @backupJobName + ' , SKIP , STATS = 10, NOFORMAT'
END
WHEN 2 THEN Select @tmpStr =
CASE @initFlag
WHEN 1 THEN 'BACKUP Database ' + @databaseName + 'TO '+ @backupDeviceName + ' WITH DIFFERENTIAL, INIT , NOUNLOAD, NAME = ' + @backupJobName + ', SKIP, STATS = 10, NOFORMAT'
ELSE 'BACKUP Database ' + @databaseName + 'TO '+ @backupDeviceName + ' WITH DIFFERENTIAL, NOINIT , NOUNLOAD, NAME = ' + @backupJobName + ', SKIP , STATS = 10, NOFORMAT'
END
WHEN 3 THEN Select @tmpStr =
CASE @initFlag
WHEN 1 THEN 'BACKUP Log ' + @databaseName + 'TO '+ @backupDeviceName + ' WITH INIT, NOUNLOAD, NAME = ' + @backupJobName + ', SKIP , STATS = 10, NOFORMAT'
ELSE 'BACKUP LOG ' + @databaseName + 'TO '+ @backupDeviceName + ' WITH NOINIT, NOUNLOAD, NAME = ' + @backupJobName + ', SKIP , STATS = 10, NOFORMAT'
END
ELSE ''
END

TIA,

Romy Stevensen

View Replies !

---

Nested Case Statements???
Can anyone show me how to write or post a sample of a nested case statement?

Thanks,

LOC

View Replies !

---

Nesting Case Statements...
I am wondering if there is a way that we could do a nesting case statement in an SQL Query?

This is what I have now... FicaWages = CASE WHEN (UPR00900.FICAMWGS_1 + UPR00900.FICAMWGS_2) >= '84900' AND UPR00400.SBJTSSEC = 1 THEN '0' WHEN UPR00400.SBJTSSEC = 0 THEN '0' ELSE (UPR30100.GRWGPRN - (SELECT SUM(UPR30300.UPRTRXAM) FROM UPR30300 WHERE UPR30300.EMPLOYID = UPR00100.EMPLOYID AND UPR30300.PAYROLCD LIKE '3%' AND AUCTRLCD = 'UPRCC00000007')) END

What I want:

FicaWages = CASE
WHEN
('Sql Statement') = 'GRM'
THEN
CASE
WHEN (UPR00900.FICAMWGS_1+UPR00900.FICAMWGS_2)
>= '84900' AND UPR00400.SBJTSSEC = 1 THEN '0'
WHEN UPR00400.SBJTSSEC = 0 THEN '0'
ELSE (UPR30100.GRWGPRN - ('Sql Statement'))
END
ELSE
CASE
WHEN (UPR00900.FICAMWGS_1+UPR00900.FICAMWGS_2)
>= '84900' AND UPR00400.SBJTSSEC = 1 THEN '0'
WHEN UPR00400.SBJTSSEC = 0 THEN '0'
ELSE (UPR30100.GRWGPRN - ('Sql Statement'))
END
END


I hope this is clear enough.

View Replies !

---

Nested Case Statements
 

Is it possible to use nested case statements in the SQL for your dataset when you are using Reporting Services?  I keep getting an error saying "Unable to parse expression" and my report won't run.
 



Code SnippetCASE WHEN (CASE WHEN DateDiff(d , GetDate() , DATEADD(d , - 1 , DATEADD(m , 1 , DATEADD(m , 1 , DATEADD(d , - (1 * (DAY(TRANSACTION_DATE) - 1)) , TRANSACTION_DATE))))) < '0 THEN 'Overdue' WHEN DateDiff(d , GetDate(), DATEADD(m , FIELD1 / 30 - 1 , DATEADD(m , 1 , DATEADD(d , - (1 * (DAY(TRANSACTION_DATE) - 1)) , TRANSACTION_DATE)))) > 0 THEN 'Not Due' ELSE 'Due' END)= 'Not Due' AND FIELD2 > 0 THEN DateDiff(m , GetDate() , DATEADD(m , FIELD1 / 30 - 1 , DATEADD(m , 1 , DATEADD(d , - (1 * (DAY(TRANSACTION_DATE) - 1)) , TRANSACTION_DATE))) * 30) / 360 * FIELD2 * @PARAMETER1 ELSE NULL END
 
 

I know this is quite a complex statement, so at first I was worried that maybe I had brackets in the wrong places, but I've been through the code and made sure that every opening bracket has an equivalent closing bracket, and everything appears to be OK in that respect.  So I'm thinking that maybe the structure of my nested case statements is incorrect?  The inner case statement is necessary to calculate whether a transaction is due, overdue or not due.  The outer case statement depends on the result of the inner case statement. 
 
Basically, we only want the calculations following the "THEN" in the outer case statement to be executed if the result of the inner case statement is "not due" and Field2 is greater than zero.  If either of those criteria aren't met, then it should go straight to the ELSE NULL END statement and stop.

View Replies !

---

Help !!!!!using Case Statements In Update
Hi,
I have this update statement that works, it updates the totalamount to calc amount, but I want to update totalamount only when it is not equal to calcamt.I have tried many things but in vain.Can some one please help me.
How do i use case statements to update only when totalamount!=calcamt.

update c
set totalamount= calcamt
from Prepay c
 JOIN
( select sum(amt) as calcamt, payid
  from pay
  group by payid
 )b ON b.payid= c.payid
where c.cust_no='somenum'

View Replies !

---

Nested Case Statements
Hi i am having some trouble with a nested case statement, what i want to do is set the value of a new column called Result depending on a series of case statements. Basically i want to check Test.Webstatus = 'Rd' and FinalResult = 'true' if this is true i want it to set the value in the Results field to ReportableResult + '~' + ReportableUnitDisplay then go through all the limits fields adding either the value of the field or 'blank' onto the end of the value in the Results field, depending on if the limits field has Null or a value in it. Producing a value in the Results field similiar to: 10~kg:10:5:2:1 or 10~kg:blank:5:blank:1 etc


select ClientRef, Sample.WebStatus as SampleStatus, Analysis, FinalResult, Test.WebStatus,
'Result' = Case
when Test.WebStatus = 'Rd' and FinalResult = 'true' then
Case
Case
when UpperCriticalLimit is null then ReportableResult + '~' + ReportableUnitDisplay + ':blank'
else ReportableResult + '~' + ReportableUnitDisplay + ':' + UpperCriticalLimit
end
Case
when UpperWarningLimit is null then ReportableResult + '~' + ReportableUnitDisplay + ':blank'
else ReportableResult + '~' + ReportableUnitDisplay + ':' + UpperWarningLimit
end
Case
when LowerWarningLimit is null then ReportableResult + '~' + ReportableUnitDisplay + ':blank'
else ReportableResult + '~' + ReportableUnitDisplay + ':' + UpperWarningLimit
end
Case
when LowerCriticalLimit is null then ReportableResult + '~' + ReportableUnitDisplay + ':blank'
else ReportableResult + '~' + ReportableUnitDisplay + ':' + LowerCriticalLimit
end
end
when FinalResult = 'false' then Null
else Test.WebStatus
from Job
inner join sample on Job.JobID = Sample.JobID
inner join Test on Sample.SampleID = Test.SampleID
left join Result on Test.TestID = Result.TestID


Any Advice Would Be Great
Thanks
David

View Replies !

---

How To Use Case In Update Statements
Hi,
I have this update statement that works, it updates the totalamount to calc amount, but I want to update totalamount only when it is not equal to calcamt.I have tried many things but in vain.Can some one please help me.
How do i use case statements to update only when totalamount!=calcamt.

update c
set totalamount= calcamt
from Prepay c
JOIN
(select sum(amt) as calcamt, payid
from pay
group by payid
)b ON b.payid= c.payid
where c.cust_no='somenum'

View Replies !

---

Dividing Two CAse Statements
How do I divide these two case statements:

1. sum(CASE WHEN o.sap_apc_indic is null THEN wip.wip_oth_exp_amt
ELSE 0
END) Markup,

2. sum(CASE WHEN o.sap_apc_indic is not null THEN wip.wip_oth_exp_amt
ELSE 0
END) AP,

View Replies !

---

Multiple Case Statements
This query brings back 2 rows for each record, one for each case statement. How can I change this to bring in the contsupp.notes for both types of data? Or could it be fixed in the joins?

select
contact1.accountno,
contact1.key5 as "Ch#",
contact2.uexpsort as "Sort",
contact1.company as "Church",
contact1.contact as "Editor",
contact1.phone1 as "Phone",
contact2.uemerg as "Emergency",
contact1.Address1 as "Address",
contact1.city as "City",
contact1.state as "State",
contact1.zip as "Zip",
contact2.ubulletnqt as "Qty",
contact2.ubullcolor as "BullColor",
contact2.ubarcode as "Barcode",
contact2.udelivery as "Delivery",
contact2.uoutputdev as "OutputDev",
contact2.utimedeliv as "Time",
contact2.ucolor as "DelivColor",
contact2.ucollated as "Collated",
contact2.ustapled as "Stapled",
case when contsupp.contsupref = 'Delivery Notes' then contsupp.notes end as "Deliverynotes",
case when contsupp.contsupref = 'Cover Change Slip' then contsupp.notes end as "CoverChangeSlip"

from
Contact1 inner join contact2 on Contact1.Accountno = Contact2.Accountno
inner join contsupp on Contact1.Accountno=Contsupp.Accountno
where
contact1.key5 not like '' and contsupp.contsupref='Delivery Notes' or contsupp.contsupref ='Cover Change Slip' order by contact1.key5

Thanks for your help

View Replies !

---

How Should I Write This Query Wiht Case Statements
Hi
  I have a stored procedure and i am trying to add case statements to them.. but i am getting an Error. which is
Msg 125, Level 15, State 3, Procedure udf_EndDate, Line 34
Case expressions may only be nested to level 10.
 
And This is my sproc-- ================================================
-- Template generated from Template Explorer using:
-- Create Scalar Function (New Menu).SQL
--
-- Use the Specify Values for Template Parameters
-- command (Ctrl-Shift-M) to fill in the parameter
-- values below.
--
-- This block of comments will not be included in
-- the definition of the function.
-- ================================================
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:<Author,,Name>
-- Create date: <Create Date, ,>
-- Description:<Description, ,>
-- =============================================
Create FUNCTION [dbo].[udf_EndDate] (@PeriodId int)
RETURNS datetime
AS

BEGIN
DECLARE
@Month int,
@Year char(4)

SELECT
@Month = [Month],
@Year = Cast([Year] as char(4))
FROM
Period
WHERE
PeriodId = @PeriodId

RETURN
CASE @Month WHEN 1 THEN '1/31/' + @Year ELSE
CASE @Month WHEN 2 THEN '2/28/' + @Year ELSE
CASE @Month WHEN 3 THEN '3/31/' + @Year ELSE
CASE @Month WHEN 4 THEN '4/30/' + @Year ELSE
CASE @Month When 5 Then '5/31/' + @Year ELSE
CASE @Month When 6 Then '6/30/' + @Year ELSE
CASE @Month When 7 Then '7/31/' + @Year ELSE
CASE @Month When 8 Then '8/31/' + @Year ELSE
CASE @Month When 9 Then '9/30/' + @Year ELSE
CASE @Month When 10 Then '10/31/' + @Year ELSE
CASE @Month When 11 Then '11/30/' + @Year ELSE
CASE @Month When 12 Then '12/31/' + @Year ELSE null END
END
END
END
END
END
END
END
END
END
END
END
END

Any help will be appreciated.
 
Regards
Karen

View Replies !

---

SQL Statement, Adding Two COUNT/CASE Statements
 

SELECT     COUNT(DISTINCT CASE WHEN visit_type = 0 THEN visitor_id END) AS [New Visitors], 
                      COUNT(DISTINCT CASE WHEN visit_type = 0 THEN visitor_id END) AS [Returning Visitors]
FROM         content_hits_tbl
WHERE     (hit_date BETWEEN DATEADD(mm, - 1, GETDATE()) AND GETDATE())
 
=======================
 
How do I add up both COUNT/CASE columns?  Would it be:
SUM([New Visitors] + [Returning Visitors]) AS Total

 
I tried this and it doesn't work. I get invalid column names error for both.
 
I have even tried:
SUM([COUNT(DISTINCT CASE WHEN visit_type = 0 THEN visitor_id END)] + [COUNT(DISTINCT CASE WHEN visit_type = 0 THEN visitor_id END)]) AS Total
 
You would think that there would be some gui functionality in VS08 that would do this...

 
Thoughts are greatly appreciated!

TT

View Replies !

---

Creating WHERE Clauses Based On IF (or CASE) STATEMENTS Transact-SQL
I am trying to create a stored procedure whose where clause isdependent on a parameter.If the parameter @myparam is null or '' then I want the where clause tobe one thing, else I want it to be a completely different thing. I cando it easily using iif but obviously that is not an option here...Here is what I have:Select @ FROM my table WHEREIF @myparam = '' or @myparam is null[MyField] = like '%'ELSEcharindex(@myparam,[MyField]) > 0)I have also tried variations of this and tried it using CASE. I keepgetting syntax errors. Do I have to write multiple SELECT statementsfor every possibility. If I can get this working the query willactually be a bit more complex, although it will follow this basicstructure...in other words, I'd rather not have to write multiplequeries when the only thing that is changing is the WHERE portion...Thanks!Ryan

View Replies !

---

Concatenating Parameter Values && Text; Nested CASE Statements
Hello.  I'm trying to reduce some code in my stored procedure and I'm running into lots of errors.  I'm somewhat of a novice with SQL and stored procedures so any help would be beneficial.
I have a SP that gets a page of user data and is also called when sorting by one of the columns (this data is placed in a repeater, btw).  I quickly learned that I wasn't able to pass in string parameters the way I had hoped in order to handle the ORDER BY and direction (ASC/DESC) so I'm trying to work around this.
So far I've tried the following with many errors.WITH Users AS (
SELECT ROW_NUMBER() OVER (ORDER BY CASE WHEN @OrderBy='FirstName' AND @Direction='DESC' THEN (FirstName + ' DESC')
WHEN @OrderBy='FirstName' THEN FirstName
WHEN @OrderBy='LastName' AND @Direction='DESC' THEN (LastName + ' DESC')
WHEN @OrderBy='LastName' THEN LastName
END
) AS Row,
UserID, FirstName, LastName, EmailAddress, [Role], Active, LastLogin, DateModified, ModifiedBy, ModifiedByName
FROM
vRF_Users
)

SELECT UserID, FirstName, LastName, EmailAddress, [Role], Active, LastLogin, DateModified, ModifiedBy, ModifiedByName
FROM Users
WHERE Row BETWEEN @StartRowIndex AND @EndRowIndex
 
I've tried a combination of similar things with parenthesises, without, doing "THEN FirstName DESC" without concatenating anything, etc.
I also tried: DECLARE @OrderByDirection varchar(32)
DECLARE @DESC varchar(4)
SET @DESC = ' DESC'

IF @Direction = 'DESC'
BEGIN
SET @OrderByDirection = (@OrderBy + @DESC)
END
And then writing my case statemet like this:ORDER BY CASE WHEN @Direction='DESC' THEN @OrderByDirection
ELSE @OrderBy
ENDObviously this didn't work either.  Is there any way to gracefully accomplish this or do I just have to use a bunch of if/else statements and lots of redundant code to evaluate all my @OrderBy and @Direction parameters???
 
Thanks in advance,
Jen

View Replies !

---

Enclosing Multiple Statements In A Variable
Hi all;

How do I enclose multiple filters in a variable, for instance how would I put the following filter into a variable and also is it actually possible or do I have to do something else before performing this type of operation:

tel1 like '072%' or tel1 like '+27 72%' or tel1 like '072-%'
or tel1 like '+2772%' or tel1 like '72%'
and len(tel1) > 7
or tel2 like '072%' or tel2 like '+27 72%' or tel2 like '072-%'
or tel2 like '+2772%' or tel2 like '72%'
and len(tel2) > 7
or tel3 like '072%' or tel3 like '+27 72%' or tel3 like '072-%'
or tel3 like '+2772%' or tel3 like '72%'
and len(tel3) > 7
or tel4 like '072%' or tel4 like '+27 72%' or tel4 like '072-%'
or tel4 like '+2772%' or tel4 like '72%'
and len(tel4) > 7
or tel5 like '072%' or tel5 like '+27 72%' or tel5 like '072-%'
or tel5 like '+2772%' or tel5 like '72%'
and len(tel5) > 7
or tel_other like '072%' or tel_other like '+27 72%' or tel_other like '072-%'
or tel_other like '+2772%' or tel_other like '72%'
and len(tel_other) > 7

the problem is that it's got a couple of apostrophes which when declaring variables pulls it out of that mode, I have looked on the internet but can't seem to find anything

View Replies !

---

Using CASE WHEN With Variable
Hello

I'm producing a script to output a file containing data in a required format. Part of this script requires me to change a person id field to something else for a number of people.

I can do this no problem using a script like:

CASE WHEN person_id = '12345678' THEN '24681012'

However I have about 400 people to do and don't really want a 400 line script that will take me about a week to write....!!

Is there a way of doing this passing a variable??

I have created a table (TRANSFER) containing the old ID (OLD_ID) and a field containing the new id (NEW_ID), with all the appropriate data in.

How could I use this table with a variable to get the required results?

Thank you

View Replies !

---

Several Select Statements?
Hello, how can i merge together several select statements?
I have something like this:
CREATE PROCEDURE Forum_GetThreads @ID int,@AscDesc bitASBEGINSET NOCOUNT ON;SELECT * FROM forum_ansageSELECT * FROM forum_topics WHERE (status = 0) ORDER BY (created) DESCIF (@AscDesc = 0)BEGIN      SELECT * FROM forum_topics WHERE (status > 0) ORDER BY (created) DESCENDELSEBEGIN      SELECT * FROM forum_topics WHERE (status > 0) ORDER BY (created) ASCENDHere i want to merge them all together and return only one SELECT statement with all the data

View Replies !

---

Select Statements
Arnie and All others. Thanks for your help.
The previous  case became lenghty and then just mixed up a lot.

To make it easy I have created two temp tables and wrote to test select statement .

You will notice that I tired two select statement but they are giving different set of result however the 2nd Select statement not giving the result as should be looking at the following requirement.


--Selected record must  RaType='b'
--                      PlanID='H321'
--                      Gender='0'
--
--And not to include in select  if any one of these meets:
--                     Hosp='1'   in other words it has to be 0
--                     ESRD='1'   or Rafctor Type in ('g','f')  in other words ESRD should be 0 and rafctorType in ('h','i')
--                     Dod is not null    in other words Dod has to be null 
--     


--copy from here

GO
Create table #MyTable

   (  RowID  int IDENTITY,
      RD     varchar(10),
      RAType varchar(5),
      History varchar(15)
   )

INSERT INTO #MyTable VALUES ( '1', 'A', '1111' )
INSERT INTO #MyTable VALUES ( '2', 'S','2222' )
INSERT INTO #MyTable VALUES ( '3', 'D', '2345')
INSERT INTO #MyTable VALUES ( '4', 'I2','1234' )
INSERT INTO #MyTable VALUES ( '5', 'C','3333' )
INSERT INTO #MyTable VALUES ( '1', 'B','4444' )
INSERT INTO #MyTable VALUES ( '2', 'X','5555' )
INSERT INTO #MyTable VALUES ( '1', 'D' ,'66666')
GO

Go
Create Table #MYTable2

(
RowID int IDentity,
 RD varchar(10),
 RaType varchar(5),
 History varchar(15),
PlanID varchar(6),
Hosp varchar(2),
ESRD varchar(2),
RafctorType varchar(3),
gender varchar(5),
dod varchar (5) NULL

)

INSERT INTO #MyTable2 VALUES ( '1', 'A', '1111', 'H321', '0','0', 'g', '0' ,NULL)
INSERT INTO #MyTable2 VALUES ( '2', 'b', '2222', 'H321', '0','0', 'e', '0',NULL )
INSERT INTO #MyTable2 VALUES ( '2', 'b', '3333', 'H321', '0','0', 'f', '0',NULL )
INSERT INTO #MyTable2 VALUES ( '2', 'b', '4444', 'H321', '0', '0','d', '0',NULL )
Go


Select #MYtable2.History from #MYTable2
INNER JOIN #mytable on #myTable2.History=#mytable.history
Where #MyTable2.RaType='b' And PlanID='H321' And Gender='0' And Hosp<>'1' And ESRD<>'1' AND RafctorType Not in ('g','f') AND Dod is NULL

Select #Mytable2.History from #MyTable2
INNER JOIN #mytable on #mytable2.History=#mytable.history
where #mytable2.Ratype='b' AND PlanID='H321' AND Gender='0' AND(Hosp<>'1') or ((ESRD<>'1') or (RafctorType Not in ('g','f')) OR DOD is NULL)


Go

drop table #Mytable2
Drop table #MYtable
              
  

View Replies !

---

How To Use Two Select Statements
Both of these work fine separately; How do I join these two Select Statements?

SELECT MPI.CREATE_DT,MPI.MPI_NBR, MPI.LAST_NM,MPI.FIRST_NM,
MPI_CURRENT_ADDR.ADDR_NBR, MPI_CURRENT_ADDR.ADDRESS_1, MPI_CURRENT_ADDR.ADDRESS_2,
MPI_CURRENT_ADDR.CITY,MPI_CURRENT_ADDR.STATE_CD,MPI_CURRENT_ADDR.ZIP_CD,MPI_CURRENT_ADDR.PHN_NBR,
MPI_PERSON.BIRTHDAY,MPI_PERSON.SEX

FROM MPI,MPI_CURRENT_ADDR,MPI_PERSON

WHERE (MPI.MPI_NBR=MPI_PERSON.MPI_NBR) AND (MPI.ADDR_NBR=MPI_CURRENT_ADDR.ADDR_NBR)
AND
MPI.CREATE_DT>=20070101
ORDER BY MPI.CREATE_DT

SELECT PATIENT.PAT_NBR,PATIENT.PHYS_NBR, PHYSICIAN.FIRST_NM,PHYSICIAN.LAST_NM,PHYSICIAN.DE_NBR, PHYSICIAN.SALUTATION_CD
FROM PATIENT, PHYSICIAN
WHERE PATIENT.PHYS_NBR=PHYSICIAN.PHYS_NBR


Thanks!
Lisa

View Replies !

---

Two Select Statements
I have a table that list Canadian provinces and American States it looks something like this:

ID | ProvState

Under ID 1-13 lists the Canadian provinces and everything over 13 lists the American states. I want to create 1 query that will list the Canadian provinces first in alphabetical order then the States in alphabetical order.

I have tried using UNION but it's not returning what I want and it does not allow me to use order by for the first statement.

SELECT * FROM SPProvince WHERE ID < 14 ORDER BY ProvState
UNION
SELECT * FROM SPProvince WHERE ID > 13 ORDER BY ProvState

Anyone have any suggestions to this problem?

View Replies !

---

SELECT EXEC Statements In SQL
I  am writing a stored procedure to select some information from two tables and I would also like to Execute a function using the userid information from the processing in the where clause of the Select statement. Is the syntax below possible?? If yes, could you please help me understand exactly what I may be doing wrong here.. If no, can you please help with better syntax. Thanks in advance
 
SELECT M.UserID, M.FullName, (EXEC calcPoints M.UserID) as 'UserPoints'
 FROM MissionUsers M LEFT OUTER JOIN MissionUserInfo MU ON M.UserID = MU.UserID
WHERE  M.EMAIL = @UserEmail
 

View Replies !

---

USING MULTIPLE SELECT STATEMENTS
how can take codes below and put them into one store procedure to supplie a gridview. also i will like to define the row name on the left like i did to the column on the top using the 'AS'
 Code1....
SELECT
SUM(CASE WHEN Month = 'January' THEN 1 ELSE 0 END) AS January, SUM(CASE WHEN Month = 'February' THEN 1 ELSE 0 END) AS February,
SUM(CASE WHEN Month = 'March' THEN 1 ELSE 0 END) AS March, SUM(CASE WHEN Month = 'April' THEN 1 ELSE 0 END) AS April,
SUM(CASE WHEN Month = 'May' THEN 1 ELSE 0 END) AS May, SUM(CASE WHEN Month = 'June' THEN 1 ELSE 0 END) AS June,
SUM(CASE WHEN Month = 'July' THEN 1 ELSE 0 END) AS July, SUM(CASE WHEN Month = 'August' THEN 1 ELSE 0 END) AS August,
SUM(CASE WHEN Month = 'September' THEN 1 ELSE 0 END) AS September, SUM(CASE WHEN Month = 'October' THEN 1 ELSE 0 END) AS October,
SUM(CASE WHEN Month = 'November' THEN 1 ELSE 0 END) AS November, SUM(CASE WHEN Month = 'December' THEN 1 ELSE 0 END) AS December,
SUM(CASE WHEN site_descr = 'SITE1' THEN 1 ELSE 0 END) AS AllTotal
FROM dbo.V_results
WHERE (site_descr = 'SITE1')
 
Code2.....
 
SELECT
SUM(CASE WHEN Month = 'January' THEN 1 ELSE 0 END) AS January, SUM(CASE WHEN Month = 'February' THEN 1 ELSE 0 END) AS February,
SUM(CASE WHEN Month = 'March' THEN 1 ELSE 0 END) AS March, SUM(CASE WHEN Month = 'April' THEN 1 ELSE 0 END) AS April,
SUM(CASE WHEN Month = 'May' THEN 1 ELSE 0 END) AS May, SUM(CASE WHEN Month = 'June' THEN 1 ELSE 0 END) AS June,
SUM(CASE WHEN Month = 'July' THEN 1 ELSE 0 END) AS July, SUM(CASE WHEN Month = 'August' THEN 1 ELSE 0 END) AS August,
SUM(CASE WHEN Month = 'September' THEN 1 ELSE 0 END) AS September, SUM(CASE WHEN Month = 'October' THEN 1 ELSE 0 END) AS October,
SUM(CASE WHEN Month = 'November' THEN 1 ELSE 0 END) AS November, SUM(CASE WHEN Month = 'December' THEN 1 ELSE 0 END) AS December,
SUM(CASE WHEN site_descr = 'SITE2' THEN 1 ELSE 0 END) AS AllTotal
FROM dbo.V_results
WHERE (site_descr = 'SITE2')
 
 thanks in advance

View Replies !

---