Select Based On Nonexistant Value

Jan 21, 2004

Hey all, I'm having an interesting problem, but also keep in mind I am certainly a newbie with DB's and SQL but here's what I got:

I'm making an application that creates quotes for our customers. I want to create a Quote Number that is based on the largest of the last number in the DB but only based on that company. For example:

CoID QuoteNum
123 1
123 2
666 1
123 3
666 2
666 3

so if I created a new quote for CoID: 666, the QuoteNum would be 4. Easy enough. But the problem arises when I create a new quote for a NEW COMPANY, one who's CoID is not already present in the DB. So what I have so far is this:

SELECT QuoteNum FROM Quote
WHERE QuoteNum = (SELECT MAX(QuoteNum) FROM Quote
WHERE CoID='222')

but since 222 does not yet exist I get an error: "Either BOF or EOF is True, or the current record has been deleted. Requested operation requires a current record."

So how do I tell the DB to ignore that error and perform another task should it arise? Any help would greatly be appreciated. THANKS!

View 4 Replies


ADVERTISEMENT

Reporting Services :: Select Text Field Dataset Based On User Select Option?

Aug 4, 2015

I have a report that uses different datasets based on the year selected by a user.

I have a year_id parameter that sets a report variable named dataset_chosen. I have varified that these are working correctly together.

I have attempted populating table cell data to display from the chosen dataset. As yet to no avail.

How could I display data from the dataset a user selects via the year_id options?

View 4 Replies View Related

How To: Create A SELECT To Select Records From A Table Based On The First Letter.......

Aug 16, 2007

Dear All
I need to cerate a SP that SELECTS all the records from a table WHERE the first letter of each records starts with 'A' or 'B' or 'C' and so on. The letter is passed via a parameter from a aspx web page, I was wondering that someone can help me in the what TSQL to use I am not looking for a solution just a poin in the right direction. Can you help.
 
Thanks Ross

View 3 Replies View Related

SQL 2012 :: SET CMD Value Based On Select Name

Oct 21, 2015

I am trying to move files to directories based on the file status. If the file contains the Flag='E' then I want to move it to an "Error" folder, otherwise it will go to a "Processed" folder.

Here is my current code:

DECLARE @cmd varchar(500)
SELECT
CASE
WHEN Processed_Flag = 'E'
THEN
SET @cmd='MOVE /Y C: empmyfiles*.dat C: empmyfilesError'
ELSE
SET @cmd='MOVE /Y C: empmyfiles*.dat C: empmyfilesProcessed'
END
FROM#TEMP

PRINT @Cmd

View 2 Replies View Related

Select Statement Based On A Date

Feb 19, 2004

i am not getting a result back when i run the query below.

select * from users where DateCreated = '2004-02-19'

so i went into the table and looked at the record. for DateCreated field i have both date and time. ex: 2004-02-19 08:40:00

how can i select this record with out using the time in the select statement. what i want to see is how many users signed up for a day. any ideas?

View 5 Replies View Related

Updating Based On A Select Iteration

Jan 8, 2001

Okay I'm going nuts.

I have a table with a max key value in it, and another table with a few rows in it. I'm trying to update the two new rows with key values that are iterative from the MAX value in the first table. Could anyone point me to some good LOOP...UPDATE, etc resources or pointers before I go postal?

::grins::

Example: first table has a key field, last value in it is say 1000. The second table has two records. They need keys too, and those keys need to start at the last value in the first table + 1, so 1001, and end on the last row of the second table, or, say 1002. I cannot figure out how to read the last value of the first table, and create an update loop to iteratively update the key value in the second table based on the max value of the first, and looping based on the number of records in the second, in this case 2.

Arrrg!


~EHunter

View 4 Replies View Related

Select Query Based On Datetime

Feb 12, 2002

I need to select certain rows based on a "datetime" column. I need to select rows from 8am yesterday until 8am today.
In Oracle I would use:
select * from foo where TIMESTAMP >= trunc(sysdate - 1) + 8/24 AND TIMESTAMP < trunc(sysdate) + 8/24.
This would start at 8am yesterday and end at 7:59am today.

How would I do this with T-SQL?

thank you,

Mark
Fiesta_donald@email.com

View 1 Replies View Related

Select Random Row Based On Weights

Feb 9, 2007

I have a table with two columns: value and weight (weight is smallint).

I want a query that will return the value of one row at random with the added requirement that rows with a higher weight have a higher probability of being chosen. Any ideas?

View 4 Replies View Related

How To Do Update Of Select Columns Based On...

Jun 21, 2007

