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
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
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
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
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
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?
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
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
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
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")
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?
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
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."
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
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.
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.
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
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.
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!
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'
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.
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.
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?
I'm looking for some online resources here. Specifically, I'm interested in finding some case/project examples to learn more. I'm looking for any and all kinds in all areas...ADO, Security, Maintenance, etc. I've worn out google, but the most I seem to find is articles. I'm looking for actual Cases, like one you'd find in a text.
I have a text book from a couple of courses I took in school. Unfortunately it doesn't delve much into said areas. Any resources you could point me I will greatly appreciate it. I'd even be interested in some actual books if there's any that any of you have experience with that you think would help me out. Thanks for reading.
I recently added a nested table to a model that I had been using for a while. I noticed that after I added the nested table that the ClusterDistance() function returned 0 for every case. I went ahead and changed some of the keys for the nested table records so that the values would show up as missing and now the cases with a missing value have a non-zero ClusterDistance() value. Can anyone help me understand why this may be happening?
I have the following sproc that gets all the items from a queue with a few filters. I however need to return records where jobstepId is 1 and job jobqueuestatusid to be 4 if any jobqueuestatusid was 4 for that jobscheduleid, 2 if any is 2, and lastly 1. I tried inserting a case when exists(select * from flexportjobqueueview where jobscheduleID = [jobscheduleID] and jobqueuestatusid = '4' then 4, else ..... then 3, else ....... then 1, end
that did not seem to work. It inserted 4 or 3's for all and not just the particular scheduleid. Any help on this will be great thanks Ludwig
CREATE TABLE #QTEMP( [JobQueueID][int], [JobScheduleID][int], [JobID][int], [JobName][varchar](50), [JobDesc][varchar](50), [JobStepID][int], [JobStepName][varchar](50), [JobStepDesc][varchar](50), [JobStepExecutable][varchar](100), [JobQueueStatus_ID][int], [JobQueueStatusDesc][varchar](100), [NextRunDateTime][datetime], [LastRunDateTime][datetime], [ProcessID][int] )ON[PRIMARY] Declare @sql nvarchar(4000) Set @sql='INSERT INTO #QTEMP SELECT [JobQueueID], [JobScheduleID], [JobID], [JobName], [JobDesc], [JobStepID], [JobStepName], [JobStepDesc], [JobStepExecutable], [JobQueueStatus_ID], [JobQueueStatusDesc], [NextRunDateTime], [LastRunDateTime], [ProcessID] FROM [FlexPort].[dbo].[FlexPortJobQueueView] WHERE [JobID] IS NOT NULL
' IF ISNull(@JobScheduleID,'')<>'' Set @sql = @sql + ' And [JobScheduleID] like ''%' + @JobScheduleID + '%''' IF ISNull(@JobID,'')<>'' Set @sql = @sql + ' And [JobID] like ''%' + @JobID + '%''' IF ISNull(@JOBName,'')<>'' Set @sql = @sql + ' And [JobName] like ''%' + @JOBName + '%''' IF ISNull(@Status,'')<>'' Set @sql = @sql + ' And [JobQueueStatus_ID] like ''%' + @Status + '%''' If IsNull(@LastRunDateTime, '') <>'' Set @sql = @sql + ' And [LastRunDateTime] > ''' + Convert(varchar, @LastRunDateTime, 101) + ''''
Exec master.dbo.sp_ExecuteSql @sqlI have the following sproc that gets all the items from a queue with a few filters. I however need to return records where jobstepId is 1 and job jobqueuestatusid to be 4 if any jobqueuestatusid was 4 for that jobscheduleid, 2 if any is 2, and lastly 1. I tried inserting a case when exists(select * from flexportjobqueueview where jobscheduleID = [jobscheduleID] and jobqueuestatusid = '4' then 4, else ..... then 3, else ....... then 1, end
that did not seem to work. It inserted 4 or 3's for all and not just the particular scheduleid. Any help on this will be great thanks Ludwig
CREATE TABLE #QTEMP( [JobQueueID][int], [JobScheduleID][int], [JobID][int], [JobName][varchar](50), [JobDesc][varchar](50), [JobStepID][int], [JobStepName][varchar](50), [JobStepDesc][varchar](50), [JobStepExecutable][varchar](100), [JobQueueStatus_ID][int], [JobQueueStatusDesc][varchar](100), [NextRunDateTime][datetime], [LastRunDateTime][datetime], [ProcessID][int] )ON[PRIMARY] Declare @sql nvarchar(4000) Set @sql='INSERT INTO #QTEMP SELECT [JobQueueID], [JobScheduleID], [JobID], [JobName], [JobDesc], [JobStepID], [JobStepName], [JobStepDesc], [JobStepExecutable], [JobQueueStatus_ID], [JobQueueStatusDesc], [NextRunDateTime], [LastRunDateTime], [ProcessID] FROM [FlexPort].[dbo].[FlexPortJobQueueView] WHERE [JobID] IS NOT NULL
' IF ISNull(@JobScheduleID,'')<>'' Set @sql = @sql + ' And [JobScheduleID] like ''%' + @JobScheduleID + '%''' IF ISNull(@JobID,'')<>'' Set @sql = @sql + ' And [JobID] like ''%' + @JobID + '%''' IF ISNull(@JOBName,'')<>'' Set @sql = @sql + ' And [JobName] like ''%' + @JOBName + '%''' IF ISNull(@Status,'')<>'' Set @sql = @sql + ' And [JobQueueStatus_ID] like ''%' + @Status + '%''' If IsNull(@LastRunDateTime, '') <>'' Set @sql = @sql + ' And [LastRunDateTime] > ''' + Convert(varchar, @LastRunDateTime, 101) + ''''
