MS SQL Server :: Error In Group By Clause...

Need Help With Group Clause.
HiI have this query I made but I am not sure how to fully use the group by clause. IF @option = 'day'
BEGIN
SELECT userID, SUM(Correct) AS CORRECT, SUM(Wrong) as Wrong, SUM(AssitanceNeeded) AS AssitanceNeeded,
CONVERT(VARCHAR(10),TimeDateStamp,101) As TimeDateStamp
FROM Charts
WHERE TimeDateStamp
BETWEEN DATEADD(d,-7,DATEDIFF(d,0,getdate() + 1))
AND
DATEADD(ss,-1,DATEADD(d,DATEDIFF(d,0,getdate())+ 1 , 0)) AND UserID = @UserID
GROUP BY UserID,TimeDateStamp
ENDSo I get a result like this:BB7EFE81-B532-46DC-B8D1-4E7A4186EEB0012003/23/2008BB7EFE81-B532-46DC-B8D1-4E7A4186EEB0012003/23/2008BB7EFE81-B532-46DC-B8D1-4E7A4186EEB0025103/23/2008BB7EFE81-B532-46DC-B8D1-4E7A4186EEB0020003/23/2008BB7EFE81-B532-46DC-B8D1-4E7A4186EEB0014003/23/2008BB7EFE81-B532-46DC-B8D1-4E7A4186EEB0012003/23/2008BB7EFE81-B532-46DC-B8D1-4E7A4186EEB0012003/23/2008 but I really just want it to be thisBB7EFE81-B532-46DC-B8D1-4E7A4186EEB001071 03/23/2008See I want everything with the same user name and date to be grouped together. But when I do that in my group clause it separates them all. Yet if I don't have my timeDateStamp in the group clause I get a syntax error.Thanks

View Replies ! View Related

Cannot Use GROUP BY Clause The Way I Want To?
I'm trying to use a GROUP BY clause in my SQL statement when retieving information from an SQL Server DB. The only problem is that it won't let me 'SELECT' columns from the database that are not part of the GROUP BY clause. Here is my example:

This works:

SELECT ColumnA, ColumnB FROM MyTable GROUP BY ColumnA, ColumnB

This does NOT work:

SELECT ColumnA, ColumnB FROM MyTable GROUP BY ColumnA

It simply will not let me have ColumnB in the SELECT clause unless I put it in the GROUP BY clause. Is there any way around this? Because I need both columns to display in the page, but I only want to group them by one column.

I'm coming from MySQL, and in MySQL what I want to do is perfectly legal. However, in SQL Server it's not...

Any ideas?

View Replies ! View Related

SQL--- GROUP BY Clause
Hi,I was wondering if anyone out there can help me with this SQL problem:I have a database that has two tables: EMPLOYEE and JOB_TITLEThe EMPLOYEE Table consists of a salary and job_title _code columns,among many others; the JOB_TITLE table contains job_title_code column,among many others.The SQL problem is: Select the employees' last names and Group them bySalary within their job_title_code. I am new to SQL statements andkinda puzzled on how to solve this problem. I would appreciate any helpI can get on this. Thanks a lot in advance.

View Replies ! View Related

About Group Clause
can i group by the column that is not in the select list? anyway thanx!

View Replies ! View Related

Group By Clause
in select apart of my statment I have the below code, but I want to exclude it from the group by clause, is there a way I can do this??

(cast(timesheethours.hoursworked as char) + '' + cast

(timesheethours.payrate as char) + ''+ objects.FileAs) as 'workers details',

Thanks

Dave,

View Replies ! View Related

Need Help Using GROUP BY Clause
I have two tables
A. TEST_SUBJECTS_TBL with the following columns   . This table contains the subjects in a test
       1. TEST_SUBJECT_ID   PK
       2. SUBJECT_ID   FK
       3. TEST_ID      FK
       4. PM // This is the passing marks for the subject

B. TEST_MARKS_TBL with the following columns         This table stores the marks scored by students for each subject
       1. TEST_SUBJECT_ID   FK
       2. STUDENT_ID   FK
       3. MARKS_OBTAINED

I need a query which gives me the max, min,avg marks obtained in each subject for a test and the total number of students who have passed in the subject

The query output should be something like this
___________________________________________________________________
SubjectID    MaxMarks   MinMarks    AvgMarks   TotalStudentsPassed
___________________________________________________________________
     1         90         30          44             11                       6
     2         80         24          22             33                       8
     ......
     ......

I can use a groupby to find max,min and avg marks but finding total students passed is posing a problem.

View Replies ! View Related

Need Help Using GROUP BY Clause
I have two tables
A. TEST_SUBJECTS_TBL with the following columns   . This table contains the subjects in a test
       1. TEST_SUBJECT_ID   PK
       2. SUBJECT_ID   FK
       3. TEST_ID      FK
       4. PM   This is the passing marks for the subject

B. TEST_MARKS_TBL with the following columns         This table stores the marks scored by students for each subject
       1. TEST_SUBJECT_ID   FK
       2. STUDENT_ID   FK
       3. MARKS_OBTAINED

I need a query which gives me the max, min,avg marks obtained in each subject for a test and the total number of students who have passed in the subject

The query output should be something like this
___________________________________________________________________
SubjectID    MaxMarks   MinMarks    AvgMarks   TotalStudentsPassed
___________________________________________________________________
     1         90         30          44             11                       6
     2         80         24          22             33                       8
     ......
     ......

I can use a groupby to find max,min and avg marks but finding total students passed is posing a problem. How do i find the total students passed ?

View Replies ! View Related

Need Help Using GROUP BY Clause
I have two tables
A. TEST_SUBJECTS_TBL with the following columns   // This table contains the subjects in a test
       1. TEST_SUBJECT_ID   //PK
       2. SUBJECT_ID   //FK
       3. TEST_ID      //Fk
       4. PM // This is the passing marks for the subject

B. TEST_MARKS_TBL with the following columns        // This table stores the marks scored by students for each subject
       1. TEST_SUBJECT_ID   //FK
       2. STUDENT_ID   //FK
       3. MARKS_OBTAINED

I need a query which gives me the max, min,avg marks obtained in each subject for a test and the total number of students who have passed in the subject

The query output should be something like this
___________________________________________________________________
SubjectID    MaxMarks   MinMarks    AvgMarks   TotalStudentsPassed
___________________________________________________________________
     1         90         30          44             11                            7
     2         80         24          22             33                            11
     ......
     ......

I can use a groupby to find max,min and avg marks but finding total students passed is posing a problem.

View Replies ! View Related

Use Of Group By Clause

create table A
(
USERID int,
USER varchar(20),
TTYPE varchar(20),
DETAIL varchar(20),
);

