What's Wrong In The Select Statement

May 21, 2007

select name from child order by name
union all
select aname from agenerate order by aname

Malathi Rao

View 12 Replies


ADVERTISEMENT

What's Wrong With This SELECT Statement

Dec 28, 2007

When I attempt to run the following select statement in Aqua I get 2 errors:
- Incorrect syntax near the keyword 'SELECT'.
- Incorrect syntax near ')'.

I belive they both refer to the creation of the subquery (i.e. "From (" and ") t" ) but I can't for the life of me discover anything wrong with the statement. If you run the multiple union subquery by itself, it works just fine. (In case your wondering, the eventual the purpose of creating a sub query is so that I can use the UnPivot statement)


SELECT DATE_DAY
, METRIC
, NUM_OF_TICKETS
, SEVERITY
, EQ_MARKET_CLUSTER
--Performance
, NUM_DISPATCH_TO
, DURATION_DISPATCH_TO
, SLA_DURATION_DISPATCH_TO
, SLA_DURATION_DISPATCH_TO_G_L
, NUM_DISPATCH_FROM
, DURATION_DISPATCH_FROM
, SLA_DURATION_DISPATCH_FROM
, SLA_DURATION_DISPATCH_FROM_G_L
, NUM_MTTR
, DURATION_MTTR
, SLA_MTTR
, SLA_MTTR_G_L
, COUNT_CLOSED_MET_DUE_DATE
, NUM_CLOSED_MET_DUE_DATE
--Costs
, OVERTIME
, COUNT_NTF
, NUM_DISPATCH_NTF
, NUM_CTS_DISPATCH
, TICKET_NOT_DISPATCHED
, REMOTE_RESTORAL
--Productivity
, NUM_TICKET_RATIO
, NUM_TECHS_RATIO
, NUM_DISPATCH_TICKET_RATIO
--FMS
, NUM_MET_DISPATCH_TO
, MET_SLA_DISPATCH_TO
, NUM_MET_DISPATCH_FROM
, MET_SLA_DISPATCH_FROM
, NUM_WIP
, DURATION_WIP_TO_RESOLVE
, SLA_WIP_TO_RESOLVE
, SLA_WIP_TO_RESOLVE_G_L
, NUM_MET_WIP
, MET_SLA_WIP_TO_RESOLVE
, NUM_MET_MTTR
, MET_SLA_MTTR
, COUNT_RESOLVE_FD
, NUM_RESOLVE_FD
--Action Reports
, AR_NUM_CREATED
, AR_NUM_CLOSED
, AR_NUM_OPEN
, COUNT_METTING_DUE_DATE
, NUM_OPEN_IN_DUE_DATE
--All Tickets
, COUNT_DISPATCHED
, NUM_AWAITING_DISPATCH
, NUM_IN_JEOPARDY
FROM (
--FMS
SELECT DATE_DAY
, CASE
WHEN upper(ENTRY_TYPE) = 'ACTION REPORT' THEN
(CASE WHEN PROBLEM_CATEGORY IS NULL THEN NULL WHEN PROBLEM_CATEGORY IN ('Compliance','Routine') THEN PROBLEM_CATEGORY ELSE 'Other' END)
WHEN upper(ENTRY_TYPE) IN ( 'TROUBLE REPORT','NET REPORT' ) THEN
(CASE WHEN SEVERITY IN ( 'Critical','Major' ) THEN SEVERITY ELSE 'Other' END)
ELSE SEVERITY END AS SEVERITY
, EQ_MARKET_CLUSTER
--Performance
, sum( C_NUM_DISPATCH_TO ) AS NUM_DISPATCH_TO
, sum( C_DURATION_DISPATCH_TO ) AS DURATION_DISPATCH_TO
, max( C_SLA_DURATION_DISPATCH_TO ) AS SLA_DURATION_DISPATCH_TO
, max( C_SLA_DURATION_DISPATCH_TO_G_L ) AS SLA_DURATION_DISPATCH_TO_G_L
, sum( C_NUM_DISPATCH_FROM ) AS NUM_DISPATCH_FROM
, sum( C_DURATION_DISPATCH_FROM ) AS DURATION_DISPATCH_FROM
, max( C_SLA_DURATION_DISPATCH_FROM ) AS SLA_DURATION_DISPATCH_FROM
, max( C_SLA_DURATION_DISPATCH_FROM_G_L ) AS SLA_DURATION_DISPATCH_FROM_G_L
, sum( NUM_CLOSED ) AS NUM_MTTR
, sum( DURATION_MTTR ) AS DURATION_MTTR
, max( SLA_MTTR ) AS SLA_MTTR
, max( SLA_MTTR_G_L ) AS SLA_MTTR_G_L
, NULL AS COUNT_CLOSED_MET_DUE_DATE
, NULL AS NUM_CLOSED_MET_DUE_DATE
--Costs
, 0 AS OVERTIME
, sum( NUM_CLOSED ) AS COUNT_NTF
, sum( NUM_DISPATCH_NTF ) AS NUM_DISPATCH_NTF
, NULL AS NUM_CTS_DISPATCH
, NULL AS TICKET_NOT_DISPATCHED
, 0 AS REMOTE_RESTORAL
--Productivity
, NULL AS NUM_TICKET_RATIO
, NULL AS NUM_TECHS_RATIO
, NULL AS NUM_DISPATCH_TICKET_RATIO
--FMS
, sum( C_NUM_DISPATCH_TO ) AS NUM_MET_DISPATCH_TO
, sum( C_MET_SLA_DISPATCH_TO ) AS MET_SLA_DISPATCH_TO
, sum( C_NUM_DISPATCH_FROM ) AS NUM_MET_DISPATCH_FROM
, sum( C_MET_SLA_DISPATCH_FROM ) AS MET_SLA_DISPATCH_FROM
, sum( NUM_CLOSED ) AS NUM_WIP
, sum( DURATION_WIP_TO_RESOLVE ) AS DURATION_WIP_TO_RESOLVE
, max( SLA_WIP_TO_RESOLVE ) AS SLA_WIP_TO_RESOLVE
, max( SLA_WIP_TO_RESOLVE_G_L ) AS SLA_WIP_TO_RESOLVE_G_L
, sum( NUM_CLOSED ) AS NUM_MET_WIP
, sum( MET_SLA_WIP_TO_RESOLVE ) AS MET_SLA_WIP_TO_RESOLVE
, sum( NUM_CLOSED ) AS NUM_MET_MTTR
, sum( MET_SLA_MTTR ) AS MET_SLA_MTTR
, sum( NUM_CLOSED ) AS COUNT_RESOLVE_FD
, sum( NUM_RESOLVE_FD ) AS NUM_RESOLVE_FD
--Action Reports
, NULL AS AR_NUM_CREATED
, NULL AS AR_NUM_CLOSED
, NULL AS AR_NUM_OPEN
, NULL AS COUNT_METTING_DUE_DATE
, NULL AS NUM_OPEN_IN_DUE_DATE
--All Tickets
, sum( NUM_DISPATCH_FROM ) AS COUNT_DISPATCHED
, sum( NUM_AWAITING_DISPATCH ) AS NUM_AWAITING_DISPATCH
, sum( CASE WHEN upper(FORCE_STATUS) = 'JEOPARDY' THEN NUM_OPEN ELSE NULL END ) AS NUM_IN_JEOPARDY
FROM CTS.dbo.NDC_Scorecard_Summary_Table t
WHERE DATE_DAY >= cast(convert(varchar(8),getdate()-1,1) as datetime)
AND DATE_DAY < cast(convert(varchar(8),getdate(),1) as datetime)
AND ENTRY_TYPE IN ('Trouble Report')
AND EQ_REGION IN ( 'NSD', 'NORTHEAST', 'CENTRAL', 'SOUTHEAST', 'WEST' )
AND EQ_MARKET_CLUSTER IN ( 'NEW ENGLAND','GEORGIA' )
AND SEVERITY IN ( 'Critical','Major' )
GROUP BY DATE_DAY
, CASE
WHEN upper(ENTRY_TYPE) = 'ACTION REPORT' THEN
(CASE WHEN PROBLEM_CATEGORY IS NULL THEN NULL WHEN PROBLEM_CATEGORY IN ('Compliance','Routine') THEN PROBLEM_CATEGORY ELSE 'Other' END)
WHEN upper(ENTRY_TYPE) IN ( 'TROUBLE REPORT','NET REPORT' ) THEN
(CASE WHEN SEVERITY IN ( 'Critical','Major' ) THEN SEVERITY ELSE 'Other' END)
ELSE SEVERITY END-- AS SEVERITY
, EQ_MARKET_CLUSTER

UNION
--AR
SELECT DATE_DAY
, CASE
WHEN upper(ENTRY_TYPE) = 'ACTION REPORT' THEN
(CASE WHEN PROBLEM_CATEGORY IS NULL THEN NULL WHEN PROBLEM_CATEGORY IN ('Compliance','Routine') THEN PROBLEM_CATEGORY ELSE 'Other' END)
WHEN upper(ENTRY_TYPE) IN ( 'TROUBLE REPORT','NET REPORT' ) THEN
(CASE WHEN SEVERITY IN ( 'Critical','Major' ) THEN SEVERITY ELSE 'Other' END)
ELSE SEVERITY END AS SEVERITY
, EQ_MARKET_CLUSTER
--Performance
, NULL AS NUM_DISPATCH_TO
, NULL AS DURATION_DISPATCH_TO
, NULL AS SLA_DURATION_DISPATCH_TO
, NULL AS SLA_DURATION_DISPATCH_TO_G_L
, NULL AS NUM_DISPATCH_FROM
, NULL AS DURATION_DISPATCH_FROM
, NULL AS SLA_DURATION_DISPATCH_FROM
, NULL AS SLA_DURATION_DISPATCH_FROM_G_L
, NULL AS NUM_MTTR
, NULL AS DURATION_MTTR
, NULL AS SLA_MTTR
, NULL AS SLA_MTTR_G_L
, sum( NUM_CLOSED ) AS COUNT_CLOSED_MET_DUE_DATE
, sum( AR_NUM_MET_DUE_DATE ) AS NUM_CLOSED_MET_DUE_DATE
--Costs
, NULL AS OVERTIME
, NULL AS COUNT_NTF
, NULL AS NUM_DISPATCH_NTF
, NULL AS NUM_CTS_DISPATCH
, NULL AS TICKET_NOT_DISPATCHED
, NULL AS REMOTE_RESTORAL
--Productivity
, NULL AS NUM_TICKET_RATIO
, NULL AS NUM_TECHS_RATIO
, NULL AS NUM_DISPATCH_TICKET_RATIO
--FMS
, NULL AS NUM_MET_DISPATCH_TO
, NULL AS MET_SLA_DISPATCH_TO
, NULL AS NUM_MET_DISPATCH_FROM
, NULL AS MET_SLA_DISPATCH_FROM
, NULL AS NUM_WIP
, NULL AS DURATION_WIP_TO_RESOLVE
, NULL AS SLA_WIP_TO_RESOLVE
, NULL AS SLA_WIP_TO_RESOLVE_G_L
, NULL AS NUM_MET_WIP
, NULL AS MET_SLA_WIP_TO_RESOLVE
, NULL AS NUM_MET_MTTR
, NULL AS MET_SLA_MTTR
, NULL AS COUNT_RESOLVE_FD
, NULL AS NUM_RESOLVE_FD
--Action Reports
, NULL AS AR_NUM_CREATED
, NULL AS AR_NUM_CLOSED
, NULL AS AR_NUM_OPEN
, sum( NUM_OPEN ) AS COUNT_METTING_DUE_DATE
, sum( NUM_OPEN_IN_DUE_DATE ) AS NUM_OPEN_IN_DUE_DATE
--All Tickets
, sum( NUM_DISPATCH_FROM ) AS COUNT_DISPATCHED
, sum( NUM_AWAITING_DISPATCH ) AS NUM_AWAITING_DISPATCH
, sum( CASE WHEN upper(FORCE_STATUS) = 'JEOPARDY' THEN NUM_OPEN ELSE NULL END ) AS NUM_IN_JEOPARDY
FROM CTS.dbo.NDC_Scorecard_Summary_Table t
WHERE DATE_DAY >= cast(convert(varchar(8),getdate()-1,1) as datetime)
AND DATE_DAY < cast(convert(varchar(8),getdate(),1) as datetime)
AND ENTRY_TYPE IN ('Action Report')
AND EQ_REGION IN ( 'NSD', 'NORTHEAST', 'CENTRAL', 'SOUTHEAST', 'WEST' )
AND EQ_MARKET_CLUSTER IN ( 'NEW ENGLAND','GEORGIA' )
GROUP BY DATE_DAY
, CASE
WHEN upper(ENTRY_TYPE) = 'ACTION REPORT' THEN
(CASE WHEN PROBLEM_CATEGORY IS NULL THEN NULL WHEN PROBLEM_CATEGORY IN ('Compliance','Routine') THEN PROBLEM_CATEGORY ELSE 'Other' END)
WHEN upper(ENTRY_TYPE) IN ( 'TROUBLE REPORT','NET REPORT' ) THEN
(CASE WHEN SEVERITY IN ( 'Critical','Major' ) THEN SEVERITY ELSE 'Other' END)
ELSE SEVERITY END-- AS SEVERITY
, EQ_MARKET_CLUSTER

UNION
--CTS
SELECT CTS_DATE AS DATE_DAY
, CASE WHEN SEVERITY IN ( 'Critical','Major' ) THEN SEVERITY ELSE 'Other' END AS SEVERITY
, EQ_MARKET_CLUSTER
--Performance
, NULL AS NUM_DISPATCH_TO
, NULL AS DURATION_DISPATCH_TO
, NULL AS SLA_DURATION_DISPATCH_TO
, NULL AS SLA_DURATION_DISPATCH_TO_G_L
, NULL AS NUM_DISPATCH_FROM
, NULL AS DURATION_DISPATCH_FROM
, NULL AS SLA_DURATION_DISPATCH_FROM
, NULL AS SLA_DURATION_DISPATCH_FROM_G_L
, NULL AS NUM_MTTR
, NULL AS DURATION_MTTR
, NULL AS SLA_MTTR
, NULL AS SLA_MTTR_G_L
, NULL AS COUNT_CLOSED_MET_DUE_DATE
, NULL AS NUM_CLOSED_MET_DUE_DATE
--Costs
, NULL AS OVERTIME
, NULL AS COUNT_NTF
, NULL AS NUM_DISPATCH_NTF
, sum( Closed_Tickets_External_NotDispatched + Closed_Tickets_NOC_NotDispatched ) AS NUM_CTS_DISPATCH
, sum( Closed_Tickets_NOC_NotDispatched ) AS TICKET_NOT_DISPATCHED
, NULL AS REMOTE_RESTORAL
--Productivity
, NULL AS NUM_TICKET_RATIO
, NULL AS NUM_TECHS_RATIO
, NULL AS NUM_DISPATCH_TICKET_RATIO
--FMS
, NULL AS NUM_MET_DISPATCH_TO
, NULL AS MET_SLA_DISPATCH_TO
, NULL AS NUM_MET_DISPATCH_FROM
, NULL AS MET_SLA_DISPATCH_FROM
, NULL AS NUM_WIP
, NULL AS DURATION_WIP_TO_RESOLVE
, NULL AS SLA_WIP_TO_RESOLVE
, NULL AS SLA_WIP_TO_RESOLVE_G_L
, NULL AS NUM_MET_WIP
, NULL AS MET_SLA_WIP_TO_RESOLVE
, NULL AS NUM_MET_MTTR
, NULL AS MET_SLA_MTTR
, NULL AS COUNT_RESOLVE_FD
, NULL AS NUM_RESOLVE_FD
--Action Reports
, NULL AS AR_NUM_CREATED
, NULL AS AR_NUM_CLOSED
, NULL AS AR_NUM_OPEN
, NULL AS COUNT_METTING_DUE_DATE
, NULL AS NUM_OPEN_IN_DUE_DATE
--All Tickets
, NULL AS COUNT_DISPATCHED
, NULL AS NUM_AWAITING_DISPATCH
, NULL AS NUM_IN_JEOPARDY
FROM CTS.dbo.FMS_Daily_Summary_Regional
WHERE CTS_DATE >= cast(convert(varchar(8),getdate()-1,1) as datetime)
AND CTS_DATE <= cast(convert(varchar(8),getdate(),1) as datetime)
AND upper( EQ_REGION ) IN ( 'NSD', 'NORTHEAST', 'CENTRAL', 'SOUTHEAST', 'WEST' )
AND EQ_MARKET_CLUSTER IN ( 'NEW ENGLAND','GEORGIA' )
GROUP BY CTS_DATE-- AS DATE_DAY
, CASE WHEN SEVERITY IN ( 'Critical','Major' ) THEN SEVERITY ELSE 'Other' END-- AS SEVERITY
, EQ_MARKET_CLUSTER

UNION
--Tickets Resolved per Tech
SELECT DATE_DAY
, ENTRY_TYPE AS SEVERITY
, EQ_MARKET_CLUSTER
--Performance
, NULL AS NUM_DISPATCH_TO
, NULL AS DURATION_DISPATCH_TO
, NULL AS SLA_DURATION_DISPATCH_TO
, NULL AS SLA_DURATION_DISPATCH_TO_G_L
, NULL AS NUM_DISPATCH_FROM
, NULL AS DURATION_DISPATCH_FROM
, NULL AS SLA_DURATION_DISPATCH_FROM
, NULL AS SLA_DURATION_DISPATCH_FROM_G_L
, NULL AS NUM_MTTR
, NULL AS DURATION_MTTR
, NULL AS SLA_MTTR
, NULL AS SLA_MTTR_G_L
, NULL AS COUNT_CLOSED_MET_DUE_DATE
, NULL AS NUM_CLOSED_MET_DUE_DATE
--Costs
, NULL AS OVERTIME
, NULL AS COUNT_NTF
, NULL AS NUM_DISPATCH_NTF
, NULL AS NUM_CTS_DISPATCH
, NULL AS TICKET_NOT_DISPATCHED
, NULL AS REMOTE_RESTORAL
--Productivity
, sum( NUM_OF_TICKETS ) AS NUM_CLOSED_TICKET_RATIO
, sum( NUM_OF_TECHS ) AS NUM_CLOSED_TECHS_RATIO
, 0 AS NUM_DISPATCH_TICKET_RATIO
--FMS
, NULL AS NUM_MET_DISPATCH_TO
, NULL AS MET_SLA_DISPATCH_TO
, NULL AS NUM_MET_DISPATCH_FROM
, NULL AS MET_SLA_DISPATCH_FROM
, NULL AS NUM_WIP
, NULL AS DURATION_WIP_TO_RESOLVE
, NULL AS SLA_WIP_TO_RESOLVE
, NULL AS SLA_WIP_TO_RESOLVE_G_L
, NULL AS NUM_MET_WIP
, NULL AS MET_SLA_WIP_TO_RESOLVE
, NULL AS NUM_MET_MTTR
, NULL AS MET_SLA_MTTR
, NULL AS COUNT_RESOLVE_FD
, NULL AS NUM_RESOLVE_FD
--Action Reports
, NULL AS AR_NUM_CREATED
, NULL AS AR_NUM_CLOSED
, NULL AS AR_NUM_OPEN
, NULL AS COUNT_METTING_DUE_DATE
, NULL AS NUM_OPEN_IN_DUE_DATE
--All Tickets
, NULL AS COUNT_DISPATCHED
, NULL AS NUM_AWAITING_DISPATCH
, NULL AS NUM_IN_JEOPARDY
FROM CTS.dbo.vw_CLOSED_TICKET_TO_TECH_RATIO
WHERE DATE_DAY >= cast(convert(varchar(8),getdate()-1,1) as datetime)
AND DATE_DAY < cast(convert(varchar(8),getdate(),1) as datetime)
AND EQ_REGION IN ( 'NSD', 'NORTHEAST', 'CENTRAL', 'SOUTHEAST', 'WEST' )
AND EQ_MARKET_CLUSTER IN ( 'NEW ENGLAND','GEORGIA' )
GROUP BY DATE_DAY
, ENTRY_TYPE-- AS SEVERITY
, EQ_MARKET_CLUSTER

UNION
--Tickets Resolved per Tech - Total
SELECT DATE_DAY
, 'Total' AS SEVERITY
, EQ_MARKET_CLUSTER
--Performance
, NULL AS NUM_DISPATCH_TO
, NULL AS DURATION_DISPATCH_TO
, NULL AS SLA_DURATION_DISPATCH_TO
, NULL AS SLA_DURATION_DISPATCH_TO_G_L
, NULL AS NUM_DISPATCH_FROM
, NULL AS DURATION_DISPATCH_FROM
, NULL AS SLA_DURATION_DISPATCH_FROM
, NULL AS SLA_DURATION_DISPATCH_FROM_G_L
, NULL AS NUM_MTTR
, NULL AS DURATION_MTTR
, NULL AS SLA_MTTR
, NULL AS SLA_MTTR_G_L
, NULL AS COUNT_CLOSED_MET_DUE_DATE
, NULL AS NUM_CLOSED_MET_DUE_DATE
--Costs
, NULL AS OVERTIME
, NULL AS COUNT_NTF
, NULL AS NUM_DISPATCH_NTF
, NULL AS COUNT_RFD
, NULL AS NUM_CTS_DISPATCH
, NULL AS TICKET_NOT_DISPATCHED
--Productivity
, sum( NUM_OF_TICKETS ) AS NUM_TICKET_RATIO
, sum( NUM_OF_TECHS ) AS NUM_TECHS_RATIO
, 0 AS NUM_DISPATCH_TICKET_RATIO
--FMS
, NULL AS NUM_MET_DISPATCH_TO
, NULL AS MET_SLA_DISPATCH_TO
, NULL AS NUM_MET_DISPATCH_FROM
, NULL AS MET_SLA_DISPATCH_FROM
, NULL AS NUM_WIP
, NULL AS DURATION_WIP_TO_RESOLVE
, NULL AS SLA_WIP_TO_RESOLVE
, NULL AS SLA_WIP_TO_RESOLVE_G_L
, NULL AS NUM_MET_WIP
, NULL AS MET_SLA_WIP_TO_RESOLVE
, NULL AS NUM_MET_MTTR
, NULL AS MET_SLA_MTTR
, NULL AS COUNT_RESOLVE_FD
, NULL AS NUM_RESOLVE_FD
--Action Reports
, NULL AS AR_NUM_CREATED
, NULL AS AR_NUM_CLOSED
, NULL AS AR_NUM_OPEN
, NULL AS COUNT_METTING_DUE_DATE
, NULL AS NUM_OPEN_IN_DUE_DATE
--All Tickets
, NULL AS COUNT_DISPATCHED
, NULL AS NUM_AWAITING_DISPATCH
, NULL AS NUM_IN_JEOPARDY
FROM CTS.dbo.vw_CLOSED_TICKET_TO_TECH_RATIO
WHERE DATE_DAY >= cast(convert(varchar(8),getdate()-1,1) as datetime)
AND DATE_DAY < cast(convert(varchar(8),getdate(),1) as datetime)
AND EQ_REGION IN ( 'NSD', 'NORTHEAST', 'CENTRAL', 'SOUTHEAST', 'WEST' )
AND EQ_MARKET_CLUSTER IN ( 'NEW ENGLAND','GEORGIA' )
GROUP BY DATE_DAY
, EQ_MARKET_CLUSTER

--AR Count
SELECT DATE_DAY
, 'All Severities' AS SEVERITY
, EQ_MARKET_CLUSTER
--Performance
, NULL AS NUM_DISPATCH_TO
, NULL AS DURATION_DISPATCH_TO
, NULL AS SLA_DURATION_DISPATCH_TO
, NULL AS SLA_DURATION_DISPATCH_TO_G_L
, NULL AS NUM_DISPATCH_FROM
, NULL AS DURATION_DISPATCH_FROM
, NULL AS SLA_DURATION_DISPATCH_FROM
, NULL AS SLA_DURATION_DISPATCH_FROM_G_L
, NULL AS NUM_MTTR
, NULL AS DURATION_MTTR
, NULL AS SLA_MTTR
, NULL AS SLA_MTTR_G_L
, NULL AS COUNT_CLOSED_MET_DUE_DATE
, NULL AS NUM_CLOSED_MET_DUE_DATE
--Costs
, NULL AS OVERTIME
, NULL AS COUNT_NTF
, NULL AS NUM_DISPATCH_NTF
, NULL AS COUNT_RFD
, NULL AS NUM_CTS_DISPATCH
, NULL AS TICKET_NOT_DISPATCHED
--Productivity
, NULL AS NUM_TICKET_RATIO
, NULL AS NUM_TECHS_RATIO
, NULL AS NUM_DISPATCH_TICKET_RATIO
--FMS
, NULL AS NUM_MET_DISPATCH_TO
, NULL AS MET_SLA_DISPATCH_TO
, NULL AS NUM_MET_DISPATCH_FROM
, NULL AS MET_SLA_DISPATCH_FROM
, NULL AS NUM_WIP
, NULL AS DURATION_WIP_TO_RESOLVE
, NULL AS SLA_WIP_TO_RESOLVE
, NULL AS SLA_WIP_TO_RESOLVE_G_L
, NULL AS NUM_MET_WIP
, NULL AS MET_SLA_WIP_TO_RESOLVE
, NULL AS NUM_MET_MTTR
, NULL AS MET_SLA_MTTR
, NULL AS COUNT_RESOLVE_FD
, NULL AS NUM_RESOLVE_FD
--Action Reports
, sum( NUM_CREATED ) AS AR_NUM_CREATED
, sum( NUM_CLOSED ) AS AR_NUM_CLOSED
, sum( NUM_OPEN ) AS AR_NUM_OPEN
, NULL AS COUNT_METTING_DUE_DATE
, NULL AS NUM_OPEN_IN_DUE_DATE
--All Tickets
, NULL AS COUNT_DISPATCHED
, NULL AS NUM_AWAITING_DISPATCH
, NULL AS NUM_IN_JEOPARDY
FROM CTS.dbo.NDC_Scorecard_Summary_Table t
WHERE DATE_DAY >= cast(convert(varchar(8),getdate()-1,1) as datetime)
AND DATE_DAY < cast(convert(varchar(8),getdate(),1) as datetime)
AND ENTRY_TYPE IN ('Action Report')
AND EQ_REGION IN ( 'NSD', 'NORTHEAST', 'CENTRAL', 'SOUTHEAST', 'WEST' )
AND EQ_MARKET_CLUSTER IN ( 'NEW ENGLAND','GEORGIA' )
GROUP BY DATE_DAY
, CASE
WHEN upper(ENTRY_TYPE) = 'ACTION REPORT' THEN
(CASE WHEN PROBLEM_CATEGORY IS NULL THEN NULL WHEN PROBLEM_CATEGORY IN ('Compliance','Routine') THEN PROBLEM_CATEGORY ELSE 'Other' END)
WHEN upper(ENTRY_TYPE) IN ( 'TROUBLE REPORT','NET REPORT' ) THEN
(CASE WHEN SEVERITY IN ( 'Critical','Major' ) THEN SEVERITY ELSE 'Other' END)
ELSE SEVERITY END-- AS SEVERITY
, EQ_MARKET_CLUSTER
) t

