Combining Subquery Results Into One Field

Jun 19, 2007

Hello there

 I have an application that allows users to book rooms in a building. I have a booking request table and a rooms booked table since there is a booking that can be made that includes multiple rooms.  I have an instance where i need to select the booking requests for a particular date and need to display the rooms for each booking.  Since the rooms booked table has the booking request id i'm wondering if there is a way to combine all the subquery results into one record to get around the error of having multiple records being returned in a sub query.  The table structures are as follow:

 bookingrequestion - bookingrequestid, startdate, enddate

roomsbooked-id, bookingrequestid, roomname

i'm basically trying to use the following query

select br.bookingrequestid, (select roomname from roomsbooked where id = br.bookingrequest) as rooms

i'd like the results of the subquery to return the room names as A,B,C.  I'm trying to avoid having to obtain the recordset for the booking requests and then loop through them and for each one obtain a recordset for the rooms, seems like too many database hits to me.

 thanks

 

 

View 1 Replies


ADVERTISEMENT

Combining Two Field Results From Same Table

Mar 19, 2012

My application saves customer email addresses in two different fields in ym table

How do i combine two fields from the same table in a select statement?

I've tried the following:

Select EmailAddy + ', ' + FriendsEmail AS Expr1
FROM dbo.Contacts

But all I get are the results from one field.

View 4 Replies View Related

SQL Server 2008 :: Elegant Way For Returning All Results When Subquery Returns No Results?

Mar 25, 2015

I have four tables: Customer (CustomerId INT, CountyId INT), County (CountyId INT), Search(SearchId INT), and SearchCriteria (SearchCriteriaId INT, SearchId INT, CountyId INT, [others not related to this]).

I want to search Customer based off of the Search record, which could have multiple SearchCriteria records. However, if there aren't any SearchCriteria records with CountyId populated for a given Search, I want it to assume to get all Customer records, regardless of CountyId.

Right now, I'm doing it this way.

