please help me to cearte this stored procedure

Hi ,

I want to make a report of records of a table, there is abut 15 fields that this report based on them , so we need a select query like this

Select f1,f2from Table1where f3=@.f3and f4=@.f4 and ….and f17=@.f17

-- f1 = field1 and ….

The problem is sometimes @.fs are empty, for example if @.f4was empty so "and f4=@.f4" should be excluded from the select query .(and it means there is no limitation for f4 field)

I know, probably I couldn't explain my purpose very well, but I hope somebody kindly try to understand it .

how can I perform that in a stored procedure?

Please help me

Thank you

try it like this:

SELECT f1,f2FROM Table1WHERE (f3=@.f3OR @.f3ISNULL)AND (f4=@.f4OR @.f4ISNULL)AND ….AND (f17=@.f17OR @.f17ISNULL)

|||

Is in your example @.f4 empty or null? I think null, so I created the following query for you:

Select

f1from table1where f1= @.f1and(f4= @.f4or @.f4isnull)

This query selects all the records where f1 matches @.f1 and f4 matches @.f4 or @.f4 is null (and will be ignored then).

I hope this helps

Richard

|||

mbanavige & richardsoeteman.net thank you very, very much!.

|||

Hi

Assume there are 3 tables like these:

Table0

Primary key

Name

1

Name1

2

Name2

3

Name3

Table 1:

Foreign key

Column1

1

Data1

2

Data1

Table2:

Foreign key

Column2

2

Data2

3

Data2

And there are 2 parameters that they may be null: @.Data1 and @.Data2

I need a select query that

-selects "Name2" from Table0 if:

@.Data1="Data1"

@.Data2="Data2"

-selects "Name1, Name2" from Table0 if:

@.Data1="Data1"

@.Data2=null

-selects "Name2, Name3" from Table0 if:

@.Data1=null

@.Data2="Data2"

And selects "Name1, Name2, Name3" from Table0 if both of@.Data1 &@.Data2 wasnull.

I know I did not explain very well again but it's the final step of my project and I really need help. So please help me again

Thanks,

|||

Same concept:

Read this post:http://forums.asp.net/thread/1440706.aspx

|||

thank you Mike,

What about parameters that areint ordecimal and we want to ignore them , they can not benull ,they can be 0.

thank you Mike,

|||

You can use the same concept

Select

f1from table1where(f1= @.f1or @.f1=0)

|||Thank you Richard,

please help me fix this SQL query

SELECT * FROM

(SELECT row_number() OVER (ORDER BY custname) as resultNum,

t_enduser.id,substring(t_enduser.custcode,1,3), custname,city1,state1,

t_enduser.createdby,t_enduser.createdtime as ct, t_details.serialnumber,

t_details.package,startdate,endDate,endProduct

from t_enduser,t_master,t_details

where t_enduser.id=t_master.custcode

and t_master.t_ref=t_details.t_ref

and lOWER(package)<>'pdahardwareonly'

and lOWER(endproduct)<>'accessories' and status='valid'

and t_details.t_ref in

(SELECT rh.t_ref FROM t_details rh,

(SELECT max(endDate) as maxdate, serialNumber FROM t_details

where lOWER(package)<>'pdahardwareonly' and

lOWER(endproduct)<>'accessories' and status='valid'

GROUP BY serialNumber

)maxresults

WHERE rh.serialNumber = maxresults.serialNumber AND

rh.endDate= maxresults.maxdate

)

)as numberResults where resultNum '1' and '10'

It's difficult to say what's wrong seeing without seeing error messages or sample data.

One thing that stands out, however, is the WHERE clause:

where resultNum '1' and '10'

Should this be:

where resultNum IN('1', '10')

or if resultNum is of a numerical datatype:

where resultNum IN(1, 10)

?

Chris

|||

The first line says

Select * from

but there is nothing in the from clause.

There is a lot more wrong with this query.

|||

The query will be perfectly valid once the final WHERE clause has been sorted out.

Chris

|||

Firstly, I hope the code is better formatted on your end. I know it is pretty ugly to past directly into the forums, so consider pasting to text first. I would consider reformatting for our value if you post such amounts of code as youi have a very messy query. I have highlighted a few things that seem troublesome, but nothing overly horrible.

You are going to have to give more information...

SELECT *
--alias all names. We can't help you if you won't give all information.
FROM (SELECT row_number() OVER (ORDER BY custname) as resultNum,
t_enduser.id,
substring(t_enduser.custcode, 1, 3),
custname, city1, state1, t_enduser.createdby,
t_enduser.createdtime as ct,
t_details.serialnumber, t_details.package,
startdate, endDate, endProduct
from t_enduser
,t_master --is this table_master, or does t_ have other meaning. Master is a weak name
--master, what?
,t_details
--learn about the JOIN criteria instead of using the comma based cartesion product style join
--syntax, it is easier to follow, and certainly to debug
where t_enduser.id = t_master.custcode --Customers are also endusers? This seems odd
and t_master.t_ref = t_details.t_ref --t_ref? Is this the pkey of one of your tables? Or all?
--Why lower? Is your database case sensitive? If so, then use a constraint to make sure
--package and products forllow the rules.
and LOWER(package) <> 'pdahardwareonly'
and LOWER(endproduct) <> 'accessories'
and status = 'valid'
and t_details.t_ref in (
SELECT rh.t_ref
FROM t_details rh
,(SELECT max(endDate) as maxdate,
serialNumber
FROM t_details
WHERE LOWER(package) <> 'pdahardwareonly'
and LOWER(endproduct) <> 'accessories'
and status = 'valid'
GROUP BY serialNumber) maxresults
WHERE rh.serialNumber = maxresults.serialNumber
AND rh.endDate = maxresults.maxdate))
as numberResults
where resultNum between 1 and 10 --this is the code that needed fixed

--also you can redo the in clause using the rowNumber function to make it a little easier...
select getRows.tref
from (SELECT rh.t_ref, row_number() over (partition by serialNumber order by endDate) as rowNum
FROM t_details
WHERE LOWER(package) <> 'pdahardwareonly'
and LOWER(endproduct) <> 'accessories'
and status = 'valid') as getRows
where getRows.rowNum = 1

|||

Louis thanks for you tips. As what you suggested “between” is the problem. Basically
I want to combine three tables and those three tables cross-reference each other.
My task requirement in such I need to pull the transactions in one
stretch customer details and his last product purchased that is by expiry date.

1. Find the product and its expiry date (maximum end date is the exp date)
2. Get the customer details for that reference
3. Pagination
4. Get row number for each records

Customer Details TABLE NAMES (T_ENDUSER)
Transaction TABLE NAMES (T_MASTER,T_DETAILS)

After I made the correction, The following query can work for me. But as what you suggested my join criteria,
I feel like dump. Can u please tune up for me?

SELECT * FROM (
SELECT row_number() OVER (ORDER BY custname) as RESULTNUM,
T_ENDUSER.id,T_MASTER.t_ref
from T_ENDUSER,T_MASTER,T_DETAILS
where T_ENDUSER.id=T_MASTER.custcode
and T_MASTER.t_ref=T_DETAILS.t_ref
and lOWER(package)<>'pdahardwareonly'
and lOWER(endproduct)<>'accessories'
and status='valid'
and T_DETAILS.t_ref in
(SELECT rh.t_ref FROM T_DETAILS rh,
(SELECT max(endDate) as maxdate, serialNumber FROM T_DETAILS where lOWER(package)<>'pdahardwareonly'
and lOWER(endproduct)<>'accessories'
and status='valid' GROUP BY serialNumber
) as EXPDATE
WHERE rh.serialNumber = EXPDATE.serialNumber
AND rh.endDate= EXPDATE.maxdate)
)as NUMRESULTS where RESULTNUM between '1' and '20'

|||

I was just suggesting that you use the new style joins instead:

SELECT *
FROM (SELECT row_number() OVER (ORDER BY custname) as RESULTNUM,
T_ENDUSER.id, T_MASTER.t_ref
from T_ENDUSER
JOIN T_MASTER
on T_ENDUSER.id = T_MASTER.custcode
JOIN T_DETAILS
on T_MASTER.t_ref = T_DETAILS.t_ref
where lOWER(package) <> 'pdahardwareonly'
and lOWER(endproduct) <> 'accessories'
and status = 'valid'
and T_DETAILS.t_ref in (
SELECT rh.t_ref
FROM T_DETAILS rh
join (SELECT max(endDate) as maxdate,
serialNumber
FROM T_DETAILS
where lOWER(package) <> 'pdahardwareonly'
and lOWER(endproduct) <> 'accessories'
and status = 'valid'
GROUP BY serialNumber) as EXPDATE
ON rh.serialNumber = EXPDATE.serialNumber
AND rh.endDate = EXPDATE.maxdate))
as NUMRESULTS
where RESULTNUM between 1 and 20 --Also, these should be numbers, not characters

The version you did will work just fine, but this is a lot easier to follow, and is far better for outer joins...

Please help deciphering error message