insert into A(
1,'X','Credit','Amount xxx',
1,'X','Debit', 'Amount xxx',
2,'Y','Debit', 'Amount xx',
2,'Y','Debit', 'Amount xxx',
1,'X','Debit', 'Amount xxxx',
1,'X','Credit', 'Amount xxxx',

);

Create table B
(
USERID int,
TRANSACTION int
);

insert into B select USERID, COUNT(case when TTYPE='Credit' 1 else null end) as TRANSACTION from A group by USERID

[Error Code: 8118, SQL State: S1000] Column 'A.DETAIL' is invalid in the select list because it is not contained in an aggregate function and there is no GROUP BY clause.

Any suggestions......

View Replies ! View Related

GROUP BY CLAUSE
Greetings!

Why does the select statement must have the same non aggregated columns which are also part of the group by clause?

I have a feeling it's to do with the way the SQL Server database engine actually executes the query? i.e. the select part is the last thing the engine performs (after doing the joins and the filtering etc...).

Your help would be appreciated.

View Replies ! View Related

How To Use The Group Clause
Hi all,

I have this stored procedure and I want it to produce the results to GROUP BY Style1, Style2, StyleColor, Whrse_No, Bin_No ...... This procedure is used by a crystal report file to produce a report.... Below is the code:
( I think that the problem is in that some fields which are not summed have to be somehow gropued or modified
)

------------------ Seelct part simply slects several variables where sum of them are summed

select Style1 ,style2,StyleColor,Description,Whrse_No, Whrse_Desc, Bin_no, size_cd, Nbr_Sizes

, Size_Desc01, Size_Desc02, Size_Desc03, Size_Desc04, Size_Desc05
, Size_Desc06, Size_Desc07,Size_Desc08, Size_Desc09, Size_Desc10
, Size_Desc11, Size_Desc12, Size_Desc13, Size_Desc14, Size_Desc15 , OnHandQty_Total = sum ( OnHandQty_Total)
, OnHandQty_Sz1 = sum (OnHandQty_Sz1), OnHandQty_Sz2 = sum (OnHandQty_Sz2), OnHandQty_Sz3 = sum (OnHandQty_Sz3), OnHandQty_Sz4 = sum (OnHandQty_Sz4), OnHandQty_Sz5 = sum (OnHandQty_Sz5)
, OnHandQty_Sz6 = sum (OnHandQty_Sz6), OnHandQty_Sz7 = sum (OnHandQty_Sz7), OnHandQty_Sz8 = sum (OnHandQty_Sz8), OnHandQty_Sz9 = sum (OnHandQty_Sz9), OnHandQty_Sz10 = sum (OnHandQty_Sz10)
, OnHandQty_Sz11 = sum (OnHandQty_Sz11), OnHandQty_Sz12 = sum (OnHandQty_Sz12), OnHandQty_Sz13 = sum (OnHandQty_Sz13), OnHandQty_Sz14 = sum (OnHandQty_Sz14), OnHandQty_Sz15 = sum (OnHandQty_Sz15)
, Total_OnHandNeg
, CompanyName
, Date

from #rInventoryOnHandStyle_Whse

Where 1 = 1

' + @sAND10 + '

------------- Group Rule

Group By Style1, style2, StyleColor, Whrse_No, Bin_no

' + @OrderBy + '

'
--)

EXEC(@cmd)
GO

View Replies ! View Related

Group By Clause
Hi all,

Got a problem here, I am not quite familiar yet with the sql group by clause. As an illustration, I have a table with column StoreName and Sales.

StoreName Sales
DFA_Main 50
DFA_Main 50
DFA_Branch 60
DFA_Branch 60
DFA_OtherBranch 10
MMDA_Main 50
MMDA_Main 50
MMDA_Branch 30
MMDA_Branch 30

In my understanding if we are going to group this table by StoreName the result will be:

StoreName Sales
DFA_Main 100
DFA_Branch 120
DFA_OtherBranch 10
MMDA_Main 100
MMDA_Branch 60

But what would be the sql statement to produce an output like this:

StoreName Sales
DFA 230
MMDA 160

Is it possible for me to do that? Thanks for the help in advance.

===============
JSC0624
===============

View Replies ! View Related

GROUP BY/ HAVING CLAUSE Problem
I'm trying to set up my adhoc query to return just one single record, which is aliased as 'foreign' in my sql statement (which is just the total amount of foreign overseas orders for just one day. All Sale_Type_Ids over 2 [integer datatype] are foreign orders):
SELECT SUM(CASE WHEN Orders.Sale_Type_Id > 2 THEN Orders.Sale_Type_Id ELSE NULL END) AS foreignFROM Orders INNER JOIN Processing ON Orders.ID = Processing.Order_IDWHERE (Processing.Orderdate = '20050915') AND (Processing.status = 1)GROUP BY CASE WHEN Orders.Sale_Type_Id > 2 THEN Orders.Sale_Type_Id ELSE NULL ENDHAVING (SUM(CASE WHEN Orders.Sale_Type_Id > 2 THEN Orders.Sale_Type_Id ELSE NULL END) >= 0)
..but my resultset is returning two records. If I remove the HAVING clause, it will return three records, with one being blank.???.netsports

View Replies ! View Related

Duplicates And The GROUP BY Clause
I am making a website where users go to a page that lists every Program in their area. The first time the page loads they see all the Programs, then then can filter it down with drop down lists. I have everything working except for the Category because some programs have more than one category. The select is working good but I get duplicates.

Here it is:

SELECT DISTINCT
p.ProgramID,
p.ProgramName,
p.ProgramCity,
p.ProgramState,
p.ProgramCountyID,
p.ProgramHours,
p.ProgramContactName,
p.ProgramPhone,
p.ProgramEmail,
p.ProgramGrades,
p.ProgramTransportation,
pc.ProgramID,
pc.CategoryID

FROM
Programs p,
ProgramCategories pc

WHERE
p.ProgramCountyID IS NOT NULL AND
p.ProgramCity IS NOT NULL AND
p.ProgramHours IS NOT NULL AND
p.ProgramGrades IS NOT NULL AND
p.ProgramTransportation IS NOT NULL AND
p.ProgramID = pc.ProgramID AND
pc.CategoryID IS NOT NULL

GROUP BY
p.ProgramID

ORDER BY
p.ProgramName ASC

When I have just p.ProgramID in the GROUP BY clause, I get the error:

"column name" is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

But when I put all the column names in the GROUP BY clause, I still get duplicates. What can I do to stop this. When the user selects a category the pc.CategoryID IS NOT NULL changes to pc.CategoryID = 3 (or whatever they select) and everything works the way its supposed to. I just want each individual program to show only once when the page first loads.

Thanks for your time
Dave

View Replies ! View Related

