select case when class_desc='OBJECT_OR_COLUMN' then 'GRANT '+permission_name+' ON '+'['+left(object_name,3)+'].'+'['+substring(object_name,5,len(object_name))+ '] TO '+username WHEN class_desc='DATABASE_ROLE' THEN EXEC sp_addrolemember N'object_name', N'MC' end from dba.dbhakyedek where username='MC'
This statement was running successfully until exec sp_addrolemember thing. I just learned that i can't call a sp in select case but i couldnt figure out how to do it.
Hi All, I've looked through the forum hoping I'm not the only one with this issue but alas, I have found nothing so I'm hoping someone out there will give me some assistance. My problem is the case statement in my Insert Statement. My overall goal is to insert records from one table to another. But I need to be able to assign a specific value to the incoming data and thought the case statement would be the best way of doing it. I must be doing something wrong but I can't seem to see it.
Here is my code: Insert into myTblA (TblA_ID, mycasefield = case when mycasefield = 1 then 99861 when mycasefield = 2 then 99862 when mycasefield = 3 then 99863 when mycasefield = 4 then 99864 when mycasefield = 5 then 99865 when mycasefield = 6 then 99866 when mycasefield = 7 then 99867 when mycasefield = 8 then 99868 when mycasefield = 9 then 99855 when mycasefield = 10 then 99839 end, alt_min, alt_max, longitude, latitude ( Select MTB.LocationID MTB.model_ID MTB.elevation, --alt min null, --alt max MTB.longitude, --longitude MTB.latitude --latitude from MyTblB MTB );
The error I'm getting is: Incorrect syntax near '='.
I have tried various versions of the case statement based on examples I have found but nothing works. I would greatly appreciate any assistance with this one. I've been smacking my head against the wall for awhile trying to find a solution.
I've this Stored procedure on a SQLserver 2000 SP3:
SELECT *,CASE immagine WHEN NULL THEN 0 ELSE 1 END AS hasImage FROM Squadre WHERE squadra = @squadra
this is a flag that returns if the image field is present or not.. i've a lot of this type of stored procedures.. but this one returns me an error..
--------------------------- Microsoft SQL-DMO (ODBC SQLState: 42000) --------------------------- Errore 306: The text, ntext, and image data types cannot be compared or sorted, except when using IS NULL or LIKE operator. --------------------------- OK ---------------------------
An i can't save.. why? reme,ber that in the same Db there's other Stored like this.. the same syntax and the same field or table.. can anyone help me??
I have been trying to get the following Selects to work using a case expression in the order by section.
I know I can easily separate out the two statements but I want to do a select using the case statement below ; however, I keep getting error 16 --"Order by items must appear in the select list if the statement contains a union.
If remove the case statement and put order by "internalID desc" I receive no errors. Moreover, when I take out the union statement and execute the two select statements with each including the case expression it runs as planned.
Can anyone tell what the problem is here? I have combed the web looking for an answer, but it seems that the statement is valid.
Thanks J declare @date set @date = '2001'
select internalID from section_data_v3
union
select internalID from section_data_v4
order by case when (@date = '2001') then internalID end desc
I am trying to write a Procedure in SQL 2005 that retreives a list of rows from the database. I pass to the procedure sorting parameters. This procedure works fine for all of the fields that I enter in the Order By, except for one. The field that does not work is a varchar(500) field (named Description). I am not sure what the problem is. The error that I am getting is:
Msg 235, Level 16, State 0, Line 13
Cannot convert a char value to money. The char value has incorrect syntax.
Attached is the T-SQL Code. The declare and set statements in the beginning are for informational purposes. Any help is appreciated. ------------------------------------------------------------------------------------------------------ declare @Match varchar(75)
WHERE CONTAINS (ci.Keywords, @Match) AND ShowOnWeb=1
)
SELECT catitems.ID,
catitems.SupplierCode,
catitems.Description,
catitems.AUDIO_LINK,
catitems.HighCost,
catitems.Channels
FROM catitems
WHERE RowNo BETWEEN @StartPos AND @StartPos + @NumRows -1 ------------------------------------------------------------------------------------------------------
I have a view where I'm using a series of conditions within a CASE statement to determine a numeric shipment status for a given row. In addition, I need to bring back the corresponding status text for that shipment status code.
Previously, I had been duplicating the CASE logic for both columns, like so:
Code Block...beginning of SQL view... shipment_status = CASE [logic for condition 1] THEN 1 WHEN [logic for condition 2] THEN 2 WHEN [logic for condition 3] THEN 3 WHEN [logic for condition 4] THEN 4 ELSE 0 END, shipment_status_text = CASE [logic for condition 1] THEN 'Condition 1 text' WHEN [logic for condition 2] THEN 'Condition 2 text' WHEN [logic for condition 3] THEN 'Condition 3 text' WHEN [logic for condition 4] THEN 'Condition 4 text' ELSE 'Error' END, ...remainder of SQL view...
This works, but the logic for each of the case conditions is rather long. I'd like to move away from this for easier code management, plus I imagine that this isn't the best performance-wise.
This is what I'd like to do:
Code Block ...beginning of SQL view... shipment_status = CASE [logic for condition 1] THEN 1 WHEN [logic for condition 2] THEN 2 WHEN [logic for condition 3] THEN 3 WHEN [logic for condition 4] THEN 4 ELSE 0 END,
shipment_status_text =
CASE shipment_status
WHEN 1 THEN 'Condition 1 text'
WHEN 2 THEN 'Condition 2 text'
WHEN 3 THEN 'Condition 3 text'
WHEN 4 THEN 'Condition 4 text'
ELSE 'Error'
END, ...remainder of SQL view...
This runs as a query, however all of the rows now should "Error" as the value for shipment_status_text.
Is what I'm trying to do even currently possible in T-SQL? If not, do you have any other suggestions for how I can accomplish the same result?
I have a simple Control Flow setup that checks to see if a particular table exists. If the table does not exists, the table is created in an alternate path, if it does exist, the table is truncated before moving to a file import Data Flow that uses an OLE DB Destination to output the imported data.
My problem is, that I get OLE DB package errors if the table the OLE DB Destination Container references does not exist when I load the package.
How can I over come this issue? I need to be able to dynamically create the table in an earlier step, then use that table to import data into in a later step in the workflow.
Is there a switch I can use to turn off checking in the OLE DB Destination Container so that it will allow me to hook up the table creation step?
Seems like this would be a common task...
Steps:
1. Execute SQL Task to see if the required table exists 2. Use expresions to test a variable to check the results of step 1 3. If table exists, truncate the table and reload it from file in Data Flow using OLE DB Destination 4. If table does not exist, 1st create it, then follow the normal Data Flow
i was tasked to created an UPDATE statement for 6 tables , i would like to update 4 columns within the 6 tables , they all contains the same column names. the table gets its information from the source table, however the data that is transferd to the 6 tables are sometimes incorrect , i need to write a UPDATE statement that will automatically correct the data. the Update statement should also contact a where clause
the columns are [No] , [Salesperson Code], [Country Code] and [Country Name]
i was thinking of doing
Update [tablename] SET [No] = CASE WHEN [No] ='AF01' THEN 'Country Code' = 'ZA7' AND 'Country Name' = 'South Africa' ELSE 'Null' END
Hello friends, I want to use select statement in a CASE inside procedure. can I do it? of yes then how can i do it ?
following part of the procedure clears my requirement.
SELECT E.EmployeeID, CASE E.EmployeeType WHEN 1 THEN select * from Tbl1 WHEN 2 THEN select * from Tbl2 WHEN 3 THEN select * from Tbl3 END FROM EMPLOYEE E
can any one help me in this? please give me a sample query.
material ========= material_id project_type project_id qty 1 AB Corporate 1 3 2 Other Project 2 7
i have taken AB Corporate for AB_Corporate_project ,Other Project for Other_project
sample query i write :--
select m.material_id ,m.project_type,m.project_id,m.qty,ab.ab_crp_id, ab.custname ,op.other_proj_id,op.other_custname,op. po case if m.project_type = 'AB Corporate' then select * from AB_Corporate_project where ab.ab_crp_id = m.project_id else if m.project_type = 'Other Project' then select * from Other_project where op.other_proj_id=m.project_id end from material m,AB_Corporate_project ab,Other_project op
but this query not work,also it gives errors
i want sql query to show data as follows
material_id project_type project_id custname other_custname qty 1 AB Corporate 1 abc -- 3 2 Other Project 2 -- dsd 7
so plz help me how can i write sql query for to show the output plz send a sql query
I had got the below error when I execute a DELETE SQL query in SSIS Execute SQL Task :
Error: 0xC002F210 at DelAFKO, Execute SQL Task: Executing the query "DELETE FROM [CQMS_SAP].[dbo].[AFKO]" failed with the following error: "The transaction log for database 'CQMS_SAP' is full. To find out why space in the log cannot be reused, see the log_reuse_wait_desc column in sys.databases". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.
But my disk has large as more than 6 GB space, and I query the log_reuse_wait_desc column in sys.databases which return value as "NOTHING".
So this confused me, any one has any experience on this?
I have a master package, which executes child packages that are located on a SQL Server. The Child packages execute other child packages which are also located on the SQL server.
Everything works fine when I execute in process. But when I set the parameter in the mater package ExecutePackageTask to ExecuteOutOfProcess = True, I get the following error
Error: 0xC00470FE at DFT Load Data, DTS.Pipeline: SSIS Error Code DTS_E_PRODUCTLEVELTOLOW. The product level is insufficient for component "Row Count" (5349).
Error: 0xC00470FE at DFT Load Data, DTS.Pipeline: SSIS Error Code DTS_E_PRODUCTLEVELTOLOW. The product level is insufficient for component "SCR Custom Split" (6399).
Error: 0xC00470FE at DFT Load Data, DTS.Pipeline: SSIS Error Code DTS_E_PRODUCTLEVELTOLOW. The product level is insufficient for component "SCR Data Source" (5100).
Error: 0xC00470FE at DFT Load Data, DTS.Pipeline: SSIS Error Code DTS_E_PRODUCTLEVELTOLOW. The product level is insufficient for component "DST_SCR Load Data" (6149).
The child packages all run fine when executed directly, and the master package runs fine if Execute Out of Process is False.
I am trying to use a case statement in one of my stored proc but I am stuck a little bit.Here is a example, something like:declare @id int set @id =1case @id When 1 then select * from contactsend case but this keeps on giving me error: Incorrect syntax near the keyword 'case'. Any help is appreciated!
Hi I have some question regarding the sql case statment.Can i use the case statement with the where clause.Example: SELECT FirstName, IDFROM myTablewhere case when ID= '123' then id = '123' and id='124' endorder by idBut the above code does not work.
Hi all, I was wondering if there is any way in an sql statement to check whether the data your trying to get out of the DB is of a particular type, ie. Int, char etc. I was thinking about a case statement such as <code> CASE WHEN (MyNum <> INT) then 0 end AS MyNum </code>
This has to be included in the sql statement cause I need this field to get other data. Any thoughts on how to achieve this would be greatly appreciated.
If I’m in the wrong thread section please advise of best one to get help in.
Hi !!!i hope one of the sql specialists answer me about the best and most effeceint way to acheive what i am looking for Scenario:-------------i have a 3 tables related to each other Addresses, Groups and GroupAddressthe relation is for both addresses and groups is one to many in the GroupAddress.the behaviour in the application : user can add addresses to his address list and from the address list a user can add an address to many groups like if you have Group name "Freinds" and you add me in it and you have Football team group and you add me to it like that !!!not i have another function called "copy group"in the GroupAddress i have this data as example GroupID AddressID1 41 61 21 441 72 82 62 93 133 73 10and the group ID called "Freinds"i want to copy the group so i can have another group that has the same addresses by one click rather than collectiong them again one by one ...by the way the new copy will have a new group name ( as this is thebusiness logic so user can not have dupicate group name )so what is the best SQL statement that i need to copy the group ???i hope that clear enough!
I am trying determine if I can do something like the code below. I have done a left join on a table. In the select statement there are three possible values. Yes, No, or NULL. I could like to use a Case statement to determine if there is Null. If so, then output N/A in place of the Null. So then my possible valus are Yes, No, and N/A.
Any clues?
Thanks, John
SELECT TOP 100 OfferDressRoomYN.yesno as OfferDressRoom = CASE WHEN offerDressRoomYN.yesno IS NULL THEN 'N/A' END, FROM dataquestionnaire dq LEFT OUTER JOIN yesno OfferDressRoomYN ON dq.c3_1 = OfferDressRoomYN.yesnoid
In my query below i have the results ,The thing to observe in the result set it for the name "Acevedo" , "Abeyta" its not doing a group by and populating the results in the following column.Rather its addind a new row and adding it as 1 in the next row. I have to populate the counts in one row for common names.Shall i use a if condition within a case block.If yes how?any other work arounds would be appriciated. Please help Thanks
select isnull(replace(Ltrim(Rtrim(P.Lastname)),',',''),'' ) Lastname , case ProductID WHEN 22 then count(S.Product) Else 0 END AS Builders , case ProductID WHEN 23 then count(S.Product) Else 0 END AS Associates , case ProductID WHEN 24 then count(S.Product) Else 0 END AS Affiliates FROM vwpersons p with (nolock) join vwSubscriptions S with (nolock) on S.RecipientID = P.ID where P.Lastname in (select Ltrim(Rtrim(H.name)) from externaldata.dbo.Hispanicnames H) group by P.Lastname, S.ProductID having count(P.LastName)>=1 order by 1
I am trying to get avg score by site, by call type. Columns are Site(varchar), Calltype(varchar), totalscore(float). Calltypes are A, B, C, D. Sites are 1, 2, 3, 4. I can do a straight average statement and only get one calltype. I want to do a CASE statement to get all average scores for all calltypes.
Select Site, avg(totalscore) as [Avg Score] FROM DB WHERE calltype = 'A' GROUP BY Site
Results
Site Avg Score (for A) 1 85 2 75.5 3 85.33
SELECT Site, AVG(CASE WHEN TotalScore > 0 AND CallType = 'A' THEN Totalscore ELSE 0 END) AS [Avg Score For A] FROM DB GROUP BY Site
Results
Site Avg Score For A 1 i get 8.5 2 i get 37.75 3 i get 36.57 Why am I getting a difference? Any help is greatly appreciated - thank you
Hi Ive got a simple query where I want to calculate an average of one number divided by the other ie: avg(x / y)
Im trying to use a case statement to return 0 in the event that y is 0, to avoid a division by zero error. My query is still returning a division by zero error anyway can anybody help?
SELECT CCode, CASE WHEN BS_TOTAL_ASSETS = 0 THEN 0 ELSE AVG(BSCLTradeCreditors / BS_TOTAL_ASSETS) END AS myaverage FROM [Company/Year] GROUP BY CCode, BS_TOTAL_ASSETS
i ahve one fucniton: create function fntotalcountcustclas
( @campaign varchar(50), @startdate datetime, @enddate datetime) RETURNS TABLE AS RETURN ( Select t.itemnmbr,t.custclas, t.custclasdescription, t.totalcustclas as totalcount
from ( select vi.itemnmbr, replace(vc.custclas,'','Unspecified') as custclas, vc.custclasdescription, count(vc.custclas) as totalcustclas from vwcustnmbr vc join vwitemnbmr vi on vi.sopnumbe=vc.sopnumbe Where vi.Campaign = @Campaign and (vc.docdate between @startdate and @enddate)
group by vi.itemnmbr,vc.custclas, vc.custclasdescription ) as t ) when i m executing it: select * from fntotalcountcustclas('copd','1/1/2008','4/11/2008') order by totalcount desc
i m getting results like: itemnmbr,custclas,custclasdescription,totalcount ------------------------------------------------ 06-5841 STANDARD Standard(web) 31 06-5840 STANDARD Standard(web) 30 kr-014 STANDARD Standard(web) 72 06-5841 INDPATIENT Patient 12 06-5840 INDPATIENT Patient 9 06-5845 INDPATIENT Patient 6 06-5841 PROGRAM Program 6 06-5841 INST-HOSPITAL Hospital 11 ...................
Basically, i ahve to use one condition to get corrrect output related to inputs:
like - i have to input @category varchar(50), @category_value varchar(50) and if category = 'campaign' then category_value = '' then output should be itemnmbr sum(totalcount) [whatever should be custclas or custclasdesscription] itemnmbr sumcount ----------------- 06-5840 52 06-541 101 06-452 26 kr-045 252
and if categroy = 'item' then category_value = any itemnmbrs(06-5840,06-5845,06-5841 etc..) then output should be itemnmbr custclas custclasdescription totalcount ----------------------------------------------------- 06-5840 STANDARD Standard(web) 31 06-5840 INDPATIENT Patient 9 06-5840 PROGRAM Program 6 06-5840 INS-HOSPITAL Hospital 17
like that..
can anyone help me to write case statement. thanks a lot!!
create function fntotalcountcustclas
( @campaign varchar(50), @startdate datetime, @enddate datetime, @category varchar(50), @category_value varchar(50)) RETURNS TABLE AS RETURN ( Select t.itemnmbr,t.custclas, t.custclasdescription, t.totalcustclas as totalcount, case when category
from ( select vi.itemnmbr, replace(vc.custclas,'','Unspecified') as custclas, vc.custclasdescription, count(vc.custclas) as totalcustclas from vwcustnmbr vc join vwitemnbmr vi on vi.sopnumbe=vc.sopnumbe Where vi.Campaign = @Campaign and (vc.docdate between @startdate and @enddate)
group by vi.itemnmbr,vc.custclas, vc.custclasdescription ) as t )
Im running the following sql statement but I dont see the expected output. There are few differences between acc & cl1, mcc & cl2 , ncr & cl3 but I dont see either 'ONE' or 'TWO' or 'THREE'. There is even a case where cl3 is null but the sql is not filling in either one or two or three. Query simply returns id & rest as null values.
SELECT P1.id, CASE WHEN p1.acc!= p1.cl1 then 'ONE' WHEN p1.mcc!= p1.cl2 then 'TWO' when p1.ncr!= p1.cl3 then 'THREE' Else NULL END As NOnMatchingColumn from (select id, acc, cl1,mcc,cl2,ncr,cl3 from dbo.ml)P1
I'm not sure if I am doing this the right way. In my table I have project ids. Each project id has several activities associated with it. Each project has a starting balance. Each activity posts an expense to the total balance of the project. If the project has enough money to handle the charges made by the activities, all the activity expenses can be "posted". If there isn't enough money, I want to loop through the activities, check to see if there is enough of a balance to "post" the first one, If there is, then I want to re-adjust the balance and check the second activity. I want to scroll through each project/activity to see what can be "posted". Here is what I have so far, but I cannot work out how to change the total balance amount. Hopefully what I am trying to do makes sense!
declare @testId nchar(6) declare @RowNum int declare @newBalance int select top 1 @testId=projID from #ProjIds set @RowNum = 0
WHILE @RowNum <= (Select Max(temp_id) from #ProjIds) BEGIN set @RowNum = @RowNum + 1 set @newBalance = (select top 1 Bal_2300 from #RevRecData where @testId=projId order by projID, activity)
select projId, activity, postCr, Bal_2300, 'New_Status' = Case when (postCr <= Bal_2300) then 'Can Clear' else 'Still Check' END, 'New_Balance' = Case when (postCr <= @newBalance) then (@newBalance - postCr) else @newBalance End from #RevRecData where @testId=projId order by projID, activity
select top 1 @testId=projId from #ProjIds where projId > @testID END
This query only display Max(Daily_Rainfall) per day from SEL
SELECT t1.MAIN_ID,t1.DATE_TAKEN,t1.TIME, t1.DAILY_RAINFALL, CASE WHEN DATEDIFF(mm,t1.DATE_TAKEN,GETDATE()) <=3 THEN t3.[3MthCumRf] WHEN DATEDIFF(mm,t1.DATE_TAKEN,GETDATE()) <=6 THEN t3.[6MthCumRf] WHEN DATEDIFF(mm,t1.DATE_TAKEN,GETDATE()) <=9 THEN t3.[9MthCumRf] WHEN DATEDIFF(mm,t1.DATE_TAKEN,GETDATE()) <=12 THEN t3.[12MthCumRf] END AS CumRf FROM (SELECT ROW_NUMBER() OVER(PARTITION BY DATE_TAKEN,MAIN_ID ORDER BY DAILY_RAINFALL DESC) AS RowNo,* FROM dbo.SEL)t1 INNER JOIN dbo.STATION_INFO t2 ON t2.MAIN_ID=t1.MAIN_ID AND t1.RowNo=1 INNER JOIN dbo.LT t3 ON t3.STATION_ID =t2.STATION_ID AND t1.DATE_TAKEN>=CONVERT(VARCHAR(10),DATEADD(m,-3,GETDATE()),101) ANDt1.DATE_TAKEN<CONVERT(VARCHAR(10), GETDATE(), 101) WHERE t2.STATE='SEL' ORDER BY MAIN_ID,DATE_TAKEN,TIME
In my query, i still can query if last3month using CASE WHEN DATEDIFF(mm,t1.DATE_TAKEN,GETDATE()) <=3 THEN t3.[3MthCumRf]
How to adjust my CASE statement if Main_ID=203, Date_Taken=5/5/2005,
i should pickup following rows Main_ID | Month_Year | 3MthCumRf | 6MthCumRf | 9MthCumRf | 12MthCumRf ------------------------------------------------------------------- 203| 5/30/2005 | 485.5 | 444.9 | 744.34 | 755.34
if Main_ID=203, Date_Taken=4/5/2005, i should pickup following rows Main_ID | Month_Year | 3MthCumRf | 6MthCumRf | 9MthCumRf | 12MthCumRf 203| 4/30/2005 | 435.5 | 400.9 | 290.34 | 234.34
In my temptable I'm still trying to replace NULL values with 0 (zero).I tried ISNULL and that didn't work.I tried ISNULL and it didn't work. I was told by a senior developer I work with that I should do something like a case when. whatever that is.
INSERT INTO #TEMPLEASEDOLLARSSUBMITTED (SalesName, UserID, LeaseDollarsSubmitted) ( SELECT (FirstName + '' + LastName)AS [Sales Team Member], Usr.UserID, SUM (ISNULL(MerAppEqL.MonthlyPayment,0))AS [Leases $ Submitted] FROM MerchantAppEquipmentLease MerAppEqL WITH(NOLOCK) INNER JOIN MerchantAppEquipment MerAppEqt ON MerAppEqL.EquipmentLeaseID = MerAppEqt.LeaseNumber
[code]...
Or should I have put the ISNULL when I updated #TEMPDASHBOARD because that is where the NULL values show up. They don't show up in #TEMPLEASEDOLLARSSUBMITTED table?
Hello, I have a sql statement that uses case statement to calculate a variable, I would like to use that variable in the same sql statement to calculate a different variable. In my code Wed_Var is calculated, but since I dont know a way to use the Wed_Var in Wed_Adj, I copy the whole formula again. Any help is appreciated.
Thanks
SELECT TOP 100 PERCENT dbo.VIEW_ManPlan_Dock_Prev_4WK.Terminal, Wed_Var = CASE WHEN dbo.VIEW_ManPlan_Dock_Prev_Current.Tue = 0 THEN dbo.VIEW_ManPlan_Dock_Prev_Run_Tuesday.Tue_Var WHEN (dbo.VIEW_ManPlan_Dock_Prev_Current.Mon + dbo.VIEW_ManPlan_Dock_Prev_Current.Tue) / dbo.VIEW_ManPlan_Dock_Prev_Run_Tuesday.Week_To_Date < .75 THEN .75 WHEN (dbo.VIEW_ManPlan_Dock_Prev_Current.Mon + dbo.VIEW_ManPlan_Dock_Prev_Current.Tue) / dbo.VIEW_ManPlan_Dock_Prev_Run_Tuesday.Week_To_Date > 1.25 THEN 1.25 ELSE (dbo.VIEW_ManPlan_Dock_Prev_Current.Mon + dbo.VIEW_ManPlan_Dock_Prev_Current.Tue) / dbo.VIEW_ManPlan_Dock_Prev_Run_Tuesday.Week_To_Date END, Wed_Adj = CASE WHEN dbo.VIEW_ManPlan_Dock_Prev_Current.Tue = 0 THEN dbo.VIEW_ManPlan_Dock_Prev_Run_Tuesday.Tue_Var * (dbo.VIEW_ManPlan_Dock_Prev_4Wk.Wed / 4) WHEN (dbo.VIEW_ManPlan_Dock_Prev_Current.Mon + dbo.VIEW_ManPlan_Dock_Prev_Current.Tue) / dbo.VIEW_ManPlan_Dock_Prev_Run_Tuesday.Week_To_Date < .75 THEN .75 * (dbo.VIEW_ManPlan_Dock_Prev_4Wk.Wed / 4) WHEN (dbo.VIEW_ManPlan_Dock_Prev_Current.Mon + dbo.VIEW_ManPlan_Dock_Prev_Current.Tue) / dbo.VIEW_ManPlan_Dock_Prev_Run_Tuesday.Week_To_Date > 1.25 THEN 1.25 * (dbo.VIEW_ManPlan_Dock_Prev_4Wk.Wed / 4) ELSE (dbo.VIEW_ManPlan_Dock_Prev_4Wk.Wed / 4) * ((dbo.VIEW_ManPlan_Dock_Prev_Current.Mon + dbo.VIEW_ManPlan_Dock_Prev_Current.Tue) / dbo.VIEW_ManPlan_Dock_Prev_Run_Tuesday.Week_To_Date) END, Week_To_Date = CASE WHEN dbo.VIEW_ManPlan_Dock_Prev_Current.Tue = 0 THEN dbo.VIEW_ManPlan_Dock_Prev_Run_Tuesday.Week_To_Date + (dbo.VIEW_ManPlan_Dock_Prev_4Wk.Wed / 4) * dbo.VIEW_ManPlan_Dock_Prev_Run_Tuesday.Tue_Var WHEN (dbo.VIEW_ManPlan_Dock_Prev_Current.Mon + dbo.VIEW_ManPlan_Dock_Prev_Current.Tue) / dbo.VIEW_ManPlan_Dock_Prev_Run_Tuesday.Week_To_Date < .75 THEN dbo.VIEW_ManPlan_Dock_Prev_Run_Tuesday.Week_To_Date + (dbo.VIEW_ManPlan_Dock_Prev_4Wk.Wed / 4) * .75 WHEN (dbo.VIEW_ManPlan_Dock_Prev_Current.Mon + dbo.VIEW_ManPlan_Dock_Prev_Current.Tue) / dbo.VIEW_ManPlan_Dock_Prev_Run_Tuesday.Week_To_Date > 1.25 THEN dbo.VIEW_ManPlan_Dock_Prev_Run_Tuesday.Week_To_Date + (dbo.VIEW_ManPlan_Dock_Prev_4Wk.Wed / 4) * 1.25 ELSE dbo.VIEW_ManPlan_Dock_Prev_Run_Tuesday.Week_To_Date + (dbo.VIEW_ManPlan_Dock_Prev_4Wk.Wed / 4) * ((dbo.VIEW_ManPlan_Dock_Prev_Current.Mon + dbo.VIEW_ManPlan_Dock_Prev_Current.Tue) / dbo.VIEW_ManPlan_Dock_Prev_Run_Tuesday.Week_To_Date) END FROM dbo.VIEW_ManPlan_Dock_Prev_4WK INNER JOIN dbo.VIEW_ManPlan_Dock_Prev_Current ON dbo.VIEW_ManPlan_Dock_Prev_4WK.Terminal = dbo.VIEW_ManPlan_Dock_Prev_Current.Terminal INNER JOIN dbo.VIEW_ManPlan_Dock_Prev_Run_Tuesday ON dbo.VIEW_ManPlan_Dock_Prev_4WK.Terminal = dbo.VIEW_ManPlan_Dock_Prev_Run_Tuesday.Terminal ORDER BY dbo.VIEW_ManPlan_Dock_Prev_4WK.Terminal
Getting error msg from the attached stored procedures! Error msg-'Invalid column name 'TRUE'
can you help/tell what I'm doing wrong
Thk
Josephine
CREATE PROCEDURE dbo.SP_EXPERIAN_QUEUE ( @FRAUDANALYSTNVARCHAR(50), @QUEUENVARCHAR(24), @STATUSNVARCHAR(24) ) AS
SELECT E.EXPERIANID, E.ALERT_RECEIVED_DATE, E.ACCT, E.SRC_INDV_ID, E.INQUIRY_GOVT_NUMBER, E.CUST_NM, E.INQUIRY_STATE, E.INQUIRY_DATE, E.ALERT_IDENTIFIER, E.[OVERRIDE] , E.BEST_SSN, E.PRECISEIDSCORE FROM TBLEXPERIAN E WHERE TRUE = CASE WHEN @FRAUDANALYST = 'ALL' THEN TRUE WHEN EXPERIANANALYSTASSIGNED = @FRAUDANALYST THEN TRUE ELSE FALSE END AND TRUE = CASE WHEN @QUEUE = 'ALL' THEN TRUE WHEN @QUEUE = 'LOWSCORE' AND ( E.PRECISEIDSCORE < '500' OR ( E.PRECISEIDSCORE < '600' AND ( E.FraudShieldIndicator4 = 'Y' OR E.FraudShieldIndicator5 = 'Y' OR E.FraudShieldIndicator6 = 'Y' OR E.FraudShieldIndicator13 = 'Y' OR E.FraudShieldIndicator14 = 'Y' OR E.FraudShieldIndicator25 = 'Y' OR E.FraudShieldIndicator26 = 'Y' OR E.FraudShieldIndicator27 = 'Y' OR E.FraudShieldIndicator16 = 'Y' OR E.FraudShieldIndicator10 = 'Y' ) ) ) THEN TRUE WHEN @QUEUE = 'HIGH' THEN TRUE ELSE FALSE END AND TRUE = CASE WHEN @STATUS = 'ALL' THEN TRUE WHEN @STATUS = 'PENDING' and ( E.EXPERIAN_DECISION_DT IS NOT NULL AND E.EXPERIAN_DECISION IN ('YELLOW', 'E-MAIL SENT', 'ESCALATED FOR DECISION', 'MANAGEMENT REVIEW', 'QUALITY REVIEW', '7 DAY LETTER') ) THEN TRUE WHEN @STATUS = 'UNWORKED' AND E.EXPERIAN_DECISION_DT is null THEN TRUE ELSE FALSE END