I just changed my dataset syntaxes from the typical
SELECT col FROM table WHERE col3 = @.value
to
="SELECT col FROM table WHERE col3 = '" & Parameters!Code.Value & "'
For some reason I'm getting the following pop-up:
--
Processing Errors
--
An error has occurred during report processing.
Cannot set the command text for data set 'ds_Legal_Entity'.
Error during processing of the CommandText expression of dataset
â'ds_Legal_Entityâ'.
--
OK
--
I am not familiar with the CommandText syntax to understand where the error
might be. My query is below, could someone tell me what the problem might be?
Thanks!
Mike
="SELECT DISTINCT dbo.t_d_legal.legal_desc
FROM dbo.t_d_legal
INNER JOIN dbo.t_pms ON dbo.t_d_legal.legal_key = dbo.t_pms.legal_key
WHERE (dbo.t_pms.mth_key = " & Parameters!mth_key.Value & ") " &
IIF(Parameters!BusKey.Value = 0,"","
AND
(dbo.t_pms.bus_key = ") & Parameters!BusKey.Value & ")" &
" ORDER BY dbo.t_d_legal.legal_desc"think you had extra single quote:
="SELECT col FROM table WHERE col3 = " & Parameters!Code.Value & " rest of
code"
otherwise if you need quotes due to your parm value being character you'll
need to double up on the quotes. read BOL.
"Bassist695" wrote:
> I just changed my dataset syntaxes from the typical
> SELECT col FROM table WHERE col3 = @.value
> to
> ="SELECT col FROM table WHERE col3 = '" & Parameters!Code.Value & "'
> For some reason I'm getting the following pop-up:
> --
> Processing Errors
> --
> An error has occurred during report processing.
> Cannot set the command text for data set 'ds_Legal_Entity'.
> Error during processing of the CommandText expression of dataset
> â'ds_Legal_Entityâ'.
> --
> OK
> --
> I am not familiar with the CommandText syntax to understand where the error
> might be. My query is below, could someone tell me what the problem might be?
> Thanks!
> Mike
> ="SELECT DISTINCT dbo.t_d_legal.legal_desc
> FROM dbo.t_d_legal
> INNER JOIN dbo.t_pms ON dbo.t_d_legal.legal_key = dbo.t_pms.legal_key
> WHERE (dbo.t_pms.mth_key = " & Parameters!mth_key.Value & ") " &
> IIF(Parameters!BusKey.Value = 0,"","
> AND
> (dbo.t_pms.bus_key = ") & Parameters!BusKey.Value & ")" &
> " ORDER BY dbo.t_d_legal.legal_desc"

Please help - setting order of select in a self-referencing table

Hello,
I have a table of product categories that looks like this (air code, so
could be some typos, but it's basically right)...
create table producttypes (
ptype int not null identity(1,1) primary key,
typename varchar(50) not null default '',
ParentCat int references producttypes(ptype)
)
This allows categories to contain subcategories and so on.
In order to allow me to see the hierarchy, I am using the following
code, modified from some found in "Inside SQL Server 7.0" by Kalen
Delaney.
-- SQL starts
declare @.level int, @.current int
create table #stack (depthlevel int, ptype int)
create table #orgchart (seqno int identity, orglevel int not null, ptype int
not null)
set rowcount 1
select @.level=1, @.current=ptype from producttypes where ptype=parentcat
set rowcount 0
insert into #stack (depthlevel, ptype) values (@.level, @.current)
while (@.level>0)
begin
if exists (select * from #stack where depthlevel=@.level)
begin
set rowcount 1
select @.current=ptype from #stack where depthlevel=@.level
set rowcount 0
insert into #orgchart (orglevel, ptype) select @.level, @.current
delete from #stack where depthlevel=@.level and ptype=@.current
insert into #stack select @.level+1, ptype from producttypes where parentcat=
@.current and parentcat<>ptype
if @.@.ROWCOUNT > 0 select @.level=@.level+1
end
else
select @.level=@.level-1
end
select o.orglevel, e.ptype, e.parentcat, e.typename from #orgchart as o
join producttypes as e on e.ptype=o.ptype order by o.seqno
drop table #stack, #orgchart
-- SQL ends
This produces a set of results that gives me all the categories. I can
use the orglevel field to tell what level I'm at, so if it changes, I
can see if I've gone up or down the hierarchy.
Now, the problem is that I can't work out how to set the order of
categories. For example, if the above SQL produces...
1 16 16 Products
2 17 16 Outdoor Toys
3 1 17 Trampolines
3 2 17 Slides
3 3 17 Swings
3 4 17 Accessories
3 5 17 Climbing Frames
3 7 17 Bicycles
you can see that the categories on level 3 are not in any obvious order.
I would like to have them in alphabetical order.
Any ideas how I would modify the above SQL to do this? TIA
Alan Silver
(anything added below this line is nothing to do with me)Try
select o.orglevel, e.ptype, e.parentcat, e.typename from #orgchart as o
join producttypes as e on e.ptype=o.ptype order by o.seqno, e.typename
The results should be sorted first by the orglevel, and results within the
same orglevel will be sorted alphabetically. The syntax reads like "order
results first by o.seqno, then by e.typename"
"Alan Silver" wrote:

> Hello,
> I have a table of product categories that looks like this (air code, so
> could be some typos, but it's basically right)...
> create table producttypes (
> ptype int not null identity(1,1) primary key,
> typename varchar(50) not null default '',
> ParentCat int references producttypes(ptype)
> )
> This allows categories to contain subcategories and so on.
> In order to allow me to see the hierarchy, I am using the following
> code, modified from some found in "Inside SQL Server 7.0" by Kalen
> Delaney.
> -- SQL starts
> declare @.level int, @.current int
> create table #stack (depthlevel int, ptype int)
> create table #orgchart (seqno int identity, orglevel int not null, ptype i
nt not null)
> set rowcount 1
> select @.level=1, @.current=ptype from producttypes where ptype=parentcat
> set rowcount 0
> insert into #stack (depthlevel, ptype) values (@.level, @.current)
> while (@.level>0)
> begin
> if exists (select * from #stack where depthlevel=@.level)
> begin
> set rowcount 1
> select @.current=ptype from #stack where depthlevel=@.level
> set rowcount 0
> insert into #orgchart (orglevel, ptype) select @.level, @.current
> delete from #stack where depthlevel=@.level and ptype=@.current
> insert into #stack select @.level+1, ptype from producttypes where par
entcat=@.current and parentcat<>ptype
> if @.@.ROWCOUNT > 0 select @.level=@.level+1
> end
> else
> select @.level=@.level-1
> end
> select o.orglevel, e.ptype, e.parentcat, e.typename from #orgchart as o
> join producttypes as e on e.ptype=o.ptype order by o.seqno
> drop table #stack, #orgchart
> -- SQL ends
>
> This produces a set of results that gives me all the categories. I can
> use the orglevel field to tell what level I'm at, so if it changes, I
> can see if I've gone up or down the hierarchy.
> Now, the problem is that I can't work out how to set the order of
> categories. For example, if the above SQL produces...
> 1 16 16 Products
> 2 17 16 Outdoor Toys
> 3 1 17 Trampolines
> 3 2 17 Slides
> 3 3 17 Swings
> 3 4 17 Accessories
> 3 5 17 Climbing Frames
> 3 7 17 Bicycles
> you can see that the categories on level 3 are not in any obvious order.
> I would like to have them in alphabetical order.
> Any ideas how I would modify the above SQL to do this? TIA
> --
> Alan Silver
> (anything added below this line is nothing to do with me)
>|||>Try
>select o.orglevel, e.ptype, e.parentcat, e.typename from #orgchart as o
>join producttypes as e on e.ptype=o.ptype order by o.seqno, e.typename
>The results should be sorted first by the orglevel, and results within the
>same orglevel will be sorted alphabetically. The syntax reads like "order
>results first by o.seqno, then by e.typename"
Mark,
Thanks for the reply, but if you look carefully, it's not that simple.
Your suggestion will list all level 2 categories together, followed by
all level 3 and so on. This will not give the correct hierarchy as there
may be several level 2 categories each with subcategories (ie level 3).
If this isn't clear, look at the longer category listing shown later on.
This is a more complete list than the abbreviated one I showed before.
Note that the ordering is done on seqno, which is the order in which the
categories were put into the orgchart table. The ordering is not done on
the level at all.
I think I need to modify the way the categories are pulled out of the
producttypes table when they are inserted into the stack table. Trouble
is, I can't see how to control the ordering as they are being pulled one
at a time.
Thanks for the reply. Any further help would be appreciated.
Longer category listing follows...
level typename
1 Products
2 Outdoor Toys
3 Trampolines
3 Slides
3 Swings
3 Accessories
3 Climbing Frames
3 Bicycles
2 Indoor Toys
3 Snooker Tables
3 Boy's Toys
4 Hard Puzzles
3 Girl's Toys
4 Easy Puzzles
3 Board Games
3 Pets
4 Ferrets
4 Dogs
4 Cats
>"Alan Silver" wrote:
>
Alan Silver
(anything added below this line is nothing to do with me)sql

Please explain this SQL Trigger

I am new to triggers.
I am unsure why on the SELECT statement the ROLLBACK is enforced. I thought this would be only for when an attempt is made to insert the data. Also what is the pupose of IF @.counter = 1
When does this counter change value?

USE Northwind
GO
CREATE TRIGGER PriceCheck
ON [Order Details]
FOR INSERT
AS
DECLARE @.counter int
SET @.counter= @.@.ROWCOUNT
DECLARE @.unitprice money
DECLARE order_details_insert_cursor CURSOR FOR
SELECT Unitprice
FROM inserted
IF @.counter = 1
BEGIN
IF(SELECT UnitPrice FROM inserted) > 300
-- If the price entered is greater than 300
BEGIN
--print a warning
PRINT 'Cannot enter price greater than 300'
--Take back the command
ROLLBACK TRANSACTION
END
END
ELSE
BEGIN
OPEN order_details_insert_cursor
FETCH NEXT FROM order_details_insert_cursor INTO @.unitprice
WHILE @.@.FETCH_STATUS = 0
BEGIN
if @.unitprice > 300
-- If the price entered is greater than 300
BEGIN
--print a warning
PRINT 'Cannot enter price greater than 300'
--Take back the command
ROLLBACK TRANSACTION
RETURN
--Exit trigger immediately
END

FETCH NEXT FROM order_details_insert_cursor INTO @.unitprice
END

CLOSE order_details_insert_cursor
END

DEALLOCATE order_details_insert_cursor
The select statement checks to see what the inserted value is, and ifit's greater than 300, its rolling back the attempted insert as beingoutside of the range of allowed values. As an aside, this isn'treally a good use of a trigger. A check constraint would be justas good, and would perform better.
The counter is set to the number of rows being inserted, so it'll beequal to the number of rows in the set being inserted into thetable. Theoretically, it could have a different value for everyattempted insert.

|||Thanks - I am trying to follow the logic of it and wondered if it works from top to bottom how does it come back to evaluate the counter? I can't see a looping structure in there.
Also could you explain what is meant by 'inserted' as in SELECT Unitprice
FROM inserted
Is 'inserted' a reserved word. Usually I would expect to see a table name there.|||The counter is set when the trigger is run. It isn't changedduring the execution of the of the trigger, so the value that isinitally set is the only value that it'll have until the next time thatthe trigger is fired.
"inserted" is a table, as you would expect. "inserted" and"deleted" are the names of two special internal tables that SQL Serveruses specifically to expose data being changed by INSERT, UPDATE, andDELETE statements to triggers.

please check this not null SQL String

the SQL string below worked, and then started bringing up every record.
it should only select records with a value in at least one of the columns, but it apears to be suggesting that all records have some data in one of the columns. if I check the database or the output on the web page there apears to be no data. ?? confused.

"SELECT id, make, model FROM vehicles WHERE workToBeDone1 IS NOT NULL OR workToBeDone2 IS NOT NULL OR workToBeDone3 IS NOT NULL OR workToBeDone4 IS NOT NULL OR workToBeDone5 IS NOT NULL"

Any ideas how I could implement this more robustly?
cheers
MSorry, doesn't work that way.

You need a condition for each column|||Cheat. Execute:
"SELECT id, make, model
, CAST(workToBeDone1 AS VARBINARY(10)) AS w1
, CAST(workToBeDone2 AS VARBINARY(10)) AS w2
, CAST(workToBeDone3 AS VARBINARY(10)) AS w3
, CAST(workToBeDone4 AS VARBINARY(10)) AS w4
, CAST(workToBeDone5 AS VARBINARY(10)) AS w5
FROM vehicles
WHERE workToBeDone1 IS NOT NULL
OR workToBeDone2 IS NOT NULL
OR workToBeDone3 IS NOT NULL
OR workToBeDone4 IS NOT NULL
OR workToBeDone5 IS NOT NULL"If the Cast() columns do not ALL show NULL as their value, then you have data in the offending column(s). Empty strings, and sometimes even the constant "NULL" have been known to sneak into tables when you do not expect them!

-PatP|||thanks guys.
I'm sure my version was working fine until the database seemed to put something invisible into the columns.
I tried your code Pat but it returns "ADODB.Recordset error '800a0cc1'
Item cannot be found in the collection corresponding to the requested name or ordinal."

What does the 'as w1' part do?

my code looks like this:
"SELECT id, make, model, CAST(workToBeDone1 AS VARBINARY(10)) AS w1, CAST(workToBeDone2 AS VARBINARY(10)) AS w2, CAST(workToBeDone3 AS VARBINARY(10)) AS w3, CAST(workToBeDone4 AS VARBINARY(10)) AS w4, CAST(workToBeDone5 AS VARBINARY(10)) AS w5 FROM vehicles WHERE workToBeDone1 IS NOT NULL OR workToBeDone2 IS NOT NULL OR workToBeDone3 IS NOT NULL OR workToBeDone4 IS NOT NULL OR workToBeDone5 IS NOT NULL"|||Drop the quotes from around the SQL statement for starters ;)

For the 'As w1' try running this

SELECT id As 'Example'
FROM vehicles|||thanks georgev
sorry, I missed a crucial bit re the quotes: SQLstring="Select..."
I'll have a play with your example and see if I get it.|||nope, sorry, couldn't figure out what I am supposed to do with your example George.|||Run the thing in QA and see if you notice something.
Basically it's giving the column an alias http://doc.ddart.net/mssql/sql70/sa-ses_3.htm - scroll down to columns_alias :p|||can't use QA on this, I have to run scripts on pages on the server.
Not sure why I need aliases.
My database columns seem to contain invisible data, is there a way to discover if the columns have any meaningful data in them? NULL seems to be a bit flakey

I need to find cars that need work done - i.e. someone has inputted something like: 'replace tyres' in one of the workToBeDone fields for a Volvo. but my search is returning every car in the database because it is seeing something in the columns. (I think!).

I tried casting as varchar(255) - made no difference|||The "as W1" simply assigns an alias to the column as GeorgeV observed. It appears that your ADO implementation doesn't like the aliases.

If Query Anylyzer (or its equivalent) is available, then I'd use it instead of writing/changing code to support your ADO implementation. Operative word being "should", you should be able to simply drop the column names and move on without them.

-PatP|||And by drop the column names we don't mean physically dropping the columns... Just remove the "As ..." from your SQL statement.

The reason the aliases were applied in the first place because as soon as you perform any function on a column it loses the reference to the column name (because it's not the same as the column data any more!). The Aliases allow us to access the columns by referenec in ADO (or so I believe).|||I dropped the aliases, but it made no difference, I'm still getting:
'Item cannot be found in the collection corresponding to the requested name or ordinal',|||Ok, let's try to solve the problem from a different vector and execute:"SELECT id, make, model
, CASE WHEN workToBeDone1 IS NULL THEN 0 WHEN 0 = Len(workToBeDone1) THEN 1 ELSE 2 END
, CASE WHEN workToBeDone2 IS NULL THEN 0 WHEN 0 = Len(workToBeDone2) THEN 1 ELSE 2 END
, CASE WHEN workToBeDone3 IS NULL THEN 0 WHEN 0 = Len(workToBeDone3) THEN 1 ELSE 2 END
, CASE WHEN workToBeDone4 IS NULL THEN 0 WHEN 0 = Len(workToBeDone4) THEN 1 ELSE 2 END
, CASE WHEN workToBeDone5 IS NULL THEN 0 WHEN 0 = Len(workToBeDone5) THEN 1 ELSE 2 END
FROM vehicles
WHERE workToBeDone1 IS NOT NULL
OR workToBeDone2 IS NOT NULL
OR workToBeDone3 IS NOT NULL
OR workToBeDone4 IS NOT NULL
OR workToBeDone5 IS NOT NULL"-PatP|||Thanks Pat,
still getting the same error. here's more of the code (inc. your bit) to give you a bigger picture:

Set linkRS = Server.CreateObject("ADODB.Recordset")

salePrice = request.Form("salePrice")
make=request.Form("make")
model2show=request.Form("model2show")
salePrice=request.Form("salePrice")
fuel=request.Form("fuel")
sold=request.Form("sold")
workOutstanding=request.Form("workOutstanding")
notOnWebsite=request.Form("notOnWebsite")

strSQL="SELECT id, make, model, model2show, registration, price FROM vehicles WHERE price BETWEEN "& salePrice &""
if make <> "" then strSQL = strSQL & " AND make = '" & make & "'"

if fuel <> "" then strSQL = strSQL & " AND fuel = '" & fuel & "'"

if model2show <> "" then strSQL = strSQL & " AND model2show = '" & model2show & "'"

if sold = "yes" then strSQL = strSQL & " AND sold = 'yes'"

if workOutstanding = "yes" then strSQL = "SELECT id, make, model, CASE WHEN workToBeDone1 IS NULL THEN 0 WHEN 0 = Len(workToBeDone1) THEN 1 ELSE 2 END, CASE WHEN workToBeDone2 IS NULL THEN 0 WHEN 0 = Len(workToBeDone2) THEN 1 ELSE 2 END, CASE WHEN workToBeDone3 IS NULL THEN 0 WHEN 0 = Len(workToBeDone3) THEN 1 ELSE 2 END, CASE WHEN workToBeDone4 IS NULL THEN 0 WHEN 0 = Len(workToBeDone4) THEN 1 ELSE 2 END, CASE WHEN workToBeDone5 IS NULL THEN 0 WHEN 0 = Len(workToBeDone5) THEN 1 ELSE 2 END FROM vehicles WHERE workToBeDone1 IS NOT NULL OR workToBeDone2 IS NOT NULL OR workToBeDone3 IS NOT NULL OR workToBeDone4 IS NOT NULL OR workToBeDone5 IS NOT NULL"

if notOnWebsite = "yes" then strSQL = strSQL & " AND active = 'no'"

strSQL = strSQL & " ORDER BY make"
'response.Write(strSQL)
linkRS.Open strSQL, oConn, 2, 3
if (linkRS.BOF and linkRS.EOF) then
response.Write("<p class=""inputRed"">No vehicles to display - try selecting fewer parameters</p>")
else
linkRS.moveFirst
Do while not linkRS.eof
make = linkRS("make")
'etc.
'etc.

most of this works fine, but the error message is odd because those fields do exist.|||Uncomment your 'response.Write(strSQL) and post the result.
First glance suggests you have a problem with your BETWEEN statement|||here you go:
SELECT id, make, model, CASE WHEN workToBeDone1 IS NULL THEN 0 WHEN 0 = Len(workToBeDone1) THEN 1 ELSE 2 END, CASE WHEN workToBeDone2 IS NULL THEN 0 WHEN 0 = Len(workToBeDone2) THEN 1 ELSE 2 END, CASE WHEN workToBeDone3 IS NULL THEN 0 WHEN 0 = Len(workToBeDone3) THEN 1 ELSE 2 END, CASE WHEN workToBeDone4 IS NULL THEN 0 WHEN 0 = Len(workToBeDone4) THEN 1 ELSE 2 END, CASE WHEN workToBeDone5 IS NULL THEN 0 WHEN 0 = Len(workToBeDone5) THEN 1 ELSE 2 END FROM vehicles WHERE workToBeDone1 IS NOT NULL OR workToBeDone2 IS NOT NULL OR workToBeDone3 IS NOT NULL OR workToBeDone4 IS NOT NULL OR workToBeDone5 IS NOT NULL ORDER BY make|||Maybe it contain spaces, try this
where coalesce(workToBeDone1,workToBeDone2,workToBeDone3 ,workToBeDone4,workToBeDone5,'') != ''|||thanks,
same error msg tho'

Please Check my store Procedure !

I have a store Procedure modify structTable but check syntax is error !

Please help me ?
--
IF EXISTS(SELECT NAME FROM SYSOBJECTS WHERE NAME ='MODISTRCTTABLE ' AND TYPE='P')
BEGIN
DROP PROCEDURE MODISTRUCT_TABLE
END
GO

CREATE PROCEDURE MODISTRUCT_TABLE
@.TABLE_NAME VARCHAR(60),
@.COLUMN_NAME VARCHAR(60),
@.COLUMN_TYPE VARCHAR(60),
@.COLUMN_SIZE INT(10)
AS
BEGIN
IF EXISTS (SELECT @.COLUMN_NAME FROM syscolumns)
BEGIN
ALTER TABLE @.TABLE_NAME ALTER @.COLUMN_NAME + ' ' + @.COLUMN_TYPE+'('+ @.COLUMN_SIZE +')'
END
ELSE
BEGIN
ALTER TABLE @.TABLE_NAME ADD @.COLUMN_NAME + ' ' + @.COLUMN_TYPE+'('+ @.COLUMN_SIZE +')'
END
END

-

CREATE PROCEDURE ENCRYPTION getString()

AS
SELECT * FORM CUSTOMER

-

Funtion getString() return a string is " With Encryption"?

Do I call function getString() in here ?

You need to use dynamic sql for this.

Code Snippet

IF EXISTS(SELECT NAME FROM SYSOBJECTS WHERE NAME ='MODISTRCTTABLE ' AND TYPE='P')
BEGIN
DROP PROCEDURE MODISTRUCT_TABLE
END
GO

CREATE PROCEDURE MODISTRUCT_TABLE
@.TABLE_NAME VARCHAR(60),
@.COLUMN_NAME VARCHAR(60),
@.COLUMN_TYPE VARCHAR(60),
@.COLUMN_SIZE INT(10)
AS
BEGIN
IF EXISTS (SELECT @.COLUMN_NAME FROM syscolumns)
BEGIN
exec ('ALTER TABLE '+@.TABLE_NAME+' ALTER '+@.COLUMN_NAME + ' ' + @.COLUMN_TYPE+'('+ @.COLUMN_SIZE +')' )
END
ELSE
BEGIN
exec('ALTER TABLE '+@.TABLE_NAME+' ADD '+@.COLUMN_NAME + ' ' + @.COLUMN_TYPE+'('+ @.COLUMN_SIZE +')' )
END
END

The other proc is also incorrect. It should be like so.

Code Snippet

CREATE PROCEDURE getString()
with ENCRYPTION
AS
SELECT * FROM CUSTOMER

|||Thanks Oj my idea is not clearly ! I have a Store procedure :
--
Create procedure MyEnCryption With Encryption
AS
Begin
Select * from Customer
End

I want replace string " With Encryption" with calling Function GetString(); Function GetString() return a string is "With Encryption"

this store rewrite :
-
Create procedure MyEnCryption GetString()
AS
Begin
Select * from Customer
End

--

Is this store run ?|||

What you're trying to do is not possible. The syntax to create a procedure is like so:

CREATE PROC [ EDURE ] procedure_name [ ; number ]
[ { @.parameter data_type }
[ VARYING ] [ = default ] [ OUTPUT ]
] [ ,...n ]

[ WITH
{ RECOMPILE | ENCRYPTION | RECOMPILE , ENCRYPTION } ]

[ FOR REPLICATION ]

AS sql_statement [ ...n ]

|||

While creating the objects you have to use the proper syntax what is already defined..

You can achive your desired output if you create your object using dynamic SQL...

But it is not recommanded to do this.. & I didn’t understand why you need this logic..

Note:

When you encrypt the procedure/function/view/trigger you have to store your soruce code on filesystem or source safe for future reference, you never get back the soruce text from the SQL Server. So, it is really bad idea to have dynamic sql to create encrypted objects.

Code Snippet

Declare @.sql as varchar(8000)

Set @.sql = 'Create procedure MyEnCryption ' +GetString() + '

AS

Begin

Select * from Customer

End'

exec (@.sql)

|||

Thanks Admin !

Now I'am understading !

Please Assist... Report Templates to save time in designing

In RS 2000 I was able to create a template report and put it in the Project
Items directory, then when I would select New Report Item, I could choose my
template report instead of redesigning everything from scratch.
Where is this folder, or to better phrase this question:
Where do I put my template reports that I want to show up when I choose "Add
new report item" in the designer in SQL 2005 RS?
I have checked the folders - but obviously I am missing something.
Thanks in advance!
=-ChrisI had to search through my computer for *.rdl files. Finally I think I found
the templates at
C:\Program Files\Microsoft Visual Studio
8\Common7\IDE\PrivateAssemblies\ProjectItems\ReportProject
Kaisa M. Lindahl Lervik
"Chris Conner" <Chris.Conner@.NOSPAMPolarisLibrary.com> wrote in message
news:uOtrsue%23GHA.1220@.TK2MSFTNGP05.phx.gbl...
> In RS 2000 I was able to create a template report and put it in the
> Project
> Items directory, then when I would select New Report Item, I could choose
> my
> template report instead of redesigning everything from scratch.
> Where is this folder, or to better phrase this question:
> Where do I put my template reports that I want to show up when I choose
> "Add
> new report item" in the designer in SQL 2005 RS?
> I have checked the folders - but obviously I am missing something.
> Thanks in advance!
> =-Chris
>

Please assist with a query...

I have a table that can contain several entries for a given item id.
How would my select statement look if I wanted to query for:
- only the 'last' entry for 'each' id that meets certain criteria
I don't want a TOP x query, because I want the dataset to contain all
items whose 'last' entry meets certain criteria.Can you post your DLL? What is/are the key(s)? How can you tell which record
is last one?
"indee" <jeffderoche@.gmail.com> wrote in message
news:1125423719.220718.119790@.g47g2000cwa.googlegroups.com...
>I have a table that can contain several entries for a given item id.
> How would my select statement look if I wanted to query for:
> - only the 'last' entry for 'each' id that meets certain criteria
> I don't want a TOP x query, because I want the dataset to contain all
> items whose 'last' entry meets certain criteria.
>|||What criteria can we use to spot the last row for each group?
Example:
use northwind
go
-- last orders for each customer with an order
select
customerid, orderid
from
dbo.orders as a
where
orderdate = (select max(orderdate) from dbo.orders as b where
b.customerid = a.customerid)
go
AMB
"indee" wrote:

> I have a table that can contain several entries for a given item id.
> How would my select statement look if I wanted to query for:
> - only the 'last' entry for 'each' id that meets certain criteria
> I don't want a TOP x query, because I want the dataset to contain all
> items whose 'last' entry meets certain criteria.
>|||The primary key is an autonumber, however, there is an itemid field.
So, for example, here's what I want in plain english:
For each distinct item id, show me all whose last one entry (per the
autonumber) has status <> 1
So, I want the query to return all item ids whose last entry does not
have the status field set to 1.|||Can you try this (assume your autonumber is an Identity column):
select (whatever columns you have)
from YourTable T1
where T1.autonumber = (select max(T2.autonumber)
from YourTable T2
where T2.itemid = T1.itemid)
and T1.Status <> 1
"indee" <jeffderoche@.gmail.com> wrote in message
news:1125429028.896197.68730@.g14g2000cwa.googlegroups.com...
> The primary key is an autonumber, however, there is an itemid field.
> So, for example, here's what I want in plain english:
> For each distinct item id, show me all whose last one entry (per the
> autonumber) has status <> 1
> So, I want the query to return all item ids whose last entry does not
> have the status field set to 1.
>|||Try,
select
*
from
t1 as a
where
status != 1
and autonumber = (select max(b.autonumber) from t1 as b where b.itemid =
a.itemid)
AMB
"indee" wrote:

> The primary key is an autonumber, however, there is an itemid field.
> So, for example, here's what I want in plain english:
> For each distinct item id, show me all whose last one entry (per the
> autonumber) has status <> 1
> So, I want the query to return all item ids whose last entry does not
> have the status field set to 1.
>|||We're almost there, however, the query can return only the itemid
field, as it is a subquery...I really appreciate your help...
Here is the full query:
SELECT ID FROM vwAssets WHERE ID NOT IN(SELECT AssetID FROM
vwAssetValuation WHERE ActionEID IN (211, 212, 215) AND Stamp <= '" &
mCommon.FormatSQLDate(AsOf) & "') AND AssetID NOT IN(** QUERY GOES HERE
**)|||Let's get back to the basics of an RDBMS. Rows are not records; fields
are not columns; tables are not files; there is no sequential access or
ordering in an RDBMS, so "first", "next" and "last" are totally
meaningless.
THIS IS THE BASICS!! Have you ever bothered to do ANY studying
first?|||>> The primary key is an autonumber <<
Again, an autonumber cannot be a relationalo key BY DEFINITION. And
you still do not know that a field and columns are TOTALLY different
concepts.|||I see that you also use prefixes and data element names that violate
ISO-11179 data element rules. Does "vw-" mean Volkswagen?
Please stop programming until you learn the foundations. You are
dangerously ingnorant. Also you are going to get in Newsgroup is
kludges that will let you limp along until you have a disaster. Please
post DDL, so that people do not have to guess what the keys,
constraints, Declarative Referential Integrity, data types, etc. in
your schema are. Sample data is also a good idea, along with clear
specifications.

Please Advice !

Hi,

I am trying to solve this procedure.

Let me try to explain it...I am getting DEGREEID from one of the SELECT query . I want to OUTPUT (ie , COUNT) from procedure,the number of departments with the degreeid, got from the above query.

With below procedure, Since an employee can have multiple DEGREEID , the cursor is giving OUPTUT ie, COUNT for the LAST Degreeid. Eventhough the previous DEGREEID dont have any DEPARTMENT...but only for the LAST DEGREEID...!

How can I solve this.... whether I can solve this with CURSOR or I have to use someother way...Please advice me !

DATA
---
DEGREE_EARNED
-------
EMPID DEGREEID
-- ----
201 12
201 3
201 250
202 3
202 10
203 17

DEPARTMENT
-----
DEPID DEGREEID
-- ----
10 1 12
111 250
111 12
121 3
121 12
121 250
-----------



---------------------
DECLARE @.vchid int
DECLARE testcursor CURSOR FOR

SELECT degree_id
FROM degree_earned WHERE emp_id= @.empid

OPEN testcursor
FETCH NEXT FROM testcursor INTO @.vchid

WHILE (@.@.FETCH_STATUS <> -1)

BEGIN
Select @.outresult = COUNT(*)
from
department
where degree_id = @.vchid

FETCH NEXT FROM testcursor INTO @.vchid
END
---------------------Can somebody help me,please (:|||So is what you want something like:EMPID DEGREEID DEPT_COUNT
-- --- ----
201 12 2
201 3 1
201 250 2
202 3 1
202 10 0
203 17 0I don't understand exactly what you want, so I can't be much help until I do. Sorry!

-PatP|||Hey Pat,

Thats what i wanted...Can u please show me , how to solve this...Thanks!|||USE Northwind
GO

CREATE TABLE DEGREE_EARNED(EMPID int, DEGREEID int)
CREATE TABLE DEPARTMENT(DEPID int, DEGREEID int)
GO
INSERT INTO DEGREE_EARNED(EMPID, DEGREEID)
SELECT 201, 12 UNION ALL
SELECT 201, 3 UNION ALL
SELECT 201, 250 UNION ALL
SELECT 202, 3 UNION ALL
SELECT 202, 10 UNION ALL
SELECT 203, 17

INSERT INTO DEPARTMENT(DEPID, DEGREEID)
SELECT 101, 12 UNION ALL
SELECT 111, 250 UNION ALL
SELECT 111, 12 UNION ALL
SELECT 121, 3 UNION ALL
SELECT 121, 12 UNION ALL
SELECT 121, 250
GO

SELECT A.DEGREEID, ISNULL(ROW_OCCURS,0)
FROM ( SELECT DISTINCT DEGREEID FROM DEGREE_EARNED) AS A
LEFT JOIN ( SELECT DEGREEID, COUNT(*) AS ROW_OCCURS
FROM DEPARTMENT GROUP BY DEGREEID) AS B
ON A.DEGREEID = B.DEGREEID
GO

--DROP TABLE DEGREE_EARNED
--DROP TABLE DEPARTMENT
GOsql

Plan Guide

Is it possible to do something like this with a query plan?
I want to get
Query1
select * from tableA
where col1 like '%value%'
to run like
Query2
select * from tableA
where contains(col1, "value")
using a plan_guide.
Basically what's happening is a 3rd party product is executing a query that
looks like query1, which is searching on a text field (col1).
I wanted to try a full text index, but I don't think that I can get the
vendor to change their code to use a contains instead.
Any ideas?
Tia
--
MGEgads, why isn't the vendor using stored procedures?
"Hurme" <michael.geles@.thomson.com> wrote in message
news:FD780483-2D13-4284-8911-EF08FA332560@.microsoft.com...
> Is it possible to do something like this with a query plan?
> I want to get
> Query1
> select * from tableA
> where col1 like '%value%'
> to run like
> Query2
> select * from tableA
> where contains(col1, "value")
> using a plan_guide.
> Basically what's happening is a 3rd party product is executing a query
> that
> looks like query1, which is searching on a text field (col1).
> I wanted to try a full text index, but I don't think that I can get the
> vendor to change their code to use a contains instead.
> Any ideas?
> Tia
> --
> MG|||"Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:eXbLZijfIHA.5560@.TK2MSFTNGP04.phx.gbl...
> Egads, why isn't the vendor using stored procedures?
LOL with SQL statements hardwired into the client-side code the customer
can't go in and make changes like this to the code

placeholder for uniqueidentifier

Hi,
I need help with a SQL SELECT statement. I am using a UNION to merge four
tables together. For the tables that did not include a particular field, I
have used a placeholder of 'N/A' in the SELECT statement as follows:
SELECT organizationid, organization_name, businessunitid,
business_unit_name, subdivision_id, subdivision_name, lot_no, plan_number,
plan_name, current_retail_price, current_expiration_date,
plan_availability_pricing_id, plan_id
FROM hb_view_lot_plan
UNION
SELECT organizationid, organization_name, businessunitid,
business_unit_name, subdivision_id, subdivision_name, 'N/A', plan_number,
plan_name, current_retail_price, current_expiration_date,
plan_availability_pricing_id, plan_id
FROM hb_view_subdivision_plan
UNION
SELECT organizationid, organization_name, businessunitid,
business_unit_name, 'N/A', 'N/A', 'N/A', plan_number, plan_name,
current_retail_price, current_expiration_date, plan_availability_pricing_id,
plan_id
FROM hb_view_business_unit_plan
UNION
SELECT organizationid, organization_name, businessunitid, 'N/A', 'N/A',
'N/A', 'N/A', plan_number, plan_name, current_retail_price,
current_expiration_date, plan_availability_pricing_id, plan_id
FROM hb_view_organization_plan
I am having a problem with using the 'N/A' placeholder in lieu of a
uniqueidentifier. It works for varchar field types but apparently not
uniqueidentifier type fields. I receive the error message "error converting
from character string into uniqueidentifier". Thanks for your help.
DonYou could cast the top uniqueidentifier to a varchar(36) and it will work.
It seems a bit wierd though, because are you going to display the guid to
the user? Pretty hideous if you are.
----
Louis Davidson - drsql@.hotmail.com
SQL Server MVP
Compass Technology Management - www.compass.net
Pro SQL Server 2000 Database Design -
http://www.apress.com/book/bookDisplay.html?bID=266
Blog - http://spaces.msn.com/members/drsql/
Note: Please reply to the newsgroups only unless you are interested in
consulting services. All other replies may be ignored :)
<dbj> wrote in message news:uwbfXluPFHA.2136@.TK2MSFTNGP14.phx.gbl...
> Hi,
> I need help with a SQL SELECT statement. I am using a UNION to merge four
> tables together. For the tables that did not include a particular field,
> I have used a placeholder of 'N/A' in the SELECT statement as follows:
>
> SELECT organizationid, organization_name, businessunitid,
> business_unit_name, subdivision_id, subdivision_name, lot_no, plan_number,
> plan_name, current_retail_price, current_expiration_date,
> plan_availability_pricing_id, plan_id
> FROM hb_view_lot_plan
> UNION
> SELECT organizationid, organization_name, businessunitid,
> business_unit_name, subdivision_id, subdivision_name, 'N/A', plan_number,
> plan_name, current_retail_price, current_expiration_date,
> plan_availability_pricing_id, plan_id
> FROM hb_view_subdivision_plan
> UNION
> SELECT organizationid, organization_name, businessunitid,
> business_unit_name, 'N/A', 'N/A', 'N/A', plan_number, plan_name,
> current_retail_price, current_expiration_date,
> plan_availability_pricing_id, plan_id
> FROM hb_view_business_unit_plan
> UNION
> SELECT organizationid, organization_name, businessunitid, 'N/A',
> 'N/A', 'N/A', 'N/A', plan_number, plan_name, current_retail_price,
> current_expiration_date, plan_availability_pricing_id, plan_id
> FROM hb_view_organization_plan
>
> I am having a problem with using the 'N/A' placeholder in lieu of a
> uniqueidentifier. It works for varchar field types but apparently not
> uniqueidentifier type fields. I receive the error message "error
> converting from character string into uniqueidentifier". Thanks for your
> help.
>
> Don
>
>|||When you use union, first query defines column data types for all other
queries.
So, the problem is (probably, you did not provide DDl, so I cannot tell for
sure) in 3th and/or 4th query, where you set 'N/A' for subdivision_id which
can be of GUID data type.
Solution can be to replace 'N/A' with "empty" guid
'{00000000-0000-0000-0000-000000000000}' or with null.
Regards,
Marko Simic
"dbj" wrote:

> Hi,
> I need help with a SQL SELECT statement. I am using a UNION to merge four
> tables together. For the tables that did not include a particular field,
I
> have used a placeholder of 'N/A' in the SELECT statement as follows:
>
> SELECT organizationid, organization_name, businessunitid,
> business_unit_name, subdivision_id, subdivision_name, lot_no, plan_number,
> plan_name, current_retail_price, current_expiration_date,
> plan_availability_pricing_id, plan_id
> FROM hb_view_lot_plan
> UNION
> SELECT organizationid, organization_name, businessunitid,
> business_unit_name, subdivision_id, subdivision_name, 'N/A', plan_number,
> plan_name, current_retail_price, current_expiration_date,
> plan_availability_pricing_id, plan_id
> FROM hb_view_subdivision_plan
> UNION
> SELECT organizationid, organization_name, businessunitid,
> business_unit_name, 'N/A', 'N/A', 'N/A', plan_number, plan_name,
> current_retail_price, current_expiration_date, plan_availability_pricing_i
d,
> plan_id
> FROM hb_view_business_unit_plan
> UNION
> SELECT organizationid, organization_name, businessunitid, 'N/A', 'N/A'
,
> 'N/A', 'N/A', plan_number, plan_name, current_retail_price,
> current_expiration_date, plan_availability_pricing_id, plan_id
> FROM hb_view_organization_plan
>
> I am having a problem with using the 'N/A' placeholder in lieu of a
> uniqueidentifier. It works for varchar field types but apparently not
> uniqueidentifier type fields. I receive the error message "error converti
ng
> from character string into uniqueidentifier". Thanks for your help.
>
> Don
>
>
>|||Sorry I forgot to write the most important part :)
SQL try to convert 'N/A' to uniqueidentifier data type which is not
possible. you may try this to see what will happen:
select newid()
union
select 'N/A'
Regards,
Marko Simic
"dbj" wrote:

> Hi,
> I need help with a SQL SELECT statement. I am using a UNION to merge four
> tables together. For the tables that did not include a particular field,
I
> have used a placeholder of 'N/A' in the SELECT statement as follows:
>
> SELECT organizationid, organization_name, businessunitid,
> business_unit_name, subdivision_id, subdivision_name, lot_no, plan_number,
> plan_name, current_retail_price, current_expiration_date,
> plan_availability_pricing_id, plan_id
> FROM hb_view_lot_plan
> UNION
> SELECT organizationid, organization_name, businessunitid,
> business_unit_name, subdivision_id, subdivision_name, 'N/A', plan_number,
> plan_name, current_retail_price, current_expiration_date,
> plan_availability_pricing_id, plan_id
> FROM hb_view_subdivision_plan
> UNION
> SELECT organizationid, organization_name, businessunitid,
> business_unit_name, 'N/A', 'N/A', 'N/A', plan_number, plan_name,
> current_retail_price, current_expiration_date, plan_availability_pricing_i
d,
> plan_id
> FROM hb_view_business_unit_plan
> UNION
> SELECT organizationid, organization_name, businessunitid, 'N/A', 'N/A'
,
> 'N/A', 'N/A', plan_number, plan_name, current_retail_price,
> current_expiration_date, plan_availability_pricing_id, plan_id
> FROM hb_view_organization_plan
>
> I am having a problem with using the 'N/A' placeholder in lieu of a
> uniqueidentifier. It works for varchar field types but apparently not
> uniqueidentifier type fields. I receive the error message "error converti
ng
> from character string into uniqueidentifier". Thanks for your help.
>
> Don
>
>
>|||Oops, no it won't (d'oh!) you will have to cast them all to char:
select cast( 'na' as varchar(36))
union
select cast(newId() as varchar(36))
Sorry,
----
Louis Davidson - drsql@.hotmail.com
SQL Server MVP
Compass Technology Management - www.compass.net
Pro SQL Server 2000 Database Design -
http://www.apress.com/book/bookDisplay.html?bID=266
Blog - http://spaces.msn.com/members/drsql/
Note: Please reply to the newsgroups only unless you are interested in
consulting services. All other replies may be ignored :)
"Louis Davidson" <dr_dontspamme_sql@.hotmail.com> wrote in message
news:u%238on1uPFHA.532@.TK2MSFTNGP09.phx.gbl...
> You could cast the top uniqueidentifier to a varchar(36) and it will work.
> It seems a bit wierd though, because are you going to display the guid to
> the user? Pretty hideous if you are.
> --
> ----
--
> Louis Davidson - drsql@.hotmail.com
> SQL Server MVP
> Compass Technology Management - www.compass.net
> Pro SQL Server 2000 Database Design -
> http://www.apress.com/book/bookDisplay.html?bID=266
> Blog - http://spaces.msn.com/members/drsql/
> Note: Please reply to the newsgroups only unless you are interested in
> consulting services. All other replies may be ignored :)
> <dbj> wrote in message news:uwbfXluPFHA.2136@.TK2MSFTNGP14.phx.gbl...
>|||Marko,
Thank you very much. Your answer worked on the first try.
Don
"Simic Marko" <SimicMarko@.discussions.microsoft.com> wrote in message
news:CA618990-5DFD-4753-B3CC-13FA859F136C@.microsoft.com...
> When you use union, first query defines column data types for all other
> queries.
> So, the problem is (probably, you did not provide DDl, so I cannot tell
> for
> sure) in 3th and/or 4th query, where you set 'N/A' for subdivision_id
> which
> can be of GUID data type.
> Solution can be to replace 'N/A' with "empty" guid
> '{00000000-0000-0000-0000-000000000000}' or with null.
> Regards,
> Marko Simic
> "dbj" wrote:
>

