Joining Function - Query

Feb 27, 2008

CREATE FUNCTION dbo.fn_copdmailinglist(@list_ varchar(60))
RETURNS @copdmailinglist TABLE
(
list_ varchar(60) ,
title_ varchar(255) ,
desc_ varchar(255),
message_id int ,
txt varchar(255) ,
cnt int ,
cnt_txt varchar(255)
)

--Returns a result set that lists all the copds
AS
BEGIN
WITH ListManager.dbo.[List Copd](list_ , title_ , message_id , txt , cnt , cnt_txt ) AS
(select @list_ , gmc.name_, osc.message_id , txt , cnt , cnt_txt from ListManager.dbo.[Open statisticscopd]('') osc
left outer join ListManager.dbo.get_mailingidcopd_('') gmc
on gmc.name_ = osc.title_
where list_ = @list_
)

-- copy the required columns to the result of the function
INSERT @copdmailinglist
SELECT list_ , title_ , message_id , txt , cnt , cnt_txt
FROM ListManager.dbo.[List Copd]
RETURN
END
GO

i m getting error that Incorrect syntax near the keyword 'WITH'.

can anyone tell me how to join functions in sql?

thanks.

View 4 Replies


ADVERTISEMENT

Joining On And Grouping By CASE Function Column Alias (URGENT)

Apr 14, 2004

I REALLY need to perform a JOIN and a GROUP BY on a CASE function column alias, but I'm receiving an "Invalid column name" error when attempting to run the query. Here's a snippet:

SELECT NewColumn=
CASE
WHEN Table1.Name LIKE '%FOO%' THEN 'FOO TOO'
END,
Table2.SelectCol2
FROM Table1
JOIN Table2 ON NewColumn = Table2.ColumnName
GROUP BY NewColumn, Table2.SelectCol2
ORDER BY Table2.SelectCol2

I really appreciate any help anyone can provide.

Thanks,
DC Ross

View 5 Replies View Related

Joining 3 Tables On The Query

Mar 15, 2007

I know how to join 2 tables, but I have a third I need to insert. For some reason, this doesn't work:


Code:

$rows = ff_select(
"select ".
"u.email as email, ".
"c.user_id as user_id, ".
"u.name as name, ".
"r.age as age ".
"from #__comprofiler as c ".
"left join #__users as u on c.user_id = u.id ".
"left join #__rnr_contest as r on c.user_id = r.userid ".
"where (r.age != chicken) and (r.age != nystrip) and (r.age != regrets) and (u.block = 0) and (c.cb_contactmethod LIKE '%Email%') and (u.usertype != 'Super Administrator') and (u.email != 'please@change.com') and (u.username != 'guest') and (u.username != 'piedmont') ".
"order by email"



anyone see why? It tells me that "chicken" is not a column which is weird because I don't think it's listed as a column in my query... is it?

View 3 Replies View Related

Newbie Needs Help On Joining Query

Oct 11, 2007

Hi All,

First off sorry for my complete lack of experience with SQL, but I've just started a new position which involved learning SQL. I'm currently looking for a course but in the mean time I just have to try and fumble by doing basic things.

I'm trying to write my first basic select / join statement, and I just can't seem to get it working.

Any help would really be appreciated. It's T-SQL we use btw.

Thanks :)



USE MLSDHeat
GO

select * from dbo.CallLog
where callstatus between 'open'and 'pending'
right outer join dbo.Asgnmnt
on callid = callid
order by callid



--the above returns error Msg 156, Level 15, State 1, Line 4
Incorrect syntax near the keyword 'right'.

View 2 Replies View Related

Query Help Joining 2 Tables

Jul 23, 2005

Select LOCGeneralHave.LSCH, LOCSubClassHave.LSCH from LOCGeneralHave ,LOCSubClassHave Where (LOCGeneralHave.LCNT <> '0' andLOCSubClassHave.LCNT = '0')This query seems to be ignoring the 'and' part of the clause.Basically I want select from table1, table2 where LCNT in table1 is not0 andLCNT in table2 is 0.I have verified the LCNT's numbersThis query returns 2 columns with 1700 rowsIt needs to only find a few rows.What am I missing, any ideas, thanks for any help.

View 4 Replies View Related

Joining Two Fields In A Query

Apr 26, 2006



I am trying to join two fields in a query in SQL 2000. For example.

Update myTable SET field_1 = @field_1_value , field_2 = @field_2_value, field_3 = @field_1_value + ' x ' + field_2_value



Is this even possible.

