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 populate my date fields

Hi,

I have the following table:

CREATE TABLE [Orders] (
[OrdID] [int] ,
[OrderDate] [datetime] ,
[OrderDateONLY] [datetime],
[OrderDayName] [nvarchar] (15),
[OrderMonth] [nvarchar] (25),
[OrderDayOfMonth] [smallint] NULL ,
[OrderWeekofYear] [smallint] NULL
)
GO

The field OrderDate contains BOTH Date and Time information. What I want to do is populate the fields (in bold above) from the OrderDate field... I have tried all that I could think of but nothing works. The last and the best try that I gave was the following but that too does not work. Can you PLEASE help. Many thanks in advance:

Insert ORDERS
(OrderDateONLY, OrderDayName, OrderMonth, OrderDayOfMonth, OrderWeekofYear)
select
d, datename (dw, d), datename (mm, d), Year (d), datepart (ww, d)
from
(select convert (char (8), OrderDate, 112)as d
from ORDERS
) as xupdate orders set orderdateonly=convert(varchar,orderdate,105),order Dayname=datename(dw,orderdate),orderMonth=datename (m,orderdate),
orderDayOfMonth=day(orderdate),orderweekofyear=dat ename(wk,orderdate)|||populate only orderDate in this

CREATE TABLE [dbo].[Orders] (
[OrdID] [int] NULL ,
[OrderDate] [datetime] NULL ,
[OrderDateONLY] AS ([orderdate]) ,
[OrderDayName] AS (datename(weekday,[orderdate])) ,
[OrderMonth] AS (datename(month,[orderdate]))
.....
)|||harsal_in,

Thanks for the reply...i think i'm very close now except for one problem...when I run the statement i get an error message saying:

"The conversion of a char datatype to datetime datatype resulted in an out of range datetime value"

The problem seems to be with the "orderdateonly=convert(varchar,orderdate,105)" but I can't figure out :confused:|||it works very well on my machine.
anyways try this:
update orders set orderdateonly=convert(datetime,convert(varchar,ord erdate,105)),orderDayname=datename(dw,orderdate),o rderMonth=datename (m,orderdate),
orderDayOfMonth=day(orderdate),orderweekofyear=dat ename(wk,orderdate)|||harshal_in,

I wonder what's wrong at my end becuase I am still getting the same error. I have SP1 and could this be the problem? I'll update to SP3 but for now is there a solution.

Thanks.|||Format 120 (or 121 Brett...) are better for date conversion because they are interpreted unambiguously by SQL Server.

update orders
set orderdateonly=convert(datetime,convert(char(10),or derdate,120)),
orderDayname=datename(dw,orderdate),
orderMonth=datename (m,orderdate),
orderDayOfMonth=day(orderdate),
orderweekofyear=datename(wk,orderdate)

...but the problem with your design here is that you will need to ensure that anytime the orderdate is modified that all the other columns are updated as well. I recommend that instead of having columns to store these values directly you should create them as calculated fields using the above formulas. This way, they will automatically be synchronized with the orderdate field.|||Unless it's a warehouse...or for performance reasons...which I can't see it...

NEVER store derived data....

What's the reason..

If you store derived data, then you get caught in the trap of making sure the derived data is TRUE to the source...all the time...

Which means an additional process..

This is OLTP, right?

in OLAP, it done once and never changes, so it is TRUE at the time of the derivation

OLTP is fluid, and alway in a state of flux...(like the capacitor :D )

So why?sql

Please Help Just Upsized Access To Sql And Now Code Doesnt Work

here is the code i was using from a command button which basically used the first and last name fields to make a primary key called chart number. now it doesnt work and i belive it is in the dim rs as dao.recordset statement because this data now resides on an sql server. here is the code snippet.

Public Function chartlookup()
Dim db As DAO.Database <--believe to be the problem
Dim rs As DAO.Recordset <-- belive to be the problem
Dim SQL As String
Dim NewNum As Integer
Dim NeWChartNum As String

SQL = "Select max(Cint(Right([chartnumber],6))) As RecNum From tblpatientinfo WHERE UCase(Left([chartnumber],5)) = '" & UCase(Left([Forms]![fpatient]![lname], 3)) & UCase(Left([Forms]![fpatient]![fname], 2)) & "'"

