how do i query a table and if it doesn't have a value for a field return something anyway?
e.g.
(table 1)
id title
--------
1 a
2 b
3 c
4 (null)
e.g. if null return 'not assigned'
?
Hi
I have written a query for viewing the results of an on-line survey. I have three tables involved in this query: answers, answerpossibilities and users. So I use a few joins and made this query:
ALTER PROCEDURE dbo.GeefAntwoordenMeerkeuze ( @question_id int ) AS SET NOCOUNT ON; SELECT answerpossibilities.answerpossibility_content AS[Answerpossiblity], COUNT(answers.answers_id) AS [Times chosen] FROM answers right OUTER JOIN answerpossibilities ON answers.answerpossibility_id = answerpossibilities.answerpossibility_id left join users on answers.user_id = users.user_id WHERE ((answerpossibilities.question_id = @question_id AND nswerpossibilities.answerpossibility_content!='-- choose answer --')) GROUP BY nswerpossibilities.answerpossibility_content ORDER BY [Times chosen] desc
The above query works fine. The data returned by this query is shown in a gridview. When an answerpossibilty was never chosen it shows 0 as times chosen. So that's fine. But the problem is, only answers of users who completed the survey should be shown. In the users table there's a field user_completed. So the query should check whether this field is 1 (true).
ALTER PROCEDURE dbo.GeefAntwoordenMeerkeuze ( @question_id int ) AS SET NOCOUNT ON; SELECT answerpossibilities.answerpossibility_content AS[Answerpossiblity], COUNT(answers.answers_id) AS [Times chosen] FROM answers right OUTER JOIN answerpossibilities ON answers.answerpossibility_id = answerpossibilities.answerpossibility_id left join users on answers.user_id = users.user_id WHERE ((answerpossibilities.question_id = @question_id AND nswerpossibilities.answerpossibility_content!='-- choose answer --') and users.user_completed = 1) GROUP BY nswerpossibilities.answerpossibility_content ORDER BY [Times chosen] desc
Using this query only answers of users who completed the survey are shown but answer possibilities that were never chosen are no longer shown with 0 as times chosen. The gridview simply doesn't show them anymore.
Thanks for helping me!
Something went wrong by posting this message I guess, all blank lines were gone.. maybe because I used Safari on my iMac
Problem is that if the [Receiving] table doesn't have a match then no records are return. I want all matches from the [Orders Subtable] and any matches from the [Receiving] Table. If no [Receiving] table matches then I still want all matches from the [Orders Subtable]. Attached is the query.
Note: The query has to run in Access 2000 and I will be coding it in VB.
SELECT Orders.[Orders ID],
[Orders Subtable].ID,
[Orders Subtable].Quantity,
Receiving.Quantity,
Receiving.[Component #]
FROM (Orders
LEFT JOIN Receiving ON Orders.[Orders ID] = Receiving.[Orders ID])
INNER JOIN [Orders Subtable] ON Orders.[Orders ID] = [Orders Subtable].[Orders ID]
GROUP BY Orders.[Orders ID], [Orders Subtable].ID,
[Orders Subtable].Quantity, Receiving.Quantity,
Orders.[Project #], [Orders Subtable].On_Order,
[Orders Subtable].[Component #],
Receiving.[Component #]
HAVING (((Orders.[Project #])="Speed1aaaaa") AND
(([Orders Subtable].On_Order)=True) AND
(([Orders Subtable].[Component #])="R02101A") AND
((Receiving.[Component #])="R02101A"));
Hi,
I was trying to create a simple SP that return a single value as follows:CREATE PROCEDURE IsListingSaved@MemberID INT,@ListingID INTASIF EXISTS (SELECT [Member_ID] FROM [Member_Listing_Link] WHERE [Member_ID] = @MemberID AND [Listing_ID] = @ListingID) Return 1ELSE Return 0GOWhen I try it out in the Tableadapter's preview table, I get the correct result (1, where the entries exist). However, in the BLL, I tried to get the value as:Dim intResult as IntegerintResult = CType(Adapter.IsListingSaved(intMemberID, intListingID), Integer). However, this always returns 0 (when it should be returning 1). P.S. Curiously, breakpoints skipped the VS generated code for the adapter. What could be the problem? Thanks,Wild Thing
I have two tables that share a common identity row. I need to build a query where data that exists in one table does not contain data in the other table. For example, table 1 has columns of Owner_ID, LastName, FirstName and table 2 has columns Auto_ID, Owner_ID, AutoMake. Both tables are joined by the Owner_ID column. I need a query that provides all owners from table 1 who do not have an entry in table 2.
Thanks in advance,
Mark
Hi all,
When I execute a stored procedure that returns a single value using a TableAdapter, I always get error message "Object reference not set to an instance of an object"
I followed all the steps in microsoft help center:http://msdn2.microsoft.com/en-us/library/37hwc7kt.aspx
but the problem still unsolved.
here are my simple stored procedure:create PROCEDURE dbo.StoredProcedure1
AS
RETURN 4
and here are the simple code in my asp.net webpage that should display the number "4": protected void Button1_Click(object sender, EventArgs e)
{
DataSet1TableAdapters.QueriesTableAdapter TA
= new DataSet1TableAdapters.QueriesTableAdapter();
int i = (int) TA.StoredProcedure1();
Response.Write(i.ToString());
}
so, please could any one help me solve this problem?
Hi,
I have one package which pull data out from Oracle and dumps into SQL tables.
The issue is when i run the 'dtsrun' it reports " package completion", but doesn't return to command prompt.
Any help is highly appreciated.
Thanks,
I have a written a dll in 2.0 that calls a webservice.
This webservice is used to authenticate users in Active Directory. I created a assembly to that calls this dll because of the diverse languages versions that will use it (from asp,vb6 on up) and all can get values from a stored procedure that calls that assembly. I works great. Until now, I have to add another function to my dll that calls the webservice and returns the Users Full name from Active directory for electronic signitures. Okay I added to the dll then tried to reconstruct my Assembly and stored procedures and recieved the following error. "CREATE PROCEDURE failed because a CLR Procedure may only be defined on CLR methods that return either SqlInt32, System.Int32, void"
I want to keep all these Active directory call all in one place so I can be consistant in all the different applications. I was reading about UDF but that could get messy as I have a config file for the dll that allows the user to dynamically change the url for the webservice. Any suggestions/help will be greatly appreciated
Hi there,
It's a very strange thing!
I havea a table called invoices, and a table calle customer payments which has the invoiceID of the payment.
I have many invoices that haven't been paid (so they don't have a record on the customer payments). I know this, as i can for example do:
select * from invoices where invoiceID = 302247 (and i'll get one result)
select * from customer_payments where invoice = 302247 (and i'll get none results)
however, if i do the following:
select * from invoices where invoice_id not in
(select invoice_id from customer_payments)
I get nothing!!!???
It doesn't make any sense, as I should get at least 300 (including the 302247) - both invoiceids fields are int... so i just don't understand what's wrong?
thank you so much for any help!
Grazi
For each blog entry viewed in my web app, a (IPNumber, Date, BlogID) row is inserted into table "BlogViews"
How can I fetch the BlogID which has been most often visited, that is has the most number of rows?
i have a quite strange condition...when i add some value in database with getdate() function it only returns date and minute not the seconds...does somebody have an experience about this
View 3 Replies View RelatedI am writing some functions that work on a time series database of prices, ie volatility, correlation. I need to use the SELECT TOP syntax, but cannot do this with a variable, ie 'SELECT @x TOP * from prices'. My solution is to simply have a function for each potential period that will be looked at - 30day_volatility, 60day_volatility, etc. I looked at setting the ROWCOUNT variable but this is not allowed in functions. I haven't posted any DDL because I think the question is general enough - How do I return n ordered rows from a function without using SELECT TOP, or is there a way to use SELECT TOP with a variable that I am not aware of.
Thanks!
Hello,
I created a linked server in sql server 2005 which links to a AS400 DB. I use ODBC driver.
For some tables, it return all data but for another tables, it only return part of the rows.
How it may happen?
Thanks
Given the following data how do I make a SQL query that returns only 1 row per product?
The returned rows need consist of only currently active products (that is WHERE (DateEffective <= { fn NOW() }).
The twist: Sometimes a product will have duplicate DateEffective records. In that case, only return the record created latest because that's the most current data that exists for a product. RowTimeStap is when the record was created.
Example Data:
HistoryID ProductID Name Color DateEffective RowTimeStamp
(auto-number PK)
1 1 Wheel Red 2/1/2008 2/1/2008
2 1 Wheel Blue 3/5/2008 3/1/2008
3 1 Wheel Orange 3/5/2008 3/2/2008
4 1 Wheel Black 1/1/2010 3/3/2008
5 2 Knob Blue 3/2/2008 3/2/2008
6 2 Knob Green 3/3/2008 3/3/2008
Query should return:
3 1 Wheel Orange 3/5/2008 3/2/2008
5 2 Knob Green 3/3/2008 3/3/2008
The query I've created fails on the twist part. I have to allow duplicate DateEffective to keep a history of changes.
Can anyone help?
I don't know why this is so difficult. What I want to do is take a table name as a parameter to build a query and get an integer value from the result of the query. But from all of the research I have been doing, Dynamic SQL is bad in SQL server because of SQL Injections. But my users are not going to be supplying the table names.
Things I have learned:
 - SQL Functions cannot use Exec to execute query strings.
 - SQL Functions can return a concatenated string that could be used by a stored procedure to Exec the query string.
So how can I write a stored procedure that will
Â
1. take a parameter
2. Pass the parameter to a function that will return a string
3. Execute that string as SQL
4. Get a return value from that SQL statement
5. Then finally, from a View, how can I pass a parameter to the stored procedure and get the returned value from the stored procedure to be used as a field in the View?
Numbers 3, 4, and 5 are where I am really stuck. I guess I don't know the proper syntax and limitations of SQL Server.
I have
Customer table, tblCust: ID; Name
1 Peter2 Mary
Product table, tblProduct: ID; Name
1 Banana2 Orange3 Apple
Order tblOrder, tblOrder: CustID; ProductID; Amount
1 ;2 ;$20 – means Peter ordered $20 oranges
How do I write the SQL query so that the values in tblProduct become column, currently I have 20 items in that table. So, it will return something like this according to the information that I provide above?
Name Banana Orange AppleÂ
Peter 0 20 0
I am trying to catch the @retval which is returned finally after executing sp_update_schedule stored procedure (o or 1) but i cannot catch the final resultIf i put Print statement just before the return (@retval), then i am seeing 0 as output but i want to catch that value when i execute the SP with the parameters. How can i do that?? same thing with all other system stored procedures.thanks alot in advance....if you want more info on this Q, plz let me know
View 5 Replies View RelatedI am using SQL 2014 RTM (may be it's time to upgrade).
I have the following view:
create view [dbo].[SiriusV_Max4SaleList]
as
select m.id as Max4SaleId,
mt.[Description] as [TypeDescription],
CAST(m.[type] as tinyint) as [Type],
m.start_time as [StartTime],
m.end_time as [EndTime],
[Code] ....
I am thinking I may want to remove CAST for department, category, item later on as I don't really care if these columns would be defined as key for my EF model, but I do want to search by these columns. Anyway, this is my current view.
I executed the following select statement once
select * FROM dbo.siriusv_max4saleList where department like 's%' or category like 's%' or item like 's%'
And I believe I got 29 rows initially. However, when I execute this statement now I'm getting just 13 rows. If I execute just the department like 's%' I am getting 0 rows although I can see in the first result a row where department has s in in.
I guess I keep it here since I've created the message already but now I figured out why I am not getting the expected result. I used the condition like 's%' and not like '%s%' which application is doing.
What I want to do is return a row of data when my query doesn't return a record. I have two tables:
CREATE TABLE dbo.abc(
SeqNo smallint NULL,
Payment decimal(10, 2) NULL
) ON PRIMARY
[Code] ....
So when I run the following query:
SELECT 'abc' + '-' + CAST(SeqNo AS VARCHAR) + '-' + CAST(Payment AS VARCHAR) FROM abc WHERE SeqNo = 1
UNION
SELECT 'def' + '-' + CAST(SeqNo AS VARCHAR) + '-' + CAST(Payment AS VARCHAR) FROM def WHERE SeqNo = 1
abc-1-200.00
abc-1-500.00
As you can see since 1 doesn't exists in table 'def' nothing is returned as expected. However, if a row isn't returned I want to be able to enter my own row such as
abc-1-200.00
abc-1-500.00
def-0-0.00
Can Somebody please show me how to acheive this, using the order details in Northwinddatabase or any other good example. as much details as possible. Many Thanks!
View 6 Replies View RelatedI have written this sample query to search a full-text indexed table and return the results. If the word occurs more than once I want it to return as a new record and the results show a short summary of the location. I was using 'like', but the full table scans were really slowing it down. Can performance be improved for the following (The results returned by my query are accurate)
Query
DECLARE @searchString nvarchar(255);
DECLARE @searchStringWild nvarchar(275);
SET @searchString = 'first';
SET @searchStringWild = UPPER('"' +@searchString+ '*"');
SELECT id, '...' + SUBSTRING(searchResults.MatchedCell, searchResults.StartPosition, searchResults.EndPosition - searchResults.StartPosition) + '...' as Result
[Code] ....
I'm inserting from TempAccrual to VacationAccrual . It works nicely, however if I run this script again it will insert the same values again in VacationAccrual. How do I block that? IF there is a small change in one of the column in TempAccrual then allow insert. Here is my query
INSERT INTO vacationaccrual
(empno,
accrued_vacation,
accrued_sick_effective_date,
accrued_sick,
import_date)
[Code] ....
Hello everybody,
I'm developing a report using the following structure :
declare @sql as nvarchar(4000)
declare @where as nvarchar(2000)
set @sql = 'select ....'
If <conditional1>
begin
set @where = 'some where'
end
If <conditional2>
begin
set @where = 'some where'
end
set @sql = @sql + @where
exec(@sql)
I run it in query analyser and works fine, but when I try to run in Reporting Services, Visual studio stops responding and the cpu reaches 100 %.
I realize that when I cut off the if clauses, then it works at Reporting services.
Does anybody know what is happening?
Why the query works in query analyser and doesn't work in Reporting Service ?
Thanks,
MaurÃcio
Hi Guys,
I've got a stored proc used for order generation which runs long sometimes when called from within our app. A normal run will complete within 20s, a long run will get terminated by the app at the 6 minute mark.
When it runs long once, repeated attempts will also do so until I execute the same query the app did, but from within Query Analyzer. At which time the problem will disappear for a day or two. The app connects to the SQL Server 200 SP4 database using ADO.
I suspected statistics might be at fault here but have tried both "UPDATE STATISTICS table WITH FULLSCAN" and "DBCC DBREINDEX('table') to no avail. This issue has occurred and been worked around in this manner a few dozen times.
Any idea what might be going on here?
I'm pulling data from Oracle 8.05 using Oracle ODBC with DTS to SQL Server 7 sr2. If I choose my columns only, I have no problem. When I try to edit the SQL statement either in the Import Wizard or the DTS Designer, the package won't run. I am selecting on a date column. I corrected the TSQL using the "convert" statement. Does someone know what might be happening?
View 1 Replies View Related
Hi can any one see why this will now run when sent to the server. Don't worry about the parameters as these are replaced at runtime in a c# app I have created.
It is not the easiest to read but your help is appreciated.
Code Snippet
/* Create new table of dates using the Week Ending date selected by the user
This is a list of week ending dates between the start and end dates selected by the user */
DECLARE @Temp AS DATETIME
DECLARE @result table(WeekEndDate datetime)
SET @Temp = @Start + ((@WeekEnding - (DATEPART(w, @Start) - 1)) % 7) + CASE WHEN (@WeekEnding - (DATEPART(w, @Start) - 1))
< 0 THEN 7 ELSE 0 END
WHILE @Temp <= @End
BEGIN
INSERT INTO @result values(@Temp)
SET @Temp = @Temp + 7
END
/* Aggregate data by Week ending date so that we can see the Metrics for that particular week*/
SELECT dbo.MSP_WEB_PROJECTS.PROJ_NAME AS Project, EV_View.[Week Ending], EV_View.BAC, EV_View.EV, EV_View.PV,
EV_View.AC, EV_View.FV, EV_View.FC, EV_View.[Baseline Work], EV_View.[Work], EV_View.[Actual Work], EV_View.[ETC],
CASE WHEN EV_View.[Week Ending] BETWEEN dbo.MSP_VIEW_PROJ_PROJECTS_STD.ProjectStartDate AND
dbo.MSP_VIEW_PROJ_PROJECTS_STD.ProjectFinishDate THEN 'YES' ELSE 'NO' END AS [1 Project Duration]
FROM
(
SELECT ProjectID, WeekEndDate AS [Week Ending], SUM(BAC) / @TimeUnits AS BAC, SUM(EV) / @TimeUnits AS EV,
SUM(PV) / @TimeUnits AS PV, SUM(AC) / @TimeUnits AS AC, SUM(FV) / @TimeUnits AS FV, SUM(FC) / @TimeUnits AS FC,
SUM([Baseline Work]) / @TimeUnits AS [Baseline Work], SUM([Work]) / @TimeUnits AS [Work],
SUM([Actual Work]) / @TimeUnits AS [Actual Work], (SUM([Work]) - SUM([Actual Work])) / @TimeUnits AS ETC
FROM
(
SELECT ProjectID, WeekEnds.WeekEndDate, BAC,
CASE WHEN derivedtbl_1.[Baseline Finish] BETWEEN WeekEnds.WeekEndDate - 6 AND WeekEnds.WeekEndDate THEN derivedtbl_1.PV END AS PV,
CASE WHEN derivedtbl_1.[Actual Finish] BETWEEN WeekEnds.WeekEndDate - 6 AND WeekEnds.WeekEndDate THEN derivedtbl_1.EV END AS EV,
CASE WHEN derivedtbl_1.[Actual Finish] BETWEEN WeekEnds.WeekEndDate - 6 AND WeekEnds.WeekEndDate THEN derivedtbl_1.AC END AS AC,
CASE WHEN (derivedtbl_1.Finish BETWEEN WeekEnds.WeekEndDate - 6 AND WeekEnds.WeekEndDate) THEN derivedtbl_1.[Baseline Work] END AS FV,
CASE WHEN (derivedtbl_1.Finish BETWEEN WeekEnds.WeekEndDate - 6 AND WeekEnds.WeekEndDate) THEN derivedtbl_1.[Estimate at Complete] END AS FC,
CASE WHEN (TP.AssignmentTimeStart BETWEEN WeekEnds.WeekEndDate - 6 AND WeekEnds.WeekEndDate) THEN TP.[Baseline Work] END AS [Baseline Work],
CASE WHEN (TP.AssignmentTimeStart BETWEEN WeekEnds.WeekEndDate - 6 AND WeekEnds.WeekEndDate) THEN TP.[Work] END AS [Work],
CASE WHEN (TP.AssignmentTimeStart BETWEEN WeekEnds.WeekEndDate - 6 AND WeekEnds.WeekEndDate) THEN TP.[Actual Work] END AS [Actual Work]
FROM
(
SELECT dbo.MSP_VIEW_PROJ_ASSN_TP_BY_DAY.WPROJ_ID, dbo.MSP_VIEW_PROJ_ASSN_TP_BY_DAY.AssignmentTimeStart,
SUM(ISNULL(dbo.MSP_VIEW_PROJ_ASSN_TP_BY_DAY.AssignmentTimeBaselineWork, 0)) AS [Baseline Work],
SUM(ISNULL(dbo.MSP_VIEW_PROJ_ASSN_TP_BY_DAY.AssignmentTimeWork, 0)) AS [Work],
SUM(ISNULL(dbo.MSP_VIEW_PROJ_ASSN_TP_BY_DAY.AssignmentTimeActualWork, 0)) AS [Actual Work],
SUM(ISNULL(dbo.MSP_VIEW_PROJ_ASSN_TP_BY_DAY.AssignmentTimeActualWorkProtected, 0)) AS [Actual Work Protected]
FROM dbo.MSP_VIEW_PROJ_ASSN_TP_BY_DAY INNER JOIN
dbo.MSP_VIEW_PROJ_ASSN_STD ON
dbo.MSP_VIEW_PROJ_ASSN_TP_BY_DAY.WPROJ_ID = dbo.MSP_VIEW_PROJ_ASSN_STD.WPROJ_ID AND
dbo.MSP_VIEW_PROJ_ASSN_TP_BY_DAY.AssignmentUniqueID = dbo.MSP_VIEW_PROJ_ASSN_STD.AssignmentUniqueID
WHERE (dbo.MSP_VIEW_PROJ_ASSN_TP_BY_DAY.AssignmentTimeStart <= @End) AND
(dbo.MSP_VIEW_PROJ_ASSN_TP_BY_DAY.AssignmentTimeStart >= @Start)
)
AS TP INNER JOIN
(
SELECT dbo.MSP_WEB_PROJECTS.WPROJ_ID AS ProjectID,
dbo.MSP_WEB_PROJECTS.PROJ_NAME AS Project, dbo.MSP_VIEW_PROJ_TASKS_STD.TaskName AS [Task Name],
DATEADD(day, 0, DATEDIFF(day, 0, dbo.MSP_VIEW_PROJ_TASKS_STD.TaskBaselineStart)) AS [Baseline Start],
DATEADD(day, 0, DATEDIFF(day, 0, dbo.MSP_VIEW_PROJ_TASKS_STD.TaskBaselineFinish)) AS [Baseline Finish],
DATEADD(day, 0, DATEDIFF(day, 0, dbo.MSP_VIEW_PROJ_TASKS_STD.TaskActualFinish)) AS [Actual Finish],
dbo.MSP_VIEW_PROJ_TASKS_STD.TaskBaselineWork AS [Baseline Work],
dbo.MSP_VIEW_PROJ_TASKS_STD.TaskWork AS [Estimate at Complete],
dbo.MSP_VIEW_PROJ_TASKS_STD.TaskActualWorkProtected AS [Actual Work],
dbo.MSP_VIEW_PROJ_TASKS_STD.TaskRemainingWork AS [Estimate to Complete],
dbo.MSP_VIEW_PROJ_TASKS_STD.TaskBaselineWork AS BAC,
CASE WHEN dbo.MSP_VIEW_PROJ_TASKS_STD.TaskPercentComplete >= 100 THEN dbo.MSP_VIEW_PROJ_TASKS_STD.TaskBaselineWork
END AS EV,
dbo.MSP_VIEW_PROJ_TASKS_STD.TaskBaselineWork AS PV,
CASE WHEN dbo.MSP_VIEW_PROJ_TASKS_STD.TaskPercentComplete >= 100 THEN dbo.MSP_VIEW_PROJ_TASKS_STD.TaskActualWorkProtected
ELSE 0 END AS AC, dbo.MSP_VIEW_PROJ_TASKS_STD.TaskPercentComplete AS [% Complete],
DATEADD(day, 0, DATEDIFF(day, 0, dbo.MSP_VIEW_PROJ_TASKS_STD.TaskFinish)) AS Finish
FROM dbo.MSP_VIEW_PROJ_TASKS_STD INNER JOIN
dbo.MSP_VIEW_PROJ_TASKS_ENT ON
dbo.MSP_VIEW_PROJ_TASKS_STD.WPROJ_ID = dbo.MSP_VIEW_PROJ_TASKS_ENT.WPROJ_ID AND
dbo.MSP_VIEW_PROJ_TASKS_STD.TaskUniqueID = dbo.MSP_VIEW_PROJ_TASKS_ENT.ENT_TaskUniqueID INNER JOIN
dbo.MSP_WEB_PROJECTS ON dbo.MSP_VIEW_PROJ_TASKS_STD.WPROJ_ID = dbo.MSP_WEB_PROJECTS.WPROJ_ID INNER JOIN
dbo.MSP_VIEW_PROJ_PROJECTS_ENT ON
dbo.MSP_VIEW_PROJ_TASKS_STD.WPROJ_ID = dbo.MSP_VIEW_PROJ_PROJECTS_ENT.WPROJ_ID
WHERE (dbo.MSP_VIEW_PROJ_TASKS_STD.TaskSummary = 0)
)
AS derivedtbl_1 ON
TP.WPROJ_ID = derivedtbl_1.ProjectID CROSS JOIN
@result AS WeekEnds
WHERE (DATEADD(day, 0, DATEDIFF(day, 0, [Baseline Finish])) >= @Start AND DATEADD(day, 0, DATEDIFF(day, 0, [Baseline Finish])) <= @End) OR
(DATEADD(day, 0, DATEDIFF(day, 0, [Actual Finish])) >= @Start AND DATEADD(day, 0, DATEDIFF(day, 0, [Actual Finish])) <= @End) OR
(DATEADD(day, 0, DATEDIFF(day, 0, Finish)) >= @Start AND DATEADD(day, 0, DATEDIFF(day, 0, Finish)) <= @End)
)
AS WithWeekEnds
WHERE (BAC IS NOT NULL) OR (EV IS NOT NULL) OR (PV IS NOT NULL) OR
(AC IS NOT NULL) OR (FV IS NOT NULL) OR (FC IS NOT NULL)
GROUP BY WeekEndDate, ProjectID
)
AS EV_View INNER JOIN
dbo.MSP_WEB_PROJECTS ON
EV_View.ProjectID = dbo.MSP_WEB_PROJECTS.WPROJ_ID INNER JOIN
dbo.MSP_VIEW_PROJ_PROJECTS_STD ON
EV_View.ProjectID = dbo.MSP_VIEW_PROJ_PROJECTS_STD.WPROJ_ID INNER JOIN
dbo.MSP_VIEW_PROJ_PROJECTS_ENT ON
EV_View.ProjectID = dbo.MSP_VIEW_PROJ_PROJECTS_ENT.WPROJ_ID
ORDER BY dbo.MSP_WEB_PROJECTS.PROJ_NAME, EV_View.[Week Ending]
I'm having a tough time understanding my current problem and why I'm having it.
I'm using an ODBC db connection to connect to my sql server and trying
to insert data into a table I've created, but I keep getting an error
saying that the table doesn't exist. This is the first time I've
ever used ODBC and DSN to connect to a db so I don't konw if there are
qerks associated with this, but I've tried everything I can think
of. I've changed the name of the table, placed it in brackets,
placed "dbo" infront of the name, and still I get the same error about
the table not existing. My "Intial Catalog" in my connection
string is the correct db, I just can't seem to get it going.
Any suggestions?
Hi, I have the following query:UPDATE lista SET audio='3847f5e9-4ef7-42d7-9e57-e5cbad9131b1.jpg' WHERE id='13';If the id already exists it'll modify the row correctly as expected. But if the id doesn't exist at the table I want the row to be inserted anyway, but this is not happening. The same query works well in mysql + php. The table has an identity increment of 1 for the primary key also. Any ideas?Thanks in advance.
View 1 Replies View RelatedHi!
Do you know why this query is ok on SQL 2005 and not on SQL ce ??
Code Snippet
DELETE ProfilesGroups
FROM ProfilesGroups pg
INNER JOIN Groups g
ON tpGroupID = g.gId
WHERE pg.tpProfileID = '7df60fae-a026-4a0b-878a-0dd7e5308b09'
AND g.gProfileID = '8a6859ce-9f99-4aaf-9ed6-1af66cd15894'
Thx for help ;-).
PlaTyPuS
hi i am running this program
and its giving some problem while execution.
When the program tries to create the table expt1..its says
Msg 2714, Level 16, State 1
There is already an object named 'expt1' in the database.
and if i try to drop the table expt1 it says
Msg 3701, Level 11, State 1
Cannot drop the table 'expt1', because it doesn't exist in the system catalogs.
How should I resolve this problem...?
==========================
Declare @cmdstring varchar(255)
Declare @filename varchar(32)
DECLARE @v datetime
SELECT @v = GetDate()
Create table expt1(GMSKEY char,CONTRACT char,ORIGINALCONTRACT char,STATEMENTID char,
STATEMENTDATE char ,partner char ,ADDRESSSEQ char,BOOKINGAMOUNT char,
BOOKINGCURRENCY char,TAXEDIND char,TAXTYPE char,
DUEPERIOD char,ACCOUNTNUMBER char,PRODMONTH char,PRODYEAR char,BOOKINGUNIT char,
BOOKINGQUANTITY char,STATEMENTSEQ char,COSTID char,DELIVERYMETHOD char)
SELECT
DETAILTRANS.GMSKEY,
DETAILTRANS.CONTRACT,
DETAILTRANS.ORIGINALCONTRACT,
DETAILTRANS.STATEMENTID,
convert(varchar,DETAILTRANS.STATEMENTDATE,106) statementdate,
DETAILTRANS.partner,
DETAILTRANS.ADDRESSSEQ,
DETAILTRANS.BOOKINGAMOUNT,
DETAILTRANS.BOOKINGCURRENCY,
DETAILTRANS.TAXEDIND,
DETAILTRANS.TAXTYPE,
TITLECT.DUEPERIOD,
DETAILTRANS.ACCOUNTNUMBER,
DETAILTRANS.PRODMONTH,
DETAILTRANS.PRODYEAR,
DETAILTRANS.BOOKINGUNIT,
DETAILTRANS.BOOKINGQUANTITY,
DETAILTRANS.STATEMENTSEQ,
DETAILTRANS.COSTID,
TITLECT.DELIVERYMETHOD,
'' RECEND
INTO expt1
FROM DETAILTRANS,TITLECT
where DETAILTRANS.TITLEID=TITLECT.TITLEID
AND DETAILTRANS.CONTRACT IS NOT NULL
AND DETAILTRANS.CONTRACT!='N/A'
AND DETAILTRANS.EXPREV='E'
AND DETAILTRANS.PRELIMORFINAL='F'
AND DETAILTRANS.postedDATE IS NULL
ORDER BY DETAILTRANS.GMSKEy
select @filename = 'XAPIALTMKT'+CONVERT(varchar, @v, 112)+ SUBSTRING(CONVERT(varchar, @v, 108), 1, 2)
+ SUBSTRING(CONVERT(varchar, @v, 108), 4, 2)+ SUBSTRING(CONVERT(varchar, @v, 108), 7, 2) + '.TXT'
Select @cmdstring = 'bcp GM_PROD..expt1 out d:est' + @filename + ' -c -t"|" -r -S -Usa -Psa'
exec master..xp_cmdshell @cmdstring
drop table expt1
===========
Thanks
Harish
Hi all:
for example: testDataContext db=new testDataContext();var res=db.tables.single(p=>p.columnName=="hi");
if there is a record in the database, it works well, but if there isn't , it will throw an exception, then , How could I know the record exists or not ? I don't think exception is a resonable way. and in my opinion, there should be--------even must be ------a resonable way , to evaluate the query result to a bool variable, then program could judge the bool variable like :
if(bExist) show("yes, I find it");else show("sorry, the record doesn't exist in the database");
I can't imagine I got the bool variable via exception...
thanks to all..
SQL Server 2000 Enterprise
While testing an update script I found that a number of data rows are inaccessible using my query, though the data does exist in the table. I can browse records to find the data or query the record by some column names, but not by others.
For example:
SELECT name FROM tblPersonalInformation WHERE [ID Number] = 2358899;
would not find the data sought. However, if I put in:
SELECT address FROM tblPersonalInformation WHERE name = ‘Doe, John’;
the query yields the desired data. Many of the records queried display the specified data with no problem. I have found this problem to exist on a number of data rows, but can’t figure out a reason. The front-end application displays the data without any apparent problems.
Ideas please.
SQL Server 2000 Enterprise
While testing an update script I found that a number of data rows are inaccessible using my query, though the data does exist in the table. I can browse records to find the data or query the record by some column names, but not by others.
For example:
SELECT name FROM tblPersonalInformation WHERE [ID Number] = 2358899;
would not find the data sought. However, if I put in:
SELECT address FROM tblPersonalInformation WHERE name = ‘Doe, John’;
the query yields the desired data. Many of the records queried display the specified data with no problem. I have found this problem to exist on a number of data rows, but can’t figure out a reason. The front-end application displays the data without any apparent problems.
Ideas please.