I want the user to input values for fields 1 and 2, then in the background combine the two and insert that value in field 3.



Thanks in advance,

Scotty_C

View 7 Replies View Related

SQL-Query Mindbugger Joining A 2 Views

Oct 23, 2006

Ok, What I want to achieve is plain stuff, I want to join 2 views on a common key.It all works well with the SQL2000 Query Analyzer, but not trough ADO.NET or should I say my webapplication.With that I mean that my query return rows when executed from SQL2000 Query Analyzer, But not when used in my application or Executed from the Visual Studio Server Explorer.I have struggled with this one for several hours, I cant get this one right.So lets bring in the one who actually know what his doing View1: 1 select
2 cast((PS.RabattProsent/100.00)*PS.Pris AS decimal(11,2)) AS Rabatt
3 ,cast((PS.MVAProsent/100.00)*PS.Pris AS decimal(11,2)) AS MVA
4 ,cast(PS.Antall * ((PS.Pris*(100-PS.RabattProsent))/100)*((PS.MvaProsent/100.00)+1) AS decimal(11,2)) AS Belop
5 ,PS.*
6 ,K.Kunde_ID
7 FROM
8 tbl_ProduktSalg AS PS
9 INNER JOIN
10 tbl_Ordre AS O
11 ON
12 O.Ordre_ID = PS.Ordre_ID
13 INNER JOIN
14 tbl_Kunde AS K
15 ON
16 K.Kunde_ID = O.Kunde_IDView2: 1 SELECT
2 PS.Ordre_ID
3 ,SUM(cast((PS.RabattProsent/100.00)*PS.Pris AS decimal(11,2))) AS TotalRabatt
4 ,SUM(cast(PS.Antall * ((PS.Pris*(100-PS.RabattProsent))/100)*((PS.MvaProsent/100.00)+1) AS decimal(11,2))) AS TotalBelop
5 ,SUM(PS.Pris) AS TotalPris
6 ,SUM(cast((PS.MVAProsent/100.00)*PS.Pris AS decimal(11,2))) AS TotalMVA
7 FROM
8 tbl_ProduktSalg AS PS
9 GROUP BY
10 PS.Ordre_ID
   MyQuery/SPRC: 1 create procedure %PROC% (@Kunde_ID int, @Ordre_ID int)
2 as
3 begin
4 SELECT
5 v_PSD.*
6 ,v_OTS.TotalRabatt
7 ,v_OTS.TotalBelop
8 ,v_OTS.TotalPris
9 ,v_OTS.TotalMVA
10 FROM
11 v_ProduktSalgDetaljer AS v_PSD
12 INNER JOIN
13 v_OrdreTotalSum AS v_OTS
14 ON
15 v_OTS.Ordre_ID = v_PSD.Ordre_ID
16 WHERE
17 v_PSD.Kunde_ID = @Kunde_ID
18 AND
19 v_PSD.Ordre_ID = @Ordre_ID
20
21 end
22
  

View 3 Replies View Related

Joining Views && Query Performance

May 1, 2006

Over the years I've read and experienced where joining more then 5 tables can lead to performance problems. This number can vary based upon the amount of data in each table, if and how indexes are used and the complexity of the query, but 5 has always been a good rule of thumb. Unfortunately I do not know what rule to apply in regards to joing views.

A developer has experienced timeout problems periodically when opening a view in EM or when running the code which makes-up the view. I decided to look at the view and noticed it references tables and views, which reference more views, which in turn reference other views. In all the initial view references 5 tables and 8 views directly and indirectly, with some of the views containing function calls. What are your thoughts on how many views and tables are too many when it comes to joins and query performance.

Thanks, Dave

View 6 Replies View Related

Joining Four Tables In A Single Query

Mar 19, 2015

I have four tables:

a, b, c and d

table a is related to table b by a foreign key. table b is related to c and so on.

I used the sql statement below to join the tables:

