MS SQL Server :: Wrong Result In Query Statement How To Fix?!!

Wrong Query Result

Jul 15, 2002

Hi all,
As my user runs a query for her data, the query shows up with someone else's data. Can somebody tell me what happened and how o fix the problem. Thanks!

DangKhoa

View 4 Replies View Related

99pShop writes "I am trying to create a query to select all record for a specific 'PersonnelID'that have vacation booked in 2006 from a database.

I have written this query but it returns all PersonnelID that fit into the date block

requrirements

PersonnelID
select if DateStart falls within 2006
select if DateEnd falls within 2006

query i built:

Select * From tblResourceList Where PersonnelID=72
And tblResourceList.DateStart
Between '01 January 2006' And '31 December 2006'
Or tblResourceList.DateEnd Between '01 January 2006'
And '31 December 2006'
Or (tblResourceList.DateStart < '01 January 2006'
And tblResourceList.DateEnd > '31 December 2006')"

View 4 Replies View Related

Wrong Query Result

Apr 16, 2008

Hello,
i have a table EMP

EMPNO int Checked
ENAME nchar(10) Checked
JOB nchar(10) Checked
MGR varchar(50) Checked
HIREDATE nvarchar(50) Checked
SAL int Checked
COMM varchar(50) Checked
DEPTNO int Unchecked
Unchecked

Code Snippet

EMPNO
ENAME
JOB
MGR
HIREDATE
SAL
COMM
DEPTNO

7369
SMITH
CLERK
7902
17-Dec-80
800

20

7499
ALLEN
SALEMAN
7698
20-Feb-81
1600
300
30

7521
WARD
SALEMAN
7698
22-Feb-81
1250
500
30

7566
JONES
MANAGER
7839
2-Apr-81
2975

20

7654
MARTIN
SALESMAN
7698
28-Sep-81
1250
1400
30

7698
BLAKE
MANAGER
7839
1-May-81
2850

30

7782
CLARK
MANAGER
7839
9-Dec-82
2450

10

7788
SCOTT
ANALYST
7566
9-Dec-82
3000

20

7839
KING
PRESIDENT

17-Nov-81
5000

10

7844
TURNER
SALESMAN
7698
8-Sep-81
1500
0
30

7876
ADAMS
CLERK
7788
12-Jan-83
1100

20

7900
JAMES
CLERK
7698
3-Dec-81
950

30

7902
FORD
ANALYST
7566
3-Dec-81
3000

20

7934
MILLER
CLERK
7782
23-Jan-82
1300

10

After I execute the query

Code Snippet
select *
from emp
where( deptno=10
or comm is not null
or sal <= 2000
)
and deptno=20

I got

Code Snippet
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
7369 SMITH CLERK 7902 17-Dec-80 800 20
7566 JONES MANAGER 7839 2-Apr-81 2975 20
7788 SCOTT ANALYST 7566 9-Dec-82 3000 20
7876 ADAMS CLERK 7788 12-Jan-83 1100 20
7902 FORD ANALYST 7566 3-Dec-81 3 000 20

Obviously, it is wrong with SAL.
Why?

Thanks

View 1 Replies View Related

Please Help Me To Optimize This Sql Query, It Takes 28 Seconds To Return Result. Please Give Me A Tips Where I Went Wrong?

Aug 21, 2006

SELECT * FROM
( SELECT TOP 15 * FROM
(SELECT TOP 15 CMDS.STOCKCODE AS CODE,CMDS.STOCKNAME AS NAME,CMDS.Sector AS SEC, CMD7.REFERENCE AS REF,T1.HIGHP AS HIGH,
T1.LOW,T1.B1_CUM AS 'B/QTY', T1.B1_PRICE AS BUY,T1.S1_PRICE AS SELL,
T1.S1_CUM AS 'S/QTY', T1.D_PRICE AS LAST,T1.L_CUM AS LVOL,T1.Chg AS CHG,T1.Chgp AS CHGP, T1.D_CUM AS VOLUME,substring(T1.ST,7,6) AS TIME,
CMDS.SERIAL as SERIAL FROM CMD7,CMDS,CMD4 AS T1 WHERE T1.ST IN
(SELECT max(T2.ST) FROM CMD4 AS T2 ,CMDS WHERE
T1.SERIAL=T2.SERIAL
AND CMDS.SERIAL=T2.SERIAL
AND T2.sd='20060821'
AND CMDS.sd='20060821'
AND T2.L_CUM < '1900'
AND CMDS.sector >='1'
AND CMDS.sector <='47')
AND CMDS.SERIAL=T1.SERIAL AND
CMDS.SERIAL=CMD7.SERIAL AND
CMDS.sd='20060821' AND
CMD7.sd='20060821' AND
T1.sd='20060821' AND
T1.L_CUM < '1900' AND
CMDS.sector >='1' AND
CMDS.sector <='47' ORDER BY T1.D_CUM desc)
AS TBL1 ORDER BY VOLUME asc) AS TBL1 ORDER BY VOLUME desc;