Group By Clause Query Help
This is my queryselect ano,max(date),a_subject from MY_TAB where table_name='xyz' andano=877group by a_subject,ano order by a_subjectANOmax(Date)A_Subject8772005-01-20 00:00:00.000Subject_18771900-01-01 00:00:00.000Subject_28772004-12-20 00:00:00.000Subject_38772005-01-19 00:00:00.000Subject_4--------------------------------------------------------------------------When I put the status column in, it fetches all the rows.select ano,max(date),a_subject,status from MY_TAB wheretable_name='xyz' and ano=877 group by a_subject,ano,status order bya_subjectANOmax(Date)A_SubjectStatus8772005-01-20 00:00:00.000Subject_1Not Started8771900-01-01 00:00:00.000Subject_2Not Started8772004-12-20 00:00:00.000Subject_3Completed8771900-01-01 00:00:00.000Subject_3Not Started8771900-01-01 00:00:00.000Subject_4Not Started8772005-01-19 00:00:00.000Subject_4Not Started-----------------------------------------------------------------------now what i want isANOmax(Date)A_SubjectStatus8772005-01-20 00:00:00.000Subject_1Not Started8771900-01-01 00:00:00.000Subject_2Not Started8772004-12-20 00:00:00.000Subject_3Completed8772005-01-19 00:00:00.000Subject_4Not StartedThanks a lot for your help.AJ

View Replies ! View Related

Group By Clause Limitation
Code is:
select
case when ItemCode is null then '-'
else ItemCode
End,
case when sum(RecdQty) is null then '-'
else sum(RecdQty)
End
from ItemMaster where ItemCode='V001' group by ItemCode

Problem Statement:
If query is not getting any records for above mentioned condition, then I want zero to be displayed if datatype is int (i.e. for sum(RecdQty) field) and '-' to be diplayed if datatype is varchar (i.e. for ItemCode field).
In this situation, "ItemCode is null" and "sum(RecdQty) is null" conditions are not been utilised.
Is this a limitation of case or group by clause?

View Replies ! View Related

Best Practices: GROUP BY Clause
I was wondering what the best way to write a GROUP BY clause when there are many (and time consuming) operations in the fields by grouped.

Fictious example:

SELECT DeptNo, AVG(Salary) FROM Department GROUP BY DeptNo;

This will give me the average salary per department. Let's say, however that
I had 10-15 fields being returned (along with the AVG(Salary)) and some fields even had operations being performed on them. Is it better to create a temporary table to calculate the sum per department (or a VIEW) and then
perform a JOIN with the rest of the data?

Fictious example:

SELECT DATENAME(y, StartDate), DATENAME(m, StartDate), DATEPART(d, StartDate), SUBSTR(DeptName, 1, 10), SomeFunction(SomeField), SomeFunction(SomeField), AVG(Salary)
GROUP BY DATENAME(y, StartDate), DATENAME(m, StartDate), DATEPART(d, StartDate), SUBSTR(DeptName, 1, 10), SomeFunction(SomeField), SomeFunction(SomeField);

Am I better off writing my query this way or using a JOIN on some temporary table or view?

Thanks

View Replies ! View Related

Using UPDATE On A GROUP BY Clause
I'd like to update the attribute Active of the table COMPANY_BRANCH to the value 0 (or false, since it is a bin field) on the below recordset:

SELECT COMPANY_BRANCH.CompBranchID, MAX(PLACEMENT.ExpirationDate) AS Expr1

FROM COMPANY_BRANCH INNER JOIN

PLACEMENT ON COMPANY_BRANCH.CompBranchID = PLACEMENT.ProducerBranchID

GROUP BY COMPANY_BRANCH.CompBranchID

HAVING (MAX(PLACEMENT.ExpirationDate) < DATEADD(yyyy, - 1, GETDATE()))

yet, I am unable to understand the concept on how this can be done. If I put it into query designer and change the query type to update, the code that gets generated is not correct - can someone help me?

View Replies ! View Related

Group By Clause Killing Performance
I have recently started working with a new group of people and I find myself doing a lot of reporting. While doing this reporting I have been writing a TON of sql. Some of my queries were not performing up to par and another developer in the shop recommended that I stay away from the "GROUP BY" clause.
Backing away from the "GROUP BY" clause and using "INNER SELECTS" instead as been more effective and some queries have gone from over 1 minute to less that 1 second.
Obviously if it works then it works and there is no arguing that point. My question to the forum is more about gather some opinions so that I can build an opinion of my own.
If I cannot do a reasonable query of a couple of million records using a group by clause what is the problem and what is the best fix?
Is the best fix to remove the "GROUP BY" and write a query that is a little more complex or should I be looking at tuning the database with more indexes and statistics?
I want to make sure that this one point is crystal clear. I am not against following the advice of my coworker and avoiding the "GROUP BY" clause. I am only intersted in listening to a few others talk about why the agree or disagree with my coworked so that I can gain a broader understanding.

View Replies ! View Related

GROUP By Clause, /sub Query Problems
I'm trying to list salesreps (if they have any sales for a particular date) with their total sales amounts for a queried date, but when running this sql string in QueryAnalyzer, it says there is an error with syntax on Line 1 near "s" :SELECT o .Rep_ID, o .ID, s.ID, SUM(b.orderamount) AS totalsales, b.order_ID
FROM (SELECT b.Deal_ID
FROM btransactions b
WHERE b.BoardDate = '20050815') SalesReps s INNER JOIN
orders o ON o .Rep_ID = s.ID INNER JOIN
b ON o.ID = b.Deal_ID
GROUP BY d .Rep_ID, d .ID, s.ID, b.order_ID
HAVING (SUM(b.orderamount) > 0)???.NetSports

View Replies ! View Related

SQL Query Problem In Group By Clause
I am developing Staff Allocation System,
database is sql server 2000.
I have problem in retrieve the staff informations,

employee working which Project and what project have assign to him, what is his assign project or contract no,

One employee working more then one project, retrieve information one employee how many projects are working,

What is his approved position, what is his assign position.
It the main data have to retrieve, as well as retrieve all fields which related to those tables.

I use this query.

select name,apppos approved_position,appcont approved_contract,appdate employee_appr_date,munref Municipality_Ref,dcilref DCIL_REF,projtype Project_Type,strdate Project_str_date,comdate Projcet_comp_date,extdate Proejct_ext_date,dept,emptype Employee_Type from contract,emp,apprecords where contract.rec_id=emp.rec_id and emp.rec_id=apprecords.rec_id and apprecords.name='dewachi'

above query retrieve no data,

how can use group by clause in the above query ?

group by apprecords.appcontract

group by clause give error.

above query have to retrieve data from the three tables, I have four tables, what query I use so that all four tables data retrieve like this.

Name, approved_position, approved_contract,assign_position,assign_contract,startdate,completion_date,........ and so onâ€¦
Group by apprecords.appposition
â€¦â€¦â€¦.

Contract Table (basic data entry contract table)
-------------------------------------------------------
rec_id
Contract No.
ProjectType
StartDate
CompletionDate
ExtendedDate

