MS SQL Server :: Urgent !!! Query Help?

Query Works In 'test Query' But Refuses To Show Up In The Datagrid On A Web Page - Urgent!

Mar 28, 2007

Hey, i've written a query to search a database dependant on variables chosen by user etc etc. Opened up a new sqldatasource, entered the query shown below and went on to the test query page. Entered some test variables, everything works as it should do. Try to get it to show in a datagrid on a webpage - nothing. No data shows.
SELECT dbo.DERIVATIVES.DERIVATIVE_ID, count(*) AS Matches
FROM dbo.MAKES INNER JOIN
dbo.MODELS ON dbo.MAKES.MAKE_ID = dbo.MODELS.MAKE_ID INNER JOIN
dbo.DERIVATIVES ON dbo.MODELS.MODEL_ID = dbo.DERIVATIVES.MODEL_ID INNER JOIN
dbo.[VALUES] ON dbo.DERIVATIVES.DERIVATIVE_ID = dbo.[VALUES].DERIVATIVE_ID INNER JOIN
dbo.ATTRIBUTES ON dbo.[VALUES].ATTRIBUTE_ID = dbo.ATTRIBUTES.ATTRIBUTE_ID
WHERE ((ATTRIBUTES.ATTRIBUTE_ID = @ATT_ID1 and (@VAL1 is null or VALUE = @VAL1)) or
(ATTRIBUTES.ATTRIBUTE_ID = @ATT_ID2 and (@VAL2 is null or VALUE = @VAL2)) or
(ATTRIBUTES.ATTRIBUTE_ID = @ATT_ID3 and (@VAL3 is null or VALUE = @VAL3)) or
(ATTRIBUTES.ATTRIBUTE_ID = @ATT_ID4 and (@VAL4 is null or VALUE = @VAL4)) )
GROUP BY dbo.DERIVATIVES.DERIVATIVE_ID
HAVING count(*) >= CASE WHEN @VAL1 IS NOT NULL THEN 1 ELSE 0 END +
CASE WHEN @VAL2 IS NOT NULL THEN 1 ELSE 0 END +
CASE WHEN @VAL3 IS NOT NULL THEN 1 ELSE 0 END +
CASE WHEN @VAL4 IS NOT NULL THEN 1 ELSE 0 END -2
ORDER BY count(*) DESC

Here is the page source

<%@ Page Language="VB" MasterPageFile="~/MasterPage.master" Title="Untitled Page" %>
<asp:Content ID="Content1" ContentPlaceHolderID="ContentPlaceHolder1" Runat="Server">
<asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:DevConnectionString1 %>"
SelectCommand="	SELECT dbo.DERIVATIVES.DERIVATIVE_ID, count(*) AS Matches
	FROM dbo.MAKES INNER JOIN
				 dbo.MODELS ON dbo.MAKES.MAKE_ID = dbo.MODELS.MAKE_ID INNER JOIN
				 dbo.DERIVATIVES ON dbo.MODELS.MODEL_ID = dbo.DERIVATIVES.MODEL_ID INNER JOIN
				 dbo.[VALUES] ON dbo.DERIVATIVES.DERIVATIVE_ID = dbo.[VALUES].DERIVATIVE_ID INNER JOIN
				 dbo.ATTRIBUTES ON dbo.[VALUES].ATTRIBUTE_ID = dbo.ATTRIBUTES.ATTRIBUTE_ID
	WHERE ((ATTRIBUTES.ATTRIBUTE_ID = @ATT_ID1 and (@VAL1 is null or VALUE = @VAL1)) or
		 (ATTRIBUTES.ATTRIBUTE_ID = @ATT_ID2 and (@VAL2 is null or VALUE = @VAL2)) or
		 (ATTRIBUTES.ATTRIBUTE_ID = @ATT_ID3 and (@VAL3 is null or VALUE = @VAL3)) or
		 (ATTRIBUTES.ATTRIBUTE_ID = @ATT_ID4 and (@VAL4 is null or VALUE = @VAL4)) )
	GROUP BY dbo.DERIVATIVES.DERIVATIVE_ID
	HAVING count(*) >= CASE WHEN @VAL1 IS NOT NULL THEN 1 ELSE 0 END +
									 CASE WHEN @VAL2 IS NOT NULL THEN 1 ELSE 0 END +
									 CASE WHEN @VAL3 IS NOT NULL THEN 1 ELSE 0 END +
									 CASE WHEN @VAL4 IS NOT NULL THEN 1 ELSE 0 END -2
	ORDER BY count(*) DESC
">
<SelectParameters>
<asp:ControlParameter ControlID="DropDownList1" Name="ATT_ID1" PropertyName="SelectedValue" />
<asp:ControlParameter ControlID="TextBox1" Name="VAL1" PropertyName="Text" />
<asp:Parameter Name="ATT_ID2" />
<asp:Parameter Name="VAL2" />
<asp:Parameter Name="ATT_ID3" />
<asp:Parameter Name="VAL3" />
<asp:Parameter Name="ATT_ID4" />
<asp:Parameter Name="VAL4" />
</SelectParameters>
</asp:SqlDataSource>
<asp:SqlDataSource ID="SqlDataSource2" runat="server" ConnectionString="<%$ ConnectionStrings:DevConnectionString1 %>"
SelectCommand="SELECT * FROM [ATTRIBUTES]"></asp:SqlDataSource>
<br />
<asp:DropDownList ID="DropDownList1" runat="server" DataSourceID="SqlDataSource2"
DataTextField="ATTRIBUTE_NAME" DataValueField="ATTRIBUTE_ID">
</asp:DropDownList>
<asp:TextBox ID="TextBox1" runat="server" AutoPostBack="True"></asp:TextBox><br />
<br />
<br />
<asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="False" DataKeyNames="DERIVATIVE_ID"
DataSourceID="SqlDataSource1">
<Columns>
<asp:BoundField DataField="DERIVATIVE_ID" HeaderText="DERIVATIVE_ID" InsertVisible="False"
ReadOnly="True" SortExpression="DERIVATIVE_ID" />
<asp:BoundField DataField="Matches" HeaderText="Matches" ReadOnly="True" SortExpression="Matches" />
</Columns>
</asp:GridView>
</asp:Content>
AFAIK I have configured the source to pick up the dropdownlist value and the textbox value (the text box is autopostback).
Am i not submitting the data correctly? (It worked with a simple query...just not with this one). I have tried a stored procedure which works when testing just not when its live on a webpage.
Please help!