Pl/SQL Beginner Problem, Selecting all records?

SET SERVEROUTPUT ON;

DECLARE
student_rec student%ROWTYPE;
BEGIN
SELECT *
INTO student_rec
FROM student
WHERE student_id = 156 ;
DBMS_OUTPUT.PUT_LINE ('Last Name : '|| student_rec.last_name || chr(10)||
'First Name : '|| student_rec.first_name || chr(10)||
'Phone Number : '|| student_rec.phone || chr(10)||
'Reg Date : '|| student_rec.registration_date);
EXCEPTION
WHEN no_data_found THEN
RAISE_APPLICATION_ERROR(-20001, 'Student with id = 156 is not in the Database');
WHEN others THEN
DBMS_OUTPUT.PUT_LINE(SQLCODE || ' '|| substr(SQLERRM,1,80));
END;
.

In the above code it selects the information for the student with the id of 156, how do I change it so that it selects all students from the table instead of just the one?

Any help appreciated.just remove the condition tht where student id = 156:

SELECT *
INTO student_rec
FROM student

above script will select all the student in student and store it into the sudent_rec .

RAISE_APPLICATION_ERROR(-20001, 'Student with id = 156 is not in the Database');

when you want to raise the exeception just write it ther is no record in the table instad of student with id= 156 is not in the database.|||Hi,