View 1 Replies View Related

My Update Statement Isn't Working But Select And Insert Are. What's Wrong?

Aug 11, 2007



here is my code:


Dim cn As New System.Data.SqlClient.SqlConnection(ConfigurationManager.ConnectionStrings("LocalSqlServer").ToString())

cn.Open()

Dim adapter1 As New System.Data.SqlClient.SqlDataAdapter()

adapter1.SelectCommand = New Data.SqlClient.SqlCommand("update aspnet_Membership_BasicAccess.Products
set id = '" & textid.Text & "', name = '" & textname.Text & "', price = '" & textprice.Text & "', description = '" &
textdescription.Text & "', count = '" & textcount.Text & "', pictureadd = '" & textpictureadd.Text & "', artist = '" &textartist.Text & "', catergory = '" & textcategory.text & "' where id = " & Request.Item("id") & ";", cn)

cn.Close()

Response.Redirect("database.aspx")

it posts and the page loads but the data is still the same in my datagrid. what could be wrong with this simple statement... i've tried testing the statement above with constant values of the correct type but i don't think that matters because the SqlCommand() accepts a string only anyways.. doesn't it?

View 5 Replies View Related

What Is Wrong With My OR Statement?

Apr 20, 2007

I am trying to write a stored procedure with an OR statement and can't get it to work.  Using SQL Server 2000.  (wish I had SQL 2005)
SELECT [C_CompanyName], [C_ID], [C_Email], [C_Phone], [C_SubExpireDate], [C_CompanyEmail] FROM [tblCompanies] WHERE ([C_Email] OR [C_CompanyEmail]) LIKE ('%' + @C_Email + '%')
 