(Visual Web Devleoper 2005 Express and SQL Server Management Studio Express)

View 4 Replies View Related

Urgent !!! Query Help ?

Nov 15, 2001

Hi ALL,

I am looking for a query to solve this.
I had two table's.

1) Employee Table

Employee_ID Designation
---------- -------------
Savin Database Administrator
Ray Software Engineer
Adam Software Engineer
Scott Software Engineer

2). EmployeeCred Table

Employee_ID SkillSet
----------- ---------
Savin Oracle8i
Savin SQL Server 7.0.
Savin SQL2000
Ray VB 6.0
Ray Java2.0
Ray C++
Adam Share point
Adam VB 6.0
Adam Java2.0
Scott ASP
Scott VB 6.0

Output I requried.
-----------------

Employee_ID Designation SkillSet
------------ ------------ --------
Savin Database Administrator Oracle8i, Sql Server 7.0, SQL2000
Ray Software Engineer VB 6.0, Java2.0, C++
Adam Software Engineer Share point, VB 6.0, Java2.0
Scott Software Engineer ASP, VB 6.0

I need a query to solve this problem. Kindly please help me. Its urgent!!!

Thanks in advance.
venkat.

View 4 Replies View Related

Urgent: Need Help In SQL Query

Aug 14, 2002

Here is the scenario:

There are 2 tables:
1. ItemMaster Table: This table contains all the available Items (around 500,000). Fields are:
(a) ItemId Integer (PK)
(b) ItemDetails Varchar(100)

2. ItemImage Table: This item contains the location of picture of few items out of above table (around 10,000). Fields are:
(a) ItemId Integer (PK)
(b) ImageURL Varchar(50)

Requirement: I want to create a query or stored procedure, which would return me: ItemId, ItemDetails and a flag to indicate if image is available for that item or not.

Can anybody help me out in getting this flag which has to be build dynamically in the query as its not there in the tables. And what would be the query for acheiving the desired result.

Thanks in advance
Dinesh

View 4 Replies View Related

Sql Query Help - Very Urgent

Apr 5, 2001

Hi,
I am a new SQL programmer. I need your help for writing a query. I am able to get the desired result set by looping through but it takes lots of time and resourses to run and crashes my machine. Is there any way to write a sql statement which performs batchwise? I really appreciate your help.

Following is a data from a view called ind_results.

last_name first_name course_code course_title score
-------------------------------------------------------------------------
Walker Jay 1 Mid-Term Exam 71
Walker Jay 2 Final Part I Online Exam 64
Walker Jay 3 Final Exam Part II Online Exam NULL

Reed Steve 1 Mid-Term Exam 70
Reed Steve 2 Final Part I Online Exam 78
Reed Steve 3 Final Exam Part II Online Exam 80

I need help in writing a query, where

1) If Score is null for either course_code 2 or 3, display score = 'incomplete' for course_code 2 and 3 (for Jay Walker)
2) If Score is not null for either course_code 2 or 3, display score = average value of score for course_code 2 and 3 (For steve Reed)

i.e. the resultset should look like this.

last_name first_name course_code course_title score
-------------------------------------------------------------------------
Walker Jay 1 Mid-Term Exam '71'
Walker Jay 2 Final Part I Online Exam 'incomplete'
Walker Jay 3 Final Part II Online Exam 'incomplete'

Reed Steve 1 Mid-Term Exam '70'
Reed Steve 2 Final Part I Online Exam '79'
Reed Steve 3 Final Exam Part II Online Exam '79'

I have at least 67000 rows in a view.

How can I display the desired result?

Thanks for reading it through

View 3 Replies View Related

Sql Query Help Urgent !!

Jun 13, 2001

Hi ,

I am a new programmer. I need help on writing a query to accomplish the following.

Data is like this:

part_no status replaced_by_part_no

A111 superceded B111
B111 superceded C111
C111 superceded D111
D111 current null

I am trying to write a query where I get the following result. Basically
I have to display latest part_number in the chain with status as 'current'
for any part number that is superceded by another part_number.

part_number new_part_number
A111 D111
B111 D111
C111 D111

Please help.

I have written a code using cursor. But I just learnt that cursor should not be used. So I am looking for different syntax so that I can learn programming without cursor.

Thanks,

View 1 Replies View Related

Query Help - Urgent

Oct 22, 2001

Hello I have the following query that is producing the following errors;

Code:
declare @temp_request as varchar(8)

Select request as temp_request,BUSINESS_REQ_ID,TEST_CASE_SOURCE as SOURCE from TEAM3B_PULL_TOTAL_TST
if (not exist (select * from TEST_REPORT.dbo.BUS_REQ_DESCRIPTION_TST where REQUEST = temp_request))
begin

