Second Occurance

Mar 26, 2008

Hi!
I have a problem with a sql script.

This little nice script works fine by itself.

select top 1 MWh from
(
select Top 2 Mwh, tDeklarationElleverantorID from tDeklarationElleverantor where tDeklarationElleverantor.DeklarationID = tDeklaration.DeklarationID
and tDeklarationElleverantor.Borttagen IS NULL order by DeklarationElleverantorID
) as [Elleverantor 2 MWh]


But when I put in my long select it doesnt work. Also the long script works fine without these rows inserted. I hope someone can tell me why.
//Tommy

I post the entire script and the error message can be found below.

Msg 156, Level 15, State 1, Line 59
Incorrect syntax near the keyword 'select'.
Msg 170, Level 15, State 1, Line 114
Line 114: Incorrect syntax near '-'.


SELECT tDeklaration.Diarienummer,
IO.OrgNr,
IO.OrgNamn,
tDeklaration.KontaktpersonNamn,
tDeklaration.KontaktpersonEpost,
tDeklaration.KontaktpersonTele,
(select sum(tForbrukadEgen.EgenProduceradEl) from tForbrukadEgen where tForbrukadEgen.DeklarationID = tDeklaration.DeklarationID) as [Egen producerad],
-- Hämta ut Orgnamnet för en Elleverantör. Finns det inte i Tabellen ta intressentID:t och hoppa iväg till IREG.
(select
Case When
(Select OrgNamn from tDeklarationElleverantor Where DeklarationElleverantorID =
(SELECT top 1 DeklarationElleverantorID FROM tDeklarationElleverantor
WHERE tDeklaration.DeklarationID = tDeklarationElleverantor.DeklarationID and tDeklarationElleverantor.Borttagen IS NULL )) IS NULL
then
(Select intressentregister_skarp.dbo.tIntressentOrganisation.OrgNamn from intressentregister_skarp.dbo.tIntressentOrganisation Where intressentregister_skarp.dbo.tIntressentOrganisation.IntressentID =
(SELECT top 1 IntressentID FROM tDeklarationElleverantor
WHERE tDeklaration.DeklarationID = tDeklarationElleverantor.DeklarationID and tDeklarationElleverantor.Borttagen IS NULL ))
ELse (Select OrgNamn from tDeklarationElleverantor Where DeklarationElleverantorID =
(SELECT top 1 DeklarationElleverantorID FROM tDeklarationElleverantor
WHERE tDeklaration.DeklarationID = tDeklarationElleverantor.DeklarationID and tDeklarationElleverantor.Borttagen IS NULL )) End) [Elleverantor 1 Namn],
(select
Case When
(Select OrgNr from tDeklarationElleverantor Where DeklarationElleverantorID =
(SELECT top 1 DeklarationElleverantorID FROM tDeklarationElleverantor
WHERE tDeklaration.DeklarationID = tDeklarationElleverantor.DeklarationID and tDeklarationElleverantor.Borttagen IS NULL )) IS NULL
then
(Select intressentregister_skarp.dbo.tIntressentOrganisation.OrgNr from intressentregister_skarp.dbo.tIntressentOrganisation Where intressentregister_skarp.dbo.tIntressentOrganisation.IntressentID =
(SELECT top 1 IntressentID FROM tDeklarationElleverantor
WHERE tDeklaration.DeklarationID = tDeklarationElleverantor.DeklarationID and tDeklarationElleverantor.Borttagen IS NULL ))
ELse (Select OrgNr from tDeklarationElleverantor Where DeklarationElleverantorID =
(SELECT top 1 DeklarationElleverantorID FROM tDeklarationElleverantor
WHERE tDeklaration.DeklarationID = tDeklarationElleverantor.DeklarationID and tDeklarationElleverantor.Borttagen IS NULL )) End) [Elleverantor 1 OrgNr],
(Select MWh from tDeklarationElleverantor Where DeklarationElleverantorID =
(SELECT top 1 DeklarationElleverantorID FROM tDeklarationElleverantor
WHERE tDeklaration.DeklarationID = tDeklarationElleverantor.DeklarationID and tDeklarationElleverantor.Borttagen IS NULL )) as [Elleverantor 1 MWh],