Set db = CurrentDb() <-- -problem
Set rs = db.OpenRecordset(SQL) <--problem

If IsNull([Forms]![fpatient]![chartnumber]) = False Then GoTo 400

If rs.EOF = False Then
If IsNull(rs!RecNum) = False Then
NewNum = rs!RecNum + 1
Else
NewNum = 1
End If
End If
'If NewNum = 1 Then

[Forms]![fpatient]![chartnumber] = UCase(Left([Forms]![fpatient]![lname], 3)) & UCase(Left([Forms]![fpatient]![fname], 2)) & Format(NewNum, "000000")
'End If

400 End Function

basically if you typw in john smith this code would put smijo00001 into chartnumber field now i get a run-time error 91.What error are you receiving?

Also be advised that UCase is not supported in MS SQL, you should use UPPER, nor is Cint.

You have a few access specific casts there that are going to cause trouble for you.|||Change you dao to ado - using recordset and/or connection objects.|||Originally posted by Teddy
What error are you receiving?

Also be advised that UCase is not supported in MS SQL, you should use UPPER, nor is Cint.

You have a few access specific casts there that are going to cause trouble for you.

im receiving a run-time error 91 in the set rs= db.recordset(sql) statement but i know it coming from previous dim db as dao.database and dim rs as dao.recordset. i dont know how to convert this vb code to work with sql. maybe dim rs as sql.recordset and dim db as sql.database . please forgive my ignorance but i am new to sql and i dont know the syntax for sql. by the way thank you for the advice with ucase and cint not working . i tried a previous suggestion and turned dim rs as ado.recordset but the only option i get is adobe.recordset am i missing a reference .|||You need to use the ms ado 2.x library reference. Using adodb.recordset and adodb.connection - however, in your case you only need adodb.recordset.|||Hey
Who one saying that ODBC Connections not possible by DAO?

[QUOTE][SIZE=1]Originally posted by opcbriley
here is the code i was using from a command button which basically used the first and last name fields to make a primary key called chart number. now it doesnt work and i belive it is in the dim rs as dao.recordset statement because this data now resides on an sql server. here is the code snippet.

Public Function chartlookup()
Dim wrkODBC As Workspace
Dim conPubs As Connection
Dim rs As DAO.Recordset <-- belive to be the problem
Dim SQL As String
Dim NewNum As Integer
Dim NeWChartNum As String

SQL = "Select max(Cint(Right([chartnumber],6))) As RecNum From tblpatientinfo WHERE UCase(Left([chartnumber],5)) = '" & UCase(Left([Forms]![fpatient]![lname], 3)) & UCase(Left([Forms]![fpatient]![fname], 2)) & "'"

Set wrkODBC = CreateWorkspace("NewODBCWorkspace", "admin", "", dbUseODBC)
Set conPubs = wrkODBC.OpenConnection("costing", dbDriverNoPrompt)
.........

You may also open connection by using this code
remaining code will remain same
plz use it n reply me
thx|||You can use odbc - but don't - you are much better off using oledb (or use tds in .net). Better performance, resource usage and flexibility/functionality.|||yeah i know that OLEDB performance much better that dao but person asked abt DAO libraray n no one was there to reply by DAO libraray so I hv ginven reply by DAO library|||i appreciate all your help i was able to switch to ado connection i finally learned the syntax last night after about hour of reading. that code is working perfect now.

now on to find all my other problems that i have to change to be compatible with sql. i sure hope sql is worth all this trouble.

Pivottable 10/11 with SSAS 2005 cube 'hang' when using filter fields

Dear all,

I am running into some difficulties with an SSAS 2005 SP2 cube in combination with an OWC 11 pivottable client. Everything basically works fine.

The problem occurs when I make a selection with one or more dimensions in the 'filter' section.

For instance, if I build a pivottable which shows:

YEAR in the Colums

PRODUCT GROUP in the ROWS

CUSTOMER NAME in the ROWS

PRODUCT FAMILY in FILTER SECTION

SALES as Measure

As soon as I make a selection on YEAR (show only 2007) and PRODUCT FAMILY (show only 'bikes' and 'minibikes') , the pivottable basically hangs. On the server the memory usage of SSAS shoots up to 1.8(!) GB

After +/- 60 seconds I get a blank screen and sometimes: 'Cannot display data because of structural changes in the database'.