Insert into TEST_REPORT.dbo.BUS_REQ_DESCRIPTION_TST(request,bu siness_req_id,source)
select request,business_req_id,test_case_source from TEAM3B_PULL_TOTAL_TST where request=temp_request
end

Else

begin
Insert into TEST_REPORT.dbo.RDD_UPDATE_TST(request,business_re q_id,test_case_id,test_case_descr)
select request,business_req_id,test_case_id,test_case_des cr from TEAM3B_PULL_TOTAL_TST where request=temp_request
end

Errors:

Server: Msg 156, Level 15, State 1, Line 4
Incorrect syntax near the keyword 'select'.
Server: Msg 170, Level 15, State 1, Line 4
Line 4: Incorrect syntax near ')'.
Server: Msg 156, Level 15, State 1, Line 11
Incorrect syntax near the keyword 'Else'.

Thank you in advance,
Terry

View 1 Replies View Related

Urgent !!! Query Help ?

Nov 15, 2001

Hi ALL,

I am looking for a query to solve this.
I had two table's.

1) Employee Table

Employee_ID Designation
---------- -------------
Savin Database Administrator
Ray Software Engineer
Adam Software Engineer
Scott Software Engineer

2). EmployeeCred Table

Employee_ID SkillSet
----------- ---------
Savin Oracle8i
Savin SQL Server 7.0.
Savin SQL2000
Ray VB 6.0
Ray Java2.0
Ray C++
Adam Share point
Adam VB 6.0
Adam Java2.0
Scott ASP
Scott VB 6.0

Output I requried.
-----------------

Employee_ID Designation SkillSet
------------ ------------ --------
Savin Database Administrator Oracle8i, Sql Server 7.0, SQL2000
Ray Software Engineer VB 6.0, Java2.0, C++
Adam Software Engineer Share point, VB 6.0, Java2.0
Scott Software Engineer ASP, VB 6.0

I need a query to solve this problem. Kindly please help me. Its urgent!!!

Thanks in advance.
venkat.

View 1 Replies View Related

Query Its Urgent

Apr 20, 2006

DATEDIFF([day], dbo.Nonconformances.OpenDate, GETDATE()) AS [Days Still Active],

what is its function ?

View 1 Replies View Related

Is This Query Right? (urgent)

May 23, 2007

Hi

I have a report which has a table and that table has 4 columns

I want to represent the Data like this.

Company Match or Profit Sharing or Safe Harbor Company Match or ProfitSharing or Safeharbor

Years of Service Vesting Years of service Vesting

1 40 1 50

I have 3 text boxes saying Company Match, Safe harbor and Profit Sharing, and the User normally can click 2 checkboxes

Suppose if the user clicks only company Match, i want the company Match to display on the left hand side if the users clicks on 2 things say company match and safe harbor.

I want the Company match to come on the left and safe harbor to be on the right. and my Expression is as follows:

for the Left hand side its :

IIf(Fields!CompanyMatch.Value = true,"Company Match",IIf(Fields!SafeHarbor.Value = true,"Safe Harbor","Profit Sharing"))

and the right hand side its:

IIf(Fields!ProfitSharing.Value = true," Profit Sharing","")

so how can i display the details in the above fashion.

any help is appreciated.

Regards,

Karen

View 4 Replies View Related

Need Urgent Help In Mdx Query

Nov 27, 2007

WITH
SET [FilteredAccountSet] AS 'order( [Acc��BU_ Account_BCDsc].[Account],[Measures].[_MeasureBillingCode],ASC)'
SET [FilteredBUList] AS 'order( [Acc��BU_ Account_BCDsc].[Business Unit],[Measures].[_MeasureBillingCode],ASC)'
SET [FilteredBillingCodeSet] AS 'order( [Acc��BU_ Account_BCDsc].[Billing Code Description],[Measures].[_MeasureBillingCode],ASC)'

SET [FilteredEngagementGroupSet] AS '[Acc��BCEngTypeGroup_BCDsc].[Billing Code Engagement Type Group]'

MEMBER [Measures].[AverageTeamSize] AS '[Measures].[Consultant Utilization]/(COUNT(GENERATE({[Date��Year_Month_Day].
[Date��Year_Quarter_Month].[All Date��Year_Quarter_Month].[2007].[Quarter 1].[May]:
[Date��Year_Month_Day].[Date��Year_Quarter_Month].[All Date��Year_Quarter_Month].[2007].[Quarter 1].[July]},
DESCENDANTS([Date��Year_Month_Day].CURRENTMEMBER,[Date��Year_Month_Day].[Day]))))'

MEMBER [Measures].[MdaCodeTotal] AS

'VBA!ABS([Measures].[AverageTeamSize])+
VBA!ABS (([Ledger��L1_L2_L3_L4_AccountCode].[All Ledger��L1_L2_L3_L4_AccountCode].[Revenue].[Service].[On - site],[Measures].[Amount Usd]))+
VBA!ABS(([Ledger��L1_L2_L3_L4_AccountCode].[All Ledger��L1_L2_L3_L4_AccountCode].[Revenue].[Service].[Off shore],[Measures].[Amount Usd] )) +
VBA!ABS(([Ledger��L1_L2_L3_L4_AccountCode].[All Ledger��L1_L2_L3_L4_AccountCode].[Revenue].[Service Travel & Other],[Measures].[Amount Usd] ))+
VBA!ABS(([Ledger��L1_L2_L3_L4_AccountCode].[All Ledger��L1_L2_L3_L4_AccountCode], [Measures].[Effort Based Cost])) +
VBA!ABS(([Ledger��L1_L2_L3_L4_AccountCode].[All Ledger��L1_L2_L3_L4_AccountCode],[Measures].[Pulse Non Compliance Cost])) +
VBA!ABS(([Ledger��L1_L2_L3_L4_AccountCode].[All Ledger��L1_L2_L3_L4_AccountCode].[Expense].[On-site living],[Measures].[Amount Usd] )) +
VBA!ABS(([Ledger��L1_L2_L3_L4_AccountCode].[All Ledger��L1_L2_L3_L4_AccountCode].[Expense].[Other Expenses],[Measures].[Amount Usd] )) +
VBA!ABS(([Ledger��L1_L2_L3_L4_AccountCode].[All Ledger��L1_L2_L3_L4_AccountCode],[Measures].[Effort Based Onshore Billing Project Days])) +
VBA!ABS(([Ledger��L1_L2_L3_L4_AccountCode].[All Ledger��L1_L2_L3_L4_AccountCode],[Measures].[Effort Based Offshore Billing Project Days]))'