-- Here is the problem.......
select top 1 MWh from
(
select Top 2 Mwh, tDeklarationElleverantorID from tDeklarationElleverantor where tDeklarationElleverantor.DeklarationID = tDeklaration.DeklarationID
and tDeklarationElleverantor.Borttagen IS NULL order by DeklarationElleverantorID
) as [Elleverantor 2 MWh],


(select (select sum(tDeklarationElleverantor.MWh) from tDeklarationElleverantor where tDeklarationElleverantor.DeklarationID = tDeklaration.DeklarationID)
+ (select sum(tForbrukadEgen.EgenProduceradEl) from tForbrukadEgen where tForbrukadEgen.DeklarationID = tDeklaration.DeklarationID)
) as [Summa använd och vidare fakturerad],
ElTillverkning AS Tillverkningsprocess,
ElDel AS [Tillverkningsprocess del av år],
ForsaljningsVarde AS Försäljningsvärde,
(select
Case When ElTillverkning > 0 or Forsaljningsvarde > 0 Then ElTillverkning/Forsaljningsvarde
ELse 0 End) As Kvot,
(select
Case When ElTillverkning <= 0 or Forsaljningsvarde <= 0 Then 0
When ElTillverkning/Forsaljningsvarde < 40 then 0
When ElTillverkning/Forsaljningsvarde >= 40 and ElTillverkning/Forsaljningsvarde < 50 then ElTillverkning/Forsaljningsvarde * 0.5
When ElTillverkning/Forsaljningsvarde >= 50 and ElTillverkning/Forsaljningsvarde < 60 then ElTillverkning/Forsaljningsvarde * 0.75
ELse ElTillverkning/Forsaljningsvarde End) as [Undantagen elmängd],

(select
(select SUM(tHjalpkraft.Bruttoproduktion * tSchablonAr.Schablon)/100 from
tHjalpkraft INNER JOIN tSchablonAr ON tHjalpkraft.KraftslagID = tSchablonAr.KraftslagID
where tHjalpkraft.DeklarationID = tDeklaration.DeklarationID and tSchablonAr.Ar=2007)
+
(select SUM( tFaktiskHjalpkraft.NettoProduktion) from tFaktiskHjalpkraft where tDeklaration.DeklarationID = tFaktiskHjalpkraft.DeklarationID)) AS [Hjälpkraft],

--Summa avdrag för vidare fakturerad el (ny kolumn, summan under rubrik 5)


(select SUM(tFornybarFrom07.Fornybar) from tFornybarFrom07 where tFornybarFrom07.DeklarationID = tDeklaration.DeklarationID) AS [Förnybar],
tDeklaration.Kvotplikt,
tDeklaration.BegartAnnullerat as Annullering,
tDeklaration.Ovrigt as [Övriga Upplysningar],
tDeklaration.Kvotplikt - tDeklaration.Annullerat AS Saknas,
tDeklaration.DeklarationStatusID,
tDeklarationStatus.StatusIntern
FROM tDeklaration
INNER JOIN tDeklarationStatus on tDeklaration.DeklarationStatusID = tDeklarationStatus.DeklarationStatusID
INNER JOIN tRegistreradKvotpliktIntressent ON tDeklaration.RegistreradKvotpliktID = tRegistreradKvotpliktIntressent.RegistreradKvotpliktID
INNER JOIN tIntressentforadOrg ON tIntressentforadOrg.IntressentforadOrgID = tRegistreradKvotpliktIntressent.IntressentForadOrgID
LEFT OUTER JOIN intressentregister_skarp.dbo.tIntressentOrganisation IO ON IO.IntressentID = tIntressentforadOrg.ForadAgarID
--left outer join tDeklarationElleverantor on tDeklarationElleverantor.DeklarationID = tDeklaration.DeklarationID
WHERE (tDeklaration.AktuellIntern = 1) AND (tDeklaration.KategoriID = 3) AND (tDeklaration.DeklarationStatusID <> 1) AND
(tDeklaration.DeklarationStatusID <> 3)and tdeklaration.kvotpliktar=2007
order by IO.OrgNamn

View 4 Replies


ADVERTISEMENT

Select First Occurance Of A Row

Sep 20, 2004

Hey all:

I have a table that contains the following records:
ptrecidpaidbyamtchequenoname
4791A X A1200097760LOWE, Bernard
4791A X A4380097760LOWE, Bernard
4791A X A620106406LOWE, Bernard