Employee Table (basic data entry employee table)
---------------------------------------------------------
rec_id
EmpNo
Name
Position
Department
EmployeeType

Approved Records Table (in this table all information about
the employee and his approved
position and contract )
------------------------------------------------------------------------
rec_id
Name
Approved Date
MunicipalityRefNo
DCILRefNo
ApprovedPosition
ApprovedContract

Assign Project Table (in this table all information about the
employee his assign the project)
--------------------------------------------------------------------
rec_id
Name
AssignPosition
AssignContract
EmpProjectStartDate
EmpProjectEndDate
ShiftNo
ProjectStatus

Regards.

MATEEN

View Replies ! View Related

Many Fields Update From A Group By Clause
Hi All,In Oracle, I can easily make this query :UPDATE t1 SET (f1,f2)=(SELECT AVG(f3),SUM(f4)FROM t2WHERE t2.f5=t1.f6)WHERE f5='Something'I cannot seem to be able to do the same thing with MS-SQL. There areonly 2 ways I've figured out, and I fear performance cost in both cases,which are these :1)UPDATE t1 SET f1=(SELECT AVG(f3)FROM t2WHERE t2.f5=t1.f6)WHERE f5='Something'and then the same statement but with f2, and2)UPDATE t1 SET f1=(SELECT AVG(f3)FROM t2WHERE t2.f5=t1.f6),f2=(SELECT SUM(f4)FROM t2WHERE t2.f5=t1.f6)WHERE f5='Something'Is there a way with MS-SQL to do the Oracle equivalent in this case ?Thanks,Michel

View Replies ! View Related

Median Query With Group By Clause
Hi,I have a view in which I have 3 cols...(pno,ptno,diff)..diff is thedifference in time in minutes.I want to calculate Median(diff) groupby pno,ptno...using a sql query for SQL server...Any help is greatly appreciated..ThanksAJ

View Replies ! View Related

Alternative Solution To GROUP BY ALL Clause
hi,
I was using GROUP BY ALL clause in my query .But sql server CE doesn't support ALL keyword.
I want to know is there some other solution to using ALL keyword in SQL CE.
I want to retrieve those rows which don't satisfy the where prod_position=1 condition along with those who satisfy it.

my query is

select prod_desc ,count(tbl_asa_tns_call_Detail.prod_id) as pri from tbl_Asa_prod_brnd, tbl_asa_tns_call_Detail where prod_position=1 and tbl_Asa_prod_brnd.prod_id=tbl_asa_tns_call_detail. prod_id
and call_id in(select call_id from tbl_asa_tns_call where
(day(call_date) between day('2001-01-01 00:00:00') and day
('2001-03-20 00:00:00')) and (month(call_date) between month('2001-01-02 00:00:00') and month('2001-03-20 00:00:00')) and (year(call_date) between year('2001-01-02 00:00:00') and year('2001-02-08 00:00:00')))
group by all prod_desc order by prod_desc

View Replies ! View Related

Help With Simple Query Using Group By And Where Clause
have a table with sale_id, date, sales_person_id

i need to find out the sales_person_id's who did 1 sales every month
from jan 2003 and another query who did a sales every quarter.

How many sales person have atleast one sale every month (excluding prints) for either 2003, 2004, or 2005?

How many sales person had atleast 25 sales each year 2003-2005

View Replies ! View Related

Default_Schema Clause Cannot Be Used With A Windows Group
Hi All,

I am getting error when I try to create a windows group and set default schema to db_owner.

Error message

Alter failed for user 'Domainuser'.(Microsoft.Sqlserver.SMO)

Additional information
   An exception occured while executing a Transact-SQL statement or batch.
   (Microsoft.Sqlserer.ConnectionInfo)

       The DEFAULT_SCHEMA clause cannot be used wth a windows group or with principals mapped to
        Certificates or asymmetric keys.(Microsoft SQL Server, Error:15259)

Please help.

Regards
Hassan

View Replies ! View Related

Using Group By Clause In Union Query
Hi all,

I have two table having datas like

Table1
--------------------------------------------------------------------
A            C1    C2    C3 C4
--------------------------------------------------------------------
x 0    0    3    2
x 0    1    0    2
x 0    0    2    1
y             1              5              2                0

Table2
--------------------------------------------------------------------
A            C1    C2    C3 C4
--------------------------------------------------------------------
x 0    0    1    4
y             1              0              3                1
y             1              2              0                0
y             0              0              5                1

select * from(
select A,C1,C2,C3,C4 from Table1 group by A
union
select A,C1,C2,C3,C4 from Table2 group by A
)as t

Result:
--------------------------------------------------------------------
A            C1    C2    C3 C4
--------------------------------------------------------------------
x 0    1    5    5
y 1    5    2    0
x 0    0    1    4
y 2    2    8    2

But i need the result like i.e grouped by column 'A'
--------------------------------------------------------------------
A            C1    C2    C3 C4
--------------------------------------------------------------------
x 0    1    6    9
y 3    7     10     2

select * from(
select A,C1,C2,C3,C4 from Table1 group by A
union
select A,C1,C2,C3,C4 from Table2 group by A
)as t group by A

The above query gives the following error
[Error Code: 8120, SQL State: S1000] Column 't.C1' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

Please help me out.

-Anand

View Replies ! View Related

Writing View With 'Group By' Clause
Hi All,

I've the following view in my DB and I need to modify it in such a way that it should be grouped by 'categoryId' and display the count of rows that come under each categoryId. In brief, I need to use 'Group By' clause and 'Count/SUM' aggregate function.

Here is my view.
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[MessageCategoryView]') and OBJECTPROPERTY(id, N'IsView') = 1)
drop view [dbo].[MessageCategoryView]
GO

CREATE VIEW MessageCategoryView AS
SELECT
m.subjectSUBJECT,
m.senderidSENDERID,
m.createdateCREATEDATE,
m.completedateCOMPLETEDATE,
m.format MSGFORMAT,
m.isconfidentialISCONFIDENTIAL,
m.queueidQUEUEID,
mc.messageid MESSAGEID,
mc.categoryid CATEGORYID,
cv.label CATEGORYLABEL,
cv.hierarchystring HIERARCHYSTRING,
cv.hotkeys HOTKEYS

FROM
kc_message m
JOIN kc_messagecategory mc on m.messageid = mc.messageid
JOIN CategoryView cv on mc.categoryid = cv.categoryid
WHERE
mc.type not in (3,5)

Can anyone please help me out in resolving this.

Thanks in advance,
Sujatha

View Replies ! View Related

Query Based On Group By Clause
Hello!
suppose i have two tables, table1 columns(empcode (pk), empDept) and table2 columns(empcode (FK),Date,Attendance) i wanted to write a query to get output like
DEPT ABSENT
-----------------------------
Accounts 10
EDP Section 0 **
Admin 2
Stationary 0**