SELECT {
([Ledger��L1_L2_L3_L4_AccountCode],[Measures].[Billing Code ATC List]),
([Ledger��L1_L2_L3_L4_AccountCode],[Measures].[AverageTeamSize]),
([Ledger��L1_L2_L3_L4_AccountCode],[Measures].[Billing Code Engagement Type Group]),
([Ledger��L1_L2_L3_L4_AccountCode].[All Ledger��L1_L2_L3_L4_AccountCode].[Revenue].[Service].[On - site],[Measures].[Amount Usd]),
([Ledger��L1_L2_L3_L4_AccountCode].[All Ledger��L1_L2_L3_L4_AccountCode].[Revenue].[Service].[Off shore], [Measures].[Amount Usd]),
([Ledger��L1_L2_L3_L4_AccountCode].[All Ledger��L1_L2_L3_L4_AccountCode].[Revenue].[Service Travel & Other],[Measures].[Amount Usd]),
([Ledger��L1_L2_L3_L4_AccountCode],[Measures].[Effort Based Cost] ),
([Ledger��L1_L2_L3_L4_AccountCode],[Measures].[Pulse Non Compliance Cost]),
([Ledger��L1_L2_L3_L4_AccountCode].[All Ledger��L1_L2_L3_L4_AccountCode].[Expense].[On-site living],[Measures].[Amount Usd] ),
([Ledger��L1_L2_L3_L4_AccountCode].[All Ledger��L1_L2_L3_L4_AccountCode].[Expense].[Other Expenses],[Measures].[Amount Usd] ),
([Ledger��L1_L2_L3_L4_AccountCode],[Measures].[Effort Based Onshore Billing Project Days]),
([Ledger��L1_L2_L3_L4_AccountCode],[Measures].[Effort Based Offshore Billing Project Days])
} ON COLUMNS,

NON EMPTY
{

filter(
[FilteredAccountSet],[Measures].[_MeasureDataPresentBillingCodeDsc] = 1)

}
ON ROWS
FROM [Profitability]

WHERE ([Date��Year_Quarter_Month].[All Date��Year_Quarter_Month].[2007].[Quarter 1].[May]:
[Date��Year_Quarter_Month].[All Date��Year_Quarter_Month].[2007].[Quarter 1].[July])

m newbie for reporting services the above query is working on sql management studio but not working on Visual Studio BIwhy is that?pls reply me urgent

View 2 Replies View Related

Urgent SQL Query

Apr 20, 2008

OPEN orabaan
declare
@batch int,
@ttype char(3),
@doc int,
@ttdr float,
@ttcr float,
@diff float,
@s1r int,
@s2r int

WHILE
@@FETCH_STATUS = 0

BEGIN
FETCH NEXT FROM orabaan

INTO @batch,
@ttype,
@doc,
@ttdr,
@ttcr,
@diff

if @diff < 0
update
ttfgld102777
set
t_amth_2 = (t_amth_2 + @diff)
where exists
( select top 1 t_amth_2 from orabaan
where
t_dbcr = 1
==> and t_btno = @batch
and t_ttyp = @ttype
and t_docn = @doc )
==============================

I want to add a clause to auto update t_btno when value occur as "t_btno" is variable int

simple data
t_btno t_ttyp t_docn t_dbcr t_amth_2
17 NI1 20050205 1 -3914482498.0
17 NI1 20050205 1 -3914484153.3000002
17 NI1 20050205 2 -3914482497.3000002
17 NI1 20050205 1 -3914482497.3000002
17 NI1 20050205 2 -3914482498.0
17 NI1 20050205 2 -3914484153.3000002

I want to update first row only for each t_btno

thanks regards

View 3 Replies View Related

Recursive Query --- Very Urgent

May 15, 2007

Hello. I have a question. I have a table that keeps track of
claims being made by clients (like insurance claims). There is a table which
has 2 fields. Field1 and Field2. Field1 has the original claim number. If an
adjustment is made to the claim, a new claim number will be generated. For example
if a person pays $100 and files a claim, he is issued a claim number which goes
into Field1. If that person spends some more money towards the same issue and
then files another claim then he is given a new claim number which is stored in
Field2.

Field1--------------Field2
--------------------------------
100--------------------150
150--------------------200
200--------------------250
250--------------------Null (No more adjustments made)
and so on.

So in the query I will supply a claim number (say 100). I
need to get the history of all the claims attached to it. In this example 100,
150, 200, 250.

Also if the claim number provided is 200 I need to get the
original claim number (which is 100) meaning I need to back track and forward
track too. I need this in SQL Server 2000.

I hope I am clear enough. Thanks.

View 3 Replies View Related

Urgent - How To Write Query In Asp.net

Sep 26, 2007