the following criteria.
i have the selection all done but am trying to figure out how to do the following:
if column4 < 0 then add column4 to column3, move 0 to column4;
if column3 < 0 then add column3 to column2, move 0 to column3;
if column2 < 0 then add column2 to column1, move 0 to column2;
add column3 to column4;
move column2 to column3;
move column1 to column2;
if column0 > 0 move column0 to column1, move 0 to column0 else move 0 to column1;

these are all numeric data types.

View 7 Replies View Related

Select Record Based On Max Date

Sep 12, 2012

I have the following table:

Occ_Num Feature_Num Trans_Date Peril_Desc
123 1 1-2-2012 Water
123 1 1-11-2012 Ice
123 2 1-2-2012 Other
123 2 1-13-2012 Other
123 2 1-19-2012 Wind

I want to select each Occ_Num, Feature_NUM, Trans_Date, and PERIL_Desc but with only the Peril that was part of the max trans_date.

So i would want the following from above:

Occ_Num Feature_Num Trans_Date Peril_Desc
123 1 1-11-2012 Ice
123 2 1-19-2012 Wind

I'm having trouble with the syntax need to accomplish this.

View 2 Replies View Related

Select Product Based On Date?

Sep 23, 2012

Product Date
--------------------------------------------------------
pd0 2012-08-11 18:45:55.780
Pd1 2012-08-11 18:55:17.020
pd2 2012-08-11 19:06:58.623
pd3 2012-08-18 12:00:01.193
pd4 2012-08-25 12:13:04.077
pd5 2012-08-25 17:28:30.347
pd6 2012-08-25 18:23:16.473
pd7 2012-09-18 18:29:58.360

I want select the product based on from date and to date.

For Example

I want the select the product date in between 2012-08-11 to 2012-08-18

Note:dont check the time.

I want the query for select product based on only date not depend upon time

View 6 Replies View Related

Select Latest Date Based On ID

Dec 3, 2012

I want to select the latest single value based on ProductID. The value should be the last value inserted from the Date column.

I want to use this as part of a query to delete this value when a button is slected in ASP.Net C#.

Here is my code which does not work

select basketID, SessionID, UserID, MAX(Date), productID from ShoppingBasket
where userID = '1'
group by productID, basketID, SessionID, UserID

View 2 Replies View Related

How To Create Set Of Records Based On SELECT

Oct 10, 2014

I have a set of records that will always be the same. A template if you wanna call it that.

"SELECT ID, Field1, Field2, Field3, Field4, Field5, Field6, Field7 FROM TABLE WHERE Field2 = 000"

This returns a 20 record set. The user will click a button and create another set of 20 records, where they can make changes accordingly.

View 8 Replies View Related

Create Function Based On Select

Mar 3, 2015

I have select to split FullName on LastName and FirstName columnselect

Substring(FullName, 1,Charindex(',', FullName)-1) LName
,Substring(FullName, Charindex(',', FullName)+1, LEN(FullName)) FName
from Table1

Is it possible to create function based on that select? If yes. How it to do?

View 12 Replies View Related

Add A Column Based On A Select Query

Mar 5, 2006

Supose I have the following select:

Select Name, Age, (select TelNums from Telephone)
From Person

The problem is that (select TelNums from Telephone) can return more than 1 record:

tel1
tel2
.
.
.

I was wondering how I can make a select to return the tel numbers like: 'tel1,tel2,tel2'

»»» Ken.A

View 3 Replies View Related

Select Records Based On Number

Mar 8, 2006

I'm trying to select records that occur more than once. I'm trying to base this on the email column. So basically I want the query to look something like this:

select * from table where emailaddress count > 1

Can someone provide me with the correct syntax? :)

View 2 Replies View Related

Select Based Upon Multiple Criteria

Sep 13, 2006

Hi

I would like to get records from a table and present a result set based upon the search fields

the search fields could be any of the following: PNo, Year, JNo, C1No6, C2No3, C3No3, C4No3,

they could enter any combination of these however if they dont enter any of the above then the search should not retrieve any thing. the table colunms are listed below and asample data set is also shown below.

Currently the only way i think it can be done is by writing multiple queries with different queries to be executed based upon the search field that have been filled? can it be done in a stored prcedure? and can it be done using non-dynamic sql?

Name, PNo, Year, JNo, C1No6, C2No3, C3No3, C4No3, RefImage


adam, 01, 1999, 099, 3yh333, 888, 989, 999, ref1999099.jpg
Brian, 01, 2005, 029, 3yh323, 828, 929, 929, ref1929099.jpg
sid, 04, 1989, 039, 3yh343, 838, 939, 939, ref1993399.jpg
jack, 06, 1996, 069, 3yh633, 868, 969, 969, ref1669099.jpg