Compilers tells me there is a problem near the OR
THank you

View 3 Replies View Related

What's Wrong With This Statement???

May 6, 2008

cmdSQL.Parameters.Add("@SlitWidth", System.Data.SqlDbType.Decimal).Value = IIf(slitwidth = "", System.DBNull.Value, cdec(slitwidth))
When I run the application, I get the following error:
Conversion from string "" to type 'Decimal' is not valid
I can't see where the problem is with this.. 
Thanks for any help!

View 5 Replies View Related

What Is Wrong With This Sql Statement

Jul 9, 2004

declare @name nvarchar(30)
declare @Location nvarchar(30)
declare @nonbill float
declare @empType varchar(30)

select @location=location, @Name = name, @empType = employeetype, @nonbill = CASE cbetts.v_payrolltotals.jobno WHEN left(cbetts.v_payrolltotals.jobno,3) = '900' THEN sum(totals) ELSE 0 END from cbetts.v_payrolltotals
where userid = 1025
and we = '6/4/2004'

i am getting the error for the case statment.. "Incorrect syntax near '=', Incorrect syntax near the keyword 'END'.

View 2 Replies View Related

What&#39;s Wrong With This Statement

Mar 16, 2001

This is under the "Trigger Properties" section.

CREATE TRIGGER [TRIGGER NAME] ON [dbo]
FOR INSERT, UPDATE, DELETE
AS