I need to create a view that returns only the FIRST occurance of each distinct ptrecid. I do NOT want to sum amt/chequeno, but rather return only the values in the first record. Using a group by gets me close, but since the amt and chequeno are not unique, they do not group. Min also does not work, as 620 is returned for amt, and 097760 is returned for chequeno.

The desired result from this query would return:
4791A X A1200097760LOWE, Bernard

Any help on this is greatly appreciated.

View 6 Replies View Related

Select The First Occurance In A Column

Sep 18, 2001

I am trying to create a view that shows the first occurance of a particular value in a column.

For example, I have a table that contains multiple part numbers that can be associated with mulitple customers. The first occurance of the part number in the table is associated with the main customer for that part number. I want this view to only show the main customer for each part number.

I am able to do this in Access using the following SQL statement:
SELECT dbo_CustPattMast.PATTERN_NUM, First (dbo_CustPattMast.CUST_NUM)
FROM dbo_CustPattMast
GROUP BY dbo_CustPattMast.PATTERN_NUM

When trying to create the view in the SQL Manager, it will not allow me to use the First statement.

Thanks in advance for your help,
Jennifer

View 4 Replies View Related

Getting Only The First Occurance Of A Sql Select Statement

Sep 23, 2006

I am making a sql query and its bring back hundreds of results but I only need the first one. I am aware that getting the first one will be faster as well. Is there an efficient way to do this?

View 2 Replies View Related

Counting Multiple Occurance Sql

Feb 22, 2008

Declare @MYTable Table
(
RecordID int identity,
EmployeeID int,
JobCode varchar(4)
)
Insert into @MYTable Values(1,'123')
Insert into @MYTable Values(2,'123')
Insert into @MYTAble Values (1,'123')
Insert into @MyTable Values (2, '123')
Insert into @MyTable Values (3,'123')
Insert into @MYTable Values (1,'222')
Insert into @MYtable Values (3,'222')
Insert into @MYTable Values (1,'222')
Insert into @MyTable Values (4,'222')
Insert into @MyTable Values (5,'123')
Insert into @MyTable Values (6,'123')
Insert into @MyTable Values (7,'457')

Select * from @Mytable

The each employee can come to this table multiple time however
the job code should be different for each every single record.
So If I have to check this error and see which record has been repeated with
the same Employee ID and JobCode and how many times. how would i do that
Thanks,

View 15 Replies View Related

18th Occurance Of ; In A Field

Mar 20, 2008

Is there a simple command to find the 18th occurance/position of ";" in a field and I need to do this in the SQL code.

I can use charindex to find the 1st position and I can write a function to keep chopping off the data at the start of the field until I get to the 18th occurance but it takes 18 lines or so of code.

Is there a simpler way of doing this?

I need to find the 18th occurance of ";" and then take the next 4 characters.

View 4 Replies View Related

Compare Two Tables And Find The Occurance

May 17, 2008

Hi friends,

I have a two table with following fields, table names are tbl_userinfo, tbl_Property.

tbl_userinfo fields are

user_id name
1 dhin
2 Mike
3 sam
4 Red


tbl_Property fields are

prpty_Id User_Id Address
1 1 3CostalRoad
2 1 westbengal
3 2 Loasass

what i want to do is, if tbl_info User_id occures in tbl_property, i want to display that full info abt tbl_userinfo

after comparing two tables Expected result is
user_id name
1 dhin
2 Mike


Please help me how to do this

View 6 Replies View Related

How To Inform The Administrator About The Occurance Of The Record Entry In The Sql Databse?

Dec 14, 2000

Hello all
my problem is like this

I have a windows nt server 4.0 .its having IIS4.0 as well as Ms sql server 7.0 installed. I am using ASP ( active server pages) to store the fields entered by the customers in the order processing form in my site( thats the default page--form)

this form calls the asp file and asp file successfully stores the fields in the corresponding table.Now the problem is that how can i configure my sql server in such a way that sql server can mail me( as i am the administrator) informing the new order placed by the customer.

I know sql mail , and i have already configured that also. but how can i create the trigger for the table updation?

i tried using the enterprise manager, i was using xp_sendmail. but sql says "the stored procedure can't be found"


so anyone who knows this topic , respond please

Regards
Shabu

View 1 Replies View Related







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