$result = mysql_query("SELECT a.colum1, a.column2,
b.column, c.column, d.column FROM a

JOIN b ON a.pkey = b.foreign key
JOIN c ON b.pkey = c.fkey
JOIN d ON c.pkey = d.fkey ")
or die(mysql_error());

[Code] .....

I succeeded in printing out the first record where the four tables are joined, but not other instances.my print out is this:

a.column1
a.colum2
b.colum
c.colum
d.colum

But there are about ten instances where the joining conditions are met. How do I print out all the records that have met the condition?

View 2 Replies View Related

Lookup Value Query Joining Two Tables

Jul 20, 2005

Two tables:T1 (c1 int, TestVal numeric(18,2), ResultFactor numeric(18,2))--c1 isthe primary key.T2 (x1 int, FromVal numeric(18,2), ToVal numeric(18,2), Factornumeric(18,2))--x1 is the primary key. T2 contains non-overlappingvalues. So for eg., a few rows in T2 may look like.1, 51, 51.999, 512, 52, 52.999, 52........32, 82, 82.999, 82........T2 is basically a lookup table. There is no relationship between thetwo tables T1 and T2. However, if the TestVal from T1 falls in therange between FromVal and ToVal in T2, then I want to updateResultFactor in T1 with the corresponding value of Factor from the T2table.------Example for illustration only---------------Even though tables cannot be joined using keys, the above problem is avery common one in our everyday life. For example T1 could beemployees PayRaise table, c1=EmployeeID, with "TestVal" representingtest scores (from 1 to 100). T2 representing lookup of the ranges,with "Factor" representing percent raise to be given to the employee.If TestVal is 65 (employee scored 65% in a test), and a row in T2(FromVal=60, ToVal=70, Factor=12), then I would like to update 12 intable T1 from T2 using sql;. Basically T2 (like a global table)applies to all the employees, so EmpID cannot serve as a key in T2.---------------------------------------------------------Could anyone suggest how I would solve MY PROBLEM using sql? I wouldlike to avoid cursors and loops.Reply appreciated.Thanks

View 1 Replies View Related

Mental Block On SQL Query. Joining One To Many With One Of The Many

Sep 25, 2007

Sorry for the akward title, not sure how to say this. I have a Person table and a addresses table. Each person may have many address records as shown below:

Person
-----------
PersonID | AutoNum
fName | varchar
lName | varchar
...etc

Addresses
---------------
addressID | AutoNum
personID | int (FK)
address1 | varchar
city | varchar
state | varchar
isPrimary | bit
...etc


What I'm trying to do is select all from Person and the city and state of each person's primary address. A little voice keeps saying subquery...but I can't figure it out. So far I have the SQL below, but if there is no address or no address where isPrimary = 1, it fails to return the person record. I need the person record regardless of if they have a primary address. Does that make sense?


Doesn't return all Person records:

SELECT Person.*, Address.City, Address.State
FROM Person LEFT OUTER JOIN Address
ON Person.PersonID = Address.PersonID
WHERE (Address.isPrimary= 1)
ORDER BY Person.lName, Person.fName

View 3 Replies View Related

Query Optimization - Joining A View And A Table

Apr 5, 2006

I am having the following situation - there is a view that aggregates and computes some values and a table that I need the details from so I join them filtering on the primary key of the table. The execution plan shows that the view is executed without any filtering so it returns 140 000 rows which are later filtered by the join operation a hash table match. This hash table match takes 47% of the query cost. I tried selecting the same view but directly giving a where clause without the join €“ it gave a completely different execution plan. Using the second method is in at least 4 folds faster and is going only through Index Seeks and nested loops.
So I tried modifying the query with third version. It gave almost the same execution plan as the version 1 with the join operation.
It seams that by giving the where clause directly the execution plan chosen by the query optimizer is completely different €“ it filters the view and the results from it and returns it at the same time, in contrast to the first version where the view is executed and return and later filtered. Is it possible to change the query some how so that it filters the view before been joined to the table.

Any suggestions will be appreciated greatly
Stoil Pankov

"vHCItemLimitUsed" - this is the view
"tHCContractInsured" - this is the table
"ixHCContractInsuredID" - is the primary key of the table

Here is a simple representation of the effect:

Version 1:
select *
from dbo.vHCItemLimitUsed
inner join tHCContractInsured on
vHCItemLimitUsed.ixHCContractInsuredID = tHCContractInsured.ixHCContractInsuredID
where tHCContractInsured.ixHCContractInsuredID in (9012,9013,9014,9015)


Version 2:
select *
from vHCItemLimitUsed
where ixHCContractInsuredID in (9012,9013,9014,9015)

Version 3:
select *
from dbo.vHCItemLimitUsed
where ixHCContractInsuredID in
(select ixHCContractInsuredID
from tHCContractInsured
where ixHCContractInsuredID in (9012,9013,9014,9015))

View 1 Replies View Related

Update Query Joining Tables From Separate Databases

Apr 17, 2008



In database DB1, I have table DB1.dbo.Suppliers1. This table has an ID column of type INT named SUPPL1_ID

In database DB2, I have table DB2.dbo.Suppliers2. This table has an ID column of type INT named SUPPL2_ID
I would like to update DB2.dbo.Suppliers2 based on values from DB1.dbo.Suppliers1 joining on SUPPL1_ID = SUPPL2_ID.

How can I do this in SSIS?

Assumptions:


linked servers are not an option, as I want the SSIS package to be portable and not dependent on server environments.
TIA.

-El Salsero

View 5 Replies View Related

DB Engine :: Possible Ways To Execute A Query Joining Three Tables

May 16, 2015

I am learning the Optimizer from the book "Querying Microsoft SQL Server 2012" for certificate exam 70-461. I really cannot understand how it explains the number of possible ways to execute a query joining three tables. the pseudo-query is:

SELECT A.col5, SUM(C.col6) AS col6sum
FROM TableA AS A
INNER JOIN TableB AS B
ON A.col1 = B.col1
INNER JOIN TableC AS C
ON B.col2 = c.col2
WHERE A.col3 = constant 1
AND B.col4 = constant2
GROUP BY A.col5;

The book says:"Start with the FROM part. Which tables should SQL Server join first, TableA and TableB or TableB and TableC? And in each join, which of the two tables joined should be the left and which one the right table? The number of all possibilities is six, if the two joins are evaluated linearly, one after another."

Q1: How could it be six possibilities? From my understanding, lets say, if the SQL Server has to join A and B first, and then join C, in this case I can think of 4 possibilities, which are:

1. When A Join B, Left: A, Right: B.
    When Join C, Left: result of A join B, Right: C

2. When A Join B, nbsp;  
When Join C, nbsp;When A Join B, nbsp;  
When Join C, nbsp;When A Join B, nbsp;   
When Join C, "line-height:13.5px;">

Q2: The section following the previous question says there are 4 different types of join.."This already gives four options for each join. So far, there are 6 x 4 = 24 different options for only the FROM part of this query."

How can it be 6 x 4? My understanding is 4 is only for 1 join, but in our case, there are 2 joins, so it should be 6 x 4 x 4.

View 4 Replies View Related

SQL Server 2008 :: Query Joining Multiple Tables Getting Duplicates?

May 17, 2013

I'm joining several tables and when I add the last one I get duplicate results. How can I get just one for each?

select a.field, b.field, c.field
from atblname as a inner join btblname as b on a.id = b.parent_id
left outer join ctblname as c on a.id = c.parent_id

There are more than one result when joining tbl a and c, but I'm getting a reult for each of them for all results from joining a and b.

View 9 Replies View Related

Query To Retrieve Max Price Without Using Max Function And Sub Query

Mar 5, 2014

ItemIDItemNamePrice

1Item1120.00
2Item2234.00
3Item3250.00
4Item4300.00
5Item5300.00
6Item6290.00
7Item7170.00
8Item890.00
9Item9170.00

the above is the existing table and i need a query to retrieve max price with out using max function and sub query

View 6 Replies View Related

Query Function

Jan 16, 2008

Has anyone ever written an SQL (Select, etc.) function that could be placed in the App_Code folder of a project? I have a few web forms that have a couple dozen queries and I'm trying to build a good function to reduce clutter. The function I made (below) is in the App_Code folder and can be used by doing: Dim dr As SqlDataReader = GlobalFunctions.BuildSQLSelect("blah", "blah") in any one of my pages.
Public Shared Function BuildSQLSelect(ByVal ConnectionStringType As String, ByVal QueryString As String)   Dim ConnectionString As String = Web.Compilation.ConnectionStringsExpressionBuilder.GetConnectionString(ConnectionStringType)   Dim Connection As New SqlConnection(ConnectionString)   Dim Command As New SqlCommand(QueryString, Connection)   Command.Connection.Open()      Return Command.ExecuteReader()End Function
It works fine, but has one major flaw that prevents me from using it. I can't (at least I don't think I can) call Command.Connection.Close() once Return is hit and the function exits (especially since I still need to work with the DataReader).
Does anyone know of a better solution or know how to fix mine so I don't have tons of open connections floating around? Thanks!