We have very little experience with this so any help will be appreciated. In fact we even tried to hire someone to come out and look at it.... Never called back...????

View 2 Replies View Related

What Is Wrong With This Sql Statement

Aug 13, 2004

I need to do a count of the number of lines of record returned from the sub query below. However the server keeps giving an error something like this
syntax error FROM CLAUSE. pls help me as it is urgent.

SELECT COUNT(*) AS DP FROM
(SELECT MOBIX.FROM_COSG_ID, MOBIX.TO_COSG_ID
FROM Mobix
WHERE (((Mobix.CONSG_DO_NO)=[testing]))
GROUP BY Mobix.FROM_COSG_ID, Mobix.To_COSG_ID);

View 2 Replies View Related

What Is Wrong With My Statement?

Mar 25, 2007

I actualy using SQL server 2005. I'm a stored procedure and it's accusing error near '=' that is in case clause. Do you know in what I'm wrong?

Select u.nick as 'user', l.nick as 'last_user', t.*
from threads t
inner join users u on t.user_id = u.user_id
inner join users l on t.last_user_id = l.user_id
where t.creation > @Period and forum_id=(@forum_id) and
case when @PageType = 'votacao' then t.votacao = 1 end,
case when @PageType = 'topicos' then t.votacao = 0 end,
case when @PageType = 'todos' then end