Use a cursor & remove the Hard coded value 156 in the query.

Originally posted by iknownothing
SET SERVEROUTPUT ON;

DECLARE
student_rec student%ROWTYPE;
BEGIN
SELECT *
INTO student_rec
FROM student
WHERE student_id = 156 ;
DBMS_OUTPUT.PUT_LINE ('Last Name : '|| student_rec.last_name || chr(10)||
'First Name : '|| student_rec.first_name || chr(10)||
'Phone Number : '|| student_rec.phone || chr(10)||
'Reg Date : '|| student_rec.registration_date);
EXCEPTION
WHEN no_data_found THEN
RAISE_APPLICATION_ERROR(-20001, 'Student with id = 156 is not in the Database');
WHEN others THEN
DBMS_OUTPUT.PUT_LINE(SQLCODE || ' '|| substr(SQLERRM,1,80));
END;
.

In the above code it selects the information for the student with the id of 156, how do I change it so that it selects all students from the table instead of just the one?

Any help appreciated.|||When I remove the WHERE statement, I get an error when I run it saying
"-1422 ORA-01422: exact fetch returns more than requested number of rows"|||Hi,

Use CURSOR to avoid this error. Because you can fetch only one row into the record type student_rec . When the value 156 is harcoded in the query , exactly one row is fetched into student_rec . So it works fine. But once you remove the hard coded value 156 from the query, all the records are fetched . Since the record type student_rec can accept only one value, it displays the error ORA-01422: exact fetch returns more than requested number of rows. To avoid this error & fetch all the records into student_rec, use a CURSOR.