I tried to make the same selecion in my Excel2007 eval, which worked fine. Pivottable 10 gives the same error. The error also occurs in Visual Studio (off course also OWC11).

Altering the cube's storage settings did not help.

My cube's main facttable containts approx 2.5 mln records.

Is this a known problem, what can I do to fix it?

Many many thanks

Rex

Dear all,

After doing some research it seems that SP2 is causing the errors. When downgrading my SSAS 2005 to SP1 all problems vanished. Only problem now is the performance compared to SP2 which is significantly lower.

To really make sure it was SP2 I installed a fresh SSAS2005 machine without any servicepacks and built my cube, after intensive testing no problems.

Upgraded to SP1, no problems.

Upgrade to SP2, massive memory usage of the SSAS service and total lockup.

I also installed the cumulative 3161 post SP2 update, but this did not fix the issue.

The problem only occurs when filtering data in the pivottable's 'filter' section.

Any ideas?

|||

Problem solved!!

After installing the Cumulative Hoftfixes 2 for SP2 (build 3175), released Jun 22nd the problem has disappeared and the performance of my cubes is great!

Link to the hotfix: http://support.microsoft.com/kb/936305

pivoting issue

Hi,
I have a expenses table, in this table I have the account, exp_date,
exp_type and amount fields.
I have three exp_type (real, budget and myu) I would like to create a table
with
the fileds account, year_month, real_amount, budget_amount and myu_amount.
I am kind of lost now. How can I do this?
Will it be a query?
do a have a ETL process if yes, how will it work?
Thanks
Search the google archives of microsoft.public.sqlserver.programming group
with the keyword "pivot" and "crosstab" and you will end up with several
solutions posted for similar questions.
Anith

Pivot Table Woes

I am very confused here. I really hope someone can help.

I have a table that contains "virtual fields" (ie. a column for field name and a column for field value). What I'd like is a pivot table that has the field names across the top and the field values as the row. I found the PivotTable service, but that seems like quite a bit of work for something that you can do in Access in a few clicks. I also know the SUM/CASE method, but unfortunately the virtual fields are dynamic, and I don't know what they could be named, nor how many of them exist. Does anybody have any ideas of what I can do? I'll include an example below to clear up any confusion.

Thanks!

What I have:

field_name | field_value

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

car_manufacturer | Jaguar

car_model | XJR

car_horsepower | 390

car_manufactuer | Ford

car_model | Mustang GT

car_horsepower | 400

What I want:

car_manufacturer | car_model | car_horsepower

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

Jaguar | XJR | 390

Ford | Mustang GT | 400Complete a primary key

car_manufacturer | Jaguar
car_manufactuer | Ford
car_model | XJR
car_model | Mustang GT
car_horsepower | 400
car_horsepower | 390

1 | car_manufacturer | Jaguar
1 | car_model | XJR
1 | car_horsepower | 390
2 | car_manufactuer | Ford
2 | car_model | Mustang GT
2 | car_horsepower | 400

and add an ordering table of value names

1 | car_manufacturer
2 | car_model
3 | car_horsepower|||create table dbo.ValuesTable(GroupId int,ValueName varchar(50),Value varchar(100) not null,primary key (GroupID,ValueName))
GO
insert dbo.ValuesTable (GroupId,ValueName,Value) values (1,'car_manufacturer','Jaguar')
insert dbo.ValuesTable (GroupId,ValueName,Value) values (1,'car_model','XJR')
insert dbo.ValuesTable (GroupId,ValueName,Value) values (1,'car_horsepower','390')
insert dbo.ValuesTable (GroupId,ValueName,Value) values (2,'car_manufacturer','Ford')
insert dbo.ValuesTable (GroupId,ValueName,Value) values (2,'car_model','Mustang GT')
insert dbo.ValuesTable (GroupId,ValueName,Value) values (2,'car_horsepower','400')
GO