Thank you very much in advance.

View 8 Replies View Related

What's Wrong With This Statement?

Jun 25, 2007

<%@ Language="VBSCRIPT" %>
<!-- #include file="../includes/include.asp" -->
<%
connect.open connectString

Dim testSQL
testSQL = "INSERT INTO development (address) VALUES 'some address'"

connect.execute(testSQL)

%>

<% connect.close() %>


Any idea what could be wrong with this statement? It's causing the following error:


Microsoft OLE DB Provider for ODBC Drivers error '80040e14'

[Microsoft][ODBC Microsoft Access Driver] Syntax error in INSERT INTO statement.

/asa_dev/gm/dbTest.asp, line 10


Thanks,
Jason

View 2 Replies View Related

What Is Wrong In This SQL STATEMENT?

Feb 20, 2008

Hi to every one



I have tow tables on sqlce server Trans & Transd



Trans is master (TTRID auto number)

TransD is child




Code Snippet
cmd.CommandText = "insert trans ([trdate],[trtype],[trempid],[trloc])" _
& "Values('" & LbTime.Text & "', '" & cboType.Text & "', '" & txtEmpID.Text _
& "', '" & txtRBar.Text & "')"
cmd.ExecuteNonQuery()
cmd.CommandText = " SELECT MAX(TTRID) AS TTRID FROM Trans"
rdr = cmd.ExecuteReader
'Here is aproblem
If rdr.Read Then MsgBox(rdr.Item("TTRID"))

LstAsset.SelectedIndex = 0
Do
cmd.CommandText = "insert transd ([ttrid],[asssn])" _
& "Values('" & rdr.Item("TTRID") & "', '" & LstAsset.Text & "')"
cmd.ExecuteNonQuery()
If LstAsset.SelectedIndex < LstAsset.Items.Count - 1 Then
LstAsset.SelectedIndex = LstAsset.SelectedIndex + 1
ElseIf LstAsset.SelectedIndex = LstAsset.Items.Count - 1 Then
Exit Do
End If
Loop









I try to read ttrid from reader but is say there is no Colum, but when i add if rdr.read it's wooooooooork.



Whey???

View 1 Replies View Related

Select Statement Within Select Statement Makes My Query Slow....

Sep 3, 2007

Hello... im having a problem with my query optimization....

I have a query that looks like this:


SELECT * FROM table1
WHERE location_id IN (SELECT location_id from location_table WHERE account_id = 998)


it produces my desired data but it takes 3 minutes to run the query... is there any way to make this faster?... thank you so much...

View 3 Replies View Related

Can Any Body Tell Me What Wrong In My Sql Statement

Feb 22, 2007

Hi,
can any body tell me what is wrong in my sql statement
 SELECT title, price,
Budget = CASE price
WHEN price > 20.00 THEN 'Expensive'
WHEN price BETWEEN 10.00 AND 19.99 THEN 'Moderate'
WHEN price < 10.00 THEN 'Inexpensive'
ELSE 'Unknown'
END
FROM titles
it gives me this error
Msg 170, Level 15, State 1, Line 3
Line 3: Incorrect syntax near '>'.
 
but when i use somthing like that i will works fine
SELECT
Budget


i am using sql server 2000
 

View 2 Replies View Related

Whats Wrong With This Statement

Nov 16, 2007