View 12 Replies View Related

Select X Record Based On Row Passed In URL

Aug 3, 2007

Hi all - this one has me stumped... PLEASE HELP!!!

I have a back/forward navigation link that passes a URL.startrow number (lets call it n) - based on n - I only want to select the record that is the n'th record based on a sort order (gall_order) - (SQL SERVER).

<cfquery name="gallHomePic1st" datasource="id" maxrows="1">
SELECT id
FROM gall_home
ORDER BY gall_order asc
</cfquery>

For e.g. - I want the 7th (14th - 21st etc) record based on gall_order asc.

Thanks guys - this has me stumped!

View 4 Replies View Related

Complicated Query - Select Based On Value

Jul 23, 2005

SQL2K on W2KserverI need some help revamping a rather complicated query. I've given thetable and existing query information below. (FYI, changing thedatabase structure is right out.)The current query lists addresses with two particular types('MN30D843J2', 'SC93JDL39D'). I need to change this to (1) check eachcontact for address type 'AM39DK3KD9' and then (2) if the contact hastype 'AM39DK3KD9' select types ('AM39DK3KD9', 'ASKD943KDI') OR if thecontact does not have that type then select types ('MN30D843J2','SC93JDL39D'). (Context - the current query selects two standardaddress types "Main" and "Secondary"; we've added new data and now havetypes "Alternate Main" and "Alternate Secondary". If the Contact hasAlternate addresses, I need to select those; if not, I need to selectthe standard addresses. There are other address types in use, so Imust specify which types to select.)Can anyone point me in the right direction?Thanks very much! jamilehCREATE TABLE [CONTACTS] ([CONTACT_X] [char] (10),[LONGNAME] [char] (75),[ACTIVE] [bit])CREATE TABLE [CONTACTADDRESSES] ([CONTACT_X] [char] (10),[ADDRESS_X] [char] (10),[ADDRESSTYPE_REFX] [char] (10),[ACTIVE] [bit])CREATE TABLE [ADDRESSES] ([ADDRESS_X] [char] (10),[ADDRESSLINE1] [char] (60),[ADDRESSLINE2] [char] (60),[CITY] [char] (20),[STATE] [char] (2),[ZIPCODE] [char] (11),[PHONE] [char] (10))CREATE TABLE [REFERENCETABLE] ([REFERENCETABLE_X] [char] (10),[ADDRESS_X] [char] (10),[DESCRIPTION] [char] (60))CREATE TABLE [MASTERTABLE] ([CONTACT_X] [char] (10),[RECORDTYPE] [char] (1),[ACTIVE] [bit])CREATE VIEW vw_CONTACTInfo_ListLocASSELECT CONTACTS.CONTACT_X, CONTACTS.LONGNAME,CONTACTADDRESSES.ADDRESSTYPE_REFX,Type_REFERENCETABLE.DESCRIPTION AS Type_DESCRIPTION,CONTACTADDRESSES.ADDRESS_X, ADDRESSES.ADDRESSLINE1,ADDRESSES.ADDRESSLINE2, ADDRESSES.CITY, ADDRESSES.STATE,ADDRESSES.ZIPCODE, ADDRESSES.PHONEFROM CONTACTS INNER JOIN CONTACTADDRESSES ONCONTACTS.CONTACT_X = CONTACTADDRESSES.CONTACT_X INNER JOINADDRESSES ON CONTACTADDRESSES.ADDRESS_X =ADDRESSES.ADDRESS_XINNER JOIN REFERENCETABLE Type_REFERENCETABLE ONCONTACTADDRESSES.ADDRESSTYPE_REFX =Type_REFERENCETABLE.REFERENCETABLE_XWHERE (CONTACTS.ACTIVE = 1) AND (CONTACTADDRESSES.ADDRESSTYPE_REFXIN('MN30D843J2', 'SC93JDL39D') AND (CONTACTADDRESSES.ACTIVE =1)) AND(CONTACTS.CONTACT_X IN(SELECT CONTACT_X FROM MASTERTABLE WHEREACTIVE = 1 AND RECORDTYPE = 'E'))

View 10 Replies View Related

Insert Based On A Select Query

Sep 20, 2005

Hi,I have a SQL query:select products.name, products.notes, products.purchase_date,products.serial_no, products.total_value, products.product_code,products.quantity, products.product_group, products.lhs1_name,lhs1.department, lhs1.address1, lhs1.city, lhs1.countryfrom products, lhs1where products.lhs1_id = lhs1.idI want to insert the results of this query into a table called 'temp' in thedatabase. I used to copy and paste this into excel then import it but itdoesn't always work.Is there a way to do it all in a SQL script. Please be aware that myknowledge of SQL is fairly basic so please explain things clearly.Thanks,Darren

