Noob: Why Does This Query Work?
Jul 23, 2005
I'm wondering how/why this query works. Trying to get my head wrapped
around SQL. Basically the Query deletes from the Import table all
records that are already in FooStrings so that when I do an insert from
the FooStringsImport table into the FooStrings table, then I won't get
primary key violations.
DELETE FROM FooStringsImport
WHERE EXISTS
(SELECT * FROM FooStrings
WHERE FooStringsImport.FooKey = FooStrings.FooKey)
It seems to work fine, but I'm wondering about how the EXISTS keyword
works.
(SELECT * FROM FooStrings
WHERE FooStringsImport.FooKey = FooStrings.FooKey)
This part is going to return only records from FooStrings correct? Or
does it do a cartesian product since I've specified more than one table
in the WHERE statement?
I wonder if it only returns records in FooStrings, then I don't see how
a record from FooStringsImport would "EXISTS" in the records returned
from FooStrings.
The reason I wondered about the cartesian product is because, if only
FooStrings is specified in the FROM part of the SELECT statement, then
I was thinking it is only going to return FooString records. These
records would then be returned by the select statement to the WHERE
EXISTS, which would look for FooStringImport records, but would find
none because the select statement only returned FooString records.
I'm guessing maybe because it has to do a cartesian product to evaluate
the WHERE Pkey's equal, then the "SELECT *" just goes ahead and gets
ALL the fields, and not just those in FooStrings.
FooStrings and FooStringsImport are identically structured tables,
where the FooKey is set as the primary key in each table:
CREATE TABLE [dbo].[FooStrings] (
[FooKey] [bigint] NOT NULL ,
[Name] [char] (40) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[StartDate] [datetime] NULL ,
[EndDate] [datetime] NULL ,
[Code] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
GO
Thanks in advance. I'm so appreciative of the help I've gotten here,
as I've been able to write several very useful queries on my own now
after everyones help and plus lots of reading on my own.
View 4 Replies
ADVERTISEMENT
Apr 12, 2007
I've created a new table in ASPNETDB.mdf called 'customerInfo'. Most of the fields are, for the moment, data that exists in one of the existing ASPNET tables eg Membership_UserId (primary key), Membership_Email, Users_UserName etc.; the list goes on. I have put a new column 'amountspent' - just to test that I can do what I think I can do, however the table doesn't display any data. Am I OK in thinking that I can use the aspnet data in my own tables and reference through foreign keys to them. I've made sure that the procedure that I used to put the table into a gridview on the webpage is OK as I did the same thing to the membership table and that displays all of its data. Can someone either explain the steps I need to make this happen correctly or point me in the direction of a noob type walkthrough.
Thanks
View 1 Replies
View Related
Dec 22, 2005
Question:
How do you concatinate fields in SQL? I tried in a SQL query, but did not have any success. I'm coming from MS Access where it is simple to add fields together.
*** MS Access SQL ***
SELECT Table1.Field1, Table1.Field2, Expr1: Table1.Field1 & " - "& Table1.Field2
FROM Table1
*** END ***
You could add fields and put characters between the two different fields like a space and/or some sort of seperation character (/,-,_,:) in an expression. Then you could call that expression from whatever you wanted.
Is there a way to do this in SQL Express 2005? Do I do it in a query? Can I add seperation characters?
Thanks
PS. If you have a good link to the basics of SQL queries, I would look at that as well.
View 6 Replies
View Related
Oct 22, 2007
Hi
I have a table which has the column [itemNumber] Which contains numbers from 000 to 999. I have another table which has the UPC data for given items
I am trying to get results from my query that will show me every number in the itemNumberSet table that does not already exist (in the substring) of the UPCcode column.
By using the query below i am able to retrieve the opposite, and it works by returning results that do exist in the UPCcode column. But I cannot seem to get it to do the opposite which is what i am after. I figured it would be as simple as using NOT IN but that returned 0 results.
SELECT itemNumber FROM itemNumberSet
WHERE itemNumber IN (select SUBSTRING(UPCcode, 9, 3) FROM itemUPCtable)
ORDER BY itemNumber
Thanks for any suggestions you might have.
J
View 3 Replies
View Related
Apr 30, 2007
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
View 2 Replies
View Related
Aug 3, 2004
What I want to do is update 1 table based on data in another. This query works great in Access XP, but I cannot make it work in SQL 2000 sp3. Is there a different way to populate table A with direct data from table B in an update?
Any help is appreciated
UPDATE [Clients - Complete], AssociateDirectory
SET [Clients - Complete].Phone = AssociateDirectory.WorkPhoneNumber
WHERE [Clients - Complete].Name=AssociateDirectory.LastName And [Clients - Complete].FNAME=AssociateDirectory.FirstName;
View 2 Replies
View Related
Jan 28, 2008
hello everybody!
I'm using ssce.
I'm trying to add the followed query in my table adapter but in the query builder (VS), when I choose execute query, I get a couple of textboxes but whatever I type in, I get an error message "The parameter is wrong"
here is the query:
SELECT EntryID, UserInfo, Kind, ActionDate, Span, SalaryAtAction
FROM Logbook
WHERE
((CASE
WHEN @Enter = 0 AND @Exit = 0 THEN 0
WHEN @Enter = 1 AND @Exit = 0 AND kind = '?????' THEN 1
WHEN @Enter = 0 AND @Exit = 1 AND kind = '?????' THEN 1
WHEN @Enter = 1 AND @exit = 1 AND kind IN ('?????', '?????') THEN 1 ELSE 0 END) = 1)
what I try to implement is, a table that one of it's columns is bit datatype. the end-user can filter out the table by these parameters whether to show only the true rows or only the false rows or both.
would you suggest to use a different various of the query?
(a help button is provided on the error message, it refers to ]ms-help://MS.VSCC.v90/MS.MSDNQTR.v90.en/dv_vdt03/html/f0bf6b59-c245-4340-a869-5d7278fe0ae5.htm in case it helps anybody).
Shimi
View 1 Replies
View Related
Jul 24, 2006
Hi,
I have a query thatI need to make into one query for the sake of an application reading only one cursor.
Here's the syntax:
select (select distinct(x.amount) from escrow k inner join e120 x on k.escrow = x.escrowinner join a10 g on x.escrow = g.escrow where k.ftype = 'S' group by x.amount, g.officer) As New,a.officer as Officer, count(distinct(j.fstatus))as Escrow_Type, count(distinct(j.amount))as Amount, count(distinct(d.open_date))as [Open], count(distinct(d.close_date)) as Closed, count(distinct(can_date))as Cancelled
from a10 a inner join escrow d on a.escrow = d.escrowinner join e120 j on j.escrow = d.escrow where j.id_scr = 'e21' and j.fstatus = 'PAID' group by a.officer
The error message i'm recieving is the following:
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.
Any ideas? any suggestions would be great.
Thanks
View 3 Replies
View Related
Apr 18, 2004
My SQL Query Analyzer just runs the clock but when the clock stops it fails to open the tool. Both my SQL Server 2000 Developer and Enterprise Edition do this. Can anyone suggest how I can get this working? It worked fine in SQL 7.
View 1 Replies
View Related
Dec 6, 2007
select distinct
a.Patients,
b.Patients,
a.pct,
b.pct
from
(
select count(*) as Patients, [pct of res] as pct
from testing
where [18 week wait] <= 18
group by [pct of res]
) as a right outer join
(select distinct[pct of res] from testing) as c on a.pct=c.[pct of res]and a.pct <> 'null' --is not null
(select count(*) as Patients, [pct of res] as pct
from testing
where [18 week wait] >18
group by [pct of res]
) as a left outer join as b on c.[pct of res]=b.pct
View 6 Replies
View Related
Jul 20, 2005
I am having alot of trouble with a union query Im trying to create.The bizarre thing is I initially created this query and managed tosave it as a viewSELECT ID, DepartureDate, City, Country, Region,fkDepartureAirport, CONVERT(MONEY, Price) AS Price, '1' AS TypeFROM dbo.vHolidayUNION ALLSELECT ID, ValidTo, DestCity, Country, Region, fkDepartureAirport,Price, '2' AS TypeFROM dbo.vFlightHowever when I tried to update the view to this:SELECT ID, DepartureDate, fkCity, City, fkCountry, Country,Region, fkDepartureAirport, CONVERT(MONEY, Price) AS Price, '1' ASTypeFROM dbo.vHolidayUNION ALLSELECT ID, ValidTo, fkCity, DestCity, fkCountry, Country, Region,fkDepartureAirport, Price, '2'FROM dbo.vFlightit comes up with the error: view definition includes no output columnsor includes no items in the from clause.Any ideas??????All suggestions appreciatedAlly
View 1 Replies
View Related
Apr 2, 2008
Hi all,
I have a table with 2 columns (simplified for this question):
Date and Action
Date is normal datetime, Action is varchar and can be either Sent or Received. The data can look like this:
1. '2008-04-02 15:09:09.847', Sent
2. '2008-04-02 15:09:10.125', Sent
3. '2008-04-02 15:09:11.125', Received
4. '2008-04-02 15:09:12.459', Received
5. '2008-04-02 15:09:15.459', Received
6. '2008-04-02 15:09:24.121', Sent
7. '2008-04-02 15:09:28.127', Received
I want to find a pair of rows Sent-Received with the Max Date difference, where Received follows immediately after Sent.
It means in our example, valid are only lines
2. and 3. or
6. and 7.
In this example, rows 6. and 7. have bigger difference of Dates, so the result of the query should be
6. '2008-04-02 15:09:24.121', Sent, 7. '2008-04-02 15:09:28.127', Received
It is probably easy, I just cannot work it out. Suprisingly finding MIN was quite easy. Thanx for any tips!
Regards,
alvar
View 6 Replies
View Related
Dec 21, 2005
In VB, I have this code...
lSQL = "SELECT * FROM OPENQUERY(liorder,'SELECT a.KF_ORDER_NO AS OrdNo, f.KU_NAME AS Customer, " & _
"a.KF_ORDER_POS AS Pos, a.KF_SCHEIB_NR AS Pane, a.KF_QTY AS Qty, d.BREITE*d.HOEHE/1000*a.KF_QTY AS SQM, " & _
"a.KF_QTY*d.SUM_NETTO AS Val, a.KF_FERT_QTY AS Done, d.BREITE*d.HOEHE/1000*a.KF_FERT_QTY AS DoneSQM, " & _
"a.KF_FERT_QTY*d.SUM_NETTO AS DoneVal FROM LIORDER.AUF_KOPF c, LIPROD.KAPA_AUS_FERT a, LIORDER.KUST_ADR f, " & _
"LIORDER.AUF_POS d WHERE (f.KU_VK_EK = 0) AND (a.KF_SCHR_NR = 12) AND (c.AUF_NR = a.KF_ORDER_NO) AND " & _
"(c.KUNR = f.KU_NR) AND (a.KF_ORDER_NO = d.AUF_NR) AND (a.KF_ORDER_POS = d.AUF_POS) AND (f.KU_NAME IS NOT NULL) " & _
"GROUP BY a.KF_ORDER_NO, f.KU_NAME, a.KF_ORDER_POS, a.KF_SCHEIB_NR, a.KF_QTY, d.BREITE, d.HOEHE, a.KF_FERT_QTY, d.SUM_NETTO')"
Set RS = New ADODB.Recordset
RS.Open lSQL, DB
Do While Not RS.EOF
Select Case RS!Pane
Case "0" Or "1"
glassSQL = "SELECT IDNR, GL_BEZ FROM OPENQUERY(liorder, 'SELECT a.IDNR, a.GL_BEZ FROM LIORDER.GLAS_DATEN a, LIORDER.AUF_POS b " & _
"WHERE a.IDNR = b.GLAS1 AND b.AUF_NR = " & RS!OrdNo & " AND b.AUF_POS = " & RS!Pos & "')"
Set glassRS = New ADODB.Recordset
glassRS.Open glassSQL, DB
SQL = "INSERT INTO PendingLamination (OrdNo, Customer, Pos, Code, GlassDesc, Qty, SQM, Val, Done, DoneSQM, DoneVal) " & _
"VALUES ('" & RS!OrdNo & "', '" & Apostrophe(RS!Customer) & "', '" & RS!Pos & "', '" & glassRS!IDNR & "', '" & glassRS!GL_BEZ & _
"', '" & RS!Qty & "', '" & RS!SQM & "', '" & RS!Val & "', '" & RS!Done & "', '" & RS!DoneSQM & "', '" & RS!DoneVal & "')"
DB.Execute SQL
Case "2"
glassSQL = "SELECT IDNR, GL_BEZ FROM OPENQUERY(liorder, 'SELECT a.IDNR, a.GL_BEZ FROM LIORDER.GLAS_DATEN a, LIORDER.AUF_POS b " & _
"WHERE a.IDNR = b.GLAS2 AND b.AUF_NR = " & RS!OrdNo & " AND b.AUF_POS = " & RS!Pos & "')"
Set glassRS = New ADODB.Recordset
glassRS.Open glassSQL, DB
SQL = "INSERT INTO PendingLamination (OrdNo, Customer, Pos, Code, GlassDesc, Qty, SQM, Val, Done, DoneSQM, DoneVal) " & _
"VALUES ('" & RS!OrdNo & "', '" & Apostrophe(RS!Customer) & "', '" & RS!Pos & "', '" & glassRS!IDNR & "', '" & glassRS!GL_BEZ & _
"', '" & RS!Qty & "', '" & RS!SQM & "', '" & RS!Val & "', '" & RS!Done & "', '" & RS!DoneSQM & "', '" & RS!DoneVal & "')"
DB.Execute SQL
Case "3"
glassSQL = "SELECT IDNR, GL_BEZ FROM OPENQUERY(liorder, 'SELECT a.IDNR, a.GL_BEZ FROM LIORDER.GLAS_DATEN a, LIORDER.AUF_POS b " & _
"WHERE a.IDNR = b.GLAS3 AND b.AUF_NR = " & RS!OrdNo & " AND b.AUF_POS = " & RS!Pos & "')"
Set glassRS = New ADODB.Recordset
glassRS.Open glassSQL, DB
SQL = "INSERT INTO PendingLamination (OrdNo, Customer, Pos, Code, GlassDesc, Qty, SQM, Val, Done, DoneSQM, DoneVal) " & _
"VALUES ('" & RS!OrdNo & "', '" & Apostrophe(RS!Customer) & "', '" & RS!Pos & "', '" & glassRS!IDNR & "', '" & glassRS!GL_BEZ & _
"', '" & RS!Qty & "', '" & RS!SQM & "', '" & RS!Val & "', '" & RS!Done & "', '" & RS!DoneSQM & "', '" & RS!DoneVal & "')"
DB.Execute SQL
End Select
ProgressBar1.Value = ProgressBar1.Value + 1
If ProgressBar1.Value = 800 Then
ProgressBar1.Value = 0
End If
RS.MoveNext
Loop
...and I'm planning to use the same flow of logic in the SQL Server Agent using T-SQL. Is there any way I can do it?
View 6 Replies
View Related
Feb 9, 2004
Hi experts,
I'm using MS SQL 2000. The closest i could get to having pl/sql within ms sql was tru its stored procedures feature.
I created a new stored procedure within the built-in Northwind database. I pasted the following inside:
-----------------------------------------------------------------
declare
vname Employees.firstname%TYPE;
begin
SELECT firstname INTO vname FROM Employees
WHERE firstname = 'Nancy';
dbms_output.put_line ('Name is ' || vname);
EXCEPTION
when NO_DATA_FOUND then
dbms_output.put_line ('no data found');
when TOO_MANY_RECORDS then
dbms_output.put_line ('too many records');
END;
-----------------------------------------------------------------
I checked the syntax and i get some error about the employees table.
Error 155: 'Employees' is not a recognized cursor option
Any idea?
Thanks..
View 1 Replies
View Related
Aug 25, 2005
Mike writes "I have a string, >!~[99DC# 4.12$2251.% 1.63& 3.58'2150.(-2050.>/~]
and I would like to extract parts of it and send it to a SQL database. here is the layout and data that should be in each column. The Data column will be filled in automatically, but once that happens I would like the other columns to get their data from the Data column.
Data = >!~[99DC# 4.12$2251.% 1.63& 3.58'2150.(-2050.>/~]
Display_Address = 99
CE = 4.12
TPK = 2251
Si= 1.63
C = 3.58
TPL = 2150
TPS = 2050
Can anyone tell me how to do this?"
View 1 Replies
View Related
Jun 7, 2007
/* hi people im doing a task and im not sure about some things. could u check if what im doing is good plz.*/
The task is:
Produce a list of all female employees who earn more than the average salary of the male employees in the company. Display employee number, first name and last name.
The table: emp
Attributes: EMPNO, FIRSTNAME, LASTNAME, SEX, SALARY
What i came up with:
selectempno, firstname, lastname
fromemp
whereavg(salary)>any(selectavg(salary)
fromemp
wheresex='M')
group by sex
havingsex = 'F'
View 3 Replies
View Related
Aug 7, 2007
Here is my code to connect, any help would be appreciated. I am a total noob at this.
############### Code
<?php
$host="localhost"; // Host name
$username=""; // Mysql username
$password=""; // Mysql password
$db_name="orderofb_test"; // Database name
$tbl_name="orderofb_test.members"; // Table name
// Connect to server and select databse.
mysql_connect("$host", "$username", "$password")or die("cannot connect");
mysql_select_db("$db_name")or die("cannot select DB");
// username and password sent from signup form
$myusername=$_POST['myusername'];
$mypassword=$_POST['mypassword'];
$sql="SELECT * FROM $tbl_name WHERE username='$myusername' and password='$mypassword'";
$result=mysql_query($sql);
// Mysql_num_row is counting table row
$count=mysql_num_rows($result);
// If result matched $myusername and $mypassword, table row must be 1 row
if($count==1){
// Register $myusername, $mypassword and redirect to file "login_success.php"
session_register("myusername");
session_register("mypassword");
header("location:login_success.php");
}
else {
echo "Wrong Username or Password";
}
?>
This is the error message I receive.
############### Code
Warning: mysql_connect() [function.mysql-connect]: Access denied for user 'nobody'@'localhost' (using password: NO) in /home/orderofb/public_html/php/checklogin.php on line 11
cannot connect
I changed all the permissons to 777 and I still receive this error.
$tbl_name="orderofb_test.members"; // Table name
Is this the correct table name? Here is a pic of my phpmyadmin
http://mnetcs.com/thumb/storage/b3583134.JPG
View 2 Replies
View Related
Nov 23, 2007
Edit: Newer mind. I tested this query more after writing this, and now it seems to work!I hope it continues to work. In following query, else is never executed.CREATE PROCEDURE Put_into_basket( @Product_code varchar(20))ASBEGIN SET NOCOUNT ON;IF NOT EXISTS(SELECT * FROM dbo.t_shopping_basket WHERE Product_code=@Product_code) BEGIN INSERT dbo.t_shopping_basket (Product_code, Name,Price) SELECT Product_code, Name,Price FROM dbo.t_product WHERE Product_code= @Product_code ENDELSE --this part is never executed BEGIN UPDATE dbo.t_shopping_basket SET Quantity=Quantity+1 WHERE Product_code=@Product_code ENDENDGO The query should test if there is a record or row with Product_code=@Product_code. If there is not, that is the first part, one such row is inserted. Quantity has a default value of 1. Insertion works, one row is inserted. At least sort of. If there is already record, That's later part, Quantity is increased by 1. That too works, if ran separately.But when I test query, it never runs the quantity+1 part.
View 3 Replies
View Related
Oct 15, 2014
I am currently stuck on how to make this 2 separate query work together, both work as i want them to individually, please note the syntax is according to a application i use that uses mysql to manipulate columns in an imported csv file.
CONCAT('at-',
REPLACE([CSV_COL(18)],'http://www.homebuy.co.uk/product.php/','')
)
removes last character i.e. /
SUBSTRING([CSV_COL(18)], 1, CHAR_LENGTH([CSV_COL(18)]) - 1)
basically i need these 2 to work together to give me an output like this
at-09fd8903
from the this. URL...url above minus the "" as i said both work on there own, but not together.
View 1 Replies
View Related
Feb 27, 2007
Not sure which forum too put this question
I have recently upgraded my Access Database too use SQL server 2005 using the upsizing wizard. I have selected too use SQL Linked tables instead off the ADP project for ease off conversion. So now the tables are on the SQL server and the queries are still local.
On off my Access forms which returns a datasheet view by accessing the a query is now running really slowly now SQL server is the backend, I was wondering if I can put an index on one of the tables too speed it up, as the query is local too Access will this work? Just wondering if anyone ever come across this.
View 1 Replies
View Related
Apr 23, 2008
I have my db in a pocket pc wm5.0, I just want to make a simple query
select * from table
where pk = '1'
but this doesnt work, if you tried any other field else than the primary key
it works... WHY???
would it be a problem with the sdf file? help please!!!
View 3 Replies
View Related
Nov 8, 2007
Hi all,
I have a script to bulk copy data from my table into textfile. This bcp script will be executed by xp_cmdshell. When I try to execute the scipt inside query windows it returned an error : Error = [Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified. But when I test the bcp script in command prompt it will execute successfully
Is there any misconfiguration ? (I'm using sqlexpress) thanks in advance.
Best regards,
Hery
View 9 Replies
View Related
Nov 7, 2006
Hello All,
I am trying to run the below query in SSIS, However it does not work, but when I try to run the same query in Oracle client it works fine. Here is the following query:
select 'AAA-'||OWNER AS SOURCE,
table_name,
column_name,
SUBSTR(data_type,1,50) DATA_TYPE ,
SUBSTR(decode(data_type,'NUMBER', DATA_PRECISION, DATA_LENGTH),1,20) DATA_LENGTH
from all_tab_cols
where owner='XXX'
ORDER BY TABLE_NAME, COLUMN_ID
Here ARE the following errorS I get when running from SSIS:
[ORA_AAA_XXX [147]] Error: There was an error with output column "SOURCE" (612) on output "OLE DB Source Output" (157). The column status returned was: "The value could not be converted because of a potential loss of data.".
[ORA_AAA_XXX [147]] Error: The "output column "SOURCE" (612)" failed because error code 0xC0209072 occurred, and the error row disposition on "output column "SOURCE" (612)" specifies failure on error. An error occurred on the specified object of the specified component.
Any help?
Regards,
Raju
View 3 Replies
View Related
Apr 19, 2007
Hi!
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
View 1 Replies
View Related
Sep 26, 2007
I am trying to create a script that will return information from 4 different tables in a database using different where clauses.
This is working correctly, however the primary table the query uses has multiple entries for different people and i only want to return the last row per person.
I have got this to work using a MAX(t.column) statement, but when used in conjunction with all the where clauses things go wrong.
This is what i have
Code:
CREATE VIEW Firereport1 AS
select max(t.punch_id) as punch, e.EMPLOYEE_FIRSTNAME, e.EMPLOYEE_LASTNAME, c.CLOCK_NAME, t.PUNCH_TIME, d.DEPT2_DESCRIPTION
FROM TM_PUNCH t, EMPLOYEE e, COMM_CLOCK c, DEPT2 d
WHERE t.PUNCH_EMPID = e.EMPLOYEE_ID
and e.EMPLOYEE_ONCLOCK <> 'False'
and e.EMPLOYEE_LASTTIMELOC = c.CLOCK_ID
and e.EMPLOYEE_LASTDEPT2 = d.DEPT2_ID
group by e.EMPLOYEE_FIRSTNAME, e.EMPLOYEE_LASTNAME, c.CLOCK_NAME, t.PUNCH_TIME, d.DEPT2_DESCRIPTION
GO
This returns multiple entries because there are multiple unique entries for t.punch_time.
What i was thinking is i need a where statement on t.time_punch, d.dept2_description, c.clock_name
that says something along the lines of where return t.punch_time where t.punch_id=punch (**substitute correct column names**)
This is a sample of the tm_punch table
Code:
PUNCH_ID PUNCH_SEQNR PUNCH_EMPID PUNCH_DATE PUNCH_TIME PUNCH_MODIFIED PUNCH_PC PUNCH_CLOCKID PUNCH_DATA1 PUNCH_DATA2 PUNCH_DATA3 PROJECT_ID
141 0 52 2007-07-05 00:00:00 603 -1 0 0 0 0 0 NULL
142 0 52 2007-07-05 00:00:00 909 -1 0 0 0 0 0 NULL
143 0 52 2007-07-05 00:00:00 911 -1 0 0 0 0 0 NULL
144 0 52 2007-07-05 00:00:00 914 -1 0 0 0 0 0 NULL
If the query works correctly what should be returned is just the line with id 144
can anyone point out where i'm going wrong
View 3 Replies
View Related
Aug 23, 2006
hi,
just wanted to ask the most efficient way to query for the following:
say i have a table with the following columns A, B, C, D, E.
i want to find the records whose A, B and C are the same but at least D or E are different and then display all the columns (A to E) for these records.
thank you!
g11DB
View 8 Replies
View Related
Nov 19, 2007
Hi Everybody.
So as the subject states, I'm a total sql noob. I have a database in SQL 2k5 that was transfered over from an acces DB. In the sql db there are a few fields using nvarchar(255) but the data held is actually dates and times. I need to convert these over to datetime format but do no know how. Also, not all rows have a full mm/dd/yyyy hh:mm format. some hold only the date and can be seen as either m/d/yyyy or full out mm/dd/yyyy format. If anyone can help with this that would be great.
Thanks
View 2 Replies
View Related
Dec 14, 2007
Hi
I installed sql sever 2005 enterprise edition in my computer.Where is the management studio? Is it a different program? Can I install the express edition of the management studio?Help please.
View 6 Replies
View Related
Mar 1, 2008
Ok so i have something like this for a simple quary,
select avg(reorderlevel)/ (avg(QOH)/(avg(reorderlevel)*2)) as "Needed qty",ItemCategory from item
group by ItemCategory
What I basicly need to do is to output the quantity needed for QOH to be 2 times the amount of reorderlevel. then grouped by diffrent catagories.
Anyways I have the formula done and it should work except for some reason "(avg(QOH)/(avg(reorderlevel)*2))" gives me 0 when in fact it should give me 0.728383 or w/e. Im wondering if its omiting the decimals or what??
Because if i run that code it tells me i cant divid by zero
Sorry im new at this and might be doing this all wrong so go easy on me =/
Thanks a lot
View 7 Replies
View Related
Apr 30, 2008
Apologies first up -I dont do security so slapped wrists all round, but I have a little situation that I need to take seriously so I thought I'd try do the job right for a change.
Scenario: I have a stored procedure that I want one, and only one, 'thing' to have access to. The 'thing' will be one and only one SSIS package invoked by external scheduling software so basically a call to the dtexec utility to run the package stored in the file system. At runtime therefore, this package has an ExecuteSQL task which connects to the server and runs the sproc. Given windows authentication how do I ensure that the ONLY user allowed to execute the stored procedure is whatever user happens to invoke the package but that this same user can never log in to sql server and execute the stored procedure. In even simpler terms, I want nothing on earth to run that stored procedure nor select the data from the table other than this package when it runs, regardless of who runs it, it is only internal processing by the package that gets the data.
View 1 Replies
View Related
Mar 17, 2007
My Table Structure
Category_ID Number
Parent_ID Number <----Category_ID reports to this colum
Category_Name Varchar....
MY QUERY <---I replaced the query above with my data
=============================
WITH Hierarchy(Category_ID, Category_Name, Parent_ID, HLevel)
AS
(
SELECT Category_ID, Category_Name, Parent_ID, 0 as HLevel FROM Dir_Categories
UNION ALL
SELECT SubCategory.Category_ID
, SubCategory.Category_Name,
SubCategory.Parent_ID,
HLevel + 1
FROM Dir_Categories SubCategory
INNER JOIN Hierarchy ParentCategory
ON SubCategory.Parent_ID = ParentCategory.Category_ID )
SELECT Category_ID,
Category_Name = Replicate('__', HLevel) + Category_Name,
Parent_ID,
HLevel
FROM Hierarchy
My OUTPUT============
All the categories under reporting to Parent_ID 0 or continuous, then the ones reporting to 1 and so fourth. Subcategories are not showing within their main categories. I AM GOING NUTS WITH THIS.
Can you help me please?
View 12 Replies
View Related
Jan 28, 2008
As in title. Is there any tool? I'm asking beceuse, I have some big bases, and processing may take a lot of time (at least few hours), and I'll be glad if it's possibility to know estimate time before query runs. I'm using MsSql 2005 Developer edition.
View 14 Replies
View Related
Jan 23, 2004
i have a query which works fine when i run it from Query Analyzer.
when i try to create a DTS script "Exectute SQL Task", then this SQL does not work within that task, and i dont know why:
ALTER TABLE T_DESCRIP COLUMN EYE varchar(3) NOT NULL
It throws and error that says:
"Error Description: Deferred prepare could not be completed. Statement could not be prepared. Incorrect syntax near the keyword 'COLUMN'.
View 7 Replies
View Related