Originally posted by iknownothing
When I remove the WHERE statement, I get an error when I run it saying
"-1422 ORA-01422: exact fetch returns more than requested number of rows"|||Its ok, fixed it! Thanks.|||As an aside from your question, you should get out of the habit of doing this:

EXCEPTION
WHEN no_data_found THEN
RAISE_APPLICATION_ERROR(-20001, 'Student with id = 156 is not in the Database');
WHEN others THEN
DBMS_OUTPUT.PUT_LINE(SQLCODE || ' '|| substr(SQLERRM,1,80));
END;

All that does is potentially hide errors from the user and allow inconsistent partial transactions to be committed. It should be simply:

EXCEPTION
WHEN no_data_found THEN
RAISE_APPLICATION_ERROR(-20001, 'Student with id = 156 is not in the Database');
END;

PK And Index

I have a primary key that comprises 2 columns (lets say ReportDate and
Symbol).

I know that if I submit a statement like SELECT * FROM T1 WHERE
ReportDate = '20031219' AND Symbol = 'XYZ' it will use the index.

But how about the statement SELECT * FROM T1 WHERE Symbol = 'XYZ'?

Do I need to create another index on symbol alone?Jason (JayCallas@.hotmail.com) writes:
> I have a primary key that comprises 2 columns (lets say ReportDate and
> Symbol).
> I know that if I submit a statement like SELECT * FROM T1 WHERE
> ReportDate = '20031219' AND Symbol = 'XYZ' it will use the index.
> But how about the statement SELECT * FROM T1 WHERE Symbol = 'XYZ'?
> Do I need to create another index on symbol alone?

