Help With SQL If Statement And Adding Fields Together

Jul 23, 2005

I have a query that I need a hand on. I am trying to add togther some
fiends based on values of another.

What I would like to add a billing total by saying more or less the
following:

SELECT labor_hours, labor_cost, expidite_fee, flat_rate,
include_repair_cost, include_cal, include_flat_rate, include_parts,
cur_bill,
(labor_hours * labor_cost) AS labor_total,
(ISNULL((SELECT TOP 1 cal_cost FROM calID WHERE orderID=79559 ORDER BY
dateCAL DESC),0)) AS cal_total,
(
ISNULL((SELECT SUM((qty * cost) + premium_charge) AS gptotal FROM
repair_partsID WHERE orderID=79559),0) +
ISNULL((SELECT SUM(qty_needed * cust_cost) AS gnptotal FROM
misc_part_assocID WHERE orderID=79559),0)
) AS parts_total,
(
(labor_hours * labor_cost) + expidite_fee + flat_rate +
ISNULL((SELECT TOP 1 cal_cost FROM calID WHERE orderID=79559 ORDER BY
dateCAL DESC),0) +
ISNULL((SELECT SUM((qty * cost) + premium_charge) AS gptotal FROM
repair_partsID WHERE orderID=79559),0) +
ISNULL((SELECT SUM(qty_needed * cust_cost) AS gnptotal FROM
misc_part_assocID WHERE orderID=79559),0)
) AS actual_total,
(
expidite_fee
IF include_repair_cost = 1
+ (labor_hours * labor_cost)
IF include_flat_rate = 1
+ flat_rate
IF include_cal = 1
+ ISNULL((SELECT TOP 1 cal_cost FROM calID WHERE orderID=79559 ORDER
BY dateCAL DESC),0)
IF include_parts = 1
+ ISNULL((SELECT SUM((qty * cost) + premium_charge) AS gptotal FROM
repair_partsID WHERE orderID=79559),0) +
ISNULL((SELECT SUM(qty_needed * cust_cost) AS gnptotal FROM
misc_part_assocID WHERE orderID=79559),0)
) AS billing_total
FROM view_inventory
WHERE orderID=79559

I know the IF part is whacked, that's where I need the help. Is this
type of thing even possible? Or even efficent? Is it wise to subquery
for totals (not like I have a choice based on the application
requirements)? help.

View 2 Replies


ADVERTISEMENT

Adding Identity Fields

Nov 2, 1998

I have a table that I need to add an identity field to. I created a field in the table as an INT and added values to all of the existing records. When I try to change it to an IDENTITY field I continually get an error saying 'Invalid cursor state' . The help function tells me that this is caused by not having enough space in the transaction log but I don't understand this b/c the trans log is configured to expand as needed. Anyone know how I can do this?

Thanks,
Tony

View 2 Replies View Related

Adding Fields To A Table

Oct 17, 2007

I am new to SQL Server 2005 and I am trying to add two fields to an existing table. The table has 15 Million records in it and the save is not completing. How do I add the new fields?

View 14 Replies View Related

Adding Calculated Fields

Jul 20, 2005

I have a table with some fields. break_mon, lunch_mon, dinner_mon, ent_mon,break_tue, lunch_tue, dinner_tue, ent_tue, .....etcI want to output the sum of the four monday columns as well as the sum ofthe four tuesday columns.I did this withSELECT break_mon+lunch_mon+dinner_mon+ent_mon AS mon_tot,break_tue+lunch_tue+dinner_tue+ent_tue AS tue_totFROM expense_reportI now want to add mon_tot and tue_tot AS total, but SQL is telling me thatit can not find mon_tot. Is there a way to do this? Thanks!DarrenMCP

View 4 Replies View Related

Adding Additional Fields To ASPNETDB.mdf

Dec 28, 2006

I am attempting to add additional fields and data to the default users database that is created as a result of enabling roles on my website.  Is it possible to add additional data fields to this file?  Where can I find the commands to do this?

View 2 Replies View Related

Adding Data From Fields To Get A Total

Apr 11, 2006

EX:  I have a table for products, and each product has a quantity.  How can I add up the QTY field in all the rows to find out the total QTY of all the products.Any help would be greatly appreciated.gkc

View 4 Replies View Related

Adding Description To Tables And Fields

Oct 27, 2000

Can I add a description to a table or field? The way access gives you to add a description to a field.