View 6 Replies View Related

How To Page SQL Query Result Returned From FOR XML Statement?

Apr 25, 2006

Gurus.I do not know if it is possible, but here is what I want to do.I want to allow user to page the SQL result, so he could decides toreturn from row 10 to row 20, or row 100 to 200, without returns thewhole resultset. Every time he sends another request, I do not mind tohit the database again, (I do not want to cache the result in themiddle tier server, scalability issue), and I know that I could achievethis with CURSOR, but unfortunately the FOR XML is not allowed in aCURSOR statement .(I know that I could achieve what I want to do by writing custom codein the middle tier, but I just want to see if there is a way to do thison the database side.)Any comments & suggestion is greatly appreciated.Thanks in advance.(I am using SQL2005)John

View 2 Replies View Related

Wrong Result Set

Apr 16, 2006

Hello all,I have the following t-sql batch:create procedure stp_test(@p_date1 as datetime = null,@p_date2 as datetime = null)as beginset @p_date1 = isnull(@p_date1, <some expression>)set @p_date2 = isnull(@p_date2, <some other expression>)select<a lot of columns>from<some table>inner join <some other table> on <expression>inner join <dirived table> on <expression>wheredate1 <= @p_date1 anddate2 <= @p_date2 and(date1 >= @p_date1 ordate2 >= @p_date2)endgoexec stp_testThis gives a WRONG resultset.When I replace the variables with hardcoded values in the right format, thereturned result set is CORRECT, as followswheredate1 <= 'hard coded date value 1' anddate2 <= 'hard coded date value 2' and(date1 >= 'hard coded date value 1' ordate2 >= 'hard coded date value 2')When I elimate the derived table with a temporary table, the returned resultset is CORRECTWhen I store the parameters in a local variable, and use the local variable,the returned result set is CORRECT, as followscreate procedure stp_test(@p_date1 as datetime = null,@p_date2 as datetime = null)as begindeclare @l_date1 datetimedeclare @l_date2 datetimeset @l_date1 = @p_date1set @l_date2 = @p_date2set @l_date1 = isnull(@l_date1, <some expression>)set @l_date2 = isnull(@l_date2, <some other expression>)select<a lot of columns>from<some table>inner join <some other table> on <expression>inner join <dirived table> on <expression>wheredate1 <= @l_date1 anddate2 <= @l_date2 and(date1 >= @l_date1 ordate2 >= @l_date2)endgoWhen I put less columns in the select list, the returned result set isCORRECT, it doesnt make sense wich columns I remove from the select list.The tables are not small (500.000 rows) and also is the result set. I usethis construction elsewhere, on other table combinations, but dont haveproblems. So the content of the data makes difference.Seems to me as a bug.My question is: Can I say the derived table is instable in SQL server andcauses the problem of the wrong result set here?Peter

View 3 Replies View Related

Wrong Result Set When Using NoLock

Oct 22, 2007

