Help On Sql Select Statment
Apr 20, 2007
Okay here is the deal. I need to take all data from tbl 1 and match it to data in 3 other tbls. I need to have it return everything back to me even if it is null....
IE tbl 1 I match the invoice_num to tbl2 site_id and then tbl2 marekt to tbl3 market. however even if tbl1 invoice_num dose not match tb2 site_id I still need to have it retun to a null value to the site_id. Here is what I have so far. This will return everything where the invoice_num and site_id match.
Code:
Select distinct t1.ID,t1.Deposit_date,t1.Ref_Num,t1.Company,t1.check_num,t1.Check_Date,t1.Check_Date,t1.Check_Total, t1.Individual_PMT,t1.Invoice_Num,t1.Invoice_Desc,
t2.site_id,t3.CompanyCode,t3.CostCenter
From( PMTK_tbl as t1
Left Join Leaseinfo as t2 on t2.site_id = t1.Invoice_Num)
inner Join CostCenters AS t3
on t2.market = t3.market and t2.market_region = t3.RegionCode
View 4 Replies
ADVERTISEMENT
Mar 20, 2007
Hello, Is there an SELECT statement to just return the last 100 row in my tables? I have about 500 rows in my tables and I only need the info on the last 100 rows.
Thanks
Steve
View 4 Replies
View Related
Oct 2, 2007
hello,
i have a page "Picture.aspx?PictureID=4"
i have a FormView witch shows details about that picture and uses a stored procedure with input parameter the "@PictureID" token from query string
the Pictures table has among other rows "PictureID", "UserID" - uniqueidentifier - from witch user the picture belongs to
i have a second FormView on the same page, witch should show "other pictures from the same user" and uses a Stored Procedure
how should i write that stored procedure...frist to take the UserID from the picture with PictureID=4, then to pass it as input parameter and select the pictures witch has as owner the user with that UserID, and if can be done, to avoid showing the PictureID=4 again
a solution should be to add at querry the UserID too, but i want to avoid that
any sugestion is welcomed, please help me
THANKS
View 5 Replies
View Related
May 23, 2008
whats wrong with this statment?
declare @alpha as nvarchar(50)
set @alpha = select top 1 monthyear from dbo.Performance_Indicators_Rolling order by recordkey
I get an error
Incorrect syntax near the keyword 'select'.
Can anyone please help?
View 3 Replies
View Related
Jul 17, 2007
Hi all,
i'm new to SQL but i've been asked to write an SQL statement to select the latest numeric version value(in this case version 3) from this table, any help?
Name Version Episode
John 4c 60
John 4b 50
John 4a 40
John 3 30
John 2 20
John 1 10
Regards
View 7 Replies
View Related
Mar 8, 2008
Im trying to get a count of all user logins to display in a report - I have a column, count, which has a value of 1 for each record entered.
Select firstname, lastname, count(count) as TotalLogins Order by TotalLogins. But the count is always appearing as 1.
Report should look like:
John Smith 132
Jane Doe 101
Doris Day 99
View 19 Replies
View Related
Jan 18, 2008
Hello all
I create sp
--------------------code----------------------
ALTER procedure [dbo].[uspInviteGroup] --uspInviteGroup 'fdi'
@strUserId nvarchar(50)=null
as
select GroupName as 'strGroupName',GroupFounder as 'strGroupFounder'
from SITE_MemberGroupswhere GroupId=
(select GroupId from SITE_GroupMember
where userId=@strUserId)
--------------------code----------------------
but when I tested the above sp --uspInviteGroup 'fdi'return this error
------------------error---------------------
Msg 512, Level 16, State 1, Procedure uspInviteGroup, Line 6
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.
------------------error---------------------
in my case the second select statment return 2 value,I need the first select statment return two row
how can I do that?
thank you
View 3 Replies
View Related
Feb 18, 2007
Hello,
Why when I make a custom select statment with multiple tables, that when I test my query I'm shown the same rolls multiple times. When I make a custom query with just one table everything works just fine. I don't have any primary keys or restraints or whatever.
SELECT * FROM table1, table2, table3 Does not work(shows each row multiple times)
SELECT * FROM table1 works just fine (shows each row just one time)
Thanks
Steve
View 1 Replies
View Related
Jun 8, 2006
I need to add some cases to the select statment for cpeorderstatus:
Here is my Select statement:
"SELECT O.ORDERID, C.FIRSTNAME, C.LASTNAME, O.CLIENTORDERID AS CRMORDERID, TO_CHAR(O.ORDERDATE, 'YYYYMMDD') AS CPEORDERDATE, TO_CHAR(O.SHIPDATE, 'YYYYMMDD') AS SHIPDATE, O.TRACKINGNBR AS TRACKINGNUMBER, O.SHIPNAME AS CARRIER, OI.ITEM AS CPEORDERTYPE, OI.QTY, O.STATUS AS CPEORDERSTATUS, OSN.ORD_SERIAL_NO AS SERIALNUMBER, C.BTN AS BTN, C.FIRSTNAME AS FIRST, C.LASTNAME AS LAST, C.SHIPADDR1 AS ADDRESSLINE1, C.SHIPADDR2 AS ADDRESSLINE2, C.CITY AS CITY, C.STATE AS STATE, C.ZIP AS ZIP, TO_CHAR(R.ISSUEDATE, 'YYYYMMDD') AS ISSUEDATE, R.RMA_ID AS RMANUMBER, R.RMA_REASON AS REASON, TO_CHAR(R.RETURNDATE, 'YYYYMMDD') AS RETURNDATE FROM SELF.ORDERS O, SELF.CUSTOMER C, SELF.ORDERITEM OI, SELF.ORD_SERIAL_NUMBER OSN, SELF.RMA R WHERE O.CUSTID = C.CUSTID AND O.ORDERID = OI.ORDERID AND O.ORDERID = OSN.ORDER_ID (+) AND O.ORDERID = R.ORDER_ID (+) AND (C.CUSTID IN (SELECT C.CUSTID FROM SELF.CUSTOMER C WHERE C.BTN='{0}')) ORDER BY O.ORDERDATE DESC"
I need to add multiple cases to cpeorderstatus, five different cases. Cane anyonye HELP
View 1 Replies
View Related
Jul 20, 2005
Do any of you know if it's possible to get the name of your field fromanother table?E.g.Select FYear as [Select description from YearTable where category=1]I need the name of the fields to be user defined and the only way Ican see this happening is through a table. But now...how do I get thefield name from the table?Any help would be appreciated!!!!Thank you,Susan
View 2 Replies
View Related
Sep 21, 2006
Hi Folks
If I wrote this in QA what does it mean?
Declare @X INT
Set @X = 2
Select (@X & 8)
What & do in this select statment
Thank you
View 5 Replies
View Related
May 1, 2008
can someone read this to me thanks.
SELECT DISTINCT PlanNumber FROM tbmembers WHERE ISNULL(PlanID, '') <> ''
View 4 Replies
View Related
Feb 19, 2008
Hi,
I have a Command that I can not get to work. I am trying to take an AVERAGE of 1 Columb and display it in a label.
ALSO what statement do I add when the Lable that is supposed to display that output is empty. Right now It gives me an Error.comm = New SqlCommand("SELECT * AVG Rating FROM Reviews WHERE CID = " & Request.QueryString("CID"), conn)
Dim SqlDataAdapter1 As New SqlDataAdapter("SELECT AVG Rating FROM Company WHERE CID = " & Request.QueryString("CID"), conn)Dim objReader As SqlDataReader
conn.Open()
objReader = comm.ExecuteReader()
objReader.Read()lblRanking.Text = objReader("Rating")
objReader.Close()
View 3 Replies
View Related
Mar 18, 2005
hi,friend
is it possible to use 'select case' statment in sql query.
give any idea or solution.
thanks in advance.
View 2 Replies
View Related
Oct 19, 2001
I want to be able to have a single select statment:
SELECT TOP 1 Call.JobNum, Call.CallID, Call.Company, Call.LastCallTime
FROM ClientJob INNER JOIN Client ON ClientJob.ClientID = Client.ClientID
INNER JOIN Call INNER JOIN Login ON Call.JobNum = Login.JobNum ON ClientJob.JobNum = Login.JobNum
WHERE (Login.LoginID = 3) AND (Call.Status = 0) AND (DATEDIFF(hh, Call.LastCallTime, getdate()) > 10)
ORDER BY Call.CallID
but with this select statment I also want to set a variable:
declare @variable int
SELECT TOP 1 Call.JobNum, @variable = Call.CallID, Call.Company, Call.LastCallTime
FROM ClientJob INNER JOIN Client ON ClientJob.ClientID = Client.ClientID
INNER JOIN Call INNER JOIN Login ON Call.JobNum = Login.JobNum ON ClientJob.JobNum = Login.JobNum
WHERE (Login.LoginID = 3) AND (Call.Status = 0) AND (DATEDIFF(hh, Call.LastCallTime, getdate()) > 10)
ORDER BY Call.CallID
Now SQL Server does not like this, can not set a variable in a multiple select statment. I NEED to do this all in one step if possible. Any suggestions?
pat
View 1 Replies
View Related
Aug 1, 2006
In my sproc this is wrong :
SELECT @DetailItems= Count(idDetail)
FROM Details
WHERE CheckNum=@CheckNumber
How should i do this?
Create PROC voidCks
@CheckNumbervarchar(30)
AS
DECLARE @DetailItemsint,
@DetailTotalMONEY,
@CheckAmountMONEY,
SELECT @DetailItems= Count(idDetail)
FROM Details
WHERE CheckNum=@CheckNumber
View 3 Replies
View Related
Apr 21, 2007
hey friends!
pleaze help, i am using sql server 2000.i have tried to fix my problem for more than five weeks just to solve for one problem and just still now it is unsolved, opps my due date is almost approaching, i don't know what to do more than what i did, i have search through the net, but still i did not get the correct answer to my problem, friends please just forward your answer to me, it may be best answer to my question .
create table mytable( english varchar(120), tigrigna Nvarchar(120))
insert into mytable values('peace',N'sälam')
insert into mytable values('kiss',N'samä ')
insert into mytable values('to kiss each other',N'täsasamä ')
then
select * from mytable where english='peace'; this works fine
but
select * from mytable where tigrigna=N'sälam'; this doesnot work;
select * from mytable where tigrigna='sälam'; this also doesnot work,
so what should i do to select this unicode select statement?
tigrigna is one of the spoken language in east africa(ethiopia).
Hopefully, i have joined this forum today, and just looking for your reply
I am Looking for your reply !
View 1 Replies
View Related
Mar 7, 2007
Will writes "I have an employees table, which gives all emp. ids. I have a second table, time_log with tasks each employee has logged:
empID date log_time duration etc
===================================
and a 3rd table - a pivot table with a single column named "i" containing consecutive integers from 0 - 1000
I need to know for each date in a series, e.g seven consecutive days, how much time each has logged. easy if everyone has logged a task for every day, but I need to include every day where they have not logged a task.
so, a cartesian join on all the dates in a series(produced using dateadd on p.i and the pivot table)
SELECT dateadd('d',p.i, #02/19/2007#), e.empID
FROM pivot1 p, employees e
WHERE i<no_dates
However I need to do a left join with time log where the date and employee ids are the same, and I have summed the durations for each date. The following query does this, but does not include dates and times where nothing has been logged.
SELECT empID, log_date, sum(duration)
FROM time_log
GROUP BY empEIN, log_date
GIVING, EVENTUALLY, ALL DATES AND EMPIDS AND THE TOTAL AMOUNT OF TIME THEY HAVE LOGGED FOR EACH DAY."
View 1 Replies
View Related
Jun 7, 2007
CREATE TABLE RS_A(ColA char(1), ColB varchar(10))INSERT INTO RS_AVALUES ('S', 'shakespeare')INSERT INTO RS_AVALUES ('B', 'shakespeare')INSERT INTO RS_AVALUES ('P', 'shakespeare')INSERT INTO RS_AVALUES ('S', 'milton')INSERT INTO RS_AVALUES ('P', 'milton')INSERT INTO RS_AVALUES ('B', 'shelley')INSERT INTO RS_AVALUES ('B', 'kafka')INSERT INTO RS_AVALUES ('S', 'kafka')INSERT INTO RS_AVALUES ('P', 'tennyson')SELECT * FROM RS_ANow i need a select which selects based on hierarchyif ColA = 'S', then select only that rowelse if ColA = 'B' then select only that rowelse if colA = 'P' then select only that rowSo my results should look likeS shakespeareS miltonB shelleyS kafkaP tennysonIs there a way to do this within a select statementI tried using a CASE in WHERE CLAUSE but it put out all rows whichexisted/If any of you can help me with this right away, its is greatlyappreciatedThanks in advance
View 2 Replies
View Related
Dec 4, 2007
I'll try to explane what I am looking for.
I'd like to select from a table, let say with 2 variables. One with a currency and one with integer. The integer value is a number between 0 and 255. The integer value represents a value of what kind of BIT is turned on in one BYTE.
Down to business.
I am looking for all BYTES with the fifth BIT = 1 as in example 00001000.This represents the value of 16. This value is an integer in my table. But as you see, the BYTE has a number of different values with the fifth=1 as the rest of the BITS can be 1 as in example 01011000 = 26.
Down to code.
Tablename = myMoney
Column Name = mCurrency; Data Type = decimal(18,2)
Column Name = bit5; Data Type = int
Code Block
SELECT sum(mCurrency)
FROM myMoney
WHERE somefunction(bit5) = 1
I have made the code in VB some time ago but this time I need to do the aclculation in the SQL server.
Hopefully Robin M
View 1 Replies
View Related
Feb 20, 2008
Hi I have a table where I am searching for the values between certain numbers. But then I want to exclude a record to be part of the search
so how I could do that.
This is what I am trying to do.
Select * from mytable Where Value Between 10000 and 200000 --this gives me the result
But then I want to exclude a record from the select statment.
For example this is what I really want to do
Select * from mytable Where Value Between 10000 and 20000 and where value != '100o'
It is not a typo '100o' and this not let me test the select statment so I want to exclude that particular record.
The type was in the table and I want to exclude that record so I can get my statment running.
View 5 Replies
View Related
Feb 6, 2007
Hi all,
I created a stored procedure which perfoms a simple select like this:
CREATE PROCEDURE Reports_Category
( @loc varchar(255), @pnum varchar(255) )
AS declare @vSQL nvarchar(1000) set @vSQL = 'SELECT ' + @loc + ', ' + @pnum + ' FROM Category ' exec sp_executesql @vSQL
RETURNGO
It takes field names as parameters. What happens is that when I supply value to only one parameter, the procedure gives error as it is expecting both values. Is there a way to make these parameters work like an 'OR' so that the procedure returns a dataset even if there is only one value supllied.
Please help,
Thanks,
bullpit
View 7 Replies
View Related
Aug 27, 2001
hI
DOES ANY BODY KNOW HOW TO EXECUTE STORE PROCEDURE IN SELECT STATMENT
THANKS
jk
View 1 Replies
View Related
Jun 21, 2002
Good morning;
My Problem is :im my transaction i use insert code like the following :
" Insert into TAB1 (F1,F2,F3)
select a.F1,b.F2,b.F3 from TAB2 a,TAB3 b
where a.KY1= b.KY1 and b.ORDN in
(Select ORDN from OTAB where USER_ID = 'MIKE')"
In order to optimise my code ,
instead of using a subquery in my select
(I have different insert in my transaction with the same subquery).
I would like to DECLARE a varibale which will contain the select of the Subquery.
and then use it im my different insert. some thing like this.
" BEGIN TRANSACTION
DECLARE @OrdSelect int
Set @OrdSelect = (Select ORDN from OTAB where USER_ID = 'MIKE')
Insert into TAB1 (F1,F2,F3)
select a.F1,b.F2,b.F3 from TAB2 a,TAB3 b
where a.KY1= b.KY1 and b.ORDN in @OrdSelect
COMMIT Tran "
I know that the @OrdSelect will receive the last value of the select not an array of values. which will make my transaction incorrect.
I dont want to use Cursor to resolve this issue Too.
Thinks.
View 1 Replies
View Related
Sep 13, 2007
Hi,
I have a little bit of a problem I cannot seem to figure it out. Is it possible to write a Select statement that contains a WHERE column_name > desired_numeric_value
The tricky part it that the column is of CHAR type and can contain numeric grades ranging from 0-100 or the letter I for Incomplete.
My SQL was working perfect when this column contained only numbers as soon as a record with I was added I get the following error:
Character to numeric conversion error
This report will be used to find students who have failing grades. Thanks for any help!
View 5 Replies
View Related
Apr 20, 2001
Using SQL Server 7 I am trying to modify an existing stored proc and make it more flexible. The below example represents the first part of that proc. The temp table that it should return is then used by another part of the proc (this query represents the foundation of my procedure). I need to figure a way to change the SQL Select statement, choosing between C.CONTRACTCODE and CB.EMPLOYERCODE on the fly. The query below will run but no records are returned. I am starting to believe/understand that I may not be able to use the @option variable the way I am currently.
I've tried creating two SQL statements, assigning them as strings to the @option variable, and using EXEC(@option). The only problem with this is that my temp table (#savingsdata1) goes out of scope as soon as the EXEC command is complete (which means I can not utilize the results for the rest of the procedure). Does anyone know how I can modify my procedure and incorporate the flexibility I've described?
Thanks,
Oliver
CREATE PROCEDURE test
@ContractCode varchar(10),
@dtFrom datetime,
@dtTo datetime,
@Umbrella int
AS
declare @option varchar(900)
if @umbrella = 0
set @option = 'c.contractcode'
else
set @option = 'cb.employercode'
select
c.claimsno,
c.attenddoctor,
c.patientcode,
p.sex,
cb.employercode
into #SavingsData1
from claimsa c inner join Patient p
on c.patientcode = p.patientcode
inner join claimsb cb on c.claimsno = cb.claimno
where
@option = @ContractCode and c.dateentered between @dtFrom and @dtTo
and c.claimsno like 'P%' and p.sex in('M','F') and c.attenddoctor <> 'ZZZZ'
select * from #SavingsData1
View 1 Replies
View Related
Oct 25, 2004
Newbie to the SQL Server.
UPDATE GOLDIE
SET GOLDIE_ID = (SELECT *,
SUBSTRING(GOLDIE_ID,1,
CASE WHEN PATINDEX('%[A-Z,a-z]%',GOLDIE_ID)= 0
THEN 0
ELSE PATINDEX('%[A-Z,a-z]%',GOLDIE_ID)-1
end) STRIPPED_COL
FROM GOLDIE_ID)
Here is the explaination of the above query, I have a column which has the values like '23462Golden Gate' or '348New York'. Above query is stripping all the characters and keeping only numbers. So I need to update the same column with only numbers which is the output of abover query.
Immd help will be greatly appreciated.
Pam
View 8 Replies
View Related
Sep 18, 2007
Hi guys/ladies I'm still having some trouble formatting a select statement correctly.
I am using a sqldatasource control on an aspx page. It is connecting via odbc string to an Informix database.
Here is my select statement cut down to the most basic elements.
SELECT commentFROM informix.ipr_stucomWHERE (comment > 70)
The column "comment" contains student grades ranging from 0-100 and the letters I, EE, P, F, etc. Therefore the column is of a char type. This is a problem because I cannot run the above statement without hitting an alpha record and getting the following error
"Character to numeric conversion error"
How can I write this statement where it will work in the datasource control and have it only look at numeric values and skip the alpha values?
I have tried case with cast and isnumeric... I don't think that I have the formating correct.
I have also used:
WHERE (NOT (comment = ' I' OR comment = ' EE' OR comment = ' NG' OR comment = ' WP' OR comment = ' WF' OR comment = ' P' OR comment = ' F'))
This works but is very clunky and could possibly break if other letters are input in the future. There has to be a better way.I am sorry for my ignorance and thanks again for your help.
View 2 Replies
View Related
Jan 18, 2008
Hi all
I have two tables I need to Select the record from the First table and insert them into the second table and delete the record from the first table how can i do that with the SQL Statment?
Thank you in advance .....
Regards,
sms
View 15 Replies
View Related
Jul 11, 2006
I cant find the error in this sql statment. What I want it to do is return everything from book, locations.locationname, and author.fullname. I want to to only return the first 10 rows. This will be used in paging, so, eventually it will be first 10, than 11-20, etc. Heres what I have, without the row limitSELECT RowNum, book.*, locations.LocationName, author.fullname FROM (SELECT book.*, locations.LocationName, author.fullname ROW_NUMBER() OVER(ORDER BY book.id) as RowNum FROM book INNER JOIN Author ON book.AuthorID = Author.ID OR book.AuthorID2 = Author.ID OR book.AuthorID3 = Author.ID OR book.AuthorID4 = Author.ID OR book.AuthorID5 = Author.ID INNER JOIN Locations ON book.LocationID = Locations.ID WHERE (Author.FullName LIKE '%' + @Search + '%') OR (Author.FirstName LIKE '%' + @Search + '%') OR (Author.LastName LIKE '%' + @Search + '%')) as BookInfo
View 6 Replies
View Related
Jul 11, 2007
I have two tables. One for videos and one for a "block list"Videos : VideoID UserID VideoURL VideoTitle etc. VideoBlockList :VideoID UserIDI have a datalist to show the videos but i want for the datalist to miss out any videos that a user can not see.So if the block list has "VideoID" = 2 and UserID = 1 if user 1 does a search then the search will skip out the video 2.how is this done? i tryed to do it using a specific / custom SQL statment but it errored cos the NOT value conflicted with the search... any ideas? thanks in advance si!
View 19 Replies
View Related
Nov 13, 2007
Select @ID = top 1 ID From Contents Where Contents.UserID=@UserID And Contents.Status=4 AND Contents.InEdit=1
why it dosn't get back the ID vlaue but when i remove the top1 one its work well why
View 5 Replies
View Related
Apr 14, 2008
Hi I have two tablesTABLE 1 named problemas with the field N_problem (numeric)Tabe 2 named resolvidos with the field Resol (numeric)
How can i select all the records from table 1 who are not in Tabe2 ?
Thank you
mario
View 2 Replies
View Related