View 2 Replies View Related

Function Vs. Sub-Query

Oct 19, 2004

When my sproc selects a function (which in itself has a select statement to gather data) it takes substantially longer time (minutes) than if I replace the function with a sub query in the sproc (split second). What is the reason for this?

Bjorn

View 2 Replies View Related

To Use Function In A Query

Feb 15, 2006

hi
i hve select query where i display many columns with many conditions from 4 tables. in displaying using 2 column outputs i need to do calculations and on one another and display. so i wrote scalar function. but calling function is not possible to retrive all columns and insert into query.. how to do this .. help me in suggesting..
chakri

View 14 Replies View Related

Function Query

May 8, 2008

i have function:


can anybody help me to figure this issue? how can i get that.
Thank you.

View 2 Replies View Related

Query With Function Won't Run

May 29, 2008

Hi...



I get the following error when trying to run this query in reporting services, but it executes perfectly in Management Studio, all I did was copy and paste:

TITLE: Microsoft Report Designer
------------------------------

An error occurred while executing the query.
Incorrect syntax near '.'.

------------------------------
ADDITIONAL INFORMATION:

Incorrect syntax near '.'. (Microsoft SQL Server, Error: 102)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=09.00.1399&EvtSrc=MSSQLServer&EvtID=102&LinkId=20476