I have a Store Procedure on a Sql Server 2000 Where I use the Table Hint"NoLock" on all selects.One of my clients (OleDbConnection from C#) doesn't get the same Result Setas the others. The result Set should have 31 rows but this client only gets5!When I remove all the "NoLocks" everything works fine. How can that be?

View 5 Replies View Related

What's Wrong With #result Temp Table?

Dec 27, 2005

question: get 10 gram of the gold from Products? i cannot think a good algorithm,just use a clumsy proach to do this job ,but also failed,the last statement occur a Error "#result" have syntax error;
here my code:
--------test condition-------------Create table Qt(id int identity primary key,Q int)Goinsert into Qt(Q) values(8);insert into Qt(Q) values(6);insert into Qt(Q) values(6);insert into Qt(Q) values(5);insert into Qt(Q) values(3);insert into Qt(Q) values(3);insert into Qt(Q) values(1);insert into Qt(Q) values(0);Go-------------------------------
Create procedure Test2( @m int) ASdeclare @i int,@j int,@n int ,@id int ,@q int ,@last intset @i=1;set @j=1;Create table #result(id int, Q int)declare __cursor cursor forselect * from Qt where Qt.Q <=@m order by Qt.q desc for read onlyOpen _cursor;select @n=@@cursor_rows;fetch last from _cursor into @lastif @n > 0 -------------------beginBegin_this:fetch absolute @i from __cursor into @id,@qif @q=@m begininsert into #result(id,Q) values(@id,@q);GoTo End_this;End-------------------else-------------------------------begin------------------------if @q=@lastbeginSet @j=@j+1;------------if @j>@n Goto End_this;elseBeginset @i=@j;Delete from #result;End------------Endelsebegininsert into #result(id,Q) values(@id,@q);set @i=@i+1;end------------------------GoTo Begin_thisEnd-----------------------------
End_this:select id,Q from #result--close __cursor--deallocate __cursor
.....please help me, =A='

View 6 Replies View Related

Sql_help Tablename (Wrong Result)

Oct 31, 2006

Hi All,

I am facing a unique problem, In my DB all tables which has nvarchar datatype columns. When i see any table in EM design mode it shows length of navrchar datatype column correct.

But if i see same table through QA using sp_help tablename
then it will show length of my nvarchar column just double.

when i see all my nvarchar columns in syscolumns it will display the length of my nvarchar columns just double then actual.

i dont know where exactly the problem. Because of that my tester are getting wrong table information through my data dictionary whic i created using sysobects,syscolumns,sysproperties.

can anybody tell where is the problem exaqctly ?

View 8 Replies View Related

Bad Executed Plan And Wrong Result By SQL

Jul 23, 2005

I have one query that executes many times in a week.I created one Maintenances plan that Rebuild all index in my Database thathas been executed at 23:40 Saturday until stop finished at Sunday.However at middle of week (Wednesday or Thursday), that query don�t returnresult like that must be. The time exceeded and the result are total wrong.I compare the normal executed plan and the �crazy� one that SQL create tomount result.The normal is nested with index seek (very fast, the wrong is Merger withhash aggregate (very slow). After Index Rebuild, the executed plan bringresult that must be, but when the merge plan are executed with many updateson that tables (SAM_GUIA_EVENTO and SAM_GUIA), at middle of week, theresult are total wrong, with many rows back.I recommended Index Seek force by coalesce function on one columnaggregate, but everyone here were very panic with that behavior of SQLServer.Please , anyone help me to explain that!Krisnamourt!P.S: Attachments :--Force Index Query with coalesceSELECT count(*)FROM SAM_GUIA_EVENTOS E,SAM_GUIA GWHERE G.PEG=736740AND E.GUIA=coalesce(G.HANDLE,G.HANDLE) AND E.CLASSEGERENCIALPAGTO is NULL--Normal QuerySELECT count(*)FROM SAM_GUIA_EVENTOS E,SAM_GUIA GWHERE G.PEG=736740AND E.GUIA=G.HANDLE AND E.CLASSEGERENCIALPAGTO is NULL--Message posted via http://www.sqlmonster.com

View 5 Replies View Related

Grand Total Is Showing Wrong Result

May 19, 2008

My FactEmployeeTable showing 13 rows of data related to employees, but i used a bridge table for connecting project and employee dimensions. In the ProjEmpBridge table i mapped only 6 employees data to the proj's data. When dragging projet name and employee name it is showing data related to 6 employees but grand total showing 13 as the result. I dont know why it is showing 13 instead of 6. Can anyone please solve this issue.

View 10 Replies View Related

Role Defined Wrong Grand Total Result

May 22, 2008

HI,

In my cube I have defined a role where thet user can only browse certain dimention value. BUt in grand Total the result showing is for all the diemnsion values.

for example The user restricted to browse only Australia and UK Country , But in grand total its showing the SUM of all the country.

Any help will be appreciated.

Thanks

View 3 Replies View Related

Saving Query Result To A File , When View Result Got TLV Error

Feb 13, 2001

HI,
I ran a select * from customers where state ='va', this is the result...

(29 row(s) affected)
The following file has been saved successfully:
C:outputcustomers.rpt 10826 bytes

I choose Query select to a file
then when I tried to open the customer.rpt from the c drive I got this error message. I am not sure why this happend
invalid TLV record

Thanks for your help

Ali

View 1 Replies View Related

End Result Is Main Query Results Ordered By Nested Result

May 1, 2008

As the topic suggests I need the end results to show a list of shows and their dates ordered by date DESC.
Tables I have are structured as follows:

SHOWS
showID
showTitle

SHOWACCESS
showID
remoteID

VIDEOS
videoDate
showID

SQL is as follows:

SELECT shows.showID AS showID, shows.showTitle AS showTitle,
(SELECT MAX(videos.videoFilmDate) AS vidDate FROM videos WHERE videos.showID = shows.showID)
FROM shows, showAccess
WHERE shows.showID = showAccess.showID
AND showAccess.remoteID=21
ORDER BY vidDate DESC;

I had it ordering by showTitle and it worked fine, but I need it to order by vidDate.
Can anyone shed some light on where I am going wrong?

thanks

View 3 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'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" %>

<%
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

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

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 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'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

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

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