i am working on asp.net
i am passing two values through querystring to the next page, that values are comming to next page but i want to display results on these two crieria hats.
i have written following query which working fine in sql server 2000
SQL 2000==> select caterername, ccity from caterer where ccity='matunga' and cateringfor like '%event%'
but when i am trying to do this in asp.net its not working. following code accepting only one value . i want to compair two value. Please guide me how to write above query in asp.net using 'and' and 'like' operatorasp.net ==>select * from addahall where HCity='" + city + "' ==> city is variable
please let me know as early as possible i have very less time.

View 5 Replies View Related

Sub Query Error(its Very Urgent)

May 6, 2008

hi frenz:
This is my query:it shows the following
error:Incorrect syntax near the keyword 'as'.plz let me know my error, and how to correct it.

My Query:

SELECT EH.EmpID, EL.UserName,EL.Password, E.FirstName, E.LastName, E.DesignationID,
E.DepartmentID, E.Sex, E.DOB, E.Age, E.Address, E.Email,
'ContactNo'= Case When E.phone is not null Then E.phone When E.phone is null
Then E.Mobile End ,E.DateOfjoining, D.Designation, DE.Department,E.HobbyID
where E.HobbyID in
(select EH.HobbyID from EMP_Tbl_EmployeeHobbies EH inner join EMP_Tbl_Hobby H on EH.HobbyID=H.HobbyID) as item1,
From dbo.EMP_Tbl_Employee E
Inner Join dbo.EMP_Tbl_Designation D On E.DesignationID=D.DesignationID
Inner Join dbo.EMP_Tbl_Department DE On E.DepartmentID=DE.DepartmentID
Inner Join dbo.EMP_Tbl_EmployeeHobbies EH On E.EmpID=EH.EmpID
Inner Join dbo.EMP_Tbl_EmployeeLogin EL On E.Sno=EL.Sno

View 1 Replies View Related

Urgent Licence Query

Dec 18, 2001

The question is regarding licences for clients who want to put their databases on the internet using our web servers. We have this in our price list as a service. We have been offering this service for a ong time.

Now we are using old licences v 7.0 for NT servers, and we need version w2000 for w2000 servers.

Our doubt is how to work the concurrent users through the internet. Can a server with mnimum number of licences serve hundereds of users through the internet,since users don't access the sql databases directly?

If anyone could help I would really appreciate it, or even offer a reference article

View 2 Replies View Related

Query Option In DTS - Urgent !!!

Apr 27, 2001

Im trying to transfer data from SQL tbl to .xls file through a DTS package.

My problem is that DTS transfers all the data from the source tbl to .xls
file.

But i would like to have the selective data transfer based on some query like ;

select * from tbl where field ='val'
for the val i want to pass different values .

instead of complete data transfer.
Thanks in advance.

Regards
Anand

View 3 Replies View Related

Query Help Reqd.......Urgent

May 19, 2001

Hi Every1 there,

I have 3 tables
Items
-----
Item_code
item_desc
gp_code
cat_code

and

inv_header
---------
invhkey
trnno
trndate
tot_amount
tot_discount

inv_Details
----------
invdetkey
invhkey
item_code
qty
rate
discount

i want a way in which i should b able to view the information in this format.

Gp_code Item_code item_Description
eg: 01aa abc abcdedf

Sales
May2001 Apr2001 Mar2001 Feb2001 Jan2001 Dec2000 till May2000(i.e 1 year)

if possible i want a flexibility wherein i should b able to give the gp_code and all the items in tht gp_code should b displayed in above format where in the starting monthh will b the current month .

Any help will b highly appreciated.
Thanx.

View 1 Replies View Related

Query Tuning____very Urgent Please

May 9, 2000

Hi all,
I am running a query which was created by front end application on (9 tables and one view) VIEW was created on 3 tables...Each table is having millions of records.. In this query there nearly 30 where conditions..are there..
When i run query plan , it is using HASH JOIN..
when i run a query, it is giving ERROR 1101..It is occupying all tempdb size, i gave autogrowth of tempdb, then also tempdb memory is not enough for this query...
I am having all possible indexes on all tables including tables which are used in VIEW.
and also i tried to run PERFORM INDEX ANALYSIS on this query , it is running ,after 10 minutes i stopped the PERFORM INDEX ANALYSIS.. because it is taking so much of time..
I believe this query is retrieving millions of records,but i dont know what to do with this one.. Pls anyone suggest me, how my query is taking so much time , is this because of HASH JOIN.
I think if we have service pack2, can avoid this problem, am i right(if we have so many records in table)

Pls suggest the way....

raj

View 3 Replies View Related

Tough Query-Please Help!!! URGENT!!!

Nov 21, 2001

Hello,

I have a table with 3 cols--- id1,id2 and id3
Total no.of rows-- 18

id1id2id3
11
25
35
45
51
6100
7100
845
944
1045
11100
126
136
141
152
163
17111
18123

id1 is an identity column.
I have to populate third column which is id3 based on the data in id2.

I have to group similar values in id2 together
and insert 1,2,3 accordingly in id3.
For eg: we have three 100's in id2, so we should have values 1,2,3 for id3.
we have two 45's in id2, we should have values 1,2 for id3.

so final output should be:

id1id2id3
111
251
352
453
512
61001
71002
8451
9441
10452
111003
1261
1362
1413
1521
1631
171111
181231

Is it possible?
Please help!!!!!!!

Thanks,
cheryl

View 3 Replies View Related

Update Query (Urgent Please)

Apr 12, 2005