Thanks!

View 1 Replies View Related

Adding New Fields To Transaction Replication.

May 19, 2006

I need help to add new fields in Transaction replication & also want to know how it will be replicated to Subscriber.

View 1 Replies View Related

Adding Conditions (if/else) When Selecting Fields

Aug 18, 2004

We have three fields in a table: firstname, Surname & Organisation. Firstname & surname will always be filled but in most cases organisation is NULL.

Part of what these fields will be used for is a mailshot. If there is no organisation data then the mailshot will open with 'Dear Firstname Surname, ' but if the organisation is present they would like 'Dear Organisation'.

Is it possible to create a select state that checks the organisation field, and if a value is present return that value else return the firstname, surname combination? I have tried various things but I cannot get it to work.

I know the ideal situation would be to do this type of condition check at scripting level (PHP, ASP, Visual Basic) but my bosses would like to try it at SQL level.

View 1 Replies View Related

Adding User Define Fields Together

Apr 5, 2004

This is driving me crazy! The SQL Statement refenced is shown at the end of this email.

When I try and run the statement, an error is raised saying that Undrawn_GT5MIL_LE365Days is invalid (likewise for Undrawn_LE5MIL_LE365Days). From what I can gather, it is saying that I cannot include a User Defined variable in another argument. This is unlike Access. Any suggestions?


SQL View.......

SELECT TOP 100 PERCENT QRY_FacNew_Term.Category, QRY_FacNew_Term.Fac_No, QRY_FacNew_Term.Client_Number, QRY_FacNew_Term.Client_Name,

Undrawn_GT5MIL_LE365Days = CASE WHEN Undrawn_CDN >= 5000000 AND Term <= 365 THEN Undrawn_CDN ELSE 0 END,

Undrawn_GT5MIL_GT365Days = CASE WHEN (Undrawn_CDN >= 5000000 AND Term > 365) OR

(Cr_Limit_CDN IN (0, 1)) THEN Undrawn_CDN ELSE 0 END, [Undrawn_GT5MIL_LE365Days]+[Undrawn_GT5MIL_GT365Days] AS Total

FROM dbo.QRY_FacNew_Term

WHERE (Exclude <> 'Y')

ORDER BY Category, Client_Name

View 1 Replies View Related

A Problem With Adding Fields In Ssems

Jun 1, 2007

hi,
i have a DB and it has some tables that the tables has related link (diagram).now when i wanna to change a table's field , the Sql Server errors that the table is not empty.when i try to delete the table's content , Sql server errors that the table is use a relation with another table.
so can i change a table's structure?
by the way before i forget , the Sql Server's error is below:

'UserManagement' table
- Unable to modify table.
ALTER TABLE only allows columns to be added that can contain nulls, or have a DEFAULT definition specified, or the column being added is an identity or timestamp column, or alternatively if none of the previous conditions are satisfied the table must be empty to allow addition of this column. Column 'isadmin' cannot be added to non-empty table 'UserManagement' because it does not satisfy these conditions.


thanks,
M.H.H

View 2 Replies View Related

Script For Adding Fields To Table

Jul 23, 2005

How can I create a script that updates a table with new fields withoutlosing the data in the table (i.e., without dropping and recreating thetable)?Thanks.

View 5 Replies View Related

Adding New Column Fields Into A Big Table Issue

Aug 13, 2004

I have an existing table which has about 70 columns with 3 million rows in it. I was asked to add additional 50 new columns into the table. I have tried to add them in through the Enterprise manager design table but experiencing some problems. The adding process seemed never going to be end. Is there any good efficient way to do it??? I appreciate the help!


J8

View 4 Replies View Related

Adding A Calculated Field By Using Two Fields From Different Data Sets

Oct 1, 2007



