Getting the ID that are not equal
(89 Views)
I am trying to run this wquery that compares two sets of data in my SQL DB table UserData.
But I am only trying to locate the IDs that are different. It is only three Ids that are different in each tp_ListID.
but i cannot get it to return any values, PLEASE HELP>>>>>>
Code:
SELECT dbo.UserData.tp_ID, dbo.UserData.tp_ListId, UserData_1.tp_ID AS Expr1, UserData_1.tp_ListId AS Expr2
FROM dbo.UserData INNER JOIN
dbo.UserData AS UserData_1 ON dbo.UserData.tp_ID UserData_1.tp_ID
Where dbo.UserData.tp_ListID='{9E4E6B81-42F1-4903-8D0A-4338057A0B6B}' and UserData_1.tp_ListID='{5AB67B85-4C9E-486D-874A-DBD456042F17}'
|
Help with syntax
(165 Views)
Hi I need to pass an SQL test before I can get an interview for a job but my syntax is pretty rusty and I was hoping someone would be able to take a look at what I have done already and correct me if necessary, Many thanks!
DDL for Schema:
CREATE TABLE DBTEST.JOBS
(
JOB_ID NUMBER(38) NOT NULL,
COMPANY_ID NUMBER(38) NOT NULL,
START_DATE DATE NOT NULL,
JOB_TYPE VARCHAR2(100) NOT NULL,
STATUS VARCHAR2(20) NOT NULL
)
CREATE TABLE DBTEST.SALARY_HISTORY
(
SALARY_HISTORY_ID NUMBER(38) NOT NULL,
SALARY NUMBER(7,2) NOT NULL,
START_DATE DATE NOT NULL,
END_DATE DATE NOT NULL,
EMPLOYEE_ID NUMBER(38) NOT NULL,
CONSTRAINT SYS_C0036283
FOREIGN KEY (EMPLOYEE_ID)
REFERENCES DBTEST.EMPLOYEES (EMPLOYEE_ID)
ENABLE
)
CREATE TABLE DBTEST.EMPLOYEES
(
EMPLOYEE_ID NUMBER(38) NOT NULL,
NAME VARCHAR2(40) NOT NULL,
MANAGER_ID NUMBER(38) NOT NULL,
JOB_ID NUMBER NULL,
CONSTRAINT FK_EMPLOYEE_JOB
FOREIGN KEY (JOB_ID)
REFERENCES DBTEST.JOBS (JOB_ID)
ENABLE
)
CREATE TABLE DBTEST.CURRENT_SALARY
(
CURRENT_SALARY_ID NUMBER(38) NOT NULL,
SALARY NUMBER(7,2) NOT NULL,
START_DATE DATE NOT NULL,
EMPLOYEE_ID NUMBER(38) NOT NULL,
CONSTRAINT SYS_C0036280
FOREIGN KEY (EMPLOYEE_ID)
REFERENCES DBTEST.EMPLOYEES (EMPLOYEE_ID)
ENABLE
)
Questions:
1.Provide a count of employees by manager id
2.Repeat the previous question but provide the managers name instead of the id
3.Repeat the previous query and order the output by the number of employees per manager
4.Repeat the previous query for managers with more than 2 employees
5.Provide a list of employees whose surname begins with B
6.Provide a list of employees ordered by highest current salary
7.Return a list of employees earning between 4000 and 5000
8.Get the Employee name of the longest serving employee. Using the min function. Assume all long serving employees will have a salary history.
9.Do the same query but use rownum instead of min.
10.Get a full (current and historical) salary history for the employee Fred Brown
Answers
1.CREATE VIEW EMPCOUNT_MANID (MANAGER_ID, NUM_OF_EMP)
SELECT MANAGER_ID, COUNT(MANAGER_ID) FROM EMPLOYEES
2.CREATE VIEW EMPCOUNT_MANNAME(NAME, NUM_OF_EMP)
SELECT NAME, COUNT(MANAGER_ID)
FROM EMPLOYEES
GROUP BY NAME
3.CREATE VIEW EMPCOUNT_MANNAME (NAME, NUM_OF_EMP)
SELECT COUNT(MANAGER_ID)
FROM EMPLOYEES
GROUP BY NAME
4.CREATE VIEW EMPCOUNT_MANNAME (NAME, NUM_OF_EMP)
SELECT COUNT(MANAGER_ID)
FROM EMPLOYEES
GROUP BY NAME
WHERE NUM_OF_EMP > 2
5.CREATE VIEW EMP_SURNAME (SURNAME_B)
SELECT NAME
FROM EMPLOYEES
WHERE NAME LIKE 'B%'
6.CREATE VIEW EMP_HIGHEST (ID, SALARY_HIGHEST)
SELECT EMP_ID, SALARY
FROM CURRENT_SALARY
ORDER BY SALARY DESC
7.CREATE VIEW EMP_HIGHEST (ID, SALARY_HIGHEST)
SELECT EMP_ID, SALARY
FROM CURRENT_SALARY
WHERE CURRENT_SALARY >= 4000 AND
|
Incorrect syntax near '+'
(199 Views)
i'm writing a stored procedure for enabling full text searching
on my dynamically created db's
the sp:
Code:
create procedure sp_Fulltext_Contents @strDB varchar(50)
as
begin
exec sp_fulltext_database 'enable'
exec sp_fulltext_catalog @strDB + 'Catalog','create'
end
and its throwing the error, incorrect syntax near '+' line 5
Shem
|
Network error during DTS/Table Copy
(259 Views)
, I use Enterprise to create DTS Packages and populate a few tables on a remote SQL server. Until recently everything was working fine. Now, I can't complete a DTS. Well i'll clarify. I can complete a DTS locally, just not remotely. When I try to copy the locally created table to the remote server I cannot. I always get a network error. Specifically:
[DBNETLIB][Connection(send()).]General network error
The kicker is if I try to do anything small (140k) it works fine. But as soon as it gets in the 1meg range forget it.
I've had my hosting service increase my database size. (they've also told me that there is nothing wrong which I can't really prove) I've also tried various timout values in enterprise. I'm stumped.
|
Update syntax
(178 Views)
Does know where I am incorrect in this simple update
Update Revenue
set Difference_Flag = 'Yellow' From Revenue
where (select((TY_Revenue - LY_Revenue)/LY_Revenue)*100 = 0)From Revenue
|
Error Accessing MSAccess database on network drive
(497 Views)
On a network drive accessed by all users,I have created a directory with all security permissions to . Copied the MSAccess database to this directory and granted full control to Everyone.From a personal web server on my machine , I am trying to access a MSAccess database on a network drive and get the following error.I also created a folder on my C: drive.When I change the database path/namt to clannedactivitypact.mdb everything works fine.But when I try to access the database on the network drive, I get the following errorError Type:Microsoft JET Database Engine (0x80004005)The Microsoft Jet database engine cannot open the file 'gperationssupportplannedactivitypact.mdb'. It is already opened exclusively by another user, or you need permission to view its data./html/html/Jon/jon_entry.asp, line 79Browser Type:Mozilla/4.0 (compatible; MSIE 6.0; Windows NT 5.0) Page:GET /html/html/Jon/jon_entry.asp Would appreciate some help with thi
|
How do you create an upload on a form so that the file gets sent to the database?
(515 Views)
,
I have a form written in JScript and ASP and I need to create an upload area for files of any type, ...etc. The data in the form currently gets sent to a SQL database. How does the uploaded file get to the sql database I read somewhere that the field type is "image"
The other challenge is that an email gets sent to a person with a link to view the web based form with all the data. But how is this person going to view the uploaded file Ideally I'd like for them to receive the file as an attachment. Is this possible
Please help!
Thank you,
Barnes
|
SQL Server registration errors (NEWBIE)
(297 Views)
Odd problem - I'm running SQL 7 (client install) and cannot
connect to the server, but only from my machine. From
any other machine on the LAN that I've tried (after installing
the client and adding my domain acct to the machine) I
get in fine. I cannot connect using ISQL either.
Any ideas I'm really hoping to avoid a machine reformat.
|
query returning items that don't exist in another table
(271 Views)
Hi I am really struggling.
I want a query to return items from a main table that don't exist in another table.
I have two tables. One is a navigation table and one is a table with html associating to the navigation.
In an admin panel I want to be able to choose any navigation that has not yet had the html added to it.
eg this
SELECT * FROM bazaweb.navigation INNER JOIN bazaweb.pages on (bazaweb.navigation.nav_id = bazaweb.pages.nav_id AND bazaweb.pages.pages_type'special' AND bazaweb.navigation.lang_id=bazaweb.pages.lang_id)
returns the results which have html attribute.
I want the opposite to!.
(one is for edit & one is for add)
I've tried non equal () but that gives me hundreds of rows.....
Any ideas, have searched the web to no avail. Pehaps I need toc oem at this a different way.
|
MS SQL Convert function not working
(137 Views)
I have just converted an Access database to a SQLServer one and now have the joy(!!) of changing all the SQL statements to make then compatible.
In Access I had a Format function around a date, so now I am using a convert function which takes the form of convert(datetime,TABLE_NAME.COLUMN_NAME,n).
No matter what the number n is, the resultant date always comes out the same in m/d/yyyy format. This appears to go against everything I've read so far and cannot for the life of me work out what is going on.
Can anyone help
|
xp_sendmail not working
(135 Views)
i am having trouble testing my sql mail. i set everything up between my exchange client and my mapi settings...i can run the test connection in sql server 7, but when i write the test query to send an email message, the query analyzer says that it is executing the query...but never stops. any help would be appreciated. here's the query i'm using.
xp_sendmail @recipient = 'aliaga61@earthlink.net', @message = 'test'
|
Help on stored procedure that inserts & selects
(283 Views)
hello,
I'm working on a page that has a grid and a calendar. I want to have the grid display data based on what the user clicks in the grid. It could be one day or many days.
I had a store proc that selected records and it worked alright, if the data was already there. The problem is when I don't have the data there. ( This is a timesheet app and I know my design might not be great - hence my problems).
Anyway, I was trying to combine an insert and select into one store proc, thinking this could help me, but it seems like it only helps me when I have one row of data - unless you know how to do this. How do I make my SP insert work when there is a range of dates Maybe not possible......
Here's my code
Code:
ALTER PROCEDURE dbo.TESTING
(
@PRESENT int,
@TSKey int ,
@Tdate datetime,
@TotHours int,
@Amend datetime ,
@beginTime as datetime,
@endTime as Datetime
)
AS
IF ( @PRESENT =1 )
BEGIN
--TEST
INSERT INTO TIMEDATA (tkey,TDate)
values (@TSKey,@Tdate) where ( tdate = @beginTime)
--this does not work
where tdate > @begindate and tdate < @enddate
END
ELSE
BEGIN
UPDATE TimeData
SET TotHours = @TotHours ,
AuthAMend = @AMend
where tkey = @tskey and TDate=@Tdate
END
|
Incorrect syntax near 'GO'.
(170 Views)
my query:
Code:
'create contents table
sqlContents = "USE [Shem]"
sqlContents = sqlContents & "GO"
sqlContents = sqlContents & "/****** Object: Table [dbo].[contents] Script Date: 03/13/2007 15:27:58 ******/"
sqlContents = sqlContents & "SET ANSI_NULLS ON"
sqlContents = sqlContents & "GO"
sqlContents = sqlContents & "SET QUOTED_IDENTIFIER ON"
sqlContents = sqlContents & "GO"
sqlContents = sqlContents & "SET ANSI_PADDING ON"
sqlContents = sqlContents & "GO"
sqlContents = sqlContents & "CREATE TABLE [dbo].[contents]("
sqlContents = sqlContents & " [idkey] [int] IDENTITY(1,1) NOT FOR REPLICATION NOT NULL,"
sqlContents = sqlContents & " [contents] [varchar](max) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,"
sqlContents = sqlContents & " [tags] [varchar](max) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,"
sqlContents = sqlContents & " [userID] [int] NULL,"
sqlContents = sqlContents & " [hiddenTags] [varchar](max) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,"
sqlContents = sqlContents & " [title] [varchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,"
sqlContents = sqlContents & " [theDate] [datetime] NULL,"
sqlContents = sqlContents & " [timekey] [varchar](max) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,"
sqlContents = sqlContents & "CONSTRAINT [PK_contents] PRIMARY KEY CLUSTERED "
sqlContents = sqlContents & "("
sqlContents = sqlContents & " [idkey] ASC"
sqlContents = sqlContents & ")WITH (PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]"
sqlContents = sqlContents & ") ON [PRIMARY]"
sqlContents = sqlContents & "GO"
sqlContents = sqlContents & "SET ANSI_PADDING OFF"
unfortunately I don't know which GO
|
UPDATE: worked around the code = 3001 error
(228 Views)
I replaced the recordset command line with adCmdTableDirect and it fixed that error right up. I have another error now.. I'm a SQL noob any help is appriciated.
-- Database error
Procedure = usp_Childs_Name
The database could not retrieve the Birth record(s) because of the following unexpected error:
Procedure 'usp_Childs_Name' expects parameter '@ChildsLast', which was not supplied.
Microsoft OLE DB Provider for SQL Server
Return code = -2147217904
|
Stored Procedure email not working
(154 Views)
Here is my stored procedure:
[highlight=sql]
CREATE PROCEDURE [sproc_sales_referral_reminder] AS
set nocount on
IF exists(SELECT TOP 1 1
FROM dbo.tbl_sales_referrals INNER JOIN
dbo.tbl_hr_emp ON dbo.tbl_sales_referrals.referred_to = dbo.tbl_hr_emp.hr_emp_id INNER JOIN
dbo.tbl_hr_emp tbl_hr_emp_1 ON dbo.tbl_sales_referrals.referred_for = tbl_hr_emp_1.hr_emp_id
WHERE (dbo.tbl_sales_referrals.qualified_csr = 'Pending') AND (dbo.tbl_sales_referrals.submit_date
|
SQL Aggregate function that combines like CONCAT
(198 Views)
all,
I'm trying to find a way to query a database, using several GROUP BY's, but I need the IDENTITYCOL to be put in an aggregate function that will give me a string representation of each IDENTITYCOL value. Something in between a SUM and a CONCAT. So basically if I have records with IDENTITYCOL's 1, 3, 4 and 5, I'd like to be able to call THISFUNCTION(IDENTITYCOL) AS Expr1 and get Expr1 to be "1,3,4,5". Is there anything remotely like this
|
How to make it work when the server isnt where i am...
(306 Views)
Hi.
I am a total newbie to this so please excuse my dumbness
I am renting webservices from somewhere and have a website going there. I have made a form now on my site and want it delivered and processed with ASP.
What I can't seem to figure out is how to get on to that server and make all the settings and stuff necessary (reading the asp-book and it doesnt fit my needs) to make this work.
I have the two documents done and written. The form.asp and thankyou.asp. Do I need one more, to get the information delivered to me
How do I put these in the correct folder (can I just use ws_ftp pro for that too) What should that folder be And do I need any special settings or something
I have no idea what I am doing here.
Please help me.
, Did I post this in the wrong forum Since no-one has answered yet...
|
How to select from a table that's 'named' in another?
(190 Views)
I'm trying to do a select on one of a dozen tables, but I don't know which table to use until I look it up in another. However, even once I get the string that names the table I want from a subquery, I don't know how to access it
eg:
select * from ( select Tablename from ChargesConfig where ID = 13 )
This returns a column with one value, but that value is a string which is the name of another table, which I want to select from somehow...
Perhaps I need to do a join or something
|
|
