do you know if I can do the following task through a single query
TableA(LocID,LocNAME)
TableB(ID,LocID,Amount)
What i need to do is to add sum amount having same locID from TableB and get LocIDs name through TableA.LocName. In the query there should be one thing more, if amount is less than zero put it into credit column, while if positive, puts in debit column
Thus Result(LocId, LocName, Debit, Credit) is the requied structure.
Can anyone help me out. I m not getting how to get the LocName if gets the sum by Groupby LocID also applying condition is confusing me:s
How to write a Query for multiple legal names that have the same CARE Number (same address) with difference of one Legal Name having a period in the name versus the other legal name that doesn't.
For example: Looking for cases of two of the same legal name one set off by period
All Season Equipment Ltd. All Season Equipment Ltd
West End Housing, Inc. West End Housing, Inc
Wellings, Norman L. Wellings, Norman L
North Texas Boats, LLC North Texas Boats, L.L.C.
Oktibbeha County Cooperative (A.A.L.) Oktibbeha County Cooperative (AAL)
S & R Turf & Irrigation Equipment, L.L.C S & R Turf & Irrigation Equipment, L.L.C.
Burke Equipment Company; Burke Equipment-Seaford, Inc.; Newark Kubota, Inc. Burke Equipment Company Burke Equipment-Seaford, Inc.
Pleasant Valley Outdoor Power, L.L.C. Pleasant Valley Outdoor Power, LLC
J & D Lawn and Tractor Sales, Inc. J&D Lawn & Tractor Sales, Inc"
hello friends.. I am newbie for sql server...I having a problem when executing this procedure .... ALTER PROCEDURE [dbo].[spgetvalues] @Uid intASBEGIN SET NOCOUNT ON; select DATEPART(year, c.fy)as fy, (select contribeamount from wh_contribute where and contribename like 'Retire-Plan B-1% JRF' ) as survivorship, (select contribeamount from wh_contribute where and contribename like 'Gross Earnings' and ) as ytdgross, (select contribeamount from wh_contribute where and contribename like 'Retire-Plan B-1.5% JRP') as totalcontrib, from wh_contribute c where c.uid=@Uid Order by fy Asc .....what is the wrong here?? " Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression."please reply asap...
I am getting 2 resultsets depending on conditon, In the secondconditon i am getting the above error could anyone help me..........CREATE proc sp_count_AllNewsPapers@CustomerId intasdeclare @NewsId intset @NewsId = (select NewsDelId from NewsDelivery whereCustomerId=@CustomerId )if not exists(select CustomerId from NewsDelivery whereNewsPapersId=@NewsId)beginselect count( NewsPapersId) from NewsPapersendif exists(select CustomerId from NewsDelivery whereNewsPapersId=@NewsId)beginselect count(NewsDelId) from NewsDelivery whereCustomerid=@CustomeridendGO
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.
while running the following query.
SELECT DISTINCT EmployeeDetails.FirstName+' '+EmployeeDetails.LastName AS EmpName,
LUP_FIX_DeptDetails.DeptName AS CurrentDepartment,
LUP_FIX_DesigDetails.DesigName AS CurrentDesignation,
LUP_FIX_ProjectDetails.ProjectName AS CurrentProject,
ManagerName=(SELECT E.FirstName+' '+E.LastName
FROM EmployeeDetails E
INNER JOIN LUP_EmpProject
ON E.Empid=LUP_EmpProject.Empid
INNER JOIN LUP_FIX_ProjectDetails
ON LUP_EmpProject.Projectid = LUP_FIX_ProjectDetails.Projectid
WHERE LUP_FIX_ProjectDetails.Managerid = E.Empid)
FROM EmployeeDetails
INNER JOIN LUP_EmpDepartment
ON EmployeeDetails.Empid=LUP_EmpDepartment.Empid
INNER JOIN LUP_FIX_DeptDetails
ON LUP_EmpDepartment.Deptid=LUP_FIX_DeptDetails.Deptid
AND LUP_EmpDepartment.Date=(SELECT TOP 1 LUP_EmpDepartment.Date
FROM LUP_EmpDepartment
WHERE EmployeeDetails.Empid=LUP_EmpDepartment.Empid
ORDER BY LUP_EmpDepartment.Date DESC)
INNER JOIN LUP_EmpDesignation
ON EmployeeDetails.Empid=LUP_EmpDesignation.Empid
INNER JOIN LUP_FIX_DesigDetails
ON LUP_EmpDesignation.Desigid=LUP_FIX_DesigDetails.Desigid
AND LUP_EmpDesignation.Date=(SELECT TOP 1 LUP_EmpDesignation.Date
FROM LUP_EmpDesignation
WHERE EmployeeDetails.Empid=LUP_EmpDesignation.Empid
ORDER BY LUP_EmpDesignation.Date DESC)
INNER JOIN LUP_EmpProject
ON EmployeeDetails.Empid=LUP_EmpProject.Empid
AND LUP_EmpProject.StartDate=(SELECT TOP 1 LUP_EmpProject.StartDate
FROM LUP_EmpProject
WHERE EmployeeDetails.Empid=LUP_EmpProject.Empid
ORDER BY LUP_EmpProject.StartDate DESC)
INNER JOIN LUP_FIX_ProjectDetails
ON LUP_EmpProject.Projectid=LUP_FIX_ProjectDetails.Projectid
I've running the below query for months ans suddenly today started getting the following error :"Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression."
Any ideas as to why??
SELECT t0.DocNum, t0.Status, t0.ItemCode, t0.Warehouse, t0.OriginNum, t0.U_SOLineNo, ORDR.NumAtCard, ORDR.CardCode, OITM_1.U_Cultivar, RDR1.U_Variety, (SELECT OITM.U_Variety FROM OWOR INNER JOIN WOR1 ON OWOR.DocEntry = WOR1.DocEntry INNER JOIN OITM INNER JOIN OITB ON OITM.ItmsGrpCod = OITB.ItmsGrpCod ON WOR1.ItemCode = OITM.ItemCode WHERE (OITB.ItmsGrpNam = 'Basic Fruit') AND (OWOR.DocNum = t0.DocNum)) AS Expr1, OITM_1.U_Organisation, OITM_1.U_Commodity, OITM_1.U_Pack, OITM_1.U_Grade, RDR1.U_SizeCount, OITM_1.U_InvCode, OITM_1.U_Brand, OITM_1.U_PalleBase, OITM_1.U_Crt_Pallet, OITM_1.U_LabelType, RDR1.U_DEPOT, OITM_1.U_PLU, RDR1.U_Trgt_Mrkt, RDR1.U_Wrap_Type, ORDR.U_SCCode FROM OWOR AS t0 INNER JOIN ORDR ON t0.OriginNum = ORDR.DocNum INNER JOIN RDR1 ON ORDR.DocEntry = RDR1.DocEntry AND t0.U_SOLineNo - 1 = RDR1.LineNum INNER JOIN OITM AS OITM_1 ON t0.ItemCode = OITM_1.ItemCode WHERE (t0.Status <> 'L')
I did not get any reply for my previous post. So i am just trying to make my doubt clear.
I have a subquery within a distributed query.
Eg:
SELECT T1.deptID FROM SERVER1.ACCOUNT.DBO.DEPT as T1 where deptid IN (SELECT T2.DEPTID FROM SERVER2.DEPARTMENT.DBO.DEPT as T2 WHERE T1.DIVISIONID = T2.DIVISIONID)
In the above query.. T1.DIVISIONID is not recognoized and it says T1 is not a table or alias name though I have declared it upfront.
You have to note that both the ACCOUNT and the DEPARTMENT database are on different servers.
If they are on the same servers, this should not be a problem at all. Also, all the security is working perfectly for running a distributed query for me.
I am trying to write a query that selects all the mis_key that is not in the subquery. The left join query is working but when nested it comes out blank. Here is the query:
select * from [TLC NEW Inv. Anal.].dbo.['Home Decor$'] WHERE NOT EXISTS (SELECT * FROM [TLC New].DBO.['Fabric or basket accessory$'] LEFT JOIN [TLC NEW Inv. Anal.].DBO.['Home Decor$'] ON [TLC New].DBO.['Fabric or basket accessory$'].[Item #]=[TLC NEW Inv. Anal.].DBO.['Home Decor$'].mis_key)
Hi,I have one table name: artcolumn:symbol_art price1 price2----------- ------- -------AG-0001 20 40AG-0001S null nullAG-0002 40 60AG-0002S null null....How paste in null price1 and price2 from oryginal symbol_art AG-0001,AG-0002 ?(duplicate symbol_art %-%'S ' it's always the same for oryginal symbol_art)thanks for any helpTom
Hi.I have an insert query which inserts record that are returned from a select subquery: INSERT tbl1 (col1,col2,col3) SELECT (col1,col2,col3) FROM tbl2 WHERE... col1 and col2 in tbl1 combined ,are a unique index. So, as I understand it sql server first returns all the records from tbl2 and then starts to insert them one by one into tbl1. The problem is, that if one of the records returned from tbl2 violates the unique keys constraint in tbl1, sql server will not insert all of the records (even those which maintain the key constraint).How can I solve this ?
HiI migrate my database from Access to MS SQL Server 2005 and I have problem with one query : SELECT DISTINCT (HeadYarns_Tbl.HeadYarnsID) AS HYID, HeadYarns_Tbl.YarnType, (select max(YarnType_Tbl.Denier) from YarnType_Tbl where HeadYarns_Tbl.PrimYarnTypeID = YarnType_Tbl.YarnTypeID) AS denier1, (select max(YarnType_Tbl.Denier) from YarnType_Tbl where HeadYarns_Tbl.secYarnTypeID = YarnType_Tbl.YarnTypeID) AS denier2, HeadYarns_Tbl.YarnType & Denier1 & '/' & Denier2 AS Yarninfo2, HeadYarns_Tbl.YarnType & Denier1 AS Yarninfo FROM YarnType_Tbl, HeadYarns_Tbl INNER JOIN HeadLayout_Tbl ON HeadYarns_Tbl.HeadYarnsID = HeadLayout_Tbl.HeadYarnsID ORDER BY HeadYarns_Tbl.YarnTypeIn Access It works good, but in MS SQL Server I don't know how I can use aliases in query. (bold and underline code )please help. regards Michael
I don't know how to pass parameters required (dates) to a query1, if I'm using a subquery (query2) which is using the results of query1, but I'm not showing that field on that subquery (query2)
Example
table1 id - autonumeric id_user - id from user dates - date of register
table2 id - user id name - user name
query1 SELECT Table1.id_user, Count(Table1.id_user) AS CuentaDeid_user FROM Table1 WHERE (((Table1.datess) Between [begining] And [ending])) GROUP BY Table1.id_user ORDER BY Table1.id_user;
subquery (query2) SELECT query1.id_user, query1.CuentaDeid_user, Table2.name FROM query1 LEFT JOIN Table2 ON query1.id_user = Table2.id;
This is just an example, the think is that I want to know that if it's possible to pass the parameters requested in query1 from the SQL of the subquery (query2)?
Hi. I have an insert query which inserts record that are rturned from a select subquery:
INSERT tbl1 (col1,col2,col3) SELECT (col1,col2,col3) FROM tbl2 WHERE...
col1 and col2 in tbl1 combined ,are a unique index.
So, as I understand it sql server first returns all the records from tbl2 and then starts to insert them one by one into tbl1.
The problem is, that if one of the records returned from tbl2 violates the unique keys constraint in tbl1, sql server will not insert all of the records (even those which maintain the key constraint). How can I solve this ?
I'm trying to update column based upon the results of a subquery. I'm getting the error that my Subquery returns more than one result. I've tried adding the EXISTS or IN keywords and cannot get the syntax right. I can't find any examples of how to write this with an Update query.
Here's my query:
UPDATE temp_UpdateRemainingHours SET UsedHours =
(SELECT dbo.vw_SumEnteredHours_byCHARGE_CD.SumEnteredHours, dbo.vw_SumEnteredHours_byCHARGE_CD.CHARGE_CD FROM dbo.vw_SumEnteredHours_byCHARGE_CD INNER JOIN dbo.temp_UpdateRemainingHours ON dbo.vw_SumEnteredHours_byCHARGE_CD.CHARGE_CD = dbo.temp_UpdateRemainingHours.CHARGE_CD)
FROM dbo.vw_SumEnteredHours_byCHARGE_CD, temp_UpdateRemainingHours
WHERE dbo.vw_SumEnteredHours_byCHARGE_CD.CHARGE_CD = dbo.temp_UpdateRemainingHours.CHARGE_CD
Microsoft SQL Server 2000 - 8.00.2191 (Intel IA-64)
Mar 27 2006 11:51:52
Copyright (c) 1988-2003 Microsoft Corporation
Enterprise Edition (64-bit) on Windows NT 5.2 (Build 3790: Service Pack 1)
sp_dboption 'BB_XXXXX'
The following options are set:
-----------------------------------
trunc. log on chkpt.
auto create statistics
auto update statistics
OK, the problem is that if a run the below query in server01, i get error 512:
Msg 512, Level 16, State 1, Line 1
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.
But, if run the same query in the server02, the query work fine -.
I know that I can use IN, EXISTS, TOP, etc ... but I need understand this behavior.
I have a query where I need to select a bunch of rows from one table, hypothetically we'll call them ssn, first name, last name, and I need to select a subquery which coalesces a bunch of rows together (in no case will there be only one row returned from that subquery).
Anyone know how I could go about this? I'll give you an example of what I've tried, but it does not work currently.
delcare @path varchar(255) select e.ssn,
e.firstname, e.lastname, ( @path = select coalesce(@path + ', ', '') from space s1 inner join space s2
on s1.lft BETWEEN s2.lft AND s2.rgt and s1.rgt BETWEEN s2.lft AND s2.rgt where s1.spaceID = 133225 select @path) from employees e where e.id = 5
Using that spaceID is guaranteed to give me four rows, and I need them coalesced together, but I can't just use a function (too slow on the scale it would be used), any thoughts?
I am using SQL Server 2000. I have a somewhat large query and hope someone could help me with it. (Man, there needs to be a way to use colors...) The bolded parts of the query need to be replaced by the underlined part. I can't say 'b.HW' because of the scope of the inline query and that they are all on the same level. I've been told I need to change to a nested subquery, but can't for the life of me figure out how to do that. Can someone please show me? The current query: SELECT x.SSN,x.RealName,x.BudgetCode,x.TH,b.HW,x.HP,z.HL,x.NHW,x.FSLAOT,y.HHW AS AH,y.NHH,CASE WHEN x.TH < x.HP THEN 'XX' WHEN x.TH > x.HP THEN x.NHW - x.HP - y.NHH WHEN x.TH <= x.HP THEN 0 END AS SOT,CASE WHEN x.TH > x.HP THEN x.NHW - x.HP - y.NHH + x.FSLAOT WHEN x.TH <= x.HP THEN 0 END AS AO FROM(SELECT a.SSN,a.RealName,a.BudgetCode,SUM(a.Hours) AS TH,CASE WHEN SUM(a.hours) > 40 THEN (SUM(a.hours) - 40) * 1.5 WHEN SUM(a.hours) <= 40 THEN 0 END AS FSLAOT,CASE WHEN SUM(a.hours) >= 40 THEN 40 WHEN SUM(a.hours) < 40 THEN SUM(a.hours) END AS NHW,32 AS HP FROM dbo.ActivitiesInCurrentFiscalYear aWHERE a.ItemDate BETWEEN startdate and enddate AND a.ScheduleType = 1 AND a.EmployeeType = 2GROUP BY a.SSN, a.RealName, a.BudgetCode) x LEFT OUTER JOIN (SELECT a.SSN,a.RealName,a.BudgetCode,SUM(a.Hours) AS HHW,CASE WHEN SUM(a.Hours) >= 8 THEN 8 WHEN SUM(a.Hours) < 8 THEN SUM(a.Hours) END AS NHH FROM dbo.ActivitiesInCurrentFiscalYear a, dbo.Holidays hWHERE a.ItemDate = h.HolidayDate AND a.ItemDate BETWEEN startdate and enddate AND a.EmployeeType = 2 AND a.ScheduleType = 1GROUP BY a.SSN, a.RealName, a.BudgetCode) y ON x.SSN = y.SSN AND x.RealName = y.RealName AND x.BudgetCode = y.BudgetCode LEFT OUTER JOIN (SELECT a.SSN,a.RealName,a.BudgetCode,SUM(a.Hours) AS HLFROM dbo.ActivitiesInCurrentFiscalYear aWHERE a.ItemDate BETWEEN startdate and enddate AND a.EmployeeType = 2 AND a.ScheduleType = 1 AND (a.program_code = '0080' OR a.program_code = '0081')GROUP BY a.SSN, a.RealName, a.BudgetCode) z ON x.SSN = z.SSN AND x.RealName = z.RealName AND x.BudgetCode = z.BudgetCode LEFT OUTER JOIN (SELECT a.SSN,a.RealName,a.BudgetCode,SUM(a.Hours) AS HWFROM dbo.ActivitiesInCurrentFiscalYear aWHERE a.ItemDate BETWEEN startdate and enddate AND a.EmployeeType = 2 AND a.ScheduleType = 1 AND NOT (a.program_code = '0080' OR a.program_code = '0081')GROUP BY a.SSN, a.RealName, a.BudgetCode) b ON x.SSN = b.SSN AND x.RealName = b.RealName AND x.BudgetCode = b.BudgetCode)
When I execute the below queries it works perfectly where as my expectation is, it should break.
Select * from ChildDepartment C where C.ParentId IN (Select Id from TestDepartment where DeptId = 1) In TestDepartment table, I do not have ID column. However the select in sub query works as ID column exists in ChildDepartment. If I do change the query to something below then definately it will break - Select * from ChildDepartment C where C.ParentId IN (Select D.Id from TestDepartment D where D.DeptId = 1)
Shouldn't the default behavior be otherwise? It should throw error if column doesnt exists in sub query table and force me to define the correct source table or alias name.
create table TestDepartment ( DeptId int identity(1,1) primary key, name varchar(50) ) create table ChildDepartment ( Id int identity(1,1) primary key,
I am trying to add the results of both of these queries together:
The purpose of the first query is to find the number of nulls in the TimeZone column.
Query 1:
SELECT COUNT(*) - COUNT (TimeZone) FROM tablename
The purpose of the second query is to find results in the AAST, AST, etc timezones.
Query 2:
SELECT COUNT (TimeZone) FROM tablename WHERE TimeZone NOT IN ('EST', 'MST', 'PST', 'CST')
Note: both queries produce a whole number with no decimals. Ran individually both queries produce accurate results. However, what I would like is one query which produced a single INT by adding both results together. For example, if Query 1 results to 5 and query 2 results to 10, I would like to see a single result of 15 as the output.
What I came up with (from research) is:
SELECT ((SELECT COUNT(*) - COUNT (TimeZone) FROM tablename) + (SELECT COUNT (TimeZone) FROM tablename WHERE TimeZone NOT IN ('EST', 'MST', 'PST', 'CST'))
I get a msq 102, level 15, state 1 error.
I also tried
SELECT ((SELECT COUNT(*) - COUNT (TimeZone) FROM tablename) + (SELECT COUNT (TimeZone) FROM tablename WHERE TimeZone NOT IN ('EST', 'MST', 'PST', 'CST')) as IVR_HI_n_AK_results
but I still get an error. For the exact details see:
[URL]
NOTE: the table in query 1 and query 2 are the same table. I am using T-SQL in SQL Server Management Studio 2008.
I'm looking for some online resources here. Specifically, I'm interested in finding some case/project examples to learn more. I'm looking for any and all kinds in all areas...ADO, Security, Maintenance, etc. I've worn out google, but the most I seem to find is articles. I'm looking for actual Cases, like one you'd find in a text.
I have a text book from a couple of courses I took in school. Unfortunately it doesn't delve much into said areas. Any resources you could point me I will greatly appreciate it. I'd even be interested in some actual books if there's any that any of you have experience with that you think would help me out. Thanks for reading.
I recently added a nested table to a model that I had been using for a while. I noticed that after I added the nested table that the ClusterDistance() function returned 0 for every case. I went ahead and changed some of the keys for the nested table records so that the values would show up as missing and now the cases with a missing value have a non-zero ClusterDistance() value. Can anyone help me understand why this may be happening?
I have the following sproc that gets all the items from a queue with a few filters. I however need to return records where jobstepId is 1 and job jobqueuestatusid to be 4 if any jobqueuestatusid was 4 for that jobscheduleid, 2 if any is 2, and lastly 1. I tried inserting a case when exists(select * from flexportjobqueueview where jobscheduleID = [jobscheduleID] and jobqueuestatusid = '4' then 4, else ..... then 3, else ....... then 1, end
that did not seem to work. It inserted 4 or 3's for all and not just the particular scheduleid. Any help on this will be great thanks Ludwig
CREATE TABLE #QTEMP( [JobQueueID][int], [JobScheduleID][int], [JobID][int], [JobName][varchar](50), [JobDesc][varchar](50), [JobStepID][int], [JobStepName][varchar](50), [JobStepDesc][varchar](50), [JobStepExecutable][varchar](100), [JobQueueStatus_ID][int], [JobQueueStatusDesc][varchar](100), [NextRunDateTime][datetime], [LastRunDateTime][datetime], [ProcessID][int] )ON[PRIMARY] Declare @sql nvarchar(4000) Set @sql='INSERT INTO #QTEMP SELECT [JobQueueID], [JobScheduleID], [JobID], [JobName], [JobDesc], [JobStepID], [JobStepName], [JobStepDesc], [JobStepExecutable], [JobQueueStatus_ID], [JobQueueStatusDesc], [NextRunDateTime], [LastRunDateTime], [ProcessID] FROM [FlexPort].[dbo].[FlexPortJobQueueView] WHERE [JobID] IS NOT NULL
' IF ISNull(@JobScheduleID,'')<>'' Set @sql = @sql + ' And [JobScheduleID] like ''%' + @JobScheduleID + '%''' IF ISNull(@JobID,'')<>'' Set @sql = @sql + ' And [JobID] like ''%' + @JobID + '%''' IF ISNull(@JOBName,'')<>'' Set @sql = @sql + ' And [JobName] like ''%' + @JOBName + '%''' IF ISNull(@Status,'')<>'' Set @sql = @sql + ' And [JobQueueStatus_ID] like ''%' + @Status + '%''' If IsNull(@LastRunDateTime, '') <>'' Set @sql = @sql + ' And [LastRunDateTime] > ''' + Convert(varchar, @LastRunDateTime, 101) + ''''
Exec master.dbo.sp_ExecuteSql @sqlI have the following sproc that gets all the items from a queue with a few filters. I however need to return records where jobstepId is 1 and job jobqueuestatusid to be 4 if any jobqueuestatusid was 4 for that jobscheduleid, 2 if any is 2, and lastly 1. I tried inserting a case when exists(select * from flexportjobqueueview where jobscheduleID = [jobscheduleID] and jobqueuestatusid = '4' then 4, else ..... then 3, else ....... then 1, end
that did not seem to work. It inserted 4 or 3's for all and not just the particular scheduleid. Any help on this will be great thanks Ludwig
CREATE TABLE #QTEMP( [JobQueueID][int], [JobScheduleID][int], [JobID][int], [JobName][varchar](50), [JobDesc][varchar](50), [JobStepID][int], [JobStepName][varchar](50), [JobStepDesc][varchar](50), [JobStepExecutable][varchar](100), [JobQueueStatus_ID][int], [JobQueueStatusDesc][varchar](100), [NextRunDateTime][datetime], [LastRunDateTime][datetime], [ProcessID][int] )ON[PRIMARY] Declare @sql nvarchar(4000) Set @sql='INSERT INTO #QTEMP SELECT [JobQueueID], [JobScheduleID], [JobID], [JobName], [JobDesc], [JobStepID], [JobStepName], [JobStepDesc], [JobStepExecutable], [JobQueueStatus_ID], [JobQueueStatusDesc], [NextRunDateTime], [LastRunDateTime], [ProcessID] FROM [FlexPort].[dbo].[FlexPortJobQueueView] WHERE [JobID] IS NOT NULL
' IF ISNull(@JobScheduleID,'')<>'' Set @sql = @sql + ' And [JobScheduleID] like ''%' + @JobScheduleID + '%''' IF ISNull(@JobID,'')<>'' Set @sql = @sql + ' And [JobID] like ''%' + @JobID + '%''' IF ISNull(@JOBName,'')<>'' Set @sql = @sql + ' And [JobName] like ''%' + @JOBName + '%''' IF ISNull(@Status,'')<>'' Set @sql = @sql + ' And [JobQueueStatus_ID] like ''%' + @Status + '%''' If IsNull(@LastRunDateTime, '') <>'' Set @sql = @sql + ' And [LastRunDateTime] > ''' + Convert(varchar, @LastRunDateTime, 101) + ''''
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
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
I have a table with a field that contains an integer which represents the state of a record. This field "intType" may contain values 0-4.
A parameter in my stored procedure "@intUserType" may contain values 0-3
If @intUserType = 0, I need to select the records where intType = 0 or 3 but if @intUserType = 3, I need to return all records where intType > 1, all other values of @intUserType should return no records
The query I am working with seems a bit forced and I feel like it could be simplified, but I can't seem to wrap my head around it.
This is what I am working with:
Code: SELECT * FROM tblEmployees WHERE (intType = (CASE WHEN @intUserType = 0 THEN 0 ELSE NULL END) OR intType = (CASE WHEN @intUserType = 0 THEN 3 ELSE NULL END) OR intType > (CASE WHEN @intUserType = 3 THEN 1 ELSE NULL END))
Maybe it is as good as it needs to be ... I don't know .. I've only been using SQL regulary for a couple of months and I have not had the time to really study it in depth.
I have data in a table (@Outer) that I am matching to a lookup table (@Inner) which contains multiple "matches" where nulls can match any value. By sorting the inner table and grabbing the top record, I find the "best" match. I know the sort and the null matches work but I don't understand why the correlated sub query below doesn't understand that the OJ prefix refers to the outer table.DECLARE @Outer TABLE ( OuterID int IDENTITY (1, 1) NOT NULL, MethodID int NULL, CompID int NULL, FormID int NULL, InnerID int NULL )
-- UPDATE Outer Table with best match from Inner table UPDATE @Outer SET InnerID = IJ.InnerID FROM @Outer OJ INNER JOIN ( SELECT TOP 1 I.* FROM @Inner I WHERE IsNull(I.MethodID, OJ.MethodID) = OJ.MethodID AND IsNull(I.CompID, OJ.CompID) = OJ.CompID AND IsNull(I.FormID, OJ.FormID) = OJ.FormID ORDER BY I.MethodID DESC, I.CompID DESC, I.FormID DESC ) IJ ON OJ.MethodID = IsNull(IJ.MethodID, OJ.MethodID) AND OJ.CompID = IsNull(IJ.CompID, OJ.CompID) AND OJ.FormID = IsNull(IJ.FormID, OJ.FormID) SELECT * FROM @Outer The result should be OuterID 1 matched to Inner ID 3 and OuterID 2 matched to Inner ID 5. Can anyone help me? Thanks in advance.
I need to delete all rows that match at least one of the account_id values of another row *and* that has the same email address. However, if they have the same email address and none of the account_id values then I need to keep it. I've attached a sample dataset along with the expected results.
I have this: DELETE [acctID_emailAddress_tmp] FROM [acctID_emailAddress_tmp] JOIN (select emailaddress, account_id, max(contact_id_tmp) max_cid from [acctID_emailAddress_tmp] group by emailaddress, account_id) AS tempImportTable ON tempImportTable.[emailaddress] = [acctID_emailAddress_tmp].[emailaddress] WHERE [acctID_emailAddress_tmp].[contact_id_tmp] < tempImportTable.[max_cid] AND tempImportTable.[account_id] = [acctID_emailAddress_tmp].[account_id];
but it doesn't work since it's keeping the subset of the dupe row(s).
Finding the court cases where all children associated with that case have a programClosureDate. I can run this query:
CaseInfo Table CaseID, CaseNumber, CaseName
CaseChild Table CaseID, FK to CaseInfo ChildPartyID, FK to PartyID in Party table ProgramClosureDate
Party Table ID, PartyID, Firstname, LastName
SELECT ci.CaseNumber, ci.CaseName, p.firstname+' '+p.lastname AS child, cc.programClosureDate FROM CaseInfo ci JOIN CaseChild cc ON ci.CaseID = cc.CaseID JOIN Party p ON cc.ChildPartyID = p.PartyID
WHERE cc.ProgramClosureDate IS NOT NULL ORDER BY ci.CaseName
But this does not give me the cases where all the children have programCLosureDate IS NOT NULL.
I haven't been able to find a DMX query which will spit out the cases which support a particular association rule. I was hoping it would work sort of like drillthrough but show only the cases supporting a particular rule. Am I missing something?
What I ended up doing was extracting the itemsets of the rule from the model's content then running a SQL query to retrieve the cases that contain both the left-hand and right-hand itemset of the rule. I'm hoping there's a better way.
________________________________________________ When i use above querey and want to select any one of Attribute then it works right for @ITEM = 1 or 2 but for Item 3 it not shows the 'Factor' and show DateValues on option 3 which is wrong. Also when i Change selection and select Code instead of Startdate then it works for all three Options. so i guess that Startdate create a problem, but i dont know why it creating problem and how to resolve it Plz give me some sugessions and solutions to resolve it.