Can I make a calculated field by using two fields from different data sets?(I'm talking about SSRS data sets)

I tried to do that. But I got a error message.




"Report item expressions can only refer to other report items within the same grouping scope or a containing grouping scope."


Please can some one help me out?

View 5 Replies View Related

Adding Conditional Statement To A Sum

Jun 12, 2007

currently I am creating a total if the date is between two entered dates:

select sum(case when exigo_data_sync.orders.orderdate between @prevMonthStart and @prevMonthEnd then 1 else 0 end) as PrevMonthCount,

I need to check an additional column for ($1.00) or $1.00. If it contains $1.00 then proceed as normal and add to the total. If it contains ($1.00) then subtract one from the total. Any advice?

View 7 Replies View Related

Statement To Identify Certain Fields

Sep 19, 2013

I am extract data from one of our Sql servers with the code below

Code:
Select SectDesc, PrGDesc, 'Product Description'=SetDescF, ProdSet, ProdSect, SectChar, Prdct.ProdGrp, OrigPr

From Prdct

INNER JOIN qryPRDGroupDets on Prdct.ProdGrp=qryPRDGroupDets.PGCode

where supersed =''
And OrigPr Not Like '9%' And OrigPr Not Like '%MDM%'
And LISTPR1>'0' And STANCOST>'0'

Which works fine, but what I need to do is reference the "OrigPr" field and mark it as "valid" or "Invalid", the "OrigPr" the field contains alpha numeric data e.g. A000, A001, A002 - ZZ99 and so on, amongst all of the potential different types of codes we have codes that end in treble Zero (0) e.g. A000 which are valid, but if they end in double 00 e.g. AA00 then this is invalid, the problem I have is I can't just add

Code:
'Marker'= Case When Right(OrigPr, 2) = '00' Then 'Invalid' ELSE 'Valid' End

For it will mark the A000 as invalid, is there a way of getting around this...

View 6 Replies View Related

SP Case Statement For Adding Innerjoin

Dec 4, 2006

I want to add an INNER JOIN based on a condition. If @Sports is not '' then I want to add the inner join statement...The following doesnt work:declare @Sports nvarchar(50)set @sports='1'
SELECT DISTINCT tblUserData.UserName,tblUserData.UserCode,Description,IsDonator,IsVIP,BirthDate,IsMale,ShowAgeOnly,tblCountries.CountryPicture,case @LanguageColumnName when 'nl' then tblSexuality.nl when 'en' then tblSexuality.en when 'de' then tblSexuality.deendas sexualityname,tblCountries.CountryName,ThumbNailPicture,UserRanking,LastActivityDate,NearestBigCity,DistanceToNearestBigCity,ROW_NUMBER() OVER (ORDER BY IsVip DESC,UserRanking DESC,LastActivityDate DESC) as RowNum FROM aspnet_UsersINNER JOIN tblUserData ON aspnet_Users.UserId = tblUserData.UserIDINNER JOIN tblCountries ON tblUserData.Country=tblCountries.CountryID INNER JOIN tblSexuality ON tblUserData.Sexuality=tblSexuality.SexualityID ,case @Sportswhen <>'' INNER JOIN tblUsersAndSports ON tblUserData.UserCode=tblUsersAndSports.UserCodeend

View 5 Replies View Related

Help On Adding Another Filter Statement On The Query

May 27, 2008

Hi everybody have this query that list all the duplicate records meaning the occurence of pubinfid in more than one row. This gets me fine. Iwant thought to insert a where clause that further filters the result where pubid between 30 and 33. I tried placing it after the FROM CLAUSE BUT DOESN'T GIVE me what I want it still includes records other than the between clause. Also I tried placing it after the HAVING clause but it includes records which has only one count.. Where could I place the where clause or is ther an alternative for this.



SELECT a.pubinfid, a.pubid, a.pubcount
FROM pubssubscribe AS a INNER JOIN
(SELECT pubinfid
FROM pubssubscribe
GROUP BY pubinfid
HAVING (COUNT(*) > 1)) AS b ON a.pubinfid = b.pubinfid



thanks

View 8 Replies View Related

Adding Groups In A Select Statement

Jul 8, 2014

The select statement below works fine but just to a limited extend.

There is just one table (prices) with 3 fields (ticker, close, date).

I use this select statement to get monthly returns for 1 ticker.

My question is: can I get this same statement for multiple tickers? I guess I have to group by ticker but I wouldn´t know how to do it.

--Get Monthly Returns As Percentage
WITH PriceMonthly_CTE
AS
(select
row_number() OVER (ORDER BY DATE_) AS Period,

[Code] .....

View 2 Replies View Related

Can&#39;t Query More Than 15 Fields In A Select Statement

Jul 14, 1999

An ADODB error spawn everytime I query more than 15 fields in a specific SQL Table. My sql statement is something like this:

set rs=server.createobject("ADODB.Recordset")
rs.open "select * from mytbl",application("mycon"),1,3

And the error is something like this:

"you can't query more than the maximum field "

View 1 Replies View Related

Convert Fields In Select Statement

Oct 31, 2006

The data I have has a 1 in for each Yes answer and a 2 for each no answer. I just want the select statement to show the word yes when there's a 1 and the word no when there's a 2. I don't need to update or change the database. Could anyone lead me in the right direction here? Thanks

View 4 Replies View Related

Excluding Fields In SELECT Statement

Oct 23, 2005

Hi,Is there a way to exclude fields in a query other than just includingthe ones you want. If there are 20 fields and you want to see all but3, it would be a lot easier to exclude the 3.Thanks

View 8 Replies View Related

SELECT Statement For Fields That Are Blank?

Jan 9, 2008

I thought this was working, but apparently it was not. I was wondering how I would create a SELECT Statement for values that are blank (Equal to ""). I really could have swore that this was working, but I guess it wasn't.

Here is my code:





Code Block

try
{
string conString = @"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + Environment.CurrentDirectory + @"MyDB.accdb;Jet OLEDB:Database Password=MyPassword;";

// create an open the connection
OleDbConnection conn = new OleDbConnection(conString);
OleDbCommand command = new OleDbCommand();
command = conn.CreateCommand();

// create the DataSet
DataSet ds = new DataSet();

// run the query
command.CommandText = "SELECT ID AS [#], Company AS [Company], FName AS [First Name], LName AS [Last Name], Type AS [Type] FROM Table1 WHERE Tags = @P0;";
OleDbDataAdapter adapter = new OleDbDataAdapter();
adapter = new OleDbDataAdapter(command);
command.Parameters.Add("@P0", OleDbType.VarChar).Value = "";
adapter.Fill(ds);

// close the connection
conn.Close();

bindingSource1.DataSource = ds.Tables[0];

DataGridView.DataSource = bindingSource1;

// set the size of the dataGridView Columns
this.DataGridView.Columns[0].Visible = false;
this.DataGridView.Columns[1].Width = 234;
this.DataGridView.Columns[2].Width = 50;
this.DataGridView.Columns[3].Width = 50;
this.DataGridView.Columns[4].Width = 75;

//Sort on the Title Column
DataGridViewColumn sortColumn = DataGridView.Columns[1];
ListSortDirection direction;
direction = ListSortDirection.Ascending;
DataGridView.Sort(sortColumn, direction);

//Set the Selected Property of the First Row to False
DataGridView.Rows[0].Selected = false;
}
catch
{
}

Thank you again for your help.

View 8 Replies View Related

Adding Column Values Together In SQL SELECT Statement

Aug 22, 2007

I have an SQL Select statement that I need to add a column to called SalePrice, the SalePrice column needs to be calculated by adding together the values of 12 columns, then multiplying that value by the value in a another column to calculate margin.  My issue is that I can only get it to add 7 column values together, if I add any more columns to the equation it just returns and null result.  My DB is SQL 2005 Express SP2.  My select statement is below:  SELECT dbo.MFG_DATA_Machines.ID, dbo.MFG_DATA_Machines.MachineName, dbo.MFG_DATA_Parts_CPU.PartDescription AS CPU,
dbo.MFG_DATA_Parts_CPU.PartCost AS CPUCost, dbo.MFG_DATA_Parts_Motherboard.PartDescription AS Motherboard,
dbo.MFG_DATA_Parts_Motherboard.PartCost AS MotherboardCost, dbo.MFG_DATA_Parts_RAM.PartDescription AS RAM,
dbo.MFG_DATA_Parts_RAM.PartCost AS RAMCost, dbo.MFG_DATA_Parts_HDD.PartDescription AS HDD,
dbo.MFG_DATA_Parts_HDD.PartCost AS HDDCost, dbo.MFG_DATA_Parts_OpticalDrive.PartDescription AS OpticalDrive,
dbo.MFG_DATA_Parts_OpticalDrive.PartCost AS OpticalDriveCost, dbo.MFG_DATA_Parts_Video.PartDescription AS Video,
dbo.MFG_DATA_Parts_Video.PartCost AS VideoCost, dbo.MFG_DATA_Parts_OS.PartDescription AS OS, dbo.MFG_DATA_Parts_OS.PartCost AS OSCost,
dbo.MFG_DATA_Parts_Modem.PartDescription AS Modem, dbo.MFG_DATA_Parts_Modem.PartCost AS ModemCost,
dbo.MFG_DATA_Parts_FloppyDrive.PartDescription AS FloppyDrive, dbo.MFG_DATA_Parts_FloppyDrive.PartCost AS FloppyDriveCost,
dbo.MFG_DATA_Parts_CardReader.PartDescription AS CardReader, dbo.MFG_DATA_Parts_CardReader.PartCost AS CardReaderCost,
dbo.MFG_DATA_Parts_PowerSupply.PartDescription AS PowerSupply, dbo.MFG_DATA_Parts_PowerSupply.PartCost AS PowerSupplyCost,
dbo.MFG_DATA_Parts_CaseType.PartDescription AS CaseType, dbo.MFG_DATA_Parts_CaseType.PartCost AS CaseTypeCost,
dbo.MFG_DATA_Machines.Notes, dbo.MFG_DATA_Machines.MarginPercent, dbo.MFG_DATA_Machines.PriceOverride,
(dbo.MFG_DATA_Parts_CPU.PartCost + dbo.MFG_DATA_Parts_Motherboard.PartCost + dbo.MFG_DATA_Parts_RAM.PartCost + dbo.MFG_DATA_Parts_HDD.PartCost
+ dbo.MFG_DATA_Parts_OpticalDrive.PartCost + dbo.MFG_DATA_Parts_Video.PartCost + dbo.MFG_DATA_Parts_OS.PartCost + dbo.MFG_DATA_Parts_Modem.PartCost
+ dbo.MFG_DATA_Parts_FloppyDrive.PartCost + dbo.MFG_DATA_Parts_CardReader.PartCost + dbo.MFG_DATA_Parts_PowerSupply.PartCost + dbo.MFG_DATA_Parts_CaseType.PartCost)
* ((dbo.MFG_DATA_Machines.MarginPercent + 100) / 100) AS SalePrice
FROM dbo.MFG_DATA_Machines LEFT OUTER JOIN
dbo.MFG_DATA_Parts_CaseType ON dbo.MFG_DATA_Machines.CaseType = dbo.MFG_DATA_Parts_CaseType.ID LEFT OUTER JOIN
dbo.MFG_DATA_Parts_Motherboard ON dbo.MFG_DATA_Machines.Motherboard = dbo.MFG_DATA_Parts_Motherboard.ID LEFT OUTER JOIN
dbo.MFG_DATA_Parts_Video ON dbo.MFG_DATA_Machines.Video = dbo.MFG_DATA_Parts_Video.ID LEFT OUTER JOIN
dbo.MFG_DATA_Parts_RAM ON dbo.MFG_DATA_Machines.RAM = dbo.MFG_DATA_Parts_RAM.ID LEFT OUTER JOIN
dbo.MFG_DATA_Parts_PowerSupply ON dbo.MFG_DATA_Machines.PowerSupply = dbo.MFG_DATA_Parts_PowerSupply.ID LEFT OUTER JOIN
dbo.MFG_DATA_Parts_OS ON dbo.MFG_DATA_Machines.OS = dbo.MFG_DATA_Parts_OS.ID LEFT OUTER JOIN
dbo.MFG_DATA_Parts_OpticalDrive ON dbo.MFG_DATA_Machines.OpticalDrive = dbo.MFG_DATA_Parts_OpticalDrive.ID LEFT OUTER JOIN
dbo.MFG_DATA_Parts_Modem ON dbo.MFG_DATA_Machines.Modem = dbo.MFG_DATA_Parts_Modem.ID LEFT OUTER JOIN
dbo.MFG_DATA_Parts_HDD ON dbo.MFG_DATA_Machines.HardDisk = dbo.MFG_DATA_Parts_HDD.ID LEFT OUTER JOIN
dbo.MFG_DATA_Parts_FloppyDrive ON dbo.MFG_DATA_Machines.FloppyDrive = dbo.MFG_DATA_Parts_FloppyDrive.ID LEFT OUTER JOIN
dbo.MFG_DATA_Parts_CPU ON dbo.MFG_DATA_Machines.CPU = dbo.MFG_DATA_Parts_CPU.ID LEFT OUTER JOIN
dbo.MFG_DATA_Parts_CardReader ON dbo.MFG_DATA_Machines.CardReader = dbo.MFG_DATA_Parts_CardReader.ID  

View 2 Replies View Related

Adding Case Statement To Existing Query

Apr 3, 2008

I was asked to add an additional column to an existing query. I'm using Microsoft Query with a MS SQL 2000 server, and don't have much knowledge of SQL in general. Here's the existing query:



SELECT A.COMPANYCODE,
A.INVOICENUMBER,
A.LINENUMBER,
A.SONUMBER,
A.CUSTOMERCODE,
A.SHIPPERNUMBER,
A.INVOICEDATE,
A.ITEMCODE,
A.QUANTITYINVOICED,
A.UNITPRICE AS 'InvPrice',
A.QUANTITYINVOICED * A.UNITPRICE AS 'ExtInvPrice',
INVENTORY.UNITPRICE AS 'StdPrice',
INVENTORY.STANDARDCOST,
A.QUANTITYINVOICED * INVENTORY.STANDARDCOST AS 'ExtCost',
(A.QUANTITYINVOICED * A.UNITPRICE) - (A.QUANTITYINVOICED * INVENTORY.STANDARDCOST) AS 'GM$',
(INVENTORY.UNITPRICE - A.UNITPRICE) * -1 AS 'PriceListDiff'
FROM ABW.DBO.SALESANALYSISHISTORY A,
ABW.DBO.INVENTORY INVENTORY
WHERE INVENTORY.COMPANYCODE = A.COMPANYCODE
AND INVENTORY.ITEMCODE = A.ITEMCODE
AND ((A.COMPANYCODE = 'csp')
AND (A.QUANTITYINVOICED <>$ 0)
AND (A.INVOICEDATE BETWEEN '03/1/08' AND '03/31/08'))
ORDER BY A.INVOICEDATE,
A.ITEMCODE



They want a column added to the current query where if A.Unitprice is greater than or equal to Inventory.UnitPrice then populate the column with A.QuantityInvoiced*A.UnitPrice. I posted on another forum, and the advice I got was to add this:


SELECT NewColumn = CASE
WHEN A.Unitprice >= Inventory.UnitPrice THEN A.QuantityInvoiced * A.Unitprice
ELSE 'null'
END,
FROM ABW.DBO.SALESANALYSISHISTORY A,
ABW.DBO.INVENTORY INVENTORY

I'm not sure how to integrate it to the current query, so I've tried running it by itself, and I get the error: Didn't expect 'A' after the SELECT column list.

Any help would be greatly appreciated to modify the current query to display the new column.

View 2 Replies View Related

SQL Statement, Adding Two COUNT/CASE Statements

Dec 12, 2007



SELECT COUNT(DISTINCT CASE WHEN visit_type = 0 THEN visitor_id END) AS [New Visitors],
COUNT(DISTINCT CASE WHEN visit_type = 0 THEN visitor_id END) AS [Returning Visitors]
FROM content_hits_tbl
WHERE (hit_date BETWEEN DATEADD(mm, - 1, GETDATE()) AND GETDATE())

=======================

How do I add up both COUNT/CASE columns? Would it be:
SUM([New Visitors] + [Returning Visitors]) AS Total


I tried this and it doesn't work. I get invalid column names error for both.

I have even tried:
SUM([COUNT(DISTINCT CASE WHEN visit_type = 0 THEN visitor_id END)] + [COUNT(DISTINCT CASE WHEN visit_type = 0 THEN visitor_id END)]) AS Total

You would think that there would be some gui functionality in VS08 that would do this...


Thoughts are greatly appreciated!

TT

View 8 Replies View Related

SQL Statement For Querying Data With Dynamic Fields

Sep 19, 2006

I am working on a project in which a customer wants to be able to list and search their inventory and display the items in a table/grid on a web page.Each item in their inventory has a set of properties - for example, manufacturer, price, serial number, name, etc.  The complicated part is that they want an admin to be able to modify/add/delete the set of properties.  So for example, they could add the attribute "size."   Given that, I think what is needed is 3 tables - one to store the set of properties schema, one for the items, and one to store the actual properties for each item.  I know i COULD use alter table statements to add and delete columns, but that doesn't seem like the "right" solution.I would like to be able to write a query such that each row returns the item and all its properties - then i can easily bind to a datagrid.  However, what would the query be to do this?  I also need to be able to allow the customer to query for items with certain properties - i imagine the sql for that to be similar to this:SELECT * FROM items d WHERE d.Id IN(SELECT a.ItemId FROM attributes a WHERE a.Name = "Manufacturer" AND a.Value = "Samsung") AND d.Id IN(SELECT a.ItemId FROM attributes a WHERE a.Name = "SerialNumber" AND a.Value = "3223")

View 1 Replies View Related

Combine Fields And Text In Select Statement

Jan 1, 2007

Is it possible to combine fields and text in a select statement?
In a dropDownList I want to show a combination of two different fields, and have the value of the selected item come from a third field. So, I thought I could maybe do something like this:
  SELECT DISTINCT GRP AS GroupName, "Year: " + YEAR + "Grade: " + GRD AS ShowMe
FROM GE_Data
WHERE (DIST = @DIST) 
 
I hoped that would take the values in YEAR and GRD and concatenate them with the other text. Then my dropDownList could show the ShowMe value and have the GroupName as the value it passes on. However, when I test this in the VS Query Builder, it says that Year and Grade are unknown column names and changes the double-quotes to square brackets.
If this is possible, or there's a better way to do it, I'd love some more info.
Thanks!
-Mathminded

View 7 Replies View Related

Need Help With Insert Statement On Table With Several Non-null Fields

Apr 30, 2007

On my aspx page I have a basic sqldatasource and gridview - both were set up using the wizards. The SQL Server 2000 database table that the sqldatasource is querying has some fields in it that are set to not allow nulls, however, the gridview control is not displaying all of the fields in the table - including some of the non-null fields.
My problem is, when I run an insert on the table from my aspx page, I get an error that says: "Cannot insert the value NULL into column 'ColumnName', table 'TableName'; column does not allow nulls. INSERT fails. The statement has been terminated."
The 'ColumnName', as you may have guessed, is one of the aforementioned columns that doesn't allow nulls, but isn't in the GridView.
How can I do an insert on this table without messing with the non-null fields. Those fields are relevant to the purposes of the gridview, so I don't want to display them, let alone allow editing of them.
Any suggestions on my options would be greatly appreciated!
Thanks
Capella07

View 2 Replies View Related

SELECT Statement With 2 Columns Originating From The Same Fields

Jul 19, 2006

Hello,

I've been busy all night searching and reading trying to figure out how I can do the following.

I have a table that tracks user IDs in multiple fields. When I select records from this table I need a way to resolve those ID fields back into user names by referencing the users table. SQL statement thus far...

SELECT A.Username as NameA, B.Username As NameB, FROM theTable, Users As A, Users As B WHERE theTable.UserIDA ???

How do I resolve theTable.UserIDA and theTable.UserIDB back to Users.Username so that the records returned fill the fields NameA and NameB?

Your help is greatly appreciated.

Thanks,

-Mike
Ontario, Canada

View 5 Replies View Related

Problem With Null Fields And Case Statement

Jul 21, 2004

Can someone help me with this :

Case WHEN ISNULL (dbo.BLH.n1name,'NOTHING')= 'NOTHING' then dbo.BLH.coname else dbo.BLH.n1name End as CustName

as the strored proc is not returning value when the fileds is blank

cheers

View 2 Replies View Related

SELECT Statement To Display Combined Fields

Oct 17, 2004

Dear All
I am trying to populate an OledbDatareader for binding to a ASP datagrid.

For this I use select statement to display combined fields in a datagrid cell.
Eg. Select (Field1+ '<br/>' + Field2 + '<br/>' + Field 3) As Address .. and so on.
But the problem is if any of the three field is null the combined field 'Address' returns as Null.
Please help me to overcome this problem.

Regards
kalanad ( beginner)

View 12 Replies View Related

UPDATE Statement Creating NULL Fields?

Oct 23, 2014

I am attempting to update a table that drives a report. The report is at the order level. An order can have several types of demand (revenue $): Ship and/or Backordered. When I update one of these demand fields on the report table, I would expect those orders that have that type of demand to update, and those orders that don't have that type of demand to remain at their current value ($0 in this example which is the table default). But what is happening is that orders that don't have that type of demand are changing to NULL. What am i doing wrong with my update statement that is causing this?

Code:
CREATE TABLE #sales(
OrdNo int,
StatusGrp varchar(10),
Demand decimal(10,2)
)
INSERT INTO #sales
VALUES (1,'Ship',100.00)

[Code] .....

View 6 Replies View Related







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