--without ordering
create function dbo.ufn_ConcatOrderingDistinct()
returns varchar(8000)
with schemabinding
as
begin
declare @.Res varchar(8000)
select @.Res = isnull(@.Res+',','')+'"'+ValueName+'"=max(case when d.ValueName = '''+ValueName+''''+' then d.Value end)'
from
(
select distinct ValueName
from dbo.ValuesTable
) x
return @.Res
end
GO
declare @.Exec varchar(8000)
set @.Exec='select '+dbo.ufn_ConcatOrderingDistinct()+'from dbo.ValuesTable d group by d.GroupId'
exec(@.Exec)
GO

--with ordering
create table dbo.OrderingTable(OrderId int primary key,ValueName varchar(50) not null)
GO
insert dbo.OrderingTable(OrderId,ValueName) values (1,'car_manufacturer')
insert dbo.OrderingTable(OrderId,ValueName) values (2,'car_model')
insert dbo.OrderingTable(OrderId,ValueName) values (3,'car_horsepower' )
GO
create function dbo.ufn_ConcatOrderingTable()
returns varchar(8000)
with schemabinding
as
begin
declare @.Res varchar(8000)
select @.Res = isnull(@.Res+',','')+'"'+ValueName+'"=max(case when d.ValueName = '''+ValueName+''''+' then d.Value end)'
from dbo.OrderingTable
order by OrderId
return @.Res
end
GO
declare @.Exec varchar(8000)
set @.Exec='select '+dbo.ufn_ConcatOrderingTable()+'from dbo.ValuesTable d group by d.GroupId'
exec(@.Exec)
GO|||create table #a (id int, fld varchar(40), val varchar(20))
insert #a select 1, 'car_manufacturer', 'Jaguar'
insert #a select 1, 'car_model', 'XJR'
insert #a select 1, 'car_horsepower', '390'
insert #a select 2, 'car_manufacturer', 'Ford'
insert #a select 2, 'car_model', 'Mustang GT'
insert #a select 2, 'car_horsepower', '400'
insert #a select 2, 'Doors', '4'

declare @.sql varchar(8000)

select @.sql = coalesce(@.sql+',','') + fld + ' = (select val from #a a1 where a1.fld = ''' + fld + ''' and a1.id = #a.id)'
from (select distinct fld from #a) as a
exec ('select id,' + @.sql+ ' from #a group by id')

drop table #a

gives
id car_horsepower car_manufacturer car_model Doors
---- ------- ------- ------- -------
1 390 Jaguar XJR NULL
2 400 Ford Mustang GT 4

You will have to split up the string if you have too many fields to fit in 8000 chars.

Pivot fields into a single column

I have a row that looks like this

Year 2006 2007 2008 2009.....etc.

I want a select statement that will get me this

Year

2006

2007

2008

2009

etc.

Try:

Code Snippet

createtable #t (

c1 intnotnull,

c2 intnotnull,

c3 intnotnull,

c4 intnotnull

)

insertinto #t values(2006, 2007, 2008, 2009)

select

case t2.c1

when 1 then t1.c1

when 2 then t1.c2

when 3 then t1.c3

when 4 then t1.c4

endas [Year]

from

#t as t1

crossjoin

(select 1 as [c1] union allselect 2 union allselect 3 union allselect 4)as t2

-- 2005

select

[Year]

from

(select c1, c2, c3, c4from #t)as p

unpivot

([Year] for [c] in(c1, c2, c3, c4))as unpvt

droptable #t

AMB

|||Thanks, but I can't use create table or insert. Appreciate the effort though.|||

WVUProgramer wrote:

Thanks, but I can't use create table or insert. Appreciate the effort though.

You don't need to - he used the create table and insert to generate sample data so that his entire code example would run in its entirety.

You just need to use the select part of it (possibly modified to meet your exact requirements)|||Okay, thanks.|||

For the first select, it says there's no from clause in the select statement following the cross join.

In the second one, it says unable to parse text.

I plugged in my field names (which are f1 through f4) and my table name where the t# is

Perhaps i'm confused about the and the [Year].

|||

If you copy and paste the script, then execute it, you will see the result is the same you are expecting in your original post.

Can you post the statement you are trying to execute?

AMB

|||

select

case t2.F1

when 1 then t1.F1

when 2 then t1.F2

when 3 then t1.F3

when 4 then t1.F4

end as [Year]

from

table1 as t1

cross join

(select 1 as [F1] union all select 2 union all select 3 union all select 4) as t2

My table name is table1 my fields are F1, F2, F3, F4

Error Message: Error in SELECT clause: expression near 'UNION'.
Missing FROM clause.
Unable to parse query text.

|||

Where are you testing the script: (which application)?

AMB