Hi Guys,
I have a table created like
(cname,perfweek,orderstaken,callsmade).
the application give a report of perticular salespersons(cname) performance by ordertaken and callsmade.
somedays back, they have entered different cname for the same person. What I wanted to do is, when they give me correct cname and the wrong cname, I will have to findout in a perticular week, is there are duplication of cname and if then I have to add orderstaken and callsmade to the correct Cname for that particular week and after that delete the wrong Cname detail for that particular week(because it is added to the correct cname rows for that week).
And then I have to change the wrong Cname to the correct Cname for all the rows, if there is no data found for right cname matching for that week.
cname perfweek orderstaken callsmade
----- ------- --------- --------
abc 1 3 4
bbb 1 5 6
abc 3 3 1
bbb 3 2 3
bbb 2 4 5

in this eg: abc is the right cname and bbb is the wrong cname. Here what I have to do is I have to combine rows for the correct and wrong cname for that particular week ie: now the table looks like :
cname perfweek orderstaken callsmade
---- ------- ---------- ----------
abc 1 8 10
bbb 1 5 6
abc 3 5 4
bbb 3 2 3
bbb 2 4 5
(note: after combine, I have to delete the bbb for the perfweek of 1 and 3)
and then I have to update the rest of the wrong cname to the correct cname.
Then the firnal table looks like

cname perfweek orderstaken callsmade
----- ------- --------- --------
abc 1 8 10
abc 3 5 4
abc 2 4 5

It is an urgent requirment and I will really appreciate your valuable inputs.
Thanks very very much.

View 1 Replies View Related

Query Otimization (Urgent)

Oct 27, 2004

:confused: :confused: Hi All,

I am new to SQL Server. I want to know about SQL Query execution and compilation sequence.

Consider the following case:

SELECT A.COL1, B. COL2
FROM TAB1 A
INNER JOIN TAB2 B
ON A.COL5 = B.COL5
WHERE A.COL3 = 'A' AND B.COL3 = 'B'

Now as per my records into TAB1 and TAB2, condition B.COL3 = 'B' can reduse more number of rows then condition A.COL3 = 'A'.
So, please let me know above way to write the query is proper or not in given senario.

It would be great if one can provide the details how Sql Server 2000 compile and execute the query and some relavent data to optimize the performance while writting SQL query.

Thanks in Advance,
Jai

View 4 Replies View Related

JOIN Query Help(urgent)

Nov 2, 2004

i want to create a join query (for view) that will show one data per day for each agent.
just a select query joing these two tables..Seems like the date field is given me problem
i want result like below

TOTALCALL , TOTALESCA , AGENTID , DATE
50 , 5 , IDME1 , 10/28/2004 12:28:00 PM

TOTALESCA shows NuMbers of escalated calls out of totalcalls

table 1

TOTALCALL
AGENTID
DATE

TABLE 2

TOTALESCA
AGENTID
DATE

SAMPLE DATA ON TABLE 1

TOTALCALL, AGENTID, DATE
50 , IDME1 , 10/28/2004 12:28:00 PM

SAMPLE DATA ON TABLE 2

TOTALESCA, AGENTID , DATE
5 , IDME1 , 10/28/2004 12:28:00 PM

View 14 Replies View Related

Nested Query (Urgent)

Feb 17, 2004

Folks

I have two queries

Select Account_Id , Branch_Cd from Accounts

SELECT SUM (dbo.HOLDING.Shares_Par_Value_Qty * dbo.ASSET.Current_Prc) AS MarketValue
FROM dbo.HOLDING INNER JOIN
dbo.ASSET ON dbo.HOLDING.Property_Num = dbo.ASSET.Property_Num
Group by dbo.HOLDING.Account_ID

Account_ID is the same in both the queries ie in both the tables
Holding and Account.

I need the output like this

Select Account_Id, Branch_Cd, MarketValue from -------

But MarketValue should be calculated exactly in the above method.
How do I combine these two queries. I need it asap.
Help me out.

Thanks

View 2 Replies View Related

Urgent Query Help Needed

Apr 10, 2008

I am facing a query problem.i have 2 tables called emp-table and report-table.report table for listing report of employees.
following are criterias..

1 I want list all reports from report-table
2 But no report should have empstatus 5
3 reports need not necessary to have emp-table reference.ie emp-id reference in emp_table.
briefly saying i want all reports from report table,in which no one have empstatus 5

emp-table
empid
empname
empstatus

report-table
repid
empid
details

thanks in advance.

View 4 Replies View Related

Query Formation - Urgent

Jan 10, 2007

Please help me with the query to get the following result. I am so much worried with this and if some one can give me a query to form this, I will be so thank full
I have 3 tables

1. Quote
2. QuoteProdList
3. QuoteVersion

In Quote Table, I am storing the QuoteNumber,QuoteReferenceNumber and CustomerName
In QuoteprodList I am storing QuoteNumber and FasValue
In QuoteVersion, I am storing different versions of these QuoteRefNo

My problem is I have to get the sum of Fas value group by Customer name, but this should be for the latest version of quote.ie, In QuoteVersion Table, if some value is there in version2 field, it should take the fas value from QuoteProdList, given with the quotenumber specified in Version2 field of QuoteVersion Table.

Quote
-----

QuoteNumber.......CustomerName......QuoteRefNo
----------------------------------------------
1.................a.................xyz
2.................b.................abc
3.................a.................xyz
4.................c.................efg
5.................d.................hij
6.................a.................klm
7.................d.................hij

QuoteVersion
------------

QuoteRefNo..............Version1........Version2
------------------------------------------------
xyz.....................1..................3
abc.....................2..................Null
efg.....................4..................Null
hij.....................5..................7
klm.....................6..................Null