if no employee is absent in the department it has to display Zero

View Replies ! View Related

Age Old Question About GROUP BY Clause (i Think) - Probably Easy Answer
How does one get the primary key of the row that is joined in via agroup by aggregate clause when the aggregate is not performed on theprimary key?For example,Person table(PersonID int,FirstName varchar(50)LastName varchar(50))Visit table(VisitID int,PersonID int,VisitDate datetime)These are simplified versions of my tables. I'm trying to create aview that gets the first time each person Visited:selectp.PersonID,min(v.VisitDate)fromVisit vjoinPerson p on p.PersonID = v.PersonIDgroup byp.PersonIDThe problem is that I would like to return the VisitID in theresultset, but when I do it expands the query since I have to also putit in the group by clause.What are the different ways to achieve this?Subqueries?Only return the date and then join off of date on the outside?Neither of these seem too entising...Thanks in advance for any help.-Dave

View Replies ! View Related

Does The Group By Have To Include All Fields From The SELECT Clause?
hey all,

say i have the following function

SELECT GLF_CHART_ACCT.DESCR1, F1ADR_ADDRESS.ADDR1, F1ADR_ADDRESS.ADDR2,
F1ADR_ADDRESS.ADDR3, F1ADR_ADDRESS.ADDR_CITY, F1ADR_ADDRESS.ADDR_STATE,
F1ADR_ADDRESS.POST_CODE, F1ADR_ADDRESS.PHONE_NBR, F1ADR_ADDRESS.FAX_NBR,
F1ADR_ADDRESS.EMAIL_ADDR_NAME, F1ADR_ADDRESS.CONTACT_NAME,
F1ADR_ADDRESS.CONTACT_TITLE, GLF_CHART_ACCT.ACCNBRI, F1ADR_ADDRESS.ENTITY_UNIQUE_NBR

FROM GLF_CHART_ACCT

INNER JOIN F1ADR_ADDRESS ON (GLF_CHART_ACCT.CHART_NAME = F1ADR_ADDRESS.ENTITY_KEY1)
AND (GLF_CHART_ACCT.ACCNBRI = F1ADR_ADDRESS.ENTITY_KEY2)

GROUP BY GLF_CHART_ACCT.DESCR1, F1ADR_ADDRESS.ADDR1, F1ADR_ADDRESS.ADDR2,
F1ADR_ADDRESS.ADDR3, F1ADR_ADDRESS.ADDR_CITY, F1ADR_ADDRESS.ADDR_STATE,
F1ADR_ADDRESS.POST_CODE, F1ADR_ADDRESS.PHONE_NBR, F1ADR_ADDRESS.FAX_NBR,
F1ADR_ADDRESS.EMAIL_ADDR_NAME, F1ADR_ADDRESS.CONTACT_NAME, GLF_CHART_ACCT.ACCNBRI,
F1ADR_ADDRESS.CONTACT_TITLE, GLF_CHART_ACCT.CHART_NAME, F1ADR_ADDRESS.ENTITY_UNIQUE_NBR,
GLF_CHART_ACCT.SELN_TYPE1_CODE

HAVING CHART_NAME='ARCHART' AND GLF_CHART_ACCT.DESCR1 <> '' AND GLF_CHART_ACCT.SELN_TYPE1_CODE = 'Trade'
AND GLF_CHART_ACCT.DESCR1 LIKE '%" + Search + "%' ORDER BY GLF_CHART_ACCT.DESCR1;

I get errors if not all the fields are included in the group by clause.

what i dont get is why i have to create seperate groups for this query...or am i reading it wrong??

Cheers,

Justin

View Replies ! View Related

Sub Queries, Aggregate Functions && Group By Clause
Hi Guys,

I am having trouble with a particular query that is beyond my scope of understanding.

Basically I need to pull sales records based on the following criteria:

I have CustomerID, InvoiceNumber, ContractEndDate, MobileNumber, etc..

Customers recontract their mobile phone plans through us, and we have a new sales record for each time they recontract.

For example, CustomerNumber 123 has recontracted 3 times..

once on 2006-01-01, then on 2007-02-12, and finally on 2008-02-15..

So they have a 12 month contract each time.. then come in to recontract it.

So.. a customer has a single Customer Detail record, but may have many sales records attached. And a customer may have several sales for the SAME mobile phone number.

Currently to pull ALL sales records for all customers, my query is this:

Code:

SELECT xxx.CustomerID AS xxx_CustomerID,
xxx.Invoice AS xxx_Invoice,
yyy.PhoneType AS yyy_PhoneType,
yyy.PlanType AS yyy_PlanType,
yyy.ContractEnds AS yyy_ContractEnds,
yyy.MOB AS yyy_MobileNumber

FROM dbo.SaleControl xxx INNER JOIN dbo.SaleDetails yyy ON xxx.Invoice = yyy.Invoice

WHERE yyy.ContractEnds IS NOT NULL
AND xxx.CustomerID IS NOT NULL

We want to get a list of customers that we can call to recontract, based on the ContractEnd field.

However, we want UNIQUE mobile phone numbers, with the LATEST ContrtactEnd date.

So, Customer 123 has 6 sales, for 2 unique Mobile numbers, the sql may be like:

Code:

SELECT MAX(yyy.ContractEnds) AS LatestCED, yyy.MOB
FROM dbo.SaleControl xxx INNER JOIN dbo.SaleDetails yyy ON xxx.Invoice = yyy.Invoice
WHERE xxx.CustomerID='123'
GROUP BY yyy.MOB

Now, this works fine, and of course if i remove the WHERE clause, it collects all unique mobiles, with latest ContractEnd date for each, for all customers. (Customer 123 displays 2 mobile numbers, each with the LATEST ContractEnd date)

BUT i need this information ALONG WITH the other fields (xxx.CustomerID, xxx.Invoice, yyy.PhoneType, yyy.PlanType) and i have tried a few ways of doing it, but can't get my head around it..

Keep getting errors about Aggregate functions and Group By clause, and i understand why i am getting them, just cant think of any alternative query.

Can anyone please help me!

Thanks guys,

Mick

View Replies ! View Related

Using A Field Alias For A CASE Statement In A GROUP BY Clause
When I created a CASE statement (This is at work, Pat:)) it is about 30-40 lines long. I gave it a name and set the name = to the case statement:

ie,

SELECT fieldname1 =
CASE
WHEN condition THEN 'blah blah'
WHEN condition THEN 'blah blah'
WHEN condition THEN 'blah blah'
ELSE thisandthat
END
, fieldname2
, fieldname3
FROM tablename1
GROUP BY CASE
WHEN condition THEN 'blah blah'
WHEN condition THEN 'blah blah'
WHEN condition THEN 'blah blah'
ELSE thisandthat
END, , fieldname2, fieldname3