For best performance, yes.

But the query may use the existing index, if the index is non-clustered.
If SQL Server finds that XYZ is not a very common value, it may opt
scan the index to find the rows. This is faster than scanning the entire
table. If the value is common, however, the bookmark lookups will be
more expensive than scanning.

If the existing index is clustered, it can not help to speed up the
retrieval. Ah, that wasn't completely true, either. Because if the
there is a non-clustered index on the table as well, the keys of the
clustered index appears in the non-clustered index, so SQL Server can
scan that index.

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||To add to Erlands response. You could check the Execution Plan when using
Query Analyzer to see how SQL Server is using your indexes.

BZ

"Jason" <JayCallas@.hotmail.com> wrote in message
news:f01a7c89.0312190912.1c1ea341@.posting.google.c om...
> I have a primary key that comprises 2 columns (lets say ReportDate and
> Symbol).
> I know that if I submit a statement like SELECT * FROM T1 WHERE
> ReportDate = '20031219' AND Symbol = 'XYZ' it will use the index.
> But how about the statement SELECT * FROM T1 WHERE Symbol = 'XYZ'?
> Do I need to create another index on symbol alone?

Pivoting DataSet

I currently have from SQL a stored procedure that is vaguely doing something
like:
SELECT
FormName,
frmDueDate,
DepartmentName,
Country
frmStatus
FROM
wholeBunchOfTables
So data will be returned like:
Form1 ITDept USA Incomplete
Form2 ITDept UK Completed
Form1 HRDept FR Completed
Form2 HRDept FR Missing
The DataSet resultant from the Stored procedure execution is binded to a
sortable DataGrid. However, I would like the data "pivoted":
i.e.
Form1 Form2
ITDept USA Incomplete Completed
HRDept FR Completed Missing
Is this at all possible (natively)'
- Can't think of any pivoting SQL operators'
- Can't pivot on ASP.NET/ADO.NET' Would I have to resort to
-- creating another DataSet with columns pivoted' or
-- render my own table, etc. and handle by own sorting?Reporting Services does this quite easily... are you creating a report?
--
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant
Microsoft MVP
www.betav.com/blog/billva
www.betav.com
www.sqlreportingservices.net
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________
"Patrick" <questions@.newsgroup.nospam> wrote in message
news:BA374BD6-B1A2-41BE-9109-A611EFCC394E@.microsoft.com...
>I currently have from SQL a stored procedure that is vaguely doing
>something
> like:
> SELECT
> FormName,
> frmDueDate,
> DepartmentName,
> Country
> frmStatus
> FROM
> wholeBunchOfTables
> So data will be returned like:
> Form1 ITDept USA Incomplete
> Form2 ITDept UK Completed
> Form1 HRDept FR Completed
> Form2 HRDept FR Missing
> The DataSet resultant from the Stored procedure execution is binded to a
> sortable DataGrid. However, I would like the data "pivoted":
> i.e.
> Form1 Form2
> ITDept USA Incomplete Completed
> HRDept FR Completed Missing
> Is this at all possible (natively)'
> - Can't think of any pivoting SQL operators'
> - Can't pivot on ASP.NET/ADO.NET' Would I have to resort to
> -- creating another DataSet with columns pivoted' or
> -- render my own table, etc. and handle by own sorting?|||Patrick
This might help: http://weblogs.sqlteam.com/jeffs/articles/5091.aspx
Adrian Moore
http://www.queryadataset.com
"Patrick" <questions@.newsgroup.nospam> wrote in message
news:BA374BD6-B1A2-41BE-9109-A611EFCC394E@.microsoft.com...
>I currently have from SQL a stored procedure that is vaguely doing
>something
> like:
> SELECT
> FormName,
> frmDueDate,
> DepartmentName,
> Country
> frmStatus
> FROM
> wholeBunchOfTables
> So data will be returned like:
> Form1 ITDept USA Incomplete
> Form2 ITDept UK Completed
> Form1 HRDept FR Completed
> Form2 HRDept FR Missing
> The DataSet resultant from the Stored procedure execution is binded to a
> sortable DataGrid. However, I would like the data "pivoted":
> i.e.
> Form1 Form2
> ITDept USA Incomplete Completed
> HRDept FR Completed Missing
> Is this at all possible (natively)'
> - Can't think of any pivoting SQL operators'
> - Can't pivot on ASP.NET/ADO.NET' Would I have to resort to
> -- creating another DataSet with columns pivoted' or
> -- render my own table, etc. and handle by own sorting?|||This article is *Excellent*!! Just about what I wanted!
However, how can I modify the following:
<asp:datagrid id="OutstandingFormsDataGrid" runat="server"
AutoGenerateColumns="False" ShowHeader="true"
EditItemStyle="data" HeaderStyle-CssClass="header" AllowSorting="true"
OnSortCommand="SortCurrentMonth_OnClick"
HeaderStyle-Height="25px">
<ItemStyle CssClass="data"></ItemStyle>
<HeaderStyle Height="25px" CssClass="header"></HeaderStyle>
<Columns>
<asp:HyperLinkColumn DataNavigateUrlField="frmInstId"
DataNavigateUrlFormatString="ShowForm.aspx?id={0}"
DataTextField="'These are pivoted columns'" SortExpression="?"
HeaderText="Form Name"></asp:HyperLinkColumn>
<asp:BoundColumn DataField="Due Date"
</Columns>
</asp:datagrid>
The following are being displayed at the moment with
"AutoGenerateColumns=True":
frmInstId Country Form1 Form2 Form3 Form 4
1 UK complete
2 UK complete
3 UK missing
4 UK complete
5 US missing
6 US missing
7 US complete
8 US complete
Ideally I want:
Country Form1 Form2 Form3 Form 4
UK complete complete missing complete
US missing missing complete complete
(with the frmInstId) embedded as hyperlink the the form status
Is it possible?
"Adrian Moore" wrote:
> Patrick
> This might help: http://weblogs.sqlteam.com/jeffs/articles/5091.aspx
> Adrian Moore
> http://www.queryadataset.com
>
> "Patrick" <questions@.newsgroup.nospam> wrote in message
> news:BA374BD6-B1A2-41BE-9109-A611EFCC394E@.microsoft.com...
> >I currently have from SQL a stored procedure that is vaguely doing
> >something
> > like:
> > SELECT
> > FormName,
> > frmDueDate,
> > DepartmentName,
> > Country
> > frmStatus
> > FROM
> > wholeBunchOfTables
> >
> > So data will be returned like:
> > Form1 ITDept USA Incomplete
> > Form2 ITDept UK Completed
> > Form1 HRDept FR Completed
> > Form2 HRDept FR Missing
> >
> > The DataSet resultant from the Stored procedure execution is binded to a
> > sortable DataGrid. However, I would like the data "pivoted":
> > i.e.
> > Form1 Form2
> > ITDept USA Incomplete Completed
> > HRDept FR Completed Missing
> >
> > Is this at all possible (natively)'
> > - Can't think of any pivoting SQL operators'
> > - Can't pivot on ASP.NET/ADO.NET' Would I have to resort to
> > -- creating another DataSet with columns pivoted' or
> > -- render my own table, etc. and handle by own sorting?
>
>|||Hello Patrick,
This seems normal behavior if you use the code from the following link
directly:
http://weblogs.sqlteam.com/jeffs/articles/5091.aspx
The code adds a new row in the datatable of dataset from the original
table. You need to modify the code so that it can search all the existing
rows in datatable for the row with the same key coulumn such as Country in
your table. If there is a row existing, you shall create a new column to
this row other than create a new row.
Regards,
Peter Yang
MCSE2000/2003, MCSA, MCDBA
Microsoft Online Partner Support
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
=====================================================
This posting is provided "AS IS" with no warranties, and confers no rights.
| Thread-Topic: Pivoting DataSet
| thread-index: AcV415h2UiiUvr4YRaGnKv5nBmTZ3Q==| X-WBNR-Posting-Host: 198.240.130.75
| From: "=?Utf-8?B?UGF0cmljaw==?=" <questions@.newsgroup.nospam>
| References: <BA374BD6-B1A2-41BE-9109-A611EFCC394E@.microsoft.com>
<OsVZAg4dFHA.2288@.TK2MSFTNGP14.phx.gbl>
| Subject: Re: Pivoting DataSet
| Date: Fri, 24 Jun 2005 09:13:02 -0700
| Lines: 85
| Message-ID: <1A21D301-964E-4E83-9397-2F6B50C6403D@.microsoft.com>
| MIME-Version: 1.0
| Content-Type: text/plain;
| charset="Utf-8"
| Content-Transfer-Encoding: 7bit
| X-Newsreader: Microsoft CDO for Windows 2000
| Content-Class: urn:content-classes:message
| Importance: normal
| Priority: normal
| X-MimeOLE: Produced By Microsoft MimeOLE V6.00.3790.0
| Newsgroups:
microsoft.public.dotnet.framework.adonet,microsoft.public.sqlserver.server
| NNTP-Posting-Host: TK2MSFTNGXA03.phx.gbl 10.40.2.250
| Path: TK2MSFTNGXA01.phx.gbl!TK2MSFTNGXA03.phx.gbl
| Xref: TK2MSFTNGXA01.phx.gbl microsoft.public.sqlserver.server:61330
microsoft.public.dotnet.framework.adonet:31648
| X-Tomcat-NG: microsoft.public.sqlserver.server
|
| This article is *Excellent*!! Just about what I wanted!
|
| However, how can I modify the following:
| <asp:datagrid id="OutstandingFormsDataGrid" runat="server"
| AutoGenerateColumns="False" ShowHeader="true"
| EditItemStyle="data" HeaderStyle-CssClass="header" AllowSorting="true"
| OnSortCommand="SortCurrentMonth_OnClick"
| HeaderStyle-Height="25px">
| <ItemStyle CssClass="data"></ItemStyle>
| <HeaderStyle Height="25px" CssClass="header"></HeaderStyle>
| <Columns>
| <asp:HyperLinkColumn DataNavigateUrlField="frmInstId"
| DataNavigateUrlFormatString="ShowForm.aspx?id={0}"
| DataTextField="'These are pivoted columns'" SortExpression="?"
| HeaderText="Form Name"></asp:HyperLinkColumn>
| <asp:BoundColumn DataField="Due Date"
| </Columns>
| </asp:datagrid>
|
| The following are being displayed at the moment with
| "AutoGenerateColumns=True":
| frmInstId Country Form1 Form2 Form3 Form 4
| 1 UK complete
| 2 UK complete
| 3 UK missing
| 4 UK complete
| 5 US missing
| 6 US missing
| 7 US complete
| 8 US complete
|
| Ideally I want:
| Country Form1 Form2 Form3 Form 4
| UK complete complete missing complete
| US missing missing complete complete
|
| (with the frmInstId) embedded as hyperlink the the form status
|
| Is it possible?
|
| "Adrian Moore" wrote:
|
| > Patrick
| >
| > This might help: http://weblogs.sqlteam.com/jeffs/articles/5091.aspx
| >
| > Adrian Moore
| > http://www.queryadataset.com
| >
| >
| > "Patrick" <questions@.newsgroup.nospam> wrote in message
| > news:BA374BD6-B1A2-41BE-9109-A611EFCC394E@.microsoft.com...
| > >I currently have from SQL a stored procedure that is vaguely doing
| > >something
| > > like:
| > > SELECT
| > > FormName,
| > > frmDueDate,
| > > DepartmentName,
| > > Country
| > > frmStatus
| > > FROM
| > > wholeBunchOfTables
| > >
| > > So data will be returned like:
| > > Form1 ITDept USA Incomplete
| > > Form2 ITDept UK Completed
| > > Form1 HRDept FR Completed
| > > Form2 HRDept FR Missing
| > >
| > > The DataSet resultant from the Stored procedure execution is binded
to a
| > > sortable DataGrid. However, I would like the data "pivoted":
| > > i.e.
| > > Form1 Form2
| > > ITDept USA Incomplete Completed
| > > HRDept FR Completed Missing
| > >
| > > Is this at all possible (natively)'
| > > - Can't think of any pivoting SQL operators'
| > > - Can't pivot on ASP.NET/ADO.NET' Would I have to resort to
| > > -- creating another DataSet with columns pivoted' or
| > > -- render my own table, etc. and handle by own sorting?
| >
| >
| >
|