QuoteProdList
-------------
QuoteNumber...........Fasvalue
------------------------------
1.......................20
2.......................32
3.......................40
4.......................12
5.......................6
6.......................10
7.......................30

For me, the out put should be

CustomerName......FasValue
--------------------------
a..................50
b..................32
c..................12
d..................30

Customer ' a is having 2 quoterefno in Quote Table,xyz & klm. From QuoteVersion Table ,take QuoteNumber 3 for xyz specified by Version2 field, take it's fas value from QuoteprodList(40)+QuoteNUmber 6 specified by Version2 field, take is fas value(10)=50)
Customer d is having two quotenumbers in version1 and 2, so take the quote number in version 2 and take fas of 2 from quoteprodlist,ie, 30

Please help me to solve this.

Regards
Ceema

View 10 Replies View Related

Urgent - View Query

May 29, 2007

Dear Friends
I have 3 form with these names :

Clients <----- Projects <------- Files

I am using this query in my [ web ] page :

select clients.id ,
(select count(*) from projects where projects.clients_id = clients.id) as TotalProjects

from clients

I want to compute SUM for all relevant files for each client's projects.
how i can add it to select select statement ??

thank you.
m.o

View 5 Replies View Related

(Urgent) Help Needed With This Query.

Oct 2, 2007

Hi,
I am using this stored procedure to populate a report... if the particular report value is NULL i am getting this error

Cannot read the next data row for the data sest PlanEligiReqattri. Invalid lenght parameter passed to the right function...

This is my stored procedure

Code Block
ALTER Procedure [dbo].[rpt_PlanEligiReqAttriApplied]
@PlanId int
AS
BEGIN
Declare @List varchar(255)
Set @List =' '
Select @List = @List + ', ' + PERA.Name

From PlanEligibilityReqAttriApplied PERAA
Inner Join PlanEligibilityReqAttributes PERA on PERAA.PlanEligibilityReqId = PERA.PlanEligibilityReqId
Where PlanId = @PlanId
select Right(@List, Len(@List) - 2)
--set @List = Right(@List, Len(@List) - 1)
End

any help will be appreciated.
Regards
Karen

View 3 Replies View Related

Urgent Help With Query (Cross Join)

Sep 28, 2006

I have the following tables:members--------------member_idmember_tpc_id ( = tpc.tpc_id)tpc------tpc_idcourse------------course_idtpc_assignment---------------------------tpc_assignment_idcourse_idenrollment-------------------member_idcourse_idenrollment_status Now I want to select all members where member_tpc_id>0 and get the enrollment_status of each member in each course where course_id IN (Select course_id From tpc_assignment)Now what i did was get all the members and then all the courses and did a cross join between them. There are about 1900 members and 80 courses and when I do a cross join I get 1900*80 rows (152000) and the status of each member for all the 80 courses. If not enrolled it returns Not Enrolled (i have a UDF which takes a member_id and a course_id and returns the status). The BIG problem is that its taking about 6-8 mins to run the query and as a result its timing out on the aspx page. Can someone please tell me how I can do what i am trying to do without using the cross join because I suspect its the culprit here. The query I came up with is Select
*, dbo.returnStatus(temp1.user_id, temp2.course_id) As Status,
(Select tpc_title From tpc Where tpc_id = temp1.member_tpc_id) As Tpc_Title
From
(Select member_id As user_id, member_name, member_tpc_id
From members Where member_tpc_id> 0 And organization_id = '1'
)temp1
cross join
(Select course_id As course_id, course_title As course_title
From course Where course_id IN
(Select course_id From tpc_assignment Where tpc_requirement_id IN
(Select tpc_requirement_id From tpc_requirement) And course_id<>0 And organization_id = '1')
)temp2
Order By member_name, Tpc_TitlePlease help. Thank you.

View 1 Replies View Related

Urgent Converting A Query To A .asp Page

Aug 24, 2004

I am extremely new with .asp. We have the .net framework 1.1 installed on our server and the following query works in Crystal Reports (older version). We would like to have this converted to a .asp page with two inputs. One for the date range and one for the state. Can someone please tell me how to go about this. I do have downloaded as test. Visual Studio 2003.net and Web Matrix. I really am looking for (if there is any) a way to pretty quickly convert query to web pages so users can use them without a huge expense. IF someone already has something like this done. That would be great. The following query is for Visual Enterprise (Manufacturing software). Thanks