etc.

The long CASE statement in my GROUP BY is awkward to me. Is this the only way to do it? I tried using the fieldname1 but it comes back as an invalid field name and asks for the "expression".

Regards,

Dave

View Replies ! View Related

SQL Statement: Group By And Where Clause Do Not Display Null Rows

The following is a simplified version of my SQL statement. I am attempting to do a simple count(*) with two groupings and a where clause. This is Select command for a GridView. However, I am unable to display zeros. The rows are completely missing and I would greatly appreciate someone's help. I have already tried Group By All, but that, unfortunately, does not work. Here is my SQL statement:

"SELECT [GENDER], [RACE], COUNT(*) FROM [TABLE] WHERE ([COLUMNNAME] ='SOMETHING') GROUP BY [GENDER], [RACE]"

Thanks for the help in advance!

View Replies ! View Related

Error 15401: Windows NT Group Or Group Not Found
I have a user in SQL Server with a NT login of Mike
I changed his NT account to Mikel in User Manager

Now when I try to add Mikel, Im getting error 15401.

Do I need to delete NT login in SQL Server 'Mike' account first ?..before adding 'Mikel' ?

Can I go into the Master database and just change Mike login to Mikel ?

Thank you

View Replies ! View Related

ERROR [42000] [Lotus][ODBC Lotus Notes]Table Reference Has To Be A Table Name Or An Outer Join Escape Clause In A FROM Clause
I am using web developer 2008, while connecting to I wanted to fetch data from Lotus notes database file, for this i used notesql connector, while connectiong to notes database i am fetting error

ERROR [42000] [Lotus][ODBC Lotus Notes]Table reference has to be a table name or an outer join escape clause in a FROM clause

I have already checked that database & table name are correct, please help me out
How i can fetch the lotus notes data in my asp.net pages.

View Replies ! View Related

ERROR ON CLAUSE
Hi,

When I run this:

SELECT DISTINCT
                      property.parcel_number, property.id, property.eff_from_date, property.eff_to_date, property.situs_address_id, legal_lines.legal_desc_line,
                      tax_code_area.tca_number, usecd.value, ct.code_description, property_char.value AS Expr1, property.property_status_cd, legal_description.section,
                      legal_description.township, legal_description.range, legal_description.eff_from_date AS Expr2, legal_description.eff_to_date AS Expr3,
                      property.pact_code
FROM         property LEFT OUTER JOIN
                      legal_description INNER JOIN
                      legal_lines ON legal_description.id = legal_lines.legal_id ON property.id = legal_description.property_id AND legal_lines.line_nr = 1

                     AND
                      (legal_description.eff_to_date IS NULL OR
                      legal_description.eff_to_date >= today) AND legal_description.eff_from_date <= today LEFT OUTER JOIN
                      property_char ON property.id = property_char.property_id AND property_char.prop_char_typ_code = 'SIZE' AND
                      property_char.tax_year = '2008' LEFT OUTER JOIN
                      property_char AS usecd INNER JOIN
                      code_table AS ct ON usecd.value = ct.code_table_cd ON property.id = usecd.property_id AND usecd.prop_char_typ_code = 'USECD' AND
                      ct.code_category_id = '35' AND usecd.tax_year = '2008' CROSS JOIN
                      tax_code_area INNER JOIN
                      prop_valuation ON property.id = prop_valuation.property_id AND tax_code_area.id = prop_valuation.tca_id AND
                      prop_valuation.tax_year = '2008'

WHERE     (property.eff_to_date IS NULL OR
                      property.eff_to_date >= today) AND (property.eff_from_date <= today) AND (property.pact_code <> 'PERS')

I keep on geting an Error sayin: An ON clasue has an invalid table reference. I have looked it over so mnay times. Any help will be very helpful.

Thanks

View Replies ! View Related

Syntax Error On Where Clause
I have the code below and the where clause is giving me errors, I have already setup FT Index, I think the error is lack of ' or " or "" or some kinda syntax pls help... as I can figure it out

I passed the word MBAISE

where CONTAINS(*, '+@searchstring+' )

Declare @myStatement varchar(8000)
Declare @LastRec int
Declare @FirstRec int
SELECT @FirstRec = (@CurrentPage - 1) * @PageSize
SELECT @LastRec =(@CurrentPage * @PageSize + 1 )

SELECT @myStatement = ' SELECT SalaryStatistical, EmployerID,JobLocation, location, SectorID
FROM #TEMP

where CONTAINS(*, '+@searchstring+' )