Pivot Select

Hi,
I'm able to get Pivot to work but I'm having trouble limiting the record set. I want it to select only records from this FiscalYear. I have a table with a field called CurrentBudgetYear that I use as a control. So FiscalYear should equal CurrentBudgetYear but my results include all FiscalYears.

SELECT ProjNo, TaskCode, [1] AS P1, [2] AS P2, [3] AS P3, [4] AS P4, [5] AS P5, AS P6, [7] AS P7, AS P8, [9] AS P9, [10] AS P10, [11] AS P11, [12] AS P12
FROM
(SELECT e.ProjNo, e.TaskCode, e.FiscalPeriod, e.ActualAmt
FROM tblActualExpend AS e
INNER JOIN tblBudgetConfig ON e.FiscalYear = tblBudgetConfig.CurrentBudgetYear
)p
PIVOT
(
SUM(ActualAmt)
FOR FiscalPeriod IN
( [1], [2], [3], [4], [5], , [7], , [9], [10], [11], [12])
)AS pvt
ORDER BY ProjNo, TaskCode;

Thanks in advanced for any help.

It appears that in the derived table, you need a WHERE clause to constrain the data to a specific FiscalYear.

Something like:

WHERE e.FiscalYear >= '20060101' and e.FiscalYear < '20070101'