SELECT
SHIPPER."CUST_ORDER_ID", SHIPPER."SHIPPED_DATE", SHIPPER."INVOICE_ID",
SHIPPER_LINE."USER_SHIPPED_QTY", SHIPPER_LINE."SHIPPED_QTY", SHIPPER_LINE."UNIT_PRICE",
RECEIVABLE_CURR."CURRENCY_ID", RECEIVABLE_CURR."SELL_RATE",
CUST_ORDER_LINE."PART_ID", CUST_ORDER_LINE."ORDER_QTY", CUST_ORDER_LINE."USER_ORDER_QTY",
INVENTORY_TRANS."TYPE", INVENTORY_TRANS."ACT_MATERIAL_COST", INVENTORY_TRANS."ACT_LABOR_COST", INVENTORY_TRANS."ACT_BURDEN_COST", INVENTORY_TRANS."ACT_SERVICE_COST",
CUSTOMER_ORDER."CUSTOMER_ID", CUSTOMER_ORDER."SHIP_TO_ADDR_NO", CUSTOMER_ORDER."CURRENCY_ID",
PART."DESCRIPTION", PART."STOCK_UM",
CUSTOMER."STATE",
CUST_ADDRESS."ADDR_NO", CUST_ADDRESS."NAME", CUST_ADDRESS."STATE"
FROM
{ oj ((((((("VMFG"."dbo"."SHIPPER" SHIPPER INNER JOIN "VMFG"."dbo"."RECEIVABLE_CURR" RECEIVABLE_CURR ON SHIPPER."INVOICE_ID" = RECEIVABLE_CURR."INVOICE_ID") INNER JOIN "VMFG"."dbo"."SHIPPER_LINE" SHIPPER_LINE ON SHIPPER."PACKLIST_ID" = SHIPPER_LINE."PACKLIST_ID" AND SHIPPER."CUST_ORDER_ID" = SHIPPER_LINE."CUST_ORDER_ID") INNER JOIN "VMFG"."dbo"."INVENTORY_TRANS" INVENTORY_TRANS ON SHIPPER_LINE."TRANSACTION_ID" = INVENTORY_TRANS."TRANSACTION_ID") INNER JOIN "VMFG"."dbo"."CUSTOMER_ORDER" CUSTOMER_ORDER ON SHIPPER_LINE."CUST_ORDER_ID" = CUSTOMER_ORDER."ID") INNER JOIN "VMFG"."dbo"."CUST_ORDER_LINE" CUST_ORDER_LINE ON SHIPPER_LINE."CUST_ORDER_ID" = CUST_ORDER_LINE."CUST_ORDER_ID" AND SHIPPER_LINE."CUST_ORDER_LINE_NO" = CUST_ORDER_LINE."LINE_NO") INNER JOIN "VMFG"."dbo"."CUSTOMER" CUSTOMER ON CUSTOMER_ORDER."CUSTOMER_ID" = CUSTOMER."ID") INNER JOIN "VMFG"."dbo"."PART" PART ON CUST_ORDER_LINE."PART_ID" = PART."ID") LEFT OUTER JOIN "VMFG"."dbo"."CUST_ADDRESS" CUST_ADDRESS ON CUSTOMER_ORDER."CUSTOMER_ID" = CUST_ADDRESS."CUSTOMER_ID" AND CUSTOMER_ORDER."SHIP_TO_ADDR_NO" = CUST_ADDRESS."ADDR_NO"}
WHERE
SHIPPER."SHIPPED_DATE" >= {ts '2002-08-01 00:00:00.00'} AND
SHIPPER."SHIPPED_DATE" < {ts '2004-08-25 00:00:00.00'}
ORDER BY
SHIPPER."SHIPPED_DATE" ASC

View 3 Replies View Related

SQL Server Update Query Help -- URGENT!!!

Oct 10, 2005

Hello all. I have 2 tables members1 and members2.
members1 have a field called directory_services_idmembers2 also has a field directory_services_id and another one called employee_id
I need to update directory_services_id in members1 to the value employee_id in members2 Where members1.directory_services_id = members2.directory_services_id I dont want to update all the records. Only those records in members1 that have a match on directory_services_id in members2. So if there are 100 records that match on directory_services_id then i want to update only those 100 and not all the records.This is the query that I have so far.Update members1 M1 Set directory_services_id = (Select member_custom20 From members2 M2 Where M1.directory_services_id = M2.directory_services_id)Where M1.directory_services_id IN (Select directory_services_id From M2)And the error I am getting isServer: Msg 170, Level 15, State 1, Line 1Line 1: Incorrect syntax near 'M1'.Server: Msg 156, Level 15, State 1, Line 3Incorrect syntax near the keyword 'Where'.Please help. Thank you.

View 2 Replies View Related

Query Is Abending Because Of Time--Very Urgent

Apr 25, 2000

Hi all,
I am executing query which is using clustered index,no conditions simple select from table...
Table is having so many records, so my query is abending ,pls suggest me what i have to do..it is very urgent..

--raj

View 13 Replies View Related

SQL Query (Data Manupilation--URGENT)

May 25, 2004

Hi Guys,

Can you please help me on resolving this puzzle. I will appreciate your help.

At the botton is the data in one of the column (combined) in one of the table.

I need to split these data and insert into 4 different columns.

Structure of the data is this:
eg: if we take the first value in the following description ie: n1^m1^d1^s1
contains 4 different values for the future insert eg: n1 goes to Name column, m1 goes to Title column, d1 goes to Date column and S1 goes to salary column (This is grate).

And this information is separated by ^.
But This combined column can have multiple values for several employees
such as in 2nd value in the following data ie: n2^m2^d2^s2~n3^m3^d3^s3
(this is grate too). here n2 is a employee Name where as n3 is another employee name. These set of data is differentiated by ~.

This combined column can have multiple no of employees information (separted by ~).

Note: The employee may have some of the information missing in its corosponding set of data such as if you look at the 4th set of data, ie:
n5^^^. here we only have employeename information not other informations.
another eg: if we take the last row
ie: n7^^^~n8^^d8^~n9^m9^^~n10^^^s10
second set of data (n8^^d8^) has name(n8),null value for Title,d8 value for date,null value for salary.

So in a nutshell I need to split this data and load into 4 different columns in that table. which data goes which column is totally depend on the ^ and ~ separators.

For eg:
1) The output for the first row data should be like:: for name n1, for title m1,for date d1, for salary s1

2)The output for n6^m6^d6^ should be like : for name n6,for title m6, for date d6, for salary null

Thanks guys, This is making my head spin already and can you guys take a spin of this problem and help me.

Once again thanks for your help.

combined
--------
n1^m1^d1^s1

n2^m2^d2^s2~n3^m3^d3^s3

n4^m4^d4^s4~n4^m4^d4^s4~n5^m5^d5^s5

n5^^^

n6^m6^d6^

n7^^^~n8^^d8^~n9^m9^^~n10^^^s10

View 2 Replies View Related