AND (LastLogin >= dateadd(d, -' + @HowManyDays + ', getdate())
AND (ID > '+ convert(varchar(20),@FirstRec) +')
AND (ID < '+convert(varchar(20),@LastRec) +')
AND (SalaryStatistical > '+ convert(varchar(20),@SalaryStatistical)+')

'
IF @JobLocation != @myzero

BEGIN
SELECT @myStatement = @myStatement + ' AND (JobLocation IN ('+convert(varchar(90),@JobLocation)+')) '
END

IF @sectorID != @myzero
BEGIN
SELECT @myStatement = @myStatement + ' AND (SectorID IN ('+convert(varchar(90),@sectorID)+')) '
END

SELECT @myStatement = @myStatement
print @myStatement

exec(@myStatement)

GO

View Replies ! View Related

Error In Use Of Order By In Over Clause

Hi,
I am getting a wierd error while using order by in the over clause. Consider the following query:

select count (*) over (order by STD_CLL_CNTR_KEY) as cnt

from FCT_CLL_CS_DTLS

The error reported is :

Msg 156, Level 15, State 1, Line 2

Incorrect syntax near the keyword 'order'.

But at the same time this particular query seems to be working fine:

select rank () over (order by STD_CLL_CNTR_KEY) as cnt

from FCT_CLL_CS_DTLS

Am I missing something fundamental or is there a bigger issue.

Thanks in advance,

Regards,
Emil

View Replies ! View Related

Syntax Error In FROM Clause - Help Needed Please!!
Hi all,I am a newbie to asp and have been using VWD to make a database for an assignment but I am having big problems trying to extract some data to a datalist view. I intend to use this page to display all information of hotel rooms. I know its probably really obvious to some of you but its driving me mad!!! Any help would be greatly appreciated. Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Dim ds As New DataSet() Dim sGetRooms As String = "SELECT RoomID, RoomType, " _ & "RoomName FROM Rooms2 " _ & "WHERE RoomType LIKE @RoomType " _ & "ORDER BY RoomType" Dim sGetroomsizeandprice As String = "SELECT ID, RoomSize, RoomPrice, @RoomType " _ & "FROM roomprices JOIN Rooms2 ON Rooms2.ID = roomprices.ID " _ & "WHERE RoomType LIKE @RoomType " _ & "ORDER BY RoomPrice" Dim sConnect As String = ConfigurationManager.ConnectionStrings("White Sand's Hotel - Dan MahilConnectionString").ConnectionString Using con As New OleDbConnection(sConnect) Dim da As New OleDbDataAdapter(sGetRooms, con) Dim param As New OleDbParameter("RoomType", OleDbType.VarChar, 10) param.Value = Request.QueryString("RoomType") & "%" da.SelectCommand.Parameters.Add(param) Try da.Fill(ds, "Rooms2") da.SelectCommand.CommandText = sGetroomsizeandprice da.Fill(ds, "roomprices") Catch ex As Exception Label4.Text = "ERROR: " & ex.Message Exit Sub End Try End Using Dim pkcol As DataColumn = ds.Tables("Room2").Columns("RoomID") Dim fkcol As DataColumn = ds.Tables("roomprices").Columns("ID") Dim dr As New DataRelation("MenuLink", pkcol, fkcol) ds.Relations.Add(dr) DataList1.DataSource = ds DataList1.DataMember = "Rooms2" DataList1.DataBind() End Sub

View Replies ! View Related

Sql Error-order By Clause Not Working
Hi,

I have the following code and output

select distinct week ,sum(itemvalue) as itemvalue from (Select ATP,

WEEK=

CASE

WHEN (datepart(Dd, ATP) < 7 AND datename(Month,ATP)='JANUARY')

OR

( datepart(Dd, ATP) < 8 AND datename(Month,ATP)='JULY')

OR

( datepart(Dd, ATP) < 7 AND datename(Month,ATP)='OCTOBER')

THEN '1'

WHEN (datepart(Dd, ATP) < 14 AND datename(Month,ATP)='JANUARY')

OR

( datepart(Dd, ATP) < 15 AND datename(Month,ATP)='JULY')

OR

( datepart(Dd, ATP) < 14 AND datename(Month,ATP)='OCTOBER')

THEN '2'

WHEN (datepart(Dd, ATP) < 21 AND datename(Month,ATP)='JANUARY')

OR

( datepart(Dd, ATP) < 22 AND datename(Month,ATP)='JULY')

OR

( datepart(Dd, ATP) < 21 AND datename(Month,ATP)='OCTOBER')

THEN '3'

WHEN (datepart(Dd, ATP) < 28 AND datename(Month,ATP)='JANUARY')

OR

( datepart(Dd, ATP) < 29 AND datename(Month,ATP)='JULY')

OR

( datepart(Dd, ATP) < 28 AND datename(Month,ATP)='OCTOBER')

THEN '4'

WHEN ((datepart(Dd, ATP) IN (29,30,31)) AND datename(Month,ATP)='JANUARY')

OR

( (datepart(Dd, ATP) IN (29,30,31)) AND datename(Month,ATP)='JULY')

OR

( (datepart(Dd, ATP) IN (28,29,30,31)) AND datename(Month,ATP)='OCTOBER')

THEN '5'

WHEN (datepart(Dd, ATP) < 4 AND datename(Month,ATP)='FEBRUARY')

( datepart(Dd, ATP) < 5 AND datename(Month,ATP)='AUGUST')

OR

( datepart(Dd, ATP) < 4 AND datename(Month,ATP)='NOVEMBER')

THEN '5'

WHEN (datepart(Dd, ATP) <11 AND datename(Month,ATP)='FEBRUARY')

OR

( datepart(Dd, ATP) < 13 AND datename(Month,ATP)='MAY')

OR

( datepart(Dd, ATP) < 12 AND datename(Month,ATP)='AUGUST')

OR

( datepart(Dd, ATP) < 11 AND datename(Month,ATP)='NOVEMBER')

THEN '6'

WHEN (datepart(Dd, ATP) < 18 AND datename(Month,ATP)='FEBRUARY')

OR

( datepart(Dd, ATP) < 19 AND datename(Month,ATP)='AUGUST')

OR

( datepart(Dd, ATP) < 18 AND datename(Month,ATP)='NOVEMBER')

THEN '7'

WHEN (datepart(Dd, ATP) < 25 AND datename(Month,ATP)='FEBRUARY')

OR

( datepart(Dd, ATP) < 27 AND datename(Month,ATP)='MAY')

OR

( datepart(Dd, ATP) < 31 AND datename(Month,ATP)='AUGUST')

OR

( datepart(Dd, ATP) < 25 AND datename(Month,ATP)='NOVEMBER')

THEN '8'

WHEN ((datepart(Dd, ATP) IN (25,26,27,28)) AND datename(Month,ATP)='FEBRUARY')

OR

( (datepart(Dd, ATP) IN (27,28,29,30,31)) AND datename(Month,ATP)='MAY')

OR

( (datepart(Dd, ATP) IN (25,26,27,28,29,30)) AND datename(Month,ATP)='NOVEMBER')

OR

( (datepart(Dd, ATP) <2) AND datename(Month,ATP)='DECEMBER')

THEN '9'

WHEN (datepart(Dd, ATP) < 11 AND datename(Month,ATP)='MARCH')

OR

( datepart(Dd, ATP) < 9 AND datename(Month,ATP)='SEPTEMBER')

OR

( datepart(Dd, ATP) < 9 AND datename(Month,ATP)='DECEMBER')

THEN '10'

WHEN (datepart(Dd, ATP) < 18 AND datename(Month,ATP)='MARCH')

OR

( datepart(Dd, ATP) < 16 AND datename(Month,ATP)='SEPTEMBER')

OR

( datepart(Dd, ATP) < 16 AND datename(Month,ATP)='DECEMBER')

THEN '11'

WHEN (datepart(Dd, ATP) < 25 AND datename(Month,ATP)='MARCH')

OR

( datepart(Dd, ATP) < 23 AND datename(Month,ATP)='SEPTEMBER')

OR

( datepart(Dd, ATP) < 23 AND datename(Month,ATP)='DECEMBER')

THEN '12'

WHEN (datepart(Dd, ATP) > 24 AND datename(Month,ATP)='MARCH')

OR

( datepart(Dd, ATP) > 22 AND datename(Month,ATP)='SEPTEMBER')

OR

( datepart(Dd, ATP) < 30 AND datename(Month,ATP)='DECEMBER')

THEN '13'

ELSE 'BEYOND'

END , SUM(ITEMVALUE) as ITEMVALUE

FROM tOPENLINE_MODIFIED

LEFT OUTER JOIN

tZCHANNEL ON tOPENLINE_MODIFIED.ZCHANNEL = tZCHANNEL.ZCHANNEL

WHERE RequestQtr in ('Q4')

and tOPENLINE_MODIFIED.ATP >= '7/01/07'

and tOPENLINE_MODIFIED.ORDERTYPE in ('OR','ZBOS','ZECM','ZOR','ZOB','ZEXP')

and dbo.tZCHANNEL.ZCHANNEL in ('D','I','01', '02', '06', '07', '10')

and tOPENLINE_MODIFIED.ACCTASSIGNGRP in ('01','02')

AND tOPENLINE_MODIFIED.SOLD2NAME NOT LIKE ('%celestica%')

AND tOPENLINE_MODIFIED.SOLD2NAME NOT LIKE ('%giant%')

and tOPENLINE_MODIFIED.PLANT IN ('COF1', 'I405', 'I375', 'IOM4', 'IOM5', 'I316')

GROUP BY ATP)as A

GROUP BY week

output:

week     itemvalue

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

1         1214003.60

10      9257193.45

11       12095432.11

12       11429629.08

13       7315751.08

2         1052337.53

3         951038.10

4        274769.21

5       465278.37

6       78003.67

7        607681.02

8        9042948.17

9        2255545.25

but i need the output as

week
1
2
3
4
5
6
7
8
9
10
11
12
13

iam not able to achieve this after trying so many times.Please help me on this.

Thanks,
SVGP

View Replies ! View Related

Syntax Error When Building Up A Where Clause In Stored Procedure
Can anyone tell me why the line highlighted in blue produces the following error when I try to run this stored proc? I know the parameters are set properly as I can see them when debugging the SP.
I'm using this type of approach as my application is using the objectdatasource with paging. I have a similar SP that doesn't have the CategoryId and PersonTypeId parameters and that works fine so it is the addition of these new params that has messed up the building of the WHERE clause
The Error is: "Syntax error converting the varchar value ' WHERE CategoryId = ' to a column of data type int."
Thanks
Neil
CREATE PROCEDURE dbo.GetPersonsByCategoryAndTypeByName (@CategoryId int, @PersonTypeId int, @FirstName varchar(50)=NULL, @FamilyName varchar(50)=NULL, @StartRow int, @PageSize int)
AS
Declare @WhereClause varchar(2000)Declare @OrderByClause varchar(255)Declare @SelectClause varchar(2000)
CREATE TABLE #tblPersons ( ID int IDENTITY PRIMARY KEY , PersonId int , TitleId int NULL , FirstName varchar (50) NULL , FamilyName varchar (50) NOT NULL , FullName varchar (120) NOT NULL , AltFamilyName varchar (50) NULL , Sex varchar (6) NULL , DateOfBirth datetime NULL , Age int NULL , DateOfDeath datetime NULL , CauseOfDeathId int NULL , Height int NULL , Weight int NULL , ABO varchar (3) NULL , RhD varchar (8) NULL , Comments varchar (2000) NULL , LocalIdNo varchar (20) NULL , NHSNo varchar (10) NULL , CHINo varchar (10) NULL , HospitalId int NULL , HospitalNo varchar (20) NULL , AltHospitalId int NULL , AltHospitalNo varchar (20) NULL , EthnicGroupId int NULL , CitizenshipId int NULL , NHSEntitlement bit NULL , HomePhoneNo varchar (12) NULL , WorkPhoneNo varchar (12) NULL , MobilePhoneNo varchar (12) NULL , CreatedBy varchar(40) NULL , DateCreated smalldatetime NULL , UpdatedBy varchar(40) NULL , DateLastUpdated smalldatetime NULL, UpdateId int )
SELECT @OrderByClause = ' ORDER BY FamilyName, FirstName'
SELECT @WhereClause = ' WHERE CategoryId = ' + @CategoryId + ' AND PersonTypeId = ' + @PersonTypeIdIf NOT @Firstname IS NULLBEGIN SELECT @WhereClause = @WhereClause + ' AND FirstName LIKE ISNULL(''%'+ @FirstName + '%'','''')'ENDIf NOT @FamilyName IS NULLBEGIN SELECT @WhereClause = @WhereClause + ' AND (FamilyName LIKE ISNULL(''%'+ @FamilyName + '%'','''') OR AltFamilyName LIKE ISNULL(''%'+ @FamilyName + '%'',''''))'END
Select @SelectClause = 'INSERT INTO #tblPersons( PersonId, TitleId, FirstName, FamilyName , FullName, AltFamilyName, Sex, DateOfBirth, Age, DateOfDeath, CauseOfDeathId, Height, Weight, ABO, RhD, Comments, LocalIdNo, NHSNo, CHINo, HospitalId, HospitalNo, AltHospitalId, AltHospitalNo, EthnicGroupId, CitizenshipId, NHSEntitlement, HomePhoneNo, WorkPhoneNo, MobilePhoneNo, CreatedBy, DateCreated, UpdatedBy, DateLastUpdated, UpdateId)
SELECT PersonId, TitleId, FirstName, FamilyName , FullName, AltFamilyName, Sex, DateOfBirth, Age, DateOfDeath, CauseOfDeathId, Height, Weight, ABO, RhD, Comments, LocalIdNo, NHSNo, CHINo, HospitalId, HospitalNo, AltHospitalId, AltHospitalNo, EthnicGroupId, CitizenshipId, NHSEntitlement, HomePhoneNo, WorkPhoneNo, MobilePhoneNo, CreatedBy, DateCreated, UpdatedBy, DateLastUpdated, UpdateId
FROM vw_GetPersonsByCategoryAndType '
EXEC (@SelectClause + @WhereClause +@OrderByClause)

View Replies ! View Related

Dynamic Built Where Clause - Error In Stored Procedure
I'm sending a stored procedure a number of parameters, including an int for a foreign key field. I'm trying to dynamically build the Where Clause of the query.

I'm getting the error:

Syntax error converting the varchar value 'wo_dutfk = ' to a column of data type int.

NOTE: wo_dutfk is an int field within the WO table.

Here's the code:

CREATE PROCEDURE dbo.ewo_sp_WOLookup
(
@WODUTFK int=NULL,
@WOENDDATE datetime = NULL,
@WOSTATUS char(10) = NULL,
@DEBUG int=0
)

AS

Declare @intErrorCode int,
@chvQuery varchar (8000),
@chvWhere varchar (8000)

select @intErrorCode = @@ERROR,
@chvQuery='SET QUOTED_IDENTIFIER OFF select * from WO',
@chvWhere=''

-- DUT is specified
If @intErrorCode = 0 and @WODUTFK is not NULL
Begin
set @chvWhere = @chvWhere + 'wo_dutfk = '+@WODUTFK
End

IF @intErrorCode=0 and Len(@chvWhere)>0
Begin
set @chvQuery = @chvQuery + ' WHERE ' + @chvWhere
select @intErrorCode=@@ERROR
END

IF @DEBUG<>0
select @chvQuery Query

IF @intErrorCode=0
Begin
exec (@chvQuery)
select @intErrorCode=@@ERROR
End

return @intErrorCode

GO
---

Any suggestions on how to fix?

Thanks,

peter

View Replies ! View Related