SELECT dv.product ,
dv.itemname ,
dv.u_vlgx_plc,
dv.shorted ,
dv.onhand ,
dv.po_num ,
t10.docduedate
FROM
(SELECT t3.product ,
t7.itemname ,
t2.u_vlgx_plc,
t3.shorted ,
t4.onhand ,
t6.cardname AS t6_cardname,
MIN(
CASE
WHEN t8.linestatus = 'O'
THEN t9.docnum
ELSE NULL
END) po_num
FROM
(SELECT t0.product product ,
SUM(
CASE
WHEN t0.qty_topick <> t0.qty_picked
THEN t0.qty_topick - t0.qty_picked
ELSE 0
END) shorted
FROM rbeacon.dbo.shipline2 t0
INNER JOIN rbeacon.dbo.shiphist t1
ON t0.packslip = t1.packslip
WHERE CONVERT(VARCHAR(8),t1.date_upld,3) = @Date
GROUP BY t0.product
) t3
INNER JOIN comparison.dbo.vlgxplc t2
ON t2.itemcode = t3.product COLLATE Latin1_General_CI_AS
LEFT JOIN
(SELECT t0.product AS product,
SUM(t0.quantity) AS onhand
FROM rbeacon.dbo.binlocat t0
GROUP BY t0.product
) t4
ON t3.product = t4.product
INNER JOIN wbau.dbo.oitm t5
ON t3.product = t5.itemcode COLLATE SQL_Latin1_General_CP850_CI_AS
LEFT JOIN wbau.dbo.ocrd t6
ON t5.cardcode = t6.cardcode
INNER JOIN wbau.dbo.oitm t7
ON t3.product = t7.itemcode COLLATE SQL_Latin1_General_CP850_CI_AS
LEFT JOIN wbau.dbo.por1 t8
ON t3.product = t8.itemcode COLLATE SQL_Latin1_General_CP850_CI_AS
LEFT JOIN wbau.dbo.opor t9
ON t8.docentry = t9.docentry
WHERE t3.shorted <> 0
GROUP BY t3.product ,
t7.itemname ,
t2.u_vlgx_plc,
t3.shorted ,
t4.onhand ,
t6.cardname
) dv

OUTER APPLY comparison.dbo.podatetest(dv.po_num) AS t10


GROUP BY dv.product ,
dv.itemname ,
dv.u_vlgx_plc ,
dv.shorted ,
dv.onhand ,
t10.docduedate,
dv.po_num ,
dv.t6_cardname
ORDER BY dv.u_vlgx_plc,
dv.t6_cardname,
dv.product




I've worked out that it doesn't like me passing dv.po_num through the table valued function. If I change this to a static value, rather than the result of the case statement further up, reporting services will run the query.



Any idea how I can fix this? Thanks!

View 2 Replies View Related

Aggregate Function In Sub-query

Jan 15, 2014

I am trying to use the following syntax and it is saying I can't use an aggregate function in a subquery. I can't use a GROUP BY in this case because if another field in the project table (such as status) is different, that project will show up twice.So in this case I am using this syntax to show the most recent quote within the project.

SELECT PROJECT.*, QUOTE.QuoteDate, QUOTE.QuoteCode
FROM PROJECT LEFT JOIN QUOTE ON PROJECT.ProjectID = QUOTE.ProjectID
WHERE QUOTE.QuoteDate=(SELECT Max(Q.QuoteDate) FROM QUOTE Q WHERE Q.ProjectID = PROJECT.ProjectID);

My goal here is to show the most recent quote within each project (there can be multiple revisions of a quote within each project). I want to show other fields such as the status of the quote, but if the status is different between quotes, the GROUP BY on that field will cause it to be listed more than once. All I want to show is the most recent quote for each project.