Or whatever dates demarc your Fiscal Year.

|||Thanks Arnie, I can see where that would work. I really want something more automated. Thats why I use the control table. This way all I have to do is change the value in one field in one table and all my queries are current.|||

As you noticed, If your 'control table' has rows all of your Fiscal Years, you retreive all Fiscal Years. The only way it would work without a WHERE clause is if the 'control table' has only one row of data for the current FiscalYear.

If you want only one fiscal Year, then you will have to specify which one. The query processor can't read your mind.

You could still 'automate' the process, for example, assuming your FiscalYear begins July 1:

WHERE e.FiscalYear >= cast( cast( ( year( getdate() ) - 1) AS char(4)) + '0701' AS datetime )
AND e.FiscalYear < cast( cast( ( year( getdate() )) AS char(4)) + '0701' AS datetime )

Will always derive the Fiscal year for the current date.

|||

Sorry to have bothered you. The pivot works exactly as I want. I was running it on test data that I took at the end of the last fiscal year so I was getting all of the fiscal periods. Becuase of that I was sure it was not working. I just updated my tables and I'm now getting the results I was expecting.

As an FYI, the 'control' table only has one record in it....the FiscalYear. For reporting purposes we can't cut over to the new fiscal year at the start of the year (atleast in the database) so we use this table to control all of that.

Cheers!

|||Not a problem!

Pivot query error

I'm trying to simulate an Access query in SQL and reached a dead end.

SELECT * FROM (SELECT
AccountNumber
, Convert(varchar, startDate, 101) AS startDate
, Convert(varchar, resultDate, 101) AS resultDate
FROM [TableA]) AS D
PIVOT(SUM(Amount) FOR resultDate IN([02/27/2006],[02/28/2006],[03/01/2006],[03/02/2006],[Outstanding])) AS P

Now I want to add two things:
1) Add one more column 'Total' containing the total of amount for each startDate
2) Add a column 'OutStanding' in PIVOT which should contain SUM(Amount) where resultDate IS NULL.

How do I do it?declare @.TableA table (
AccountNumber int,
StartDate datetime,
ResultDate datetime,
amount decimal(10,2)
)

insert into @.TableA values (100, '02/27/2006', '02/28/2006', 300)
insert into @.TableA values (100, '02/28/2006', null, 500)
insert into @.TableA values (100, '03/01/2006', null, 800)
insert into @.TableA values (100, '02/27/2006', null, 100)
insert into @.TableA values (100, '02/27/2006', null, 200);

insert into @.TableA values (200, '02/27/2006', null, 50)
insert into @.TableA values (200, '02/28/2006', null, 100)
insert into @.TableA values (200, '03/01/2006', null, 200)
insert into @.TableA values (200, '02/27/2006', null, 50)
insert into @.TableA values (200, '02/27/2006', null, 100);

insert into @.TableA values (300, '02/27/2006', '02/28/2006', 50)
insert into @.TableA values (300, '02/28/2006', '03/01/2006', 100)
insert into @.TableA values (300, '03/01/2006', '03/05/2006', 200)
insert into @.TableA values (300, '02/27/2006', null, 50)
insert into @.TableA values (300, '02/27/2006', null, 100);

insert into @.TableA values (400, '02/27/2006', '02/28/2006', 50)
insert into @.TableA values (400, '02/28/2006', '03/01/2006', 100)
insert into @.TableA values (400, '03/01/2006', '03/05/2006', 200)
insert into @.TableA values (400, '02/27/2006', '03/05/2006', 50)
insert into @.TableA values (400, '02/27/2006', '03/07/2006', 100);

WITH Tot_CTE (AccountNumber, Total, OutStanding)
AS
(
SELECT AccountNumber, SUM(Amount), SUM(case when ResultDate is null then Amount else 0 end)
FROM
@.TableA
GROUP BY AccountNumber
)
SELECT a.*,b.Total,b.OutStanding
FROM (
SELECT * FROM (SELECT
AccountNumber
, Convert(varchar, startDate, 101) AS startDate
--, Convert(varchar, resultDate, 101) AS resultDate
,Amount
FROM @.TableA) AS D
PIVOT (SUM(Amount) FOR StartDate IN([02/27/2006],[02/28/2006],[03/01/2006],[03/02/2006])) AS P
) as a
join
tot_CTE b
on
a.accountNumber = b.AccountNumber|||

PIVOT has lot of restrictions and you cannot generate multiple aggregates for example. So It is easier to do this using a standard SQL query. And it is much more efficient since you can compute all the necessary aggregates in one pass of the data. Try query below. I had to guess the details about your schema and what each column means. But you should get the idea.

SELECT t.AccountNumber

, t.StartDate

, SUM(CASE WHEN t.resultDate IS NULL THEN t.Amount END) as OutStanding

, SUM(CASE t.resultDate WHEN '20060227' THEN t.Amount END) as "20060227"

, SUM(CASE t.resultDate WHEN '20060228' THEN t.Amount END) as "20060228"

...

, SUM(t.Amount) AS Total

FROM TableA AS t

WHERE t.resultDate is null

or t.resultDate between '20060227' and '20060302' -- modify accordingly

GROUP BY t.AccountNumber, t.StartDate -- may need to strip time part depending on your data

Pivot misunderstood ?

Hello, i've been looking for some samples on the pivot function, but somewhere i'm making a mistake and can't find where.

This is my statement

SELECT MOV_UI, [1] AS SUNDAY, [2] AS MONDAY, [3] AS THUESDAY, [4] AS WEDNESDAY, [5] AS THURSDAY, AS FRIDAY, [7] AS SATURDAY

FROM XX.UGENT_DAILYSALES_OF_MOVIE_FIRST_WEEK

PIVOT (SUM(TICKETS)

FOR DAYOFWEEK IN ([1], [2], [3], [4], [5], , [7])) AS PVT

ORDER BY MOV_UI

I though to receive a line per mov_ui with the 7 sums, but .. i'm getting a line for each day ....

MOV_UI SUNDAY MONDAY THUESDAY WEDNESDAY THURSDAY FRIDAY SATURDAY
29 NULL NULL NULL 73 NULL NULL NULL
29 NULL NULL NULL NULL 72 NULL NULL
29 NULL NULL NULL NULL NULL 129 NULL
29 NULL NULL NULL NULL NULL NULL 138
29 104 NULL NULL NULL NULL NULL NULL
29 NULL 68 NULL NULL NULL NULL NULL
29 NULL NULL 58 NULL NULL NULL NULL

What am i missing .... ?

Hmm, this works for me:

CREATE TABLE test
(
movie_ui int,
dayOfWeek int,
tickets int
)
INSERT INTO test
SELECT 29,1,20
UNION ALL
SELECT 29,2,38
UNION ALL
SELECT 30,1,20
UNION ALL
SELECT 30,2,44
GO
SELECT movie_ui, [1] AS SUNDAY, [2] AS MONDAY
FROM test PIVOT (SUM(TICKETS) FOR DAYOFWEEK IN ([1], [2])) AS PVT
ORDER BY movie_ui

movie_ui SUNDAY MONDAY
-- -- --
29 20 38
30 20 44

|||

Thx for the answer, i've found the problem ...

this was my syntax

SELECT MOV_UI, [1] AS SUNDAY, [2] AS MONDAY, [3] AS THUESDAY, [4] AS WEDNESDAY, [5] AS THURSDAY, AS FRIDAY, [7] AS SATURDAY

FROM XX.UGENT_DAILYSALES_OF_MOVIE_FIRST_WEEK

PIVOT (SUM(TICKETS)

FOR DAYOFWEEK IN ([1], [2], [3], [4], [5], , [7])) AS PVT

ORDER BY MOV_UI

Problem was that from the view xx.ugent.. there where other columns returned that where not used in the pivot, but they cause this effect, so adding them in the select mov_ui, ... and the order by helpen the problem, or chaning the 'from xx.ugent' to a

from (select mov_ui, dayofweek, tickets from xx_ugent_dailysales_of_movie_first_Week) p' solved the problem too.

Kind Regards