|
(SORRY FOR MY BAD ENGLISH )
i have these :
cust_line (line_num,cust_id).
line_contact(contact_period,line_num).
i want to know the customer who the customer of the longest period contact
i think this is the quest
select max(cc),cust_line.line_num from cust_line JOIN (
select SUM(contact_period)as cc , line_num from line_contact group by line_num )As a
on cust_line.line_num=a.line_num
where cust_line.line_num=A.line_num
but it make a wrong results , please help me
ty
|
|
|
|
|
hi
hope below query will work for you,
select cust_id,max(contact_period) from line_contact,cust_line where line_contact.line_num = cust_line.line_num;
thanks
-amit
|
|
|
|
|
Please tell us what is the problem exactly your are facing. If you want data according to customerid just you can add customer id to the view.
|
|
|
|
|
I am having an issue with retrieving fields from MFC CRecordset.
I am using GetFieldValue( index, strText );
Works fine until the opened recordset contains only one record. Than I get the first field OK, but then I get an error saying basically that the field has already been retrieved.
Here it the error:
Error: GetFieldValue operation failed on field %d.
Data already fetched for this field.
It seem that the field index is not being advanced.I stepped thru the GetFieldValue,
but cannot find the actuall source for the error routine.
If I do MoveFirst on this one record record set , I get an error that I am attempting to move past the beginning of the recordset.That would be expected.
I am using VC 6.0 MFC with standard CRecordset and Access database with ODBC.
So far Google is no help.
Any pointers would be greatly appreciated.
Thanks for your help.
Vaclav
|
|
|
|
|
You should post this question under C/C++/MFC.
|
|
|
|
|
Are you advancing the index in the loop so that the loop will end if you have only on value? Also would it be possible to see the source for this.
|
|
|
|
|
I am sorry, I moved the question to MFC as suggested and did not even attempted to delete it here.
I think it cannot be deleted once discussion starts.
Yes, the field index is properly advanced and it works just fine with more than one record in the recordset - initially.
The key is that I get the first field OK, than it fails on next one.
I know the field is there because it retrives the record just fine when different SQL returns more records in recordset.
Here is the code snippet, I have removed my // comments for clarity, but it is pretty normal text retrieval loop.
pRecordset->AddNew(); fix , does not really adds anything
pRecordset->MoveFirst(); should not need this on initial Open recordset
short nFields = pRecordset->GetODBCFieldCount( );
while( !pRecordset->IsEOF( ) )
{
buffer = strText.GetBuffer(256);
// set item text
for( short index = 0; index < nFields; index++ )
{
pRecordset->GetFieldValue( index, strText );
buffer = strText.GetBuffer(256);
pList->SetItemText(iItem,index,buffer); this is where it fails
TRACE("\n item %i field %i buffer %s ",iItem , index , buffer );
}
pRecordset->MoveNext( );
iItem++;
}
I would like to get you opinions on this if you have time to work on it.
If so please switch to MFC forum.
Thanks
Vaclav
|
|
|
|
|
I am using Visual Studio 2010 Pro to make a report. I am working off someone else's stored procedure that I am not allowed to modify.
The problem is the stored procedure has IF statements, by way of example:
IF (criteria_1 = 'A')
BEGIN
SELECT
col_for_a_is_varchar100
FROM
some_table_1
END
IF (criteria_1 = 'B')
BEGIN
SELECT
col_for_b_is_varchar50
FROM
some_table_1
END
My problem is that in VS I have to use the name of column in the table but depending on what criteria_1 is equal to I could have a different column name: col_for_a_is_varchar100 vs. col_for_b_is_varchar50. I don't think there will be a problem with one being a VARCHAR(50) and the other a VARCHAR(100).
I have two different reports, one for when criteria_1 = 'A' and the other for when criteria_1 = 'B'. I am trying to make a report for criteria_1 = 'B' but there is no column or field named col_for_b_is_varchar50, only the col_for_a_is_varchar100. I tried putting in col_for_a_is_varchar100 where I would want col_for_b_is_varchar50 to appear in the report but that isn't working.
Have I made some mistake elsewhere because I should be able to use col_for_a_is_varchar100 even when the default value for parameter "criteria_1" is 'B'? The preview always fails to work.
If anyone could help me out it would save me from this grunt work and let me get back to coding in TeraData SQL and SAS again.
|
|
|
|
|
I would suggest writing your own stored procedure.
|
|
|
|
|
D Hoffman wrote: I have two different reports, one for when criteria_1 = 'A' and the other for when criteria_1 = 'B'. I am trying to make a report for criteria_1 = 'B' but there is no column or field named col_for_b_is_varchar50, only the col_for_a_is_varchar100. I tried putting in col_for_a_is_varchar100 where I would want col_for_b_is_varchar50 to appear in the report but that isn't working.
Have I made some mistake elsewhere because I should be able to use col_for_a_is_varchar100 even when the default value for parameter "criteria_1" is 'B'? The preview always fails to work.
There would be a problem for taking two different name for two different conditions... problem is if you want to show both values in one report it will show only one label field. For this you can produce another label field and suppress it depending upon the condition the query returning. The best i would suggest you to produce same name in both cases and you can write convert() for converting to Varchar(100) and Varchar(50).
|
|
|
|
|
D Hoffman wrote: The problem is the stored procedure has IF statements
Having an IF isn't a problem, returning different views based on the parameters is.
Adding another SP sounds like the best way to go. What's there prohibiting the creation of a new stored procedure?
Bastard Programmer from Hell 
|
|
|
|
|
Solved the issue.
Parameters can be specified that don't show up in the list of preview items. If these parameters are returned (the IF conditions are met and the different view is instead displayed) then these parameters have the requisite value, otherwise they don't and aren't applicable.
Thank you for the attempts to answer my question but they were all incorrect.
|
|
|
|
|
I am using a windows 2000 operating system (not by choice). A VB6 program uses an access database. I am converting access db to MSDE 2000 db. I need to write a query that Deletes all of the records in a table called ResultNotes. Below is a brief schema of the db with relationships.
tblResultNotes - this is the table I want to delete from
ResultID - Links to ResultID in teh tblResults table
Notes
tblResults
startTime
JobID - Links to JobID in tblJobs
ResultID
tblJobs
CreateDate
JobID
I need to delete all records from tblResultNotes that have a ResultID corresponding to the tblResults table. The records that have the ResultID in the tblResults need to correspond to the tblJobs JobID. The actual criteria for deletion is that startTime and CreateDate are older than 12/1/2000.
Any help is appreciated!
|
|
|
|
|
Since you did not include what you have tried.
Try using an inner join
BEGIN TRAN
DELETE tblResultNotes FROM tblResultNotes
INNER JOIN tblResults
ON tblResultNotes.ResultID = tblResults.ResultID
ROLLBACK TRAN
Look up BEGIN TRAN to see what to replace ROLLBACK TRAN with to commit the delete.
|
|
|
|
|
What is equivalent of MS Access IFF in MS Sql for this expression
IIf([ID_SECTION]=1,1,IIf([ID_SECTION]=2,2,IIf([ID_SECTION]=3,[ID_Tariff_Gender],0)))
Will it be like
CASE WHEN [ID_SECTION]=1 THEN 1
ELSE
CASE WHEN [ID_SECTION]=2 then 2
ELSE
CASE WHEN [ID_SECTION] = 3 then [ID_Tariff_Gender] ELSE 0 END
END
END
Is it correct ?
Any comments !!
|
|
|
|
|
If you want to use a SQL CASE statement, then try the following:
CASE [ID_SECTION]
WHEN 1 THEN 1
WHEN 2 THEN 2
WHEN 3 THEN [ID_Tarriff_Gender]
ELSE 0
END
|
|
|
|
|
Hum Dum wrote: Is it correct ?
Yes, it works.
Hum Dum wrote: Any comments !!
In this case, CASE is an overkill and an IF statement works perfectly well.
|
|
|
|
|
I think it isn't overkill you're working on a select statement. [ID_SECTION] is probably a column name ---hint that a select statement is being used.
|
|
|
|
|
Shameel wrote: In this case, CASE is an overkill and an IF statement
works perfectly well.
As a general statement I disgree with that.
The IF is an acceptable alternative if CASE didn't exist but CASE exists specifically to deal with situations exactly like that.
Given your statement what situation would be correct for CASE then?
|
|
|
|
|
If you see the example, each nested CASE statement has only one WHEN clause, and this is where I recommended an IF clause which works exactly the same way and is more readable.
There are, of course, situations where only CASE can be used and an IF cannot be used, like for example, in a SELECT statement.
|
|
|
|
|
The question refers to a column [ID_SECTION] which implies a select/insert/update/delete statement. [ID_SECTION] couldn't possibly be a variablle since it is not prefixed by '@'.
|
|
|
|
|
Shameel wrote: If you see the example, each nested CASE statement..,
Your statement wasn't clear but is still incorrect.
For the example given it is misusing CASE. The correct solution is to use it correctly.
|
|
|
|
|
jschell wrote: The correct solution is to use it correctly.
Most of our imports come from abroad! 
|
|
|
|
|
SilimSayo wrote: Most of our imports come from abroad!
Imports become necessary when locals lack quality and become unaffordable! 
|
|
|
|
|
here is my problem.
table: Employees
id name position
----------------------------------
1 bob sales
2 joe sales
3 mark management
4 jane management
5 julie it
6 donald sales
what I want to do is write a query that would spit out
position people
------------------------------
sales bob, joe, donald
management mark, jane
it julie
so something like this
SELECT position, somefunction(names) AS people FROM Employees GROUP BY position
does that 'somefuction' exist? if not how would i emulate it.
I'm doing this for reporting purposes.
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