View 3 Replies View Related

Add Count Function To My Query

Dec 17, 2007

Hello,

I have created the following query... and need to get
the total records display for my report. I have tried
adding in the count(*) function to my select list, but I get
errors. Any help is appreciated.

SELECT
A.ParentSubjectName,
A.ParentSubject,
A.SubjectId,
B.CreatedOn

FROM dbo.Subject A
INNER JOIN dbo.Incident B ON A.SubjectId = B.SubjectId

WHERE A.ParentSubjectName LIKE 'ACDelco Products%'

AND (B.CreatedOn >= '2007-01-01' AND B.CreatedOn <= '2007-11-30')

AND A.SubjectId IN
(
'C44ADE3E-527B-DC11-8A2D-00170857BDE7',
'F8758E52-527B-DC11-8A2D-00170857BDE7',
'7E65F458-527B-DC11-8A2D-00170857BDE7',
'7F65F458-527B-DC11-8A2D-00170857BDE7',
'2BE35262-527B-DC11-8A2D-00170857BDE7',
'2AE35262-527B-DC11-8A2D-00170857BDE7',
'A2002127-527B-DC11-8A2D-00170857BDE7',
'41A8A66F-527B-DC11-8A2D-00170857BDE7',
'A3002127-527B-DC11-8A2D-00170857BDE7',
'D6C08B45-527B-DC11-8A2D-00170857BDE7',
'C439FB4B-527B-DC11-8A2D-00170857BDE7'
)

ORDER BY B.CreatedOn[/blue]

View 3 Replies View Related

Count/sum Function Query

Mar 14, 2008

create function mytotalcount

(@audit varchar(50), @startdate datetime, @enddate datetime)

returns table

as

return

(

select t.value,sum(t.countvalue) as totalcount from
(

select

sm.value,count(sm.value) as countvalue

from subjectbase s
join stringmap sm
on s.organizationid = sm.organizationid

inner join audit a
on s.subjectid=a.subjectid

inner join incidentbase i
on i.subjectid=s.subjectid

where a.auditid= @audit and (i.modifiedon between @startdate and @enddate) and
sm.attributename = 'contractservicelevelcode' and
sm.ObjectTypeCode = 112

group by sm.value

) t

group by t.value

)

value totalcount
------------------
NHLBI Employee329
NIH Employee329
Public329
VIP329


instead of different values i m getting same...
there is something wrong in joins..can anyone help me?

thanks.

View 2 Replies View Related

Median Query Without Using Function

Feb 24, 2006

I have a table (cars) with 3 fields:VIN, Class, sell_price101, sports, 10000102, sports, 11000103, luxury, 9000104, sports, 11000105, sports, 11000106, luxury, 5000107, sports, 11000108, sports, 11000109, luxury, 9000i need to write a query that WITHOUT USING A FUNCTION will return themedian selling price for each class of car. result should look like:Class, Med_Priceluxury, 9000sports, 11000thanks to all u SQLers

View 4 Replies View Related

Query On Datediff Function

May 18, 2007

Hi,



I am trying to use DataDiff function and I have used the following queries:



1.

select datediff(ms, '2007-05-18 19:35:07.320','2007-05-18 19:35:07.320') as test

Expected Result: 0 milliseconds

Actual Result: 0 milliseconds



2.

select datediff(ms, '2007-05-18 19:35:07.320','2007-05-18 19:35:07.321') as test

Expected Result: 1 milliseconds

Actual Result: 0 milliseconds



3.

select datediff(ms, '2007-05-18 19:35:07.320','2007-05-18 19:35:07.322') as test

Expected Result: 2 milliseconds

Actual Result: 3 milliseconds



4.

select datediff(ms, '2007-05-18 19:35:07.320','2007-05-18 19:35:07.323') as test

Expected Result: 3 milliseconds

Actual Result: 3 milliseconds



5.

select datediff(ms, '2007-05-18 19:35:07.320','2007-05-18 19:35:07.324') as test

Expected Result: 4 milliseconds

Actual Result: 3 milliseconds



6.

select datediff(ms, '2007-05-18 19:35:07.320','2007-05-18 19:35:07.325') as test

Expected Result: 5 milliseconds

Actual Result: 6 milliseconds



7.

select datediff(ms, '2007-05-18 19:35:07.320','2007-05-18 19:35:07.326') as test

Expected Result: 6 milliseconds

Actual Result: 6 milliseconds



8.

select datediff(ms, '2007-05-18 19:35:07.320','2007-05-18 19:35:07.327') as test