DECLARE @SearchId INT = 100
SELECT * FROM Customer WHERE
CountyId IN
(
SELECT CASE WHEN EXISTS(SELECT CountyId FROM SearchCriteria WHERE SearchId = @SearchId)
THEN SearchCriteria.CountyId

[Code] .....

This works; it just seems cludgy. Is there a more elegant way to do this?

View 4 Replies View Related

Need Help In Combining Results ..

Oct 22, 2007

Hi all,
I need some help in combining two results. I am using the Northwind Database and the Orders Table. The first select outputs the table shown below, Table 1 and the second select outputs the result in the second table  Table 2. How can I combine these two to get the third table, Table 3 ?   
SELECT     TOP 100 PERCENT EmployeeID, COUNT(ShipVia) AS CountShipVia1
FROM         dbo.Orders
WHERE     (ShipVia = 1)
GROUP BY EmployeeID
ORDER BY EmployeeID
  
Table 1 Results 
EmployeeID   CountShipVia1




1

                    82


2

                    71


3

                    81


4

                    116


5

                    29


6

                    48


7

                    44


8

                    75


9

                    29                                                                        
SELECT     TOP 100 PERCENT EmployeeID, COUNT(ShipVia) AS CountShipVia2
FROM         dbo.Orders
WHERE     (ShipVia = 2)
GROUP BY EmployeeID
ORDER BY EmployeeID   
Table 2 results 
EmployeeID   CountShipVia2




1

                    44


2

                    36


3

                    45


4

                    70


5

                    15


6

                    25


7

                    24


8

                    48


9

                    19      
Table 3 the desired result:  
EmployeeID    CountShipVia1     CountShipVia2




1

                         82                      44


2

                         71                      36


3

                         81                      45


4

                         116                    70


5

                         29                      15


6

                         48                      25


7

                         44                      24


8

                         75                      48


9

                         29                      19
 
 
thanksrobby 

View 5 Replies View Related

Combining Queries/ Results

May 4, 2005

I have created a search interface for a large table and I allow users to search on keywords. The users can enter multiple keywords and I build a SQL based on their input to search a full-text indexed table. However the users want to be able to search like an old system they had, where they enter single words and then combine their searches to drill-down into the results. What would be the best method to combine searches?At the moment I can create a merged query from 2 queries if they have searched using single words, but I know down the line it will get far more complicated if they keep combining and merging even with multiple word entries. Each time they search I store the 'where' section of each query, then if they choose to combine I have a function to build a new query through arrays (to eliminate duplicates and sort etc)Is there a better way in SQL to combine queries as sometimes the logic of the combined query means no results are returned (because of OR/ AND conditions in the wrong places etc)e.g.1. Select count(ID) as myCount FROM myTable where (CONTAINS(title,'"run"') OR CONTAINS(subject,'"run"'))2. Select count(ID) as myCount FROM myTable where (CONTAINS(title,'"level"') OR CONTAINS(subject,'"level"'))Combined using my function creates:Select count(ID) as myCount FROM myTable where (contains(title,'"level"') AND contains(title,'"run"')) OR (contains(subject,'"level"') AND contains(subject,'"run"'))
When I combine I'm drilling down, so if the first query returns a count of 400 (where the title OR subject contains 'run') and then the second query returns 600 records (where the title OR subject contains 'level') I need to combine so that I'm looking for records where the title contains both keywords 'run' AND 'level' OR else the subject contains both 'run' AND 'level' and I end up with say 50 records where the title has both keywords OR the subject holds both words. I think the main trouble lies if they try combine a previously combines search with a new search. here my logic gets totally thrown and I'm not sure how to handle soemthing like this. Has anyone got any ideas or experience with this kind of functionality? In SQL or even in vb.net is there a method to combine searches easily?

View 1 Replies View Related

Combining Multiple Results Into One Row

Mar 6, 2006

Hey guys, here is my issue, i'm trying to combine multiple columns of data into one row. For example, I have a temp table:

create table #Custom_Address
( patient_idchar(10)null,
episode_idchar(3)null,
custom_address varchar(100) null
)

As you can tell, the 'custom_address' column is going to house the results of the combination.

I am trying to INSERT (combine) using this statement:

INSERT #Custom_Address
select cm.patient_id, cm.episode_id, (cc.coverage_plan_add1 +' ' + coverage_plan_add2 + ' ' + coverage_plan_city+ ' ' + coverage_plan_st+ ' ' + coverage_plan_zip) as custom_address
FROM #ClaimMaster cm join Coverage_Custom cc on cm.patient_id = cc.patient_id and cm.episode_id = cc.episode_id
WHEREcc.coverage_plan_add1 > 0
OR cc.coverage_plan_add2 > 0
OR cc.coverage_plan_city > 0
OR cc.coverage_plan_st > 0
OR cc.coverage_plan_zip > 0
GROUP BY cm.patient_id, cm.episode_id

I want to insert the patient_id, episode_id, and then combine the 'cc.coverage_plan_add1 +' ' + coverage_plan_add2 + ' ' + coverage_plan_city+ ' ' + coverage_plan_st+ ' ' + coverage_plan_zip' data to represent my "custom_address" column and only when there is data in those columns.

Then I do my update:

UPDATE#ClaimMaster
SET #ClaimMaster.custom_address = ca.custom_address
FROM #ClaimMaster
JOIN#Custom_Address ca on #ClaimMaster.patient_id = ca.patient_id and #ClaimMaster.episode_id = ca.episode_id

However, when I do this, it says my 'cc.coverage_plan_add1 + etc' columns are invalid because they are not contained in either an aggregate function or a GROUP By clause.

If i'm combining all the data to represent a single column, how do I format my group by clause? Or is my entire INSERT statement wrong?

As a side note, my #ClaimMaster table is another temp table, but the problem is not in there.

View 5 Replies View Related

Combining Results Into Columns

Oct 18, 2013

I have a query (SELECT * FROM Pricing) that produces the following results

Stockcode|ProductID|OurSellPrice|SupplierTypeID|CompetitorPrice
SC0001___|123______|22.45_______|1_____________|25.23
SC0001___|123______|21.45_______|2_____________|25.23

I want to convert this to the following :

Stockcode|ProductID|OurSellPriceType1|OurSellPriceType2|CompetitorPrice
SC001____|123______|22.45____________|21.45____________|25.23

We only have two types of suppliers but not every product is available from each type of supplier so we might get the following results:

Stockcode|ProductID|OurSellPrice|SupplierTypeID|CompetitorPrice
SC0002___|124______|22.45_______|1_____________|25.23

Stockcode|ProductID|OurSellPriceType1|OurSellPriceType2|CompetitorPrice
SC001____|123______|22.45____________|NULL_____________|25.23

View 1 Replies View Related

Combining Two Results Sets

Jun 3, 2014

i have these 2 queries with the included results sets...What commands could I use to take the TotalBlueCircle Column from the 2nd Results Set and have it included next to the TotalRugby column in the 1st results set??Do i need to do a UNION or use Sub Queries or something else?

View 5 Replies View Related

Combining The Results Of A Cursor Loop

Aug 7, 2007

Need a little help here.

I have a set of product ids fed in as a delimited string and for each I need to extract the top 1 record from another query based on the id.

I need the results as one table.

Here is my code.
___________________________________
SET NOCOUNT ON

DECLARE @IdsString VARCHAR(255), @Id int


SELECT @IdsString = '918|808|1214|89|995|300|526|1207'

DECLARE GetData CURSOR
FOR Select s.ProductID FROM dbo.SplitProductIDs(@IdsString) as s

OPEN GetData

FETCH NEXT FROM GetData
INTO @Id

WHILE @@FETCH_STATUS = 0
BEGIN
SELECT TOP 1 v.*
FROM dbo.GetProductRateView as v
WHERE v.[id] = @Id

FETCH NEXT FROM GetData
INTO @Id
END

CLOSE GetData
DEALLOCATE GetData
_____________________________________

Do I need to create a temp table and do an 'Insert Into(Select...' with each cusor result or is there a better way?

Any help would be much appreciated.

NB Database was not designed and the client will not tolerate any changes to structure of the tables :eek:

Regards

Shaun McGuile

View 14 Replies View Related

Combining 3 Tables To Get Required Results

Jul 2, 2014

I have a quick question for the SQL community about how to combine 3 tables to get the results needed...

The table names are :
setup_zipcode,
setup_category,
and listing

and inside setup_zipcode it has the columns:
zip_id , zip_code, zip_latitude and zip_longitude

and inside the setup_category it has the columns:
category_id, category_parent, category_path and category_name

and the final table for listing has the columns:
listing_id, listing_member, listing_category, listing_address, listing_city, listing_state, listing_zip and listing_country

I am having trouble getting them to inter-relate an query the needed results as I need to get back the LAT & LONG from the zip_latitude & zip_longitude columns for specific listing ids in certain categories...

So, the ONLY same variable between them is that listing_zip from LISTING table and zip_code from SETUP_ZIPCODE show the same zip codes..

How can I create a SQL query that checks the current category that is being displayed on the page results live and insert only the listing id (clients) that are in that listing_category and also pull that listing_id client's related zip_latitude & zip_longitude that relates to their specific listing_zip from the zip_code row in setup_zipcode?

I have tried many things and this looks like it would work but does not pull the related LAT & LONGs ...

$cat is assigned in a query previously on the page...

SELECT *
FROM
setup_zipcode, setup_category, listing
WHERE
listing_category LIKE '%-$cat-%'
AND
listing_zip = zip_code
AND
category_id = '$cat'
ORDER BY listing_title ASC

When I try to take the results (not sure if I am missing a step for printing the results after querying them or having to assign them somehow) and use the SMARTY TAGS assigned to zip_latitude and zip_longitude nothing shows on the published page... The other variables for the address do..

I have a loop defined as $listing_id and section is var so when I pull the query info using {$listing_address[var]}, {$listing_city[var]}, etc.. they work, but {$zip_latitude[var]} or {$listing_id[var].$zip_latitude} and {$listing_id[var].zip_latitude} so on do not work..

If I can get the variables to exist in the SQL QUERY then at least I will know that is correct and can work on how to get them to show correctly in the address for a map afterwards...

View 1 Replies View Related

Combining Results In Comma Delimitered Strings

Jul 26, 2004

I know this has been addressed before but I can't find it...

I have a table with with a column called PersonId. I want a query that will return all the PersonId's as a comma delimited string...

Anyone able to help?

View 14 Replies View Related

Combining Results Of Two Similar Queries Into One Result Set?

Mar 5, 2012

Customers order a product and enter in a source code (sourceCd). This sourceCd is tied to a marketing program. Idea being we can see that 100 customers ordered from this promo, 200 from this catalog, etc etc. The sourceCd that a customer enters is not always accurate so there is a magic process that adjusts this OrigSourceCd into a final SourceCd, that may or may not be the same.

I am trying to generate a result set of customer count by sales program based on both the original and final source code. Problem is, I have to do each query separately because in one, I have to join SourceCdKey to SourceCdKey to get the program associated with that SourceCd and in the other i have to join OrigSourceCdKey to SourceCdKey to get the program associated with the original sourceCd. There are some programs is one results set that are not in the other, and vice versa.

I'm trying to generate a list of that shows customer counts before and after for each program, some which may be null for one, but have counts for the other. I have tries creating 2 separating views and joining them but that doesn't work because it only returns the ones they have in common.

View 6 Replies View Related

Combining Two Queries Producing Unexpected Results

Mar 5, 2008

I'm having difficulty coming up with the right syntax for a query. Suppose I have a database containing a Stores table, an ProductInventory table, and a Customers table. The Stores table has an ID field that serves as a foreign key in both the ProductInventory table and in the Customers table. I'm trying to write a query that, for each Store record, will return the total number of records in the ProductInventory table and the total number of records in the Customers table.


The following query returns, for each store, the total number of records in the ProductInventory table:

SELECT Stores.Name,
COUNT(ProductInventory.ID) AS ProductInventoryItemCount
FROM Stores
LEFT JOIN ProductInventory ON Stores.ID = ProductInventory.StoreID
GROUP BY Stores.Name

The following query returns, for each store, the total number of records in the Customers table:

SELECT Stores.Name,
COUNT(Customers.ID) AS CustomerCount
FROM Stores
LEFT JOIN ProductInventory ON Stores.ID = Customers.StoreID
GROUP BY Stores.Name



I combined the two queries:

SELECT Stores.Name,
COUNT(ProductInventory.ID) AS ProductInventoryItemCount,
COUNT(Customers.ID) AS CustomerCount
FROM Stores
LEFT JOIN ProductInventory ON Stores.ID = ProductInventory.StoreID
LEFT JOIN Customers ON Stores.ID = Customers.StoreID
GROUP BY Stores.Name

When I run this last query, however, I get an "Arithmetic overflow error converting expression to data type int" error. Using COUNT_BIG instead of COUNT eliminates the error, but the numbers that are generated are astronomical in size. This indicates to me that there is a *lot* more table joining going on than I expected


What is the correct syntax to produce the desired results? I have a few other tables similar to ProductInventory and Customers; I'm hoping to extend the correct syntax so as to be able to get a comprehensive record count list for each store. Thanks for your help!

View 7 Replies View Related

Combining Results Of Multiple Rows Based On Group?

Jul 17, 2013

I have a table of attributes set up as follows:

ID, Value, Group
1, Football, Sports
1, Baseball, Sports
1, Basketball, Sports
2, Baseball, Sports
3, Football, Sports
1, Lambda Sigma, Greeks
2, Delta Delta, Greeks
etc.

I want a query that will combine that values for each ID into one field per group. So if ID 1 has multiple sports but also a greek attribute, they end up with two rows; the first row containing the combined sports values and the second row the greek valued not combined, because there was only one value in that group for that ID. For example:

ID, Combined Values, Group
1, Football Baseball Basketball, Sports
2, Baseball, Sports
3, Football, Sports
1, Lambda Sigma, Greeks
2, Delta Delta, Greeks

View 5 Replies View Related

Narrowing Results Using Subquery

Mar 4, 2014

Use a subquery to narrow the results to only those customers with a yearly income that is greater than

-- or the same as the average of all customers.
-- 883 rows

SELECT C.LastName+', '+C.FirstName AS FullName, C.YearlyIncome
FROM dbo.DimCustomer AS C
JOIN (SELECT AVG(YearlyIncome) AS AVGIncome FROM dbo.DimCustomer) AS D
ON C.YearlyIncome >= D.AVGIncome

After executing this, I got a whopping 9000+ rows. How can I narrow down to 883 rows?

View 4 Replies View Related

SQL Subquery - Concatenate Results

Dec 26, 2007

I am trying to concatenate subquery results and am getting : "Subquery returned more than 1 value."


Here is what I am tryignt o work with:
select top 100 *,
( select TOP 100 coalesce(qcnotes + ' ', '')
from qc_lead_history qh
where qh.master_ref_lead_id = dl.master_Ref_lead_Id)
from preleads dl
order by submit_datetime desc

View 2 Replies View Related

Combine Multiple Results Of Subquery

Nov 14, 2006

Table users:
userid, name, added...

Table groups
groupid, groupname...

Table groupadmins:
userid, groupid

The users to groups relationship is many-to-many, which is why I created the intermediate table. I would like to return a recordset like:
userid, name, groupids
12344, 'Bob', '123,234,345'

If I try to just select groupid from groupadmins:
select userid, name, (select groupid from groupadmins where groupadmins.userid = users.userid) as groupids from users

then I'll get an error that a subquery is returning multiple results. Some users are not group admins and those that are may have a single or multiple groups. The only thing I can think of is to select the groupids seperately and use a cursor to loop through the results and build a string with the groupids. Then I would select the string with the rest of the fields that I want for return. Is there a better way to do this?

View 4 Replies View Related

Combining Data Into One Field

Apr 14, 2008

Hello,
I'm not sure if this is possible, but here is what I'm trying to do:

I have a table that contains for example the following:

SPECIES PERCENT
s 19
P 25
B 32
D 24

I want to end up with a single field, titled S_P_B_D with the values 19_25_32_24.

Possible?
Thanks in advance.
Amber

View 16 Replies View Related

Hide Subquery Results In Stored Procedure

Jul 23, 2005

Hi everyone, I have a stored procedure which I use to query a table.The first part of the stored procedure uses a cursor to update a temp tablewhilst the second part of the query actually retrieves information from adatabase table utilising information based on the temp table.My problem is that when I run the procedure, the cursors status is outputand therefore becomes part of the result set. I really only want theinformation returned returned by the second query as my result set.So, is it possible to hide the results of the cursor so that it does notbecome part of the result set? I have tried nocount on around the cursorroutine but this does not workThanks in advanceMark

View 4 Replies View Related

SQL Server 2012 :: Combining Multiple Rows Into One Field

Feb 26, 2015

I am needing to combine the Notes field where Number and date are the same...For example

for Number 0000000003 I need notes to Read ('CHK # 2452 FOR $122.49 REJECTED AS NSF ON 2/25/15') the note counter is different for each row, and is combination of special char, 0-Z and looks like the (!) depicts the start of a new Number.

CREATE TABLE [dbo].[MyTable](
[NUMBER] [varchar](10) NULL,
[HD_DATE_TIMEX] [datetime] NULL,
[TRANS_NO] [varchar](2) NULL,
[MESSAGE_COUNTER] [varchar](1) NULL,

[Code] .....

View 9 Replies View Related

Subquery Returned More Than 1 Value. This Is Not Permitted When The Subquery Follows =, !=, &<, &<= , &>, &>= Or When The Subquery Is Used As An Expression.

Apr 26, 2008

hello friends.. I am newbie for sql server...I having a problem when executing this procedure .... ALTER PROCEDURE [dbo].[spgetvalues]    @Uid intASBEGIN    SET NOCOUNT ON;        select                                  DATEPART(year, c.fy)as fy,                                                (select contribeamount from wh_contribute where and contribename like 'Retire-Plan B-1%      JRF' ) as survivorship,                (select contribeamount from wh_contribute where and contribename like  'Gross Earnings' and ) as ytdgross,                (select contribeamount from wh_contribute where and contribename like 'Retire-Plan B-1.5%      JRP') as totalcontrib,                                                       from    wh_contribute c                       where    c.uid=@Uid                 Order by fy Asc .....what is the wrong here??  " Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression."please reply asap... 

View 1 Replies View Related

Subquery Returned More Than 1 Value. This Is Not Permitted When The Subquery Follows =, !=, &<, &<= , &>, &>= Or When The Subquery Is Used As An Expression.

Jul 20, 2005

I am getting 2 resultsets depending on conditon, In the secondconditon i am getting the above error could anyone help me..........CREATE proc sp_count_AllNewsPapers@CustomerId intasdeclare @NewsId intset @NewsId = (select NewsDelId from NewsDelivery whereCustomerId=@CustomerId )if not exists(select CustomerId from NewsDelivery whereNewsPapersId=@NewsId)beginselect count( NewsPapersId) from NewsPapersendif exists(select CustomerId from NewsDelivery whereNewsPapersId=@NewsId)beginselect count(NewsDelId) from NewsDelivery whereCustomerid=@CustomeridendGO

View 3 Replies View Related

Subquery Returned More Than 1 Value. This Is Not Permitted When The Subquery Follows =, !=, &&<, &&<= , &&>, &&>= Or When The Subquery I

Mar 6, 2008

I am getting an error as

Msg 512, Level 16, State 1, Line 1

Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.

while running the following query.





SELECT DISTINCT EmployeeDetails.FirstName+' '+EmployeeDetails.LastName AS EmpName,

LUP_FIX_DeptDetails.DeptName AS CurrentDepartment,

LUP_FIX_DesigDetails.DesigName AS CurrentDesignation,

LUP_FIX_ProjectDetails.ProjectName AS CurrentProject,

ManagerName=(SELECT E.FirstName+' '+E.LastName

FROM EmployeeDetails E

INNER JOIN LUP_EmpProject

ON E.Empid=LUP_EmpProject.Empid

INNER JOIN LUP_FIX_ProjectDetails

ON LUP_EmpProject.Projectid = LUP_FIX_ProjectDetails.Projectid

WHERE LUP_FIX_ProjectDetails.Managerid = E.Empid)



FROM EmployeeDetails

INNER JOIN LUP_EmpDepartment

ON EmployeeDetails.Empid=LUP_EmpDepartment.Empid

INNER JOIN LUP_FIX_DeptDetails

ON LUP_EmpDepartment.Deptid=LUP_FIX_DeptDetails.Deptid

AND LUP_EmpDepartment.Date=(SELECT TOP 1 LUP_EmpDepartment.Date

FROM LUP_EmpDepartment

WHERE EmployeeDetails.Empid=LUP_EmpDepartment.Empid

ORDER BY LUP_EmpDepartment.Date DESC)

INNER JOIN LUP_EmpDesignation

ON EmployeeDetails.Empid=LUP_EmpDesignation.Empid

INNER JOIN LUP_FIX_DesigDetails

ON LUP_EmpDesignation.Desigid=LUP_FIX_DesigDetails.Desigid

AND LUP_EmpDesignation.Date=(SELECT TOP 1 LUP_EmpDesignation.Date

FROM LUP_EmpDesignation

WHERE EmployeeDetails.Empid=LUP_EmpDesignation.Empid

ORDER BY LUP_EmpDesignation.Date DESC)

INNER JOIN LUP_EmpProject

ON EmployeeDetails.Empid=LUP_EmpProject.Empid

AND LUP_EmpProject.StartDate=(SELECT TOP 1 LUP_EmpProject.StartDate

FROM LUP_EmpProject

WHERE EmployeeDetails.Empid=LUP_EmpProject.Empid

ORDER BY LUP_EmpProject.StartDate DESC)

INNER JOIN LUP_FIX_ProjectDetails

ON LUP_EmpProject.Projectid=LUP_FIX_ProjectDetails.Projectid



WHERE EmployeeDetails.Empid=1

PLEASE HELP.................

View 1 Replies View Related

Subquery Returned More Than 1 Value. This Is Not Permitted When The Subquery Follows =, !=, &&<, &&<= , &&>, &&>= Or When The Subquery I

May 14, 2008

Hi,

I've running the below query for months ans suddenly today started getting the following error :"Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression."

Any ideas as to why??

SELECT t0.DocNum, t0.Status, t0.ItemCode, t0.Warehouse, t0.OriginNum, t0.U_SOLineNo, ORDR.NumAtCard, ORDR.CardCode, OITM_1.U_Cultivar,
RDR1.U_Variety,
(SELECT OITM.U_Variety
FROM OWOR INNER JOIN
WOR1 ON OWOR.DocEntry = WOR1.DocEntry INNER JOIN
OITM INNER JOIN
OITB ON OITM.ItmsGrpCod = OITB.ItmsGrpCod ON WOR1.ItemCode = OITM.ItemCode
WHERE (OITB.ItmsGrpNam = 'Basic Fruit') AND (OWOR.DocNum = t0.DocNum)) AS Expr1, OITM_1.U_Organisation, OITM_1.U_Commodity,
OITM_1.U_Pack, OITM_1.U_Grade, RDR1.U_SizeCount, OITM_1.U_InvCode, OITM_1.U_Brand, OITM_1.U_PalleBase, OITM_1.U_Crt_Pallet,
OITM_1.U_LabelType, RDR1.U_DEPOT, OITM_1.U_PLU, RDR1.U_Trgt_Mrkt, RDR1.U_Wrap_Type, ORDR.U_SCCode
FROM OWOR AS t0 INNER JOIN
ORDR ON t0.OriginNum = ORDR.DocNum INNER JOIN
RDR1 ON ORDR.DocEntry = RDR1.DocEntry AND t0.U_SOLineNo - 1 = RDR1.LineNum INNER JOIN
OITM AS OITM_1 ON t0.ItemCode = OITM_1.ItemCode
WHERE (t0.Status <> 'L')

Thanks

Jacquues

View 4 Replies View Related

No Results Returned When Using BIT Field

Oct 16, 2007

Hi,
 I've got what is probably a simple question, but for the life of me I cannot figure it out and it is starting to do my head in .  I have the following query:-
Select e.intEmployeeID AS ID, e.txtFirstName AS [First Name], e.txtLastName As [Last Name], e.txtMobile As [Mobile], i.txtWorkEmail as from EMPLOYEE AS e INNER JOIN EMPLOYEE_INFORMATION AS i ON i.intEmployeeID = e.intEmployeeID WHERE (((e.blnActive = -1) AND (e.txtFirstName Like '%b%')) OR ((e.blnActive = -1) AND (e.txtLastName Like '%b%')) ) ORDER By e.txtFirstName
In my database, the field blnActive is a BIT field.  When I try to run this query, I get no results returned.  If I remove the (e.blnActive = -1) WHERE statement segment, I get the correct results.  The field blnActive contains -1 for every record but I cannot work out why it will not return these records .... aaarrrrggghhhh
Thanks to those who feel my pain !!

View 2 Replies View Related

Reformatting Field Results

Jul 24, 2006

Hi

I am trying to make a query from a sql server 2000 database i have a table as follows:

Operatinngsystem FQDomain
============= ========
Windows server1.gnet.net
Windows server2.gnet.net
Windows server3.gnet.net

what i want to be able to do is to return the result so that the server1-3 asre in a seperate colunm and the gnet.net seperate i want the out put as follows:

Operatinngsystem Server Domain
============= ====== ======
Windows server1 gnet.net
Windows server2 gnet.net
Windows server3 gnet.net

therefore three colunms output. I would be greatful if any one got any ideas how i can do this? (Please note im new to sql server)

Thanks

abuhassan

View 15 Replies View Related

Put Multiple Results In One Field

Jul 23, 2005

Hi all,I need to put multiple results in one field, but not sure how. Here'ssome sample code:select a.name,a.accountnum,a.ordernum,(select itemid from items where items_ordernum = a.ordernum) asItems[color=blue]>From clients a[/color]There's multiple instances of 'itemid' in 'items' with the criteria Igave, so this generates an error. I want to instead have the itemnumbers listed as just a list seperated by commas, so the results mightbe this:Name AccountNum OrderNum ItemsJohn Smith 12345 0000234 1233, 1333, 4322Mike Jones 43223 0000023 4322, 543And so forth. Is there someway to loop through this sub-query and makethe results become a list?Thanks --rlangly

View 2 Replies View Related

No Results Returned By SELECT Against Datetime Field

Jan 8, 2004

I am trying to pull results from an SQL Server DB into an dataset where a particular field (SMALLDATETIME) is within a particular date range. The code I was using worked fine when the database was in Access. I have made several changes already but am still getting 0 results returned when there is data that should be returned.

I was using in Access:
Dim StrSQL = "SELECT ID FROM myTable WHERE myDateField>=#" & startDate & "# AND myDateField<=#" & stopDate & "# ORDER BY ID"
I have changed this for SQL Server to:
Dim StrSQL = "SELECT ID FROM myTable WHERE myDateField>='01/01/2003 00:00:01' AND myDateField<='01/01/2004 23:59:59' ORDER BY ID"
But I am always returned 0 results even if the date range should return plenty of data. I have also tried using the BETWEEN structure with the same result.

Is there a particular format for the date I am comparing with?
Am I missing something else in my query?

The connection / permissions and everything else are correct as I can read and write data to the database in numerous other pages. It is just this date comparison that is not working.

Many thanks for any help or comments you can provide.

View 2 Replies View Related

3 Results From One Field - Show Levels In Right Order

Nov 19, 2007


Hi there

We have a web application (database) that uses one field called Application and another called TicketType.

When a user fills out a ticket they can choose up to 3 levels of this field.
Eg Application, Application2, Application3

Eg TicketType, TicketType2, TicketType3

The extra two levels not being compulsory.

I am using sql server 2005 // Reporting Services

My query is as below:
SELECT Ticket.TicketNumber, Ticket.CreatedDate, Application_2.ApplicationName AS Application, Application_1.ApplicationName AS [App 2],
Application.ApplicationName AS [App 3], TicketType_2.TicketTypeName AS Tickettype, TicketType_1.TicketTypeName AS [Type 2],
TicketType.TicketTypeName AS [Type 3], Ticket.Description, Company.CompanyName
FROM Ticket INNER JOIN
TicketType AS TicketType ON Ticket.TicketTypeID = TicketType.TicketTypeID LEFT OUTER JOIN
TicketType AS TicketType_1 ON TicketType.ParentTicketTypeID = TicketType_1.TicketTypeID LEFT OUTER JOIN
TicketType AS TicketType_2 ON TicketType_1.ParentTicketTypeID = TicketType_2.TicketTypeID INNER JOIN
Application AS Application ON Ticket.ApplicationID = Application.ApplicationID INNER JOIN
Company ON Application.CompanyID = Company.CompanyID FULL OUTER JOIN
Application AS Application_1 ON Application.ParentApplicationID = Application_1.ApplicationID FULL OUTER JOIN
Application AS Application_2 ON Application_1.ParentApplicationID = Application_2.ApplicationID
WHERE (Ticket.CreatedDate >= @StartDate)
ORDER BY Ticket.TicketNumber



End result looks like this:





Application

App 2

App 3

TicketType

Type 2

Type 3


Software

Internal Apps

proACT





SW Other






Office Issues





General




Application

Click Track server



Alert (App)

Service




Network

Other





Network Fault


Software

Internal Apps

Other



User Account

New




Hardware

Network





HW Fault




Application

Click Track server



Alert (App)

Disk space






Office Issues





General






proACT



Configuration

Deployment


Software

Server Software

SharePoint



SW Fault

App Failure (Function)


Software

Server Software

SharePoint



SW Fault

App Failure (Function)


Ultimately I would like the Application (TicketType) fields to have the Master Information in it and the other two fields populated in order as well.

Can someone help please.


Please ask if I haven't explained myself.

thanks
Dianne

View 9 Replies View Related

Wierd Query Results When Comparing Field Values

May 8, 2008

Hi Guys, I am experiencing weird results

SELECT DSNew, DTTM, RQDT
FROM dbo.Feb
INNER JOIN DMSEFL
ON ACTR = DSNew
where cast(DSNew as varchar(20)) = cast(ACTR As varchar(20))
If I run the above query I get zero recs back.

If I substitute a Value then I get the desired results (ie. where DSNew = '93235500') or if I enter (ACTR = '93235500') or if I put (where DSNew = '93235500' AND ACTR = '93235500')

Can anyone suggest a reason why this is happening. I know the records exist on both tables I ran the query in Acess and got the desired resutls.

Thank you,
Trudye

View 4 Replies View Related

Subquery Returned More Than 1 Value But Work FINE (field_xxx=subquery)

Jul 19, 2007

Hi guys,



A have a problem that I need understand.... I have 2 server with the same configuration...



SERVER01:

-----------------------------------------------------

Microsoft SQL Server 2000 - 8.00.2191 (Intel IA-64)

Mar 27 2006 11:51:52

Copyright (c) 1988-2003 Microsoft Corporation

Enterprise Edition (64-bit) on Windows NT 5.2 (Build 3790: Service Pack 1)



sp_dboption 'BB_XXXXX'

The following options are set:

-----------------------------------

trunc. log on chkpt.

auto create statistics

auto update statistics

********************************

SERVER02:

-----------------------------------------------------

Microsoft SQL Server 2000 - 8.00.2191 (Intel IA-64)

Mar 27 2006 11:51:52

Copyright (c) 1988-2003 Microsoft Corporation

Enterprise Edition (64-bit) on Windows NT 5.2 (Build 3790: Service Pack 1)



sp_dboption 'BB_XXXXX'

The following options are set:

-----------------------------------

trunc. log on chkpt.

auto create statistics

auto update statistics



OK, the problem is that if a run the below query in server01, i get error 512:



Msg 512, Level 16, State 1, Line 1

Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.



But, if run the same query in the server02, the query work fine -.



I know that I can use IN, EXISTS, TOP, etc ... but I need understand this behavior.



Any idea WHY?



SELECT dbo.opf_saldo_ctb_opc_flx.dt_saldo,

dbo.opf_saldo_ctb_opc_flx.cd_indice_opf,

dbo.opf_saldo_ctb_opc_flx.cd_classificacao,

dbo.opf_movimento_operacao.ds_tipo_transacao ds_tipo_transacao_movimento ,

dbo.opf_header_operacao.ds_tipo_transacao ds_tipo_transacao_header,

'SD' ds_status_operacao,

dbo.opf_header_operacao.ds_tipo_opcao ,

dbo.opf_header_operacao.id_empresa,

dbo.opf_saldo_ctb_opc_flx.ic_empresa_cliente,

0 vl_entrada_compra_ctro ,0 vl_entrada_compra_premio,

0 vl_entrada_venda_ctro , 0 vl_entrada_venda_premio,

0 vl_saida_compra_ctro, 0 vl_saida_compra_premio,

0 vl_saida_venda_ctro, 0 vl_saida_venda_premio,

0 vl_lucro , 0 vl_prejuizo, 0 vl_naoexec_contrato,

0 vl_naoexec_premio,

sum(dbo.opf_saldo_ctb_opc_flx.vl_aprop_ganho) vl_aprop_ganho,

sum(dbo.opf_saldo_ctb_opc_flx.vl_aprop_perda) vl_aprop_perda,

sum(dbo.opf_saldo_ctb_opc_flx.vl_rever_ganho) vl_rever_ganho,

sum(dbo.opf_saldo_ctb_opc_flx.vl_rever_perda) vl_rever_perda,

sum(dbo.opf_saldo_ctb_opc_flx.vl_irrf) vl_irrf

FROM dbo.opf_saldo_ctb_opc_flx,

dbo.opf_header_operacao ,

dbo.opf_movimento_operacao

WHERE dbo.opf_saldo_ctb_opc_flx.dt_saldo = '6-29-2007 0:0:0.000'

and ( dbo.opf_header_operacao.no_contrato = dbo.opf_saldo_ctb_opc_flx.no_contrato )

and ( dbo.opf_header_operacao.no_contrato = dbo.opf_movimento_operacao.no_contrato )

and ( dbo.opf_movimento_operacao.dt_pregao = (select (o.dt_pregao) from dbo.opf_movimento_operacao o

where o.no_contrato = dbo.opf_movimento_operacao.no_contrato and o.dt_pregao <='6-28-2007 0:0:0.000' ) )

and (dbo.opf_saldo_ctb_opc_flx.ic_tipo_saldo = 'S')

group by dbo.opf_saldo_ctb_opc_flx.dt_saldo,

dbo.opf_saldo_ctb_opc_flx.cd_indice_opf,

dbo.opf_saldo_ctb_opc_flx.cd_classificacao,

dbo.opf_movimento_operacao.ds_tipo_transacao,

dbo.opf_header_operacao.ds_tipo_transacao ,

ds_status_operacao,

dbo.opf_header_operacao.ds_tipo_opcao ,

dbo.opf_header_operacao.id_empresa,

dbo.opf_saldo_ctb_opc_flx.ic_empresa_cliente



Thanks

Nilton Pinheiro

View 9 Replies View Related

Cannot Copy Field Names In Column Headings From View Results

Jan 8, 2007

When I am using a SQL Query I have an ability to control whether or not I am able to Include the Column Headers when copying or saving results.

The control exists in the Options > Query Results > Results to Grid > Include column headings etc.

My question is how to get this same ability when attempting to copy the results of a VIEW vs. a Query.
The idea is that when I setup a view it€™s a drag/drop type of query building (query building for dummies if you will). Once I have a view and click the Execute icon it will return all the records selected by the View. However, when I click the upper left/top box to select all rows and column and then try to copy/paste the records into Excel all the data copies just fine but the field name/column headers are not there.
How can I get the header fieldname date to copy/paste from View result set that I'm able to copy from a Query result set?
Thank you,
Mike

View 1 Replies View Related

Reporting Services :: SSRS - Multiple Field Results In Same Cell?

Jun 11, 2015

I am trying to create an SSRS report to display a single row of Household information (from CRM Dynamics) Household being the Parent Entity, with 1:N relationship with Contact Entity.

How would I go about showing all the Firstnames (from Contact) in the same cell like the below?

View 4 Replies View Related







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