creating a Smart Average

Hello all,

I need to create "Smart Average" calculating sales following way:

Average of the "active months" of the customer.

For instance, if we're in November, we'll take the first month this customer bought (for instance October) and we'll create an average ([sales for October – November]/2)

How do I accomplish this in MDX?

Thanks,

Liran

Liran

You need first to find the first month when customer bought something. Unfortunately FirstNonEmpty semiadditive measure won't help here, since it will return the measure value, not the member on Time dimension itself. So you will have to do it in MDX - something like this:

Avg(NonEmpty(Date.Month.Month.MEMBERS, Measures.Sales).Item(0):Date.Month.CurrentMember, Measures.Sales)

HTH

Mosha (http://www.mosha.com/msolap)

sql

Creating a heirarchical output from SQL statement

This may be a basic question, but defining anything other than a cursor
is preffered.

I have, as an example, 2 tables. One with customer data (addresses,
phones, etc), the other is a listing of all 50 states (a cross reference
for short state alias to long state name, i.e. FL - Florida, etc...).

I want to sort the out put by state long name, and show each customer in
the state ... BUT ...
the output needs to be like so:

Florida
ABC,Inc Address1 City, State Zip, other Info
Dummy Corp Address1 City, State Zip, other Info
...
Georgia
XYZ, Inc Address1 City, State Zip, other Info
...
etc ...

This is a basic heirarchical listing. Can this be done with a single
T-SQL statement or are cursors needed?

Thanks in advance.

"Excellence is achieved through 1% inspiration and 99% perspiration." A.
Einstein

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!>> the output needs to be like so: ... <<

That is a report; SQL is not a report writer; you need to do this in
the front end with a report writer. This is the whole idea of
client/server architecture.

Creating a CE db at the desktop PC

Hello,

Is it possible to create a .SDF file at the develop machine?I want to write an exe which creates a sdf.So, my customer will be able to create a sdf and import this sdf to the device.

Thanks.

Hi xyzt,

Yes, you can create the .sdf either by writing an application (by calling the engine's CreateDatabase function, and scripting the schema), or with SQL Server Management Studio. You could then deliver that .sdf file to the device in your installation, or some other way.

Either way you do it, people seem to have the best results with scripting the database schema - I've heard the designers in SQL Server Management Studio need a little work.

Good luck!

Creating a calculated measure that filters

Hi,

Can I create a calculated member that will filter out based on a certain dimension? eg, I have a customer dimension and a sales cube. The sales cube has the sales count measure. Can I have a calculated measure that will give me the sales count for all customers that are flagged as 'New'? That way, I have a total count and a New customer count.

Thanks,

Brian

This should be very easy to do in MDX if you have attribute NewCustomer in the Customer dimension. The expression then would be something like

(Measures.[Sales Count], Customer.NewCustomer.[true])

|||

Thanks Mosha, indeed it is easy! I'd like to expand on this.

So, by adding a new calculated member, (Measures.[Sales Count], Customer.NewCustomer.[true]), it will give me the counts of new customers. Is is possible to now also filter on, say, customerStatus as well? eg.

My Sales cube also has a CustomerType dimension. How can I expand the filter above to also filter on the CustomerType dimension (say, customertype = E).

I'm still trying to get my head around MDX :)

Thanks!

Brian

|||I wouldn't call this "expansion" of the filter, it is more of "restricting" the filter further. Yes, you can add as many hierarchy members to the filter as you want. I recommend going over first few chapters of my "Fast Track to MDX" book to get yourself on the MDX track fast...

Createing tables

I am trying to make a customer management database and need some help. I have two tables, customer and equipment. I want to have a unique value that is in both tables such as custid. I feel I need this to be able to search for all equipment belonging to a specific customer. Help please.Not sure what exactly you are asking, your approach of having a key that identified a customer and of using that the identify the equipment sounds like a fine one.|||

If an equipment can only belong to one customer this would be something like this:

Customers
=======

CustomeriD
(OtherColumns)

Equipment
=========
EquipmentD
(OtherColumns)
CustomerID

If one Equipment can belong to more than one customer you have a N-M relation:

Customers
=======
CustomeriD
(OtherColumns)

Equipment
=========
EquipmentD
(OtherColumns)

CustomerEquipment
================
EquipmentD
CustomeriD

HTH, jens Suessmeyer.

http://www.sqlserver2005.de

Create View error

I have one customer's server that when I try to create on particular
view I get the error:
Could not find database ID 104. Database may not be activated yet of may
be in transition.
All other views can be created with no error using the create view
command. And, in query analyzer, the view itself runs with no issues.
Only when I put:
CREATE VIEW dbo.rSOPacking
AS
SELECT ...
That is when it errors out. I have tried not using dbo., using sa., and
another admin user, inxsql. All have the same error.
Help. Any ideas. View is below:
CREATE VIEW rSOPacking
AS
SELECT h.*, d.*, c.*, l.*, i.*, CASE WHEN h.ohdr_packc <> 0 THEN
h.ohdr_comments ELSE ' ' END hdr_comments,
CASE WHEN d.odtl_packc <> 0 THEN d.odtl_comments ELSE ' ' END
dtl_comments, t.ccod_description AS terms_desc,
s.ccod_description AS slmn_name, v.ccod_description AS via_desc,
f.ccod_description AS frght_desc,
h.ohdr_shipcity + ', ' + h.ohdr_shipstate + ' ' + h.ohdr_shipzip AS
ship3,
c.ccst_city + ', ' + c.ccst_state + ' ' + c.ccst_zip AS cust3,
(i.iitm_weight * d.odtl_orderqty * d.odtl_qfactor) AS ext_weight,
CASE h.ohdr_ordertype WHEN 'C' THEN 'CREDIT MEMO' WHEN 'T' THEN
'TRANSFER' WHEN 'F' THEN 'ITEM FABRICATION' WHEN 'R' THEN 'REP ORDER'
WHEN 'D' THEN 'DIRECT SALE' WHEN 'L' THEN 'RENTAL ORDER' ELSE 'STANDARD
ORDER' END type_text, p.opck_id, opck_batch, opck_order, opck_line, CASE
WHEN iitm_type='G' THEN d.odtl_orderqty-odtl_backorderqty WHEN
iitm_type='N' THEN ISNULL(p.opck_packqty, d.odtl_shipqty) ELSE
ISNULL(p.opck_packqty, 0) END opck_packqty, opck_complete,
opck_unitcost, opck_packprinted, ol.*, ser.*, RTRIM(h.ohdr_order) + '-'
+ LTRIM(RIGHT('0000' + CONVERT(varchar(10),ohdr_shipcnt), 4)) AS
ordership, fob.ccod_description AS fob_desc, CASE WHEN
p.opck_packqty>d.odtl_orderqty THEN 0 WHEN p.opck_packqty IS NULL AND
iitm_type<>'G' THEN d.odtl_orderqty WHEN iitm_type='G' THEN
odtl_backorderqty WHEN opck_complete=1 THEN 0 ELSE
d.odtl_orderqty-p.opck_packqty END bo_qty,
CAST(d.odtl_unitprice*p.opck_packqty*d.odtl_factor AS decimal(19,2)) AS
ext_price, si.*, CASE WHEN h.ohdr_shipcomp<>0 THEN 'SHIP COMPLETE' END
ship_complete, lc.*, lc.icod_city + ', ' + lc.icod_state + ' ' +
lc.icod_zip AS locn3, z.ccod_description AS zone_desc,
ic.icod_description AS condition_desc, RTRIM(CAST(oinv_batch AS
char(3))) + '-' + oinv_order AS ups_order, CASE WHEN CAST(i.iitm_bagqty
AS int)<>0 THEN CAST(p.opck_packqty/i.iitm_bagqty AS int) END num_case,
CASE WHEN CAST(i.iitm_bagqty AS int)<>0 THEN CAST(p.opck_packqty AS int)
% CAST(i.iitm_bagqty AS int) END num_single, mf.icod_name AS mfg_name,
it.*, b.ccst_name AS bill_name, b.ccst_address1 AS bill_add1,
b.ccst_address2 AS bill_add2, b.ccst_city AS bill_city, b.ccst_state AS
bill_state, b.ccst_zip AS bill_zip, b.ccst_city + ', ' + b.ccst_state +
' ' + b.ccst_zip AS bill3, b.ccst_country AS bill_country,
dbo.fARSalesmanName(c.ccst_salesman) AS cust_salesman, ci.icod_custdesc,
CASE WHEN iitm_type='A' THEN odtl_groupprice ELSE odtl_unitprice END
xunitprice, icat.icod_description AS category_desc, ISNULL((SELECT
SUM(obox_weight) FROM SOOrderBox WHERE obox_order=oinv_order AND
obox_batch=oinv_batch),0) AS shipped_wgt
FROM SOOrderHeader AS h
INNER JOIN SOOrderDetail AS d ON h.ohdr_order=d.odtl_order
LEFT JOIN SOOrderPack AS p ON h.ohdr_order=p.opck_order AND
d.odtl_line=p.opck_line
LEFT JOIN SOInvoice AS si ON h.ohdr_order=si.oinv_order AND
p.opck_batch=si.oinv_batch
LEFT JOIN SOOrderLot AS ol ON h.ohdr_order=ol.olot_order AND
d.odtl_line=ol.olot_dline AND p.opck_batch=ol.olot_batch
LEFT JOIN ARCustomer AS c ON h.ohdr_customer=c.ccst_customer AND
ohdr_ordertype<>'F'
LEFT JOIN ARCustomer AS b ON h.ohdr_billto=b.ccst_customer AND
ohdr_ordertype<>'F'
LEFT JOIN ICItem AS I ON i.iitm_item=d.odtl_item
LEFT JOIN vICLocation AS l ON d.odtl_location=l.iloc_location AND
d.odtl_item=l.iloc_item
LEFT JOIN ARTermsCode AS t ON h.ohdr_terms=t.ccod_terms
LEFT JOIN ARSalesman AS s ON h.ohdr_salesman=s.ccod_salesman
LEFT JOIN ARShipVia AS v ON si.oinv_shipvia=v.ccod_shipvia
LEFT JOIN ARFreight AS f ON si.oinv_freight=f.ccod_freight
LEFT JOIN ARFOB AS fob ON h.ohdr_fob=fob.ccod_fob
LEFT JOIN ICSerial AS ser ON d.odtl_item=ser.iser_item AND
h.ohdr_order=ser.iser_soorder AND
dbo.fUTShortDate(si.oinv_packdate)=dbo.fUTShortDate(ser.iser_outdate)
LEFT JOIN ICLocnCode AS lc ON d.odtl_location=lc.icod_location
LEFT JOIN ARZone AS z ON h.ohdr_zone=z.ccod_zone
LEFT JOIN ICCondition AS ic ON d.odtl_condition=ic.icod_condition
LEFT JOIN ICLot AS it ON ol.olot_lot=it.ilot_lot
LEFT JOIN ICManufacturer AS mf ON it.ilot_mfg=mf.icod_mfg
LEFT JOIN ICCustItem AS ci ON h.ohdr_customer=ci.icod_customer AND
d.odtl_item=ci.icod_item
LEFT JOIN ICCategory AS icat ON i.iitm_category=icat.icod_category
Darin
*** Sent via Developersdex http://www.codecomments.com ***Try creating the sp without specifying a schema/owner. If that works, you ca
n
use sp_changeobjectowner to assign a new owner.
I think you are getting a messge saying that the actual userid/login id does
not exist.
--
Joseph R.P. Maloney, CSP,CCP,CDP
"Darin" wrote:

> I have one customer's server that when I try to create on particular
> view I get the error:
> Could not find database ID 104. Database may not be activated yet of may
> be in transition.
> All other views can be created with no error using the create view
> command. And, in query analyzer, the view itself runs with no issues.
> Only when I put:
> CREATE VIEW dbo.rSOPacking
> AS
> SELECT ...
> That is when it errors out. I have tried not using dbo., using sa., and
> another admin user, inxsql. All have the same error.
> Help. Any ideas. View is below:
> CREATE VIEW rSOPacking
> AS
> SELECT h.*, d.*, c.*, l.*, i.*, CASE WHEN h.ohdr_packc <> 0 THEN
> h.ohdr_comments ELSE ' ' END hdr_comments,
> CASE WHEN d.odtl_packc <> 0 THEN d.odtl_comments ELSE ' ' END
> dtl_comments, t.ccod_description AS terms_desc,
> s.ccod_description AS slmn_name, v.ccod_description AS via_desc,
> f.ccod_description AS frght_desc,
> h.ohdr_shipcity + ', ' + h.ohdr_shipstate + ' ' + h.ohdr_shipzip AS
> ship3,
> c.ccst_city + ', ' + c.ccst_state + ' ' + c.ccst_zip AS cust3,
> (i.iitm_weight * d.odtl_orderqty * d.odtl_qfactor) AS ext_weight,
> CASE h.ohdr_ordertype WHEN 'C' THEN 'CREDIT MEMO' WHEN 'T' THEN
> 'TRANSFER' WHEN 'F' THEN 'ITEM FABRICATION' WHEN 'R' THEN 'REP ORDER'
> WHEN 'D' THEN 'DIRECT SALE' WHEN 'L' THEN 'RENTAL ORDER' ELSE 'STANDARD
> ORDER' END type_text, p.opck_id, opck_batch, opck_order, opck_line, CASE
> WHEN iitm_type='G' THEN d.odtl_orderqty-odtl_backorderqty WHEN
> iitm_type='N' THEN ISNULL(p.opck_packqty, d.odtl_shipqty) ELSE
> ISNULL(p.opck_packqty, 0) END opck_packqty, opck_complete,
> opck_unitcost, opck_packprinted, ol.*, ser.*, RTRIM(h.ohdr_order) + '-'
> + LTRIM(RIGHT('0000' + CONVERT(varchar(10),ohdr_shipcnt), 4)) AS
> ordership, fob.ccod_description AS fob_desc, CASE WHEN
> p.opck_packqty>d.odtl_orderqty THEN 0 WHEN p.opck_packqty IS NULL AND
> iitm_type<>'G' THEN d.odtl_orderqty WHEN iitm_type='G' THEN
> odtl_backorderqty WHEN opck_complete=1 THEN 0 ELSE
> d.odtl_orderqty-p.opck_packqty END bo_qty,
> CAST(d.odtl_unitprice*p.opck_packqty*d.odtl_factor AS decimal(19,2)) AS
> ext_price, si.*, CASE WHEN h.ohdr_shipcomp<>0 THEN 'SHIP COMPLETE' END
> ship_complete, lc.*, lc.icod_city + ', ' + lc.icod_state + ' ' +
> lc.icod_zip AS locn3, z.ccod_description AS zone_desc,
> ic.icod_description AS condition_desc, RTRIM(CAST(oinv_batch AS
> char(3))) + '-' + oinv_order AS ups_order, CASE WHEN CAST(i.iitm_bagqty
> AS int)<>0 THEN CAST(p.opck_packqty/i.iitm_bagqty AS int) END num_case,
> CASE WHEN CAST(i.iitm_bagqty AS int)<>0 THEN CAST(p.opck_packqty AS int)
> % CAST(i.iitm_bagqty AS int) END num_single, mf.icod_name AS mfg_name,
> it.*, b.ccst_name AS bill_name, b.ccst_address1 AS bill_add1,
> b.ccst_address2 AS bill_add2, b.ccst_city AS bill_city, b.ccst_state AS
> bill_state, b.ccst_zip AS bill_zip, b.ccst_city + ', ' + b.ccst_state +
> ' ' + b.ccst_zip AS bill3, b.ccst_country AS bill_country,
> dbo.fARSalesmanName(c.ccst_salesman) AS cust_salesman, ci.icod_custdesc,
> CASE WHEN iitm_type='A' THEN odtl_groupprice ELSE odtl_unitprice END
> xunitprice, icat.icod_description AS category_desc, ISNULL((SELECT
> SUM(obox_weight) FROM SOOrderBox WHERE obox_order=oinv_order AND
> obox_batch=oinv_batch),0) AS shipped_wgt
> FROM SOOrderHeader AS h
> INNER JOIN SOOrderDetail AS d ON h.ohdr_order=d.odtl_order
> LEFT JOIN SOOrderPack AS p ON h.ohdr_order=p.opck_order AND
> d.odtl_line=p.opck_line
> LEFT JOIN SOInvoice AS si ON h.ohdr_order=si.oinv_order AND
> p.opck_batch=si.oinv_batch
> LEFT JOIN SOOrderLot AS ol ON h.ohdr_order=ol.olot_order AND
> d.odtl_line=ol.olot_dline AND p.opck_batch=ol.olot_batch
> LEFT JOIN ARCustomer AS c ON h.ohdr_customer=c.ccst_customer AND
> ohdr_ordertype<>'F'
> LEFT JOIN ARCustomer AS b ON h.ohdr_billto=b.ccst_customer AND
> ohdr_ordertype<>'F'
> LEFT JOIN ICItem AS I ON i.iitm_item=d.odtl_item
> LEFT JOIN vICLocation AS l ON d.odtl_location=l.iloc_location AND
> d.odtl_item=l.iloc_item
> LEFT JOIN ARTermsCode AS t ON h.ohdr_terms=t.ccod_terms
> LEFT JOIN ARSalesman AS s ON h.ohdr_salesman=s.ccod_salesman
> LEFT JOIN ARShipVia AS v ON si.oinv_shipvia=v.ccod_shipvia
> LEFT JOIN ARFreight AS f ON si.oinv_freight=f.ccod_freight
> LEFT JOIN ARFOB AS fob ON h.ohdr_fob=fob.ccod_fob
> LEFT JOIN ICSerial AS ser ON d.odtl_item=ser.iser_item AND
> h.ohdr_order=ser.iser_soorder AND
> dbo.fUTShortDate(si.oinv_packdate)=dbo.fUTShortDate(ser.iser_outdate)
> LEFT JOIN ICLocnCode AS lc ON d.odtl_location=lc.icod_location
> LEFT JOIN ARZone AS z ON h.ohdr_zone=z.ccod_zone
> LEFT JOIN ICCondition AS ic ON d.odtl_condition=ic.icod_condition
> LEFT JOIN ICLot AS it ON ol.olot_lot=it.ilot_lot
> LEFT JOIN ICManufacturer AS mf ON it.ilot_mfg=mf.icod_mfg
> LEFT JOIN ICCustItem AS ci ON h.ohdr_customer=ci.icod_customer AND
> d.odtl_item=ci.icod_item
> LEFT JOIN ICCategory AS icat ON i.iitm_category=icat.icod_category
>
> Darin
> *** Sent via Developersdex http://www.codecomments.com ***
>|||I bet you that if you remove this line
AND
dbo.fUTShortDate(si.oinv_packdate)=dbo.fUTShortDate(ser.iser_outdate)
the problem will go away
I used to have the same problem with UDF and subqueries a while back
just comment it out for fun
http://sqlservercode.blogspot.com/|||By that I assume you mean don't put dbo. before the veiw name, I tried
that and still have the same error message.
Darin
*** Sent via Developersdex http://www.codecomments.com ***|||what happens if you just run the select:
SELECT h.*, d.*, c.*, l.*, i.*, CASE WHEN h.ohdr_packc <> 0 THEN
h.ohdr_comments ELSE ' ' END hdr_comments,
CASE WHEN d.odtl_packc <> 0 THEN d.odtl_comments ELSE ' ' END
dtl_comments, t.ccod_description AS terms_desc,
...
and (1=0)|||It worked fine if I just did the select statement, it returned all of
the rows.
Interestingly enough, I removed one left join ICSerial and the
corresponding area in the select statement and the create view worked
fine. I also noticed this customer isn't on SP4 of SQL Server 2000.
Does that info help anyone in figuring out exactly why the error?
Darin
*** Sent via Developersdex http://www.codecomments.com ***|||I think it has something to do with the nestlevel and number of joins
combined with the UDF
I used to have the same problem however when I moved the UDF out of the
query it was fine
And it worked on QA but not on production (same setup)
Maybe dependencies have something to do with it also
http://sqlservercode.blogspot.com/|||Once I upgraded the server to SQL Server 2000 SP4 it worked fine. But
that sucks because we have customers that aren't up on SP4, so I have to
figure out how to do that.
Darin
*** Sent via Developersdex http://www.codecomments.com ***

Create View error

I have one customer's server that when I try to create on particular
view I get the error:
Could not find database ID 104. Database may not be activated yet of may
be in transition.
All other views can be created with no error using the create view
command. And, in query analyzer, the view itself runs with no issues.
Only when I put:
CREATE VIEW dbo.rSOPacking
AS
SELECT ...
That is when it errors out. I have tried not using dbo., using sa., and
another admin user, inxsql. All have the same error.
Help. Any ideas. View is below:
CREATE VIEW rSOPacking
AS
SELECT h.*, d.*, c.*, l.*, i.*, CASE WHEN h.ohdr_packc <> 0 THEN
h.ohdr_comments ELSE ' ' END hdr_comments,
CASE WHEN d.odtl_packc <> 0 THEN d.odtl_comments ELSE ' ' END
dtl_comments, t.ccod_description AS terms_desc,
s.ccod_description AS slmn_name, v.ccod_description AS via_desc,
f.ccod_description AS frght_desc,
h.ohdr_shipcity + ', ' + h.ohdr_shipstate + ' ' + h.ohdr_shipzip AS
ship3,
c.ccst_city + ', ' + c.ccst_state + ' ' + c.ccst_zip AS cust3,
(i.iitm_weight * d.odtl_orderqty * d.odtl_qfactor) AS ext_weight,
CASE h.ohdr_ordertype WHEN 'C' THEN 'CREDIT MEMO' WHEN 'T' THEN
'TRANSFER' WHEN 'F' THEN 'ITEM FABRICATION' WHEN 'R' THEN 'REP ORDER'
WHEN 'D' THEN 'DIRECT SALE' WHEN 'L' THEN 'RENTAL ORDER' ELSE 'STANDARD
ORDER' END type_text, p.opck_id, opck_batch, opck_order, opck_line, CASE
WHEN iitm_type='G' THEN d.odtl_orderqty-odtl_backorderqty WHEN
iitm_type='N' THEN ISNULL(p.opck_packqty, d.odtl_shipqty) ELSE
ISNULL(p.opck_packqty, 0) END opck_packqty, opck_complete,
opck_unitcost, opck_packprinted, ol.*, ser.*, RTRIM(h.ohdr_order) + '-'
+ LTRIM(RIGHT('0000' + CONVERT(varchar(10),ohdr_shipcnt), 4)) AS
ordership, fob.ccod_description AS fob_desc, CASE WHEN
p.opck_packqty>d.odtl_orderqty THEN 0 WHEN p.opck_packqty IS NULL AND
iitm_type<>'G' THEN d.odtl_orderqty WHEN iitm_type='G' THEN
odtl_backorderqty WHEN opck_complete=1 THEN 0 ELSE
d.odtl_orderqty-p.opck_packqty END bo_qty,
CAST(d.odtl_unitprice*p.opck_packqty*d.odtl_factor AS decimal(19,2)) AS
ext_price, si.*, CASE WHEN h.ohdr_shipcomp<>0 THEN 'SHIP COMPLETE' END
ship_complete, lc.*, lc.icod_city + ', ' + lc.icod_state + ' ' +
lc.icod_zip AS locn3, z.ccod_description AS zone_desc,
ic.icod_description AS condition_desc, RTRIM(CAST(oinv_batch AS
char(3))) + '-' + oinv_order AS ups_order, CASE WHEN CAST(i.iitm_bagqty
AS int)<>0 THEN CAST(p.opck_packqty/i.iitm_bagqty AS int) END num_case,
CASE WHEN CAST(i.iitm_bagqty AS int)<>0 THEN CAST(p.opck_packqty AS int)
% CAST(i.iitm_bagqty AS int) END num_single, mf.icod_name AS mfg_name,
it.*, b.ccst_name AS bill_name, b.ccst_address1 AS bill_add1,
b.ccst_address2 AS bill_add2, b.ccst_city AS bill_city, b.ccst_state AS
bill_state, b.ccst_zip AS bill_zip, b.ccst_city + ', ' + b.ccst_state +
' ' + b.ccst_zip AS bill3, b.ccst_country AS bill_country,
dbo.fARSalesmanName(c.ccst_salesman) AS cust_salesman, ci.icod_custdesc,
CASE WHEN iitm_type='A' THEN odtl_groupprice ELSE odtl_unitprice END
xunitprice, icat.icod_description AS category_desc, ISNULL((SELECT
SUM(obox_weight) FROM SOOrderBox WHERE obox_order=oinv_order AND
obox_batch=oinv_batch),0) AS shipped_wgt
FROM SOOrderHeader AS h
INNER JOIN SOOrderDetail AS d ON h.ohdr_order=d.odtl_order
LEFT JOIN SOOrderPack AS p ON h.ohdr_order=p.opck_order AND
d.odtl_line=p.opck_line
LEFT JOIN SOInvoice AS si ON h.ohdr_order=si.oinv_order AND
p.opck_batch=si.oinv_batch
LEFT JOIN SOOrderLot AS ol ON h.ohdr_order=ol.olot_order AND
d.odtl_line=ol.olot_dline AND p.opck_batch=ol.olot_batch
LEFT JOIN ARCustomer AS c ON h.ohdr_customer=c.ccst_customer AND
ohdr_ordertype<>'F'
LEFT JOIN ARCustomer AS b ON h.ohdr_billto=b.ccst_customer AND
ohdr_ordertype<>'F'
LEFT JOIN ICItem AS I ON i.iitm_item=d.odtl_item
LEFT JOIN vICLocation AS l ON d.odtl_location=l.iloc_location AND
d.odtl_item=l.iloc_item
LEFT JOIN ARTermsCode AS t ON h.ohdr_terms=t.ccod_terms
LEFT JOIN ARSalesman AS s ON h.ohdr_salesman=s.ccod_salesman
LEFT JOIN ARShipVia AS v ON si.oinv_shipvia=v.ccod_shipvia
LEFT JOIN ARFreight AS f ON si.oinv_freight=f.ccod_freight
LEFT JOIN ARFOB AS fob ON h.ohdr_fob=fob.ccod_fob
LEFT JOIN ICSerial AS ser ON d.odtl_item=ser.iser_item AND
h.ohdr_order=ser.iser_soorder AND
dbo.fUTShortDate(si.oinv_packdate)=dbo.fUTShortDate(ser.iser_outdate)
LEFT JOIN ICLocnCode AS lc ON d.odtl_location=lc.icod_location
LEFT JOIN ARZone AS z ON h.ohdr_zone=z.ccod_zone
LEFT JOIN ICCondition AS ic ON d.odtl_condition=ic.icod_condition
LEFT JOIN ICLot AS it ON ol.olot_lot=it.ilot_lot
LEFT JOIN ICManufacturer AS mf ON it.ilot_mfg=mf.icod_mfg
LEFT JOIN ICCustItem AS ci ON h.ohdr_customer=ci.icod_customer AND
d.odtl_item=ci.icod_item
LEFT JOIN ICCategory AS icat ON i.iitm_category=icat.icod_category
Darin
*** Sent via Developersdex http://www.developersdex.com ***Try creating the sp without specifying a schema/owner. If that works, you can
use sp_changeobjectowner to assign a new owner.
I think you are getting a messge saying that the actual userid/login id does
not exist.
--
Joseph R.P. Maloney, CSP,CCP,CDP
"Darin" wrote:
> I have one customer's server that when I try to create on particular
> view I get the error:
> Could not find database ID 104. Database may not be activated yet of may
> be in transition.
> All other views can be created with no error using the create view
> command. And, in query analyzer, the view itself runs with no issues.
> Only when I put:
> CREATE VIEW dbo.rSOPacking
> AS
> SELECT ...
> That is when it errors out. I have tried not using dbo., using sa., and
> another admin user, inxsql. All have the same error.
> Help. Any ideas. View is below:
> CREATE VIEW rSOPacking
> AS
> SELECT h.*, d.*, c.*, l.*, i.*, CASE WHEN h.ohdr_packc <> 0 THEN
> h.ohdr_comments ELSE ' ' END hdr_comments,
> CASE WHEN d.odtl_packc <> 0 THEN d.odtl_comments ELSE ' ' END
> dtl_comments, t.ccod_description AS terms_desc,
> s.ccod_description AS slmn_name, v.ccod_description AS via_desc,
> f.ccod_description AS frght_desc,
> h.ohdr_shipcity + ', ' + h.ohdr_shipstate + ' ' + h.ohdr_shipzip AS
> ship3,
> c.ccst_city + ', ' + c.ccst_state + ' ' + c.ccst_zip AS cust3,
> (i.iitm_weight * d.odtl_orderqty * d.odtl_qfactor) AS ext_weight,
> CASE h.ohdr_ordertype WHEN 'C' THEN 'CREDIT MEMO' WHEN 'T' THEN
> 'TRANSFER' WHEN 'F' THEN 'ITEM FABRICATION' WHEN 'R' THEN 'REP ORDER'
> WHEN 'D' THEN 'DIRECT SALE' WHEN 'L' THEN 'RENTAL ORDER' ELSE 'STANDARD
> ORDER' END type_text, p.opck_id, opck_batch, opck_order, opck_line, CASE
> WHEN iitm_type='G' THEN d.odtl_orderqty-odtl_backorderqty WHEN
> iitm_type='N' THEN ISNULL(p.opck_packqty, d.odtl_shipqty) ELSE
> ISNULL(p.opck_packqty, 0) END opck_packqty, opck_complete,
> opck_unitcost, opck_packprinted, ol.*, ser.*, RTRIM(h.ohdr_order) + '-'
> + LTRIM(RIGHT('0000' + CONVERT(varchar(10),ohdr_shipcnt), 4)) AS
> ordership, fob.ccod_description AS fob_desc, CASE WHEN
> p.opck_packqty>d.odtl_orderqty THEN 0 WHEN p.opck_packqty IS NULL AND
> iitm_type<>'G' THEN d.odtl_orderqty WHEN iitm_type='G' THEN
> odtl_backorderqty WHEN opck_complete=1 THEN 0 ELSE
> d.odtl_orderqty-p.opck_packqty END bo_qty,
> CAST(d.odtl_unitprice*p.opck_packqty*d.odtl_factor AS decimal(19,2)) AS
> ext_price, si.*, CASE WHEN h.ohdr_shipcomp<>0 THEN 'SHIP COMPLETE' END
> ship_complete, lc.*, lc.icod_city + ', ' + lc.icod_state + ' ' +
> lc.icod_zip AS locn3, z.ccod_description AS zone_desc,
> ic.icod_description AS condition_desc, RTRIM(CAST(oinv_batch AS
> char(3))) + '-' + oinv_order AS ups_order, CASE WHEN CAST(i.iitm_bagqty
> AS int)<>0 THEN CAST(p.opck_packqty/i.iitm_bagqty AS int) END num_case,
> CASE WHEN CAST(i.iitm_bagqty AS int)<>0 THEN CAST(p.opck_packqty AS int)
> % CAST(i.iitm_bagqty AS int) END num_single, mf.icod_name AS mfg_name,
> it.*, b.ccst_name AS bill_name, b.ccst_address1 AS bill_add1,
> b.ccst_address2 AS bill_add2, b.ccst_city AS bill_city, b.ccst_state AS
> bill_state, b.ccst_zip AS bill_zip, b.ccst_city + ', ' + b.ccst_state +
> ' ' + b.ccst_zip AS bill3, b.ccst_country AS bill_country,
> dbo.fARSalesmanName(c.ccst_salesman) AS cust_salesman, ci.icod_custdesc,
> CASE WHEN iitm_type='A' THEN odtl_groupprice ELSE odtl_unitprice END
> xunitprice, icat.icod_description AS category_desc, ISNULL((SELECT
> SUM(obox_weight) FROM SOOrderBox WHERE obox_order=oinv_order AND
> obox_batch=oinv_batch),0) AS shipped_wgt
> FROM SOOrderHeader AS h
> INNER JOIN SOOrderDetail AS d ON h.ohdr_order=d.odtl_order
> LEFT JOIN SOOrderPack AS p ON h.ohdr_order=p.opck_order AND
> d.odtl_line=p.opck_line
> LEFT JOIN SOInvoice AS si ON h.ohdr_order=si.oinv_order AND
> p.opck_batch=si.oinv_batch
> LEFT JOIN SOOrderLot AS ol ON h.ohdr_order=ol.olot_order AND
> d.odtl_line=ol.olot_dline AND p.opck_batch=ol.olot_batch
> LEFT JOIN ARCustomer AS c ON h.ohdr_customer=c.ccst_customer AND
> ohdr_ordertype<>'F'
> LEFT JOIN ARCustomer AS b ON h.ohdr_billto=b.ccst_customer AND
> ohdr_ordertype<>'F'
> LEFT JOIN ICItem AS I ON i.iitm_item=d.odtl_item
> LEFT JOIN vICLocation AS l ON d.odtl_location=l.iloc_location AND
> d.odtl_item=l.iloc_item
> LEFT JOIN ARTermsCode AS t ON h.ohdr_terms=t.ccod_terms
> LEFT JOIN ARSalesman AS s ON h.ohdr_salesman=s.ccod_salesman
> LEFT JOIN ARShipVia AS v ON si.oinv_shipvia=v.ccod_shipvia
> LEFT JOIN ARFreight AS f ON si.oinv_freight=f.ccod_freight
> LEFT JOIN ARFOB AS fob ON h.ohdr_fob=fob.ccod_fob
> LEFT JOIN ICSerial AS ser ON d.odtl_item=ser.iser_item AND
> h.ohdr_order=ser.iser_soorder AND
> dbo.fUTShortDate(si.oinv_packdate)=dbo.fUTShortDate(ser.iser_outdate)
> LEFT JOIN ICLocnCode AS lc ON d.odtl_location=lc.icod_location
> LEFT JOIN ARZone AS z ON h.ohdr_zone=z.ccod_zone
> LEFT JOIN ICCondition AS ic ON d.odtl_condition=ic.icod_condition
> LEFT JOIN ICLot AS it ON ol.olot_lot=it.ilot_lot
> LEFT JOIN ICManufacturer AS mf ON it.ilot_mfg=mf.icod_mfg
> LEFT JOIN ICCustItem AS ci ON h.ohdr_customer=ci.icod_customer AND
> d.odtl_item=ci.icod_item
> LEFT JOIN ICCategory AS icat ON i.iitm_category=icat.icod_category
>
> Darin
> *** Sent via Developersdex http://www.developersdex.com ***
>|||I bet you that if you remove this line
AND
dbo.fUTShortDate(si.oinv_packdate)=dbo.fUTShortDate(ser.iser_outdate)
the problem will go away
I used to have the same problem with UDF and subqueries a while back
just comment it out for fun
http://sqlservercode.blogspot.com/