Expected Result: 7 milliseconds

Actual Result: 6 milliseconds



9.

select datediff(ms, '2007-05-18 19:35:07.320','2007-05-18 19:35:07.328') as test

Expected Result: 8 milliseconds

Actual Result: 6 milliseconds



10.

select datediff(ms, '2007-05-18 19:35:07.320','2007-05-18 19:35:07.329') as test

Expected Result: 9 milliseconds

Actual Result: 10 milliseconds



Does any one know, why datediff does not return the Expected Result? There does not seem to be any consistency.



Thanks,

Tim

View 1 Replies View Related

Function Help: Parameter Query

Apr 26, 2006

What SQL Function Criteria string replaces [forms]![myForm].[myField]?

I have a function that I want to pass criteria to from a drop down list. I tried using the same Access string in the Function but it does not work.

View 1 Replies View Related

How CREATE FUNCTION This Query

Feb 24, 2008

Code Snippet
Declare @DBName as varchar(100)
Declare @Query as varchar(8000)

SELECT @DBName = AccountDBName FROM Config Where SomeID=SomeValue

Set @Query ='
SELECT
ReciptItems.acc_TopicCode,
ReciptItems.acc_DetailCode,
ReciptItems.acc_CTopicCode,
SUM(ReciptItems.TotalInputPrice + ReciptItems.TotalOutputPrice),
a.MoeenName_L1
FROM
ReciptItems LEFT OUTER JOIN
' + @DBName + '.dbo.Categories AS a
ON ReciptItems.acc_TopicCode = a.TopicCode
GROUP BY
ReciptItems.acc_TopicCode,
ReciptItems.acc_DetailCode,
ReciptItems.acc_CTopicCode,
a.MoeenName_L1'

Exec (@Query)




View 10 Replies View Related

Query Table-value Function From ADO In C++ (not .net)

Apr 22, 2008

Hey everybody!

I create a function to call stored procedure, and query a view, but I am having troubles with quering table-value function.
I looked for an example on the net on how to do it, but I couldn't find any (I find only .net examples :-) )

Can anyone direct me to an example, or write a small sample?



Thanks a lot in advance!!!

View 15 Replies View Related

How Do I Test A Sql Function From The Query Analyzer?

Mar 30, 2007

I have a SQL database that has a function that returns an id value from a table after you pass in a text variable. I would like to test this functionality in Query Analyzer but when I try to do it this way:
exec dbo.fnc_ORGUNIT_GetByName 'Dummy'
It just says 'Query executed successfully' without any resutls in the results pane. What am I doing wrong?
Thanks! 

View 2 Replies View Related

Convert Function Query Error

Jul 27, 2005

I am in the middle of creating an editable DatGrid:

Sub AccessoryGrid_EditCommand(source As Object, e As MxDataGridCommandEventArgs)
    AccessoryGrid.EditItemIndex = e.Item.ItemIndex
End Sub

Sub AccessoryGrid_BeforeUpdate(source As Object, e As MxDataGridUpdateEventArgs)
    e.NewValues.Add("@AccessoryID",
AccessoryGrid.DataSource.DataSource.Tables(0).Rows(e.Item.DataSetIndex)
("AccessoryID"))
    e.NewValues.Add("@AccessoryName", CType(e.Item.Cells(1).Controls(0),TextBox).Text)
    e.NewValues.Add("@AccessoryPrice", CType(e.Item.Cells(2).Controls(0),TextBox).Text)
    e.NewValues.Add("@AccessorySold", CType(e.Item.Cells(3).Controls(0),TextBox).Text)
    e.NewValues.Add("@AccessoryDesc", CType(e.Item.Cells(4).Controls(0),TextBox).Text)
    e.NewValues.Add("@AccessoryImage", CType(e.Item.Cells(5).Controls(0),TextBox).Text)
End Sub

For some reason, I get an error message like this:
Server Error in '/' Application.

Disallowed
implicit conversion from data type nvarchar to data type smallmoney,
table 'cardb.dbo.accessories', column 'AccessoryPrice'. Use the CONVERT
function to run this query.



Description: An
unhandled exception occurred during the execution of the current web
request. Please review the stack trace for more information about the
error and where it originated in the code.

Exception Details: System.Data.SqlClient.SqlException:
Disallowed implicit conversion from data type nvarchar to data type
smallmoney, table 'cardb.dbo.accessories', column 'AccessoryPrice'. Use
the CONVERT function to run this query.

Source Error:





An unhandled exception was generated during the execution of the
current web request. Information regarding the origin and location of
the exception can be identified using the exception stack trace below.







Stack Trace:




[SqlException: Disallowed implicit conversion from data type nvarchar to data type smallmoney, table 'cardb.dbo.accessories', column 'AccessoryPrice'. Use the CONVERT function to run this query.] System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream) +723 System.Data.SqlClient.SqlCommand.ExecuteNonQuery() +194 Microsoft.Saturn.Framework.Web.UI.SqlDataSourceControl.PerformSqlCommand(SqlCommand command) +82 Microsoft.Saturn.Framework.Web.UI.SqlDataSourceControl.Update(String listName, IDictionary selectionFilters, IDictionary newValues) +114 Microsoft.Saturn.Framework.Web.UI.MxDataGrid.OnUpdateCommand(MxDataGridUpdateEventArgs e) +869 Microsoft.Saturn.Framework.Web.UI.MxDataGrid.OnBubbleEvent(Object source, EventArgs e) +546 System.Web.UI.Control.RaiseBubbleEvent(Object source, EventArgs args) +26 Microsoft.Saturn.Framework.Web.UI.MxDataGridItem.OnBubbleEvent(Object source, EventArgs e) +86 System.Web.UI.Control.RaiseBubbleEvent(Object source, EventArgs args) +26 System.Web.UI.WebControls.Button.OnCommand(CommandEventArgs e) +95 System.Web.UI.WebControls.Button.System.Web.UI.IPostBackEventHandler.RaisePostBackEvent(String eventArgument) +115 System.Web.UI.Page.RaisePostBackEvent(IPostBackEventHandler sourceControl, String eventArgument) +18 System.Web.UI.Page.RaisePostBackEvent(NameValueCollection postData) +33 System.Web.UI.Page.ProcessRequestMain() +1277






My main question is, how can I convert my column 'AccessoryPrice' to smallmoney?

I have been trying to get rid of this error by trying to change the
field type within my database with no success, I keep on getting the
same error either way.

I would be very greatful if anybody can  help me.

View 2 Replies View Related

Aggregate Function - Tough Query For Me...

Oct 17, 2006

I currently have the following query:

Quote:
select distinct a.memberFirstName, a.memberLastName, c.ChapterName, d.divisionName,
count(f.memberID) as numMembers
FROM Members a
INNER JOIN groupLeaders b
ON a.memberID = b.memberID
Inner JOIN Chapters c
ON c.chapterID = b.chapterID
LEFT JOIN divisions d
ON d.divisionID = c.divisionID
Inner Join groupsOfEight e
ON e.groupLeaderID = b.groupLeaderID
Inner Join groupOfEightMembers f
ON f.groupOfEightID = e.groupOfEightID
Group BY a.memberFirstName, a.memberLastName, c.chapterName, d.divisionName
Order By divisionName, numMembers

This query returns me the names of all of my Group Leaders, their Chapter, Division, and the number of members they have selected to be in their group.

Now, instead of the number of members in each Group I would like to know the total number of Members in each division to appear in the count.

[NOTE: All chapters have a division, linked by a divisionID in the "Chapters" table -- I need to get a count of all the "ChapterMembers" [chaptermembers is a table also] that are in the Division.

Here is the query I started to build before I ran into serious trouble:

Quote:
select a.divisionName, count('c.memberID') as numMembers
From Divisions a
Inner Join Chapters b
On b.divisionID = a.divisionID
Inner Join chapterMembers c
ON c.chapterID = b.chapterID
Left Join Members d
ON d.memberID = c.memberID
LEFT Join groupLeaders e
On e.memberID = d.memberID
Group By a.divisionName

This particular query returns only the DivisonName and the number of Members in the division as expected. However, when I try to select the information for the GroupLeader (first & last name) I am forced to add memberFirstName to the Group By statement which changes my Count...

Have I done an okay job of explaining the problem?

The goal here is to select all of the GroupLeaders first & last name, their chapterName, divisionName, and the total number of members in the division.

Thanks for any advice!

Zoop

View 3 Replies View Related

Instr Function For MSQL Query

Jun 4, 2007

Hi All

My SQL is extremly rusted so I need some help with a very basic function. I have a character field which is built up using a category code + '-' + number. The problem I have is that the category codes are all different lengths and the items were added using 9 instead of 09. I'm trying to clean up the data so that the same item with e.g. category code DZ20 cannot be added as DZ20-1 and DZ20-01. How do I find the position of the '-' in the Query Analyser for MSSQL 2000?

View 1 Replies View Related







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