View 4 Replies View Related

MDX - Select Count Based On Two Different Dimensions

May 19, 2008

I have a fact table with a create time dimension and an expiration time dimension. I'd like to have a calculated member that would compare the (count for create time) / (count with that expiration time). I already have these counts as measures.

I would be able to put the create time dimension in the "row fields" area, and see the ratio (calculated above)
over the different create time periods.

Can someone point me in the right direction on how I would create that kind of calculated member? What would the MDX look like?

Thanks for your help

View 1 Replies View Related

Select Fields Based On The Value In A Field

Sep 2, 2015

I have a table with two sets of fields, one for PRIMARY Mail Address and the other for ALTERNATE Mail Address. Both sets are five fields long.  I will call them PRIMARY & ALTERNATE for this discussion.  I want to select the PRIMARY fields if ALTERNATE is either null or zero, and the ALTERNATE fields if data is present.

I have used this syntax but it gives a table with both the PRIMARY & SECONDARY records.

SELECT    TXPRCL, TXALTR, TXANAM
FROM    dbo.PCWEBF21
WHERE    (TXALTR > 0)
UNION
SELECT    TXPRCL, TXTAXP, TXTNAM
FROM    dbo.PCWEBF21
WHERE    TXALTR = 0

Would a Select Case work? something like

Select PRIMARY when ALTERNATE = 0, and ALTERNATE when ALTERNATE > 0 or is not null.

View 17 Replies View Related

Select Based On Repeated Numbers

Nov 10, 2006

i have telephone numbers in my table and i want to select based on numbe of repeated numbers.. for example..



39657777

39111132

36399991



The first has foue 7s, second has four 1s and the thrid has four 9s.





View 5 Replies View Related

Can I Select Subset Based On Time Of Entry

Jul 5, 2001

A table gets data every 4 minutes, I only need spread of every 15 minutes. Can I select only records spread every 15 minutes apart from this table without having to run a scheduled job every 15 minutes and loading one record closest to getdate() at that point into another table(this is how I am doing it now) Is there a better way. Please help
Thanks

View 2 Replies View Related

SELECT Items Based On String Manipulation

Oct 25, 2005

I am having some trouble creating a query that will preform some string manipulations on a field, and include this as part of the WHERE clause

For example I want to do something like this:


Code:

SELECTTable1.Column1,
Table1.Column2,
Table1.Column3,
Table2.Column1

FROMTable1
INNER JOIN Table2 ON Table1.UID = Table2.UID

WHERE(SET @Temp = Table2.Column1

--remove all 0's
SET @k = patindex('%[^0 ]%', @Temp)
WHILE @k> 0
BEGIN
SET @Temp = replace(@Temp, substring(@Temp, @k, 1), '')
SET @k= patindex('%[^0 ]%', @Temp)
END
SELECT @Temp
) = ''



But of course this isn't working so much. I am wondering if I have to use a cursor?

View 1 Replies View Related

SELECT Only The Newest, But Based On Revised Letters

Feb 11, 2004

Ok here's a big one.

First I'm a big NEWBIE, so it'd be great if you can provide a little explanation as to how this should be done...

Here's what I want to do, but have no idea how to approach it.

I have a table with Quotes (table Quote) in them (for a Sale's Team). Each has a quote number (qtQN) and this number is sequencial but sometimes revised where a letter is added at the end. Like so:

qtQN__________Date
---------------------------
111q0001--------02/01/04
111q0002--------02/02/04
111q0002A------02/03/04
111q0002B -----02/04/04
222q0005--------01/15/04
etc... ------------- etc...

The first 3 digit are company codes and pretty unimportant to this problem. As you can see, 111q0002 has three versions. A, B, and no letter. The most up to date is B. So in this list I want to list only the most up to date quotes. So the resulting list would be:

111q0001
111q0002B
222q0005

Get it? Good, cuz I have no idea how to query that... any help at all is appreciated!

I'm using MS SQL Server 2k and scripting with ASP 3.0

View 6 Replies View Related

Select/where Based On Date, Advice Needed

Jun 2, 2008

I'm very green with SQL so I could do with some advice please.

I need to pull some data from a table based on the year portion of a datetime field, so far I've got this...

USE MfgSys803

SELECT orderdate, ordernum FROM orderhed

WHERE ((SELECT CONVERT(VARCHAR(4),GETDATE(),111)) = (SELECT CONVERT(VARCHAR(4),ORDERDATE,111) FROM orderhed))

... the field 'orderdate' is the datetime. The purpose of the WHERE statement is to get the current year fromt he system and then compare this to the current year of the field 'orderdate'.

Unfortunately I get the error...

Msg 512, Level 16, State 1, Line 3
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.

... the 'SELECT CONVERT' portions of the WHERE work fine on thier own but I can't use them together.

Hopefully this makes sense, thanks :)