I am getting an error Msg 8155, Level 16, State 2, Procedure usp_ImportNewASC_ParticipantPlanFundBalances, Line 1No column was specified for column 49 of 'a'.I think its something to do with the line number  48 and 102 ALLOC_PER is char(9) so i want to to cast it as a int and insert into the prodction data. 1 UPDATE
2 Statements..ParticipantPlanFundBalances1
3 SET
4 Act1 = ACT_ID1,
5 TotAct1 = TOT_ACT1,
6 Act2 = ACT_ID2,
7 TotAct2 = TOT_ACT2,
8 Act3 = ACT_ID3,
9 TotAct3 = TOT_ACT3,
10 Act4 = ACT_ID4,
11 TotAct4 = TOT_ACT4,
12 Act5 = ACT_ID5,
13 TotAct5 = TOT_ACT5,
14 Act6 = ACT_ID6,
15 TotAct6 = TOT_ACT6,
16 Act7 = ACT_ID7,
17 TotAct7 = TOT_ACT7,
18 Act8 = ACT_ID8,
19 TotAct8 = TOT_ACT8,
20 Act9 = ACT_ID9,
21 TotAct9 = TOT_ACT9,
22 Act10 = ACT_ID10,
23 TotAct10 = TOT_ACT10,
24 Act11 = ACT_ID11,
25 TotAct11 = TOT_ACT11,
26 Act12 = ACT_ID12,
27 TotAct12 = TOT_ACT12,
28 Act13 = ACT_ID13,
29 TotAct13 = TOT_ACT13,
30 Act14 = ACT_ID14,
31 TotAct14 = TOT_ACT14,
32 Act15 = ACT_ID15,
33 TotAct15 = TOT_ACT15,
34 Act16 = ACT_ID16,
35 TotAct16 = TOT_ACT16,
36 Act17 = ACT_ID17,
37 TotAct17 = TOT_ACT17,
38 Act18 = ACT_ID18,
39 TotAct18 = TOT_ACT18,
40 Act19 = ACT_ID19,
41 TotAct19 = TOT_ACT19,
42 Act20 = ACT_ID20,
43 TotAct20 = TOT_ACT20,
44 OpeningUnits = UNIT_OP,
45 OPricePerUnit = PRICE_OP,
46 ClosingUnits = UNIT_CL,
47 CPricePerUnit = PRICE_CL,
48 AllocationPercent = i.ALLOC_PER1
49
50 FROM
51 Statements..ParticipantPlanFundBalances1 pfb
52 JOIN (
53 Select
54 cp.PlanId,
55 p.ParticipantId,
56 @PeriodId Period,
57 f.FundId,
58 a.ACT_ID1,
59 a.TOT_ACT1,
60 a.ACT_ID2,
61 a.TOT_ACT2,
62 a.ACT_ID3,
63 a.TOT_ACT3,
64 a.ACT_ID4,
65 a.TOT_ACT4,
66 a.ACT_ID5,
67 a.TOT_ACT5,
68 a.ACT_ID6,
69 a.TOT_ACT6,
70 a.ACT_ID7,
71 a.TOT_ACT7,
72 a.ACT_ID8,
73 a.TOT_ACT8,
74 a.ACT_ID9,
75 a.TOT_ACT9,
76 a.ACT_ID10,
77 a.TOT_ACT10,
78 a.ACT_ID11,
79 a.TOT_ACT11,
80 a.ACT_ID12,
81 a.TOT_ACT12,
82 a.ACT_ID13,
83 a.TOT_ACT13,
84 a.ACT_ID14,
85 a.TOT_ACT14,
86 a.ACT_ID15,
87 a.TOT_ACT15,
88 a.ACT_ID16,
89 a.TOT_ACT16,
90 a.ACT_ID17,
91 a.TOT_ACT17,
92 a.ACT_ID18,
93 a.TOT_ACT18,
94 a.ACT_ID19,
95 a.TOT_ACT19,
96 a.ACT_ID20,
97 a.TOT_ACT20,
98 a.UNIT_OP,
99 a.PRICE_OP,
100 a.UNIT_CL,
101 a.PRICE_CL,
102 Cast(i.ALLOC_PER1 as int)
103 FROM
104 ASDBF a
105 -- Derive the unique PlanId from the Statements ClientPlan table
106 INNER JOIN Statements..ClientPlan cp
107 ON a.PLAN_NUM = cp.ClientPlanId
108 AND
109 cp.ClientId = @ClientId
110 -- Derive the unique ParticipantId from the Statements Participant table
111 INNER JOIN Statements..Participant p
112 ON a.PART_ID = p.PartId
113 --Derive the unique FundID from the Statements Fund Table...
114 INNER JOIN Statements..Fund f
115 ONa.FUND_ID = f.Cusip
116 OR
117 a.FUND_ID = f.Ticker
118 OR
119 a.FUND_ID = f.ClientFundId
120 -- get the allocation percent from the INVSRC
121 LEFT Outer JOIN INVSRC i
122 ONa.FUND_ID = i.INV_ID
123 AND
124 a.PLAN_NUM = i.Plan_Number
125 AND
126 a.PART_ID = i.PART_ID
127 --INNER JOIN Statements..AscActCodes as
128 --ON
129
130 WHERE
131 a.Import = 1
132 )a
133 ON pfb.PlanId = a.PlanId
134 AND
135 pfb.ParticipantId = a.ParticipantId
136 AND
137 pfb.PeriodId = PeriodId
138 AND
139 pfb.FundId = a.FundId
140
 
Any help will be appreciated
Regards
Karen

View 6 Replies View Related

INSERT INTO Statement, What Is Wrong With It?

May 4, 2004

INSERT INTO MainSearch (Date,Time,RemoteHost,Make,Model,PriceLower,PriceUpper) VALUES ('04/05/2004','12:35:49','127.0.0.1','AUDI','A6','1000','2000')

It crashes and says "Error in INSERT INTO statement", can anyone help?

Thanks

View 2 Replies View Related

What's Wrong With My INSERT Statement?

Nov 30, 2006

Below is the code used to process information provided from a form connected it :


Code:

'INSERTING INFO INTO THE VISITOR'S TABLE IN ATLAS when ALL info is needed - D is for Departure
'MAKE SURE to ENABLE ALL the passing-value formulas above for statement below to work properly
mycn.Execute "Insert into visitor " & _
"(lastname,firstname,affiliation,visitortype,a1line1,a1line2,a1city,a1state,a1zip," & _
"a1country,homephone,workphone,email,dob,citizenship,idtype,idnumber,idplace," & _
"minor1,m1dob,minor2,m2dob,minor3,m3dob,event1,voyage,event,CBY)" & _
"values(" & _
LastName & FirstName & affiliation & visitortype & a1line1 & a1line2 & a1city & a1state & a1zip & _
a1country & homephone & workphone & email & dob & citizenship & idtype & idnumber & idplace & _
minor1 & m1dob & minor2 & m2dob & minor3 & m3dob & event1 & voyage & "'D','WEB')"

set mycn = nothing



The code works correctly how it is setup here but it is no longer useful for this new form. The event variable, set as 'D' in the values section now has to be decided on outside of the insert statement and for some reason when I make the change in red below I get an unknown error:


Code:

'INSERTING INFO INTO THE VISITOR'S TABLE IN ATLAS when ALL info is needed - D is for Departure
'MAKE SURE to ENABLE ALL the passing-value formulas above for statement below to work properly
mycn.Execute "Insert into visitor " & _
"(lastname,firstname,affiliation,visitortype,a1line1,a1line2,a1city,a1state,a1zip," & _
"a1country,homephone,workphone,email,dob,citizenship,idtype,idnumber,idplace," & _
"minor1,m1dob,minor2,m2dob,minor3,m3dob,event1,voyage,event,CBY)" & _
"values(" & _
LastName & FirstName & affiliation & visitortype & a1line1 & a1line2 & a1city & a1state & a1zip & _
a1country & homephone & workphone & email & dob & citizenship & idtype & idnumber & idplace & _
minor1 & m1dob & minor2 & m2dob & minor3 & m3dob & event1 & voyage & event & "'WEB')"

set mycn = nothing

View 2 Replies View Related

Whats Wrong With This Statement?

Jun 4, 2007

Query analyser is saying there is an error near the WHERE keyword:


INSERT INTO EMPLOYEE_TABLE (BANK_ACC, SORT_CODE)
VALUES (63035391,603030)
WHERE 40 IN (SELECT EMPLOY_REF FROM EMPLOYEE_TABLE);


Thanks people.

View 4 Replies View Related

What's Wrong With My Statement? (simple)

Aug 3, 2007

Hello well-intentionned reader,

I'm having trouble figuring out what's wrong with my statement. This is what the table looks like. I want to display employees under the manager with the last name King. my statements are below the tableName Null? Type
----------------------------------------- -------- ----------------------------
EMPLOYEE_ID NOT NULL NUMBER(6)
LAST_NAME NOT NULL VARCHAR2(25)
SALARY NUMBER(8,2)
MANAGER_ID NUMBER(6)


My statement
SQL> SELECT last_name, salary
FROM employees
WHERE manager_id =
(SELECT employee_id
FROM employees
WHERE EMPLOYEE.LAST_NAME = .King.); 2 3 4 5 6
WHERE EMPLOYEE.LAST_NAME = .King.)
*
ERROR at line 6:
ORA-00936: missing expression


Thank you for your time.

/gozu

View 4 Replies View Related

What Is Wrong With This TSQL Statement

Mar 3, 2008



hello,

I have wriiten this query to select all records from those tables in a database that have "to_be_transffered" column where this "to_be_transferred_column" is not null


--code


EXEC sp_MSForEachTable 'IF EXISTS(SELECT c.table_name, c.column_name

FROM information_schema.columns c

INNER JOIN information_schema.tables t

ON c.table_name = t.table_name

WHERE c.table_name = SUBSTRING(''?'', (CHARINDEX(''['',''?'', 2) + 1),

((LEN(''?'') - (CHARINDEX(''['',''?'', 2) + 1))))

AND c.column_name = ''to_be_transferred''

AND t.table_type = ''BASE TABLE''

)

BEGIN



SELECT * FROM ?

WHERE to_be_transferred IS NOT NULL

END'



But I am getting the following error


Msg 207, Level 16, State 3, Line 12

Invalid column name 'to_be_transferred'

I thought the if statement filters all those tables with the specified column and do select statement fot those tables only

what did I do wrong, any suggestionm?

Thanks

View 6 Replies View Related

Wrong Result In Query Statement How To Fix?!!

Nov 2, 2004

The query is (Select (25/20*100))
The wrong giving result is 100 it's should be 125
How I can use a query statement to get a correct result?

View 4 Replies View Related

Whats Wrong In The Insert Statement

May 9, 2007

Hi all

insert into externaltemp(Cid,Keywords)
values(select id as Cid, f_name+','+ l_name as keywords from RPO.dbo.REQUIREMENT_RESOURCE)



Thanks in Advance.

Malathi Rao

View 2 Replies View Related

Whats Wrong With This Update Statement?

Dec 21, 2007

I wrote this update statement and it looks right but I keep getting an error that says "incorrect syntax next to c" what did I do wrong?

Here is the statement:

UPDATE tblclients c
SET intproviderId = (SELECT TOP 1 t.inttransferid FROM tbltransfer t WHERE t.intclientids = c.intclientid ORDER BY t.inttransferid DESC)

View 6 Replies View Related

Whats Wrong With My Join Statement?

Nov 2, 2005

The following sql statement is rejected by asp.net (working with sqlserver). It says something is wrong near "TableFlights". But I don'tsee anything wrong with it.Any help is appreciated:SELECT TableFlights.IdFlight, Concat = (TableFlights.OrganizerLastName+ ', ' + TableFlights.OrganizerFirstName + ' Flt:' +TableFlights.FlightNumber + ' on ' +Convert(char(10,TableFlights.FlightDate,101)) FROM TableFlights INNERJOIN TablePassengers ON TableFlights.IdFlight=TablePassengers.IdFlightWhere TablePassengers.Email = 'marvin@micro-net.com' Order ByTableFlights.OrganizerLastName, TableFlights.OrganizerFirstName-- Marvin

View 1 Replies View Related

Wrong Select Or Insert Into ?

Aug 22, 2005

helloI have strange problem.I write to MySQL data to table with one column of varchar(8000). I write750000 bytes, so it get 93 records of 8000 bytes and the last - 6000bytes.but what is strange - when I check length of this records:select len(column_name) from table_namethis last record ( which is 6000bytes length ) isn't on 94 position, but ...on 14 position !?The same, when I try to reads recods:select column name from table namethis 6000 record apear on 14 position instead on 94 positionIt's important to me to get this record on proper ( last) position to notchange ( complicating ) my C++ application algoritm.Could someone help me, pleasethanks in advanceAdam

View 4 Replies View Related

Select From 2 Tables Cause A Loop... What Am I Doing Wrong?

Apr 24, 2006

I have two tables that I wish to return values for and populate a text file with the returned values. This part works well, however, I get strange values when I rung the Select statement against both tables.
For example, if I run this statement:
Select count(page_count), sum(Page_count) from Contracts
this will return the values I expect
If I were to add another table to this query then things go crazy. For instance, if I took the same query and added the Maps table to it as such:
Select count(page_count), sum(Page_count) from Contracts, Maps
I get crazy numbers that are many times more then the actual numbers

My goal is to get the page count, number of records for Contracts and number of records from Maps all in one query

View 5 Replies View Related

Select Into Outfile: Wrong Field Length

Oct 29, 2005

Hi,

I have to export data from a database in a special form (the customer wants it this way): every record in one line, and all fields in a large string. Thus, only by convendion it is defined the first seven characters being the central number, the next fivr characters the number of this spare part type, the following 30 the description, and so on...
Just at the end of every record has to be CF-LF.

all works well so far by with this code (in PHP) -
except one thing - see details below the code snippet):

$q1 = "SELECT dirc_matdata.* FROM dirc_vorgang, dirc_matdata ";
$q2 = "WHERE dirc_vorgang.rep_out> '".$anf_dat."'";
$q3 = "AND dirc_vorgang.rep_out < '".$end_dat."'";
$q4 = "AND dirc_matdaten.auftrags_nr = dirc_vorgang.auftrags_nr";
$q5 = "INTO outfile '".$drive.":/matdata.dat'";
$q6 = "FIELDS TERMINATED BY '' LINES TERMINATED BY '
';";
$query = $q1.' '.$q2.' '.$q3.' '.$q4.' '.$q5.' '.$q6;

The INT (integer) fields appear in the (plain text) output file with wrong field lengths. Although I had set the first field (i.e. the number of this type ofparts) to a length of 5, this field appears in the outfile being 10 characters long -- the maximum length of a normal INT type field (and twice as long as it should be).

Or (when set to filed type "MEDIUMINT"), it appears in the length of 8 characters only. But I need this data exactly 5 characters long in this application.

BIG QUESTION: How can I make mySQL (Ver. 4.1) write this fields into the outfile with their correct length as defined in the database structure?

Is this flaw already known in the community?
Is there any sort of "workaround"?

Thank you for every hint!

-ah-

View 1 Replies View Related

Transact SQL :: INSERT Array Of Integer Into Table - Wrong Return Value Of Statement In ODBC

Sep 30, 2015

I would like to INSERT an array of integer into a table in MSSQL Server, then count the number of rows in the table with c++ using ODBC. Here you find my code to do this task:

#include <windows.h>
#include <stdlib.h>
#include <stdio.h>
#include<tchar.h>
#include <sql.h>
#include <sqlext.h>
#include<sqltypes.h>

[Code] ....

In my code, I would like to Insert the array and then count the number of rows:

SQLTCHAR Statement[] = _T("INSERT INTO information1(Wert1,Wert2) VALUES(?,?) select count(*) as a from information1 ") ;

Problem : My expectation is, that first 9 rows are inserted into table then comes 9 as result to user (if the table is empty) but this code returns me 1 if first the table is empty. If the table is not empty, it returns 1+number of  existing rows in the table. If I take a look inside the table, the 9 rows are successfully inserted in it. Only the number of rows in the table is wrong.

Hint : If I monitor the database using SQL Profiler. It looks like this:

Why this statement doesn't work correctly?

View 7 Replies View Related

SELECT * View Returning Wrong Column Data

Oct 16, 2007

SQL 2005 9.00.3402.00 (x64) As Above really when I select * OR select a single column from the view the wrong column data is returned. in SQL Management Studio when I expand the Columns of the view it reflects the old table structure not the new table structure. I can easily fix by compiling the view again but this would mean I would have to recompile all referencing views when I make a change to table structures. I've tried various DBCC Clean Buffers & drop cache with no effect. Is there a command to recompile all views & poss stored procs in a database. Any help or explanation would be appreciated GW

View 12 Replies View Related

Multiple Tables Used In Select Statement Makes My Update Statement Not Work?

Aug 29, 2006

I am currently having this problem with gridview and detailview. When I drag either onto the page and set my select statement to pick from one table and then update that data through the gridview (lets say), the update works perfectly.  My problem is that the table I am pulling data from is mainly foreign keys.  So in order to hide the number values of the foreign keys, I select the string value columns from the tables that contain the primary keys.  I then use INNER JOIN in my SELECT so that I only get the data that pertains to the user I am looking to list and edit.  I run the "test query" and everything I need shows up as I want it.  I then go back to the gridview and change the fields which are foreign keys to templates.  When I edit the templates I bind the field that contains the string value of the given foreign key to the template.  This works great, because now the user will see string representation instead of the ID numbers that coinside with the string value.  So I run my webpage and everything show up as I want it to, all the data is correct and I get no errors.  I then click edit (as I have checked the "enable editing" box) and the gridview changes to edit mode.  I make my changes and then select "update."  When the page refreshes, and the gridview returns, the data is not updated and the original data is shown. I am sorry for so much typing, but I want to be as clear as possible with what I am doing.  The only thing I can see being the issue is that when I setup my SELECT and FROM to contain fields from multiple tables, the UPDATE then does not work.  When I remove all of my JOIN's and go back to foreign keys and one table the update works again.  Below is what I have for my SQL statements:------------------------------------------------------------------------------------------------------------------------------------- SELECT:SELECT People.FirstName, People.LastName, People.FullName, People.PropertyID, People.InviteTypeID, People.RSVP, People.Wheelchair, Property.[House/Day Hab], InviteType.InviteTypeName FROM (InviteType INNER JOIN (Property INNER JOIN People ON Property.PropertyID = People.PropertyID) ON InviteType.InviteTypeID = People.InviteTypeID) WHERE (People.PersonID = ?)UPDATE:UPDATE [People] SET [FirstName] = ?, [LastName] = ?, [FullName] = ?, [PropertyID] = ?, [InviteTypeID] = ?, [RSVP] = ?, [Wheelchair] = ? WHERE [PersonID] = ? ---------------------------------------------------------------------------------------------------------------------------------------The only fields I want to update are in [People].  My WHERE is based on a control that I use to select a person from a drop down list.  If I run the test query for the update while setting up my data source the query will update the record in the database.  It is when I try to make the update from the gridview that the data is not changed.  If anything is not clear please let me know and I will clarify as much as I can.  This is my first project using ASP and working with databases so I am completely learning as I go.  I took some database courses in college but I have never interacted with them with a web based front end.  Any help will be greatly appreciated.Thank you in advance for any time, help, and/or advice you can give.Brian 

View 5 Replies View Related

SQL Server 2012 :: Create Dynamic Update Statement Based On Return Values In Select Statement

Jan 9, 2015

Ok I have a query "SELECT ColumnNames FROM tbl1" let's say the values returned are "age,sex,race".

Now I want to be able to create an "update" statement like "UPATE tbl2 SET Col2 = age + sex + race" dynamically and execute this UPDATE statement. So, if the next select statement returns "age, sex, race, gender" then the script should create "UPDATE tbl2 SET Col2 = age + sex + race + gender" and execute it.

View 4 Replies View Related

Using Conditional Statement In Stored Prcodure To Build Select Statement

Jul 20, 2005

hiI need to write a stored procedure that takes input parameters,andaccording to these parameters the retrieved fields in a selectstatement are chosen.what i need to know is how to make the fields of the select statementconditional,taking in consideration that it is more than one fieldaddedfor exampleSQLStmt="select"if param1 thenSQLStmt=SQLStmt+ field1end ifif param2 thenSQLStmt=SQLStmt+ field2end if

View 2 Replies View Related

TSQL - Use ORDER BY Statement Without Insertin The Field Name Into The SELECT Statement

Oct 29, 2007

Hi guys,
I have the query below (running okay):



Code Block
SELECT DISTINCT Field01 AS 'Field01', Field02 AS 'Field02'
FROM myTables
WHERE Conditions are true
ORDER BY Field01

The results are just as I need:


Field01 Field02

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

192473 8461760

192474 22810



Because other reasons. I need to modify that query to:



Code Block
SELECT DISTINCT Field01 AS 'Field01', Field02 AS 'Field02'
INTO AuxiliaryTable
FROM myTables
WHERE Conditions are true
ORDER BY Field01
SELECT DISTINCT [Field02] FROM AuxTable
The the results are:

Field02

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

22810
8461760

And what I need is (without showing any other field):

Field02

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

8461760
22810


Is there any good suggestion?
Thanks in advance for any help,
Aldo.

View 3 Replies View Related

How To Write Select Statement Inside CASE Statement ?

Jul 4, 2006

Hello friends,
I want to use select statement in a CASE inside procedure.
can I do it? of yes then how can i do it ?

following part of the procedure clears my requirement.

SELECT E.EmployeeID,
CASE E.EmployeeType
WHEN 1 THEN
select * from Tbl1
WHEN 2 THEN
select * from Tbl2
WHEN 3 THEN
select * from Tbl3
END
FROM EMPLOYEE E

can any one help me in this?
please give me a sample query.

Thanks and Regards,
Kiran Suthar

View 7 Replies View Related







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