View 7 Replies View Related

Select Query - How To Get Result Based On Given Table

Sep 14, 2014

Till now I get data form multiple table using join, but unable to understand how can i get the this result based on given table -

Result should be -

ProCodeProductName
PRO00001;PRO00002Product Test SearchedPromotion One;Promotion Two
PRO00001;PRO00002;PRO00002Product Final SearchedPromotion One;Promotion Two;Promotion Three
PRO00002TestingPromotion Two

Tables -
select * from ProMaster
CodeName
PRO00001Promotion One
PRO00002Promotion Two
PRO00003Promotion Three

select * from ProDetail
IDProCodeProduct
1PRO00001;PRO00002Product Test Searched
2PRO00001;PRO00002;PRO00002Product Final Searched
3PRO00002Testing

View 2 Replies View Related

Transact SQL :: Update Based On Results From Select

Jul 24, 2015

Update statement based on the results from this SELECT:

SELECT RELQ_REL_VERSION_NM,
RELQ_RELEASE_Q_ID,
RELQ_STATUS_CD,
RELSP_RELEASE_STEP_ID,
RELSP_STEP_TYPE_CD,
RELSP_STATUS_CD
FROM RELEASE_QUEUE WITH (NOLOCK)

[Code] ....

I need to update all records where the 

RELEASE_STEPS.RELSP_STATUS_CD = 'SKPD'TORELEASE_STEPS.RELSP_STATUS_CD = 'CMPS'

I imagine that the UPDATE statement will be something like:

UPDATE RELEASE_STEPS SET dbo.RELEASE_STEPS.RELSP_STATUS_CD = 'CMPS'
WHERE (
SELECT RELQ_REL_VERSION_NM,
RELQ_RELEASE_Q_ID,
RELQ_STATUS_CD,
RELSP_RELEASE_STEP_ID,
RELSP_STEP_TYPE_CD,

[Code] .....

View 4 Replies View Related

Select Data Based On Certain Time Period

Jul 4, 2006

Hello all,

I am using SQL Server in a project where I want to fetch the records that were inserted after a time specified in my query.

Suppose 10 records were inserted at 10:00 AM(morning) and 5 were inserted at 10:15 AM( 15 minutes later). I am running a query at 10:20 AM( 5 minutes after the second transaction). I need this query to be such that it selects the records inserted 10 minutes before. So it will show only the records inserted at and after 10:10 AM and willl not show those inserted at 10:00 AM.

Please help me in making such a query.

I am trying and I think that some Date & Time functions will help but still not able to achieve it.

Thanks in advance

View 3 Replies View Related

Reporting Services :: Select A Certain Row From Dataset Based On ID?

Oct 9, 2015

If I want to select a value from a certain row in my Dataset.

I have a dataset called labels in my Report. in this data set I have diffrent row and in each row I have a label with an

ID. For example:
ID                        Text
TEXT0001            Hi
TEXT0002            Good Morning
TEXT0003            Good bye

Is this is the right way to get the second row?

=First(Fields!ID.Value="TEXT0002", "Labels")

View 2 Replies View Related

Filter SqlDataSource Select Based On Membership User Name

Jul 11, 2006

I am running into an issue trying to declaratively set up a SqlDataSource.  I want to be able to filter some of my queries based on the user that is currently logged into the web site.  I want to do it Declaratively as that's one of my favorite 2.0 features.Is there any way to do this with the Membership information?  I know I can use the code behind to set the parameter, or store the User Name in Session Variable, and use a <asp:SessionParameter> but I think there should be a way to bind directly to the Membership user...Am I missing another option, or is there no built in way to do this?  Any other suggestions...Thanks

View 1 Replies View Related

SQL Select Command With Where Clause Variable Based On ASP.NET TextBox

Apr 19, 2007

All,
 
Please can someone advise how to use SQL select statement with where clasue which is based on a textBox.text value.
 ex. below example I set the textbox.text value to a C# variable called TextBoxValue1 but I receive error this is not a valid
This is all done in Page_Load event backend code.
string strCommandtext = "Select Type.TypeName, Type.TypeDesc FROM Type Where Type.TypeName = TextBoxValue1";

View 2 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved