I am trying to copy and paste a view into a 'Custom' view for reporting purposes. I keep getting the error message "View definition includes no output columns or includes no items in the from clause". I did see where someone had a similar issue and was resolved. Can anyone help me?

Thanks

if it has an order by you need to make sure it has

"Select top 100 percent" in hte select clause.

without seeing the actual "create view" statement it's kinda hard to tell.

David

As you can see there is no Order By

I am creating a custom report from an integrated report which needs to be
copied and pasted and called "imrpt_po_receiving" with a "_custom" for the
custom created view.

SELECT imtbl_purchase_order.PKEY, CASE WHEN
((imtbl_purchase_order.po_prefix IS NULL OR
                      imtbl_purchase_order.po_prefix = ' ') AND
(imtbl_purchase_order.po_release_num IS NULL)) THEN CONVERT(NVARCHAR(25),
                      imtbl_purchase_order.po_num) ELSE CASE WHEN
((imtbl_purchase_order.po_prefix IS NOT NULL AND
imtbl_purchase_order.po_prefix <> ' ') AND
                      (imtbl_purchase_order.po_release_num IS NULL)) THEN
RTRIM(imtbl_purchase_order.po_prefix) + '-' + CONVERT(NVARCHAR(25),
                      imtbl_purchase_order.po_num) ELSE CASE WHEN
((imtbl_purchase_order.po_prefix IS NULL OR
                      imtbl_purchase_order.po_prefix = ' ') AND
(imtbl_purchase_order.po_release_num IS NOT NULL)) THEN
CONVERT(NVARCHAR(25),
                      imtbl_purchase_order.po_num) + ' - ' + REPLICATE('0',
4 - LEN(imtbl_purchase_order.po_release_num % 10000)) + CONVERT(NVARCHAR,
                      (imtbl_purchase_order.po_release_num % 10000)) ELSE
RTRIM(imtbl_purchase_order.po_prefix) + '-' + CONVERT(NVARCHAR(25),
                      imtbl_purchase_order.po_num) + ' - ' + REPLICATE('0',
4 - LEN(imtbl_purchase_order.po_release_num % 10000)) + CONVERT(NVARCHAR,
                      (imtbl_purchase_order.po_release_num % 10000)) END
END END AS po_id, imtbl_part.issue_uom AS issue_uom,
                      imtbl_po_order.order_uom AS order_uom,
imtbl_warehouse_location.id AS location_id, imtbl_po_receipt.receipt_date,
                      imtbl_po_receipt.receipt_issue_quantity,
imtbl_po_receipt.receipt_order_quantity,
imtbl_po_receipt.receipt_exchange_rate,
                      imtbl_po_receipt.receipt_cost,
imtbl_po_receipt.receipt_order_cost, CASE WHEN
(imtbl_po_receipt.receipt_cost IS NULL)
                      THEN COALESCE
((imtbl_po_receipt.receipt_order_quantity *
imtbl_po_receipt.receipt_order_cost), 0)
                      ELSE COALESCE
((imtbl_po_receipt.receipt_issue_quantity * imtbl_po_receipt.receipt_cost),
0) END AS extended_cost,
                      CASE WHEN (imtbl_po_order.item_type = 1) THEN 'Part'
ELSE CASE WHEN (imtbl_po_order.item_type = 2)
                      THEN 'Labor' ELSE 'Misc Cost' END END AS item_type,
imtbl_warehouse.id AS warehouse_id, imtbl_warehouse.description AS
warehouse_description,
                       imtbl_po_order.order_date,
imtbl_po_order.expected_date, imtbl_po_order.order_quantity, s.id AS
site_id, s.description AS site_description,
                      imtbl_contact.id AS vendor_id, imtbl_part.id AS
part_id, imtbl_misc_cost.id AS misc_cost_id, imtbl_craft.id AS craft_id
FROM imtbl_po_receipt imtbl_po_receipt LEFT OUTER JOIN
                      imtbl_po_order imtbl_po_order ON
imtbl_po_receipt.po_order = imtbl_po_order.pkey LEFT OUTER JOIN
                      imtbl_purchase_order imtbl_purchase_order ON
imtbl_po_order.purchase_order = imtbl_purchase_order.pkey LEFT OUTER JOIN
                      imtbl_warehouse_location imtbl_warehouse_location ON
imtbl_po_receipt.location = imtbl_warehouse_location.pkey LEFT OUTER JOIN
                      imtbl_warehouse imtbl_warehouse ON
imtbl_po_order.warehouse = imtbl_warehouse.pkey LEFT OUTER JOIN
                      imtbl_part imtbl_part ON imtbl_po_order.part =
imtbl_part.pkey LEFT OUTER JOIN
                      imtbl_contact imtbl_contact ON
imtbl_purchase_order.SUPPLIER = imtbl_contact.pkey LEFT OUTER JOIN
                      imtbl_craft imtbl_craft ON imtbl_po_order.craft =
imtbl_craft.pkey LEFT OUTER JOIN
                      imtbl_misc_cost imtbl_misc_cost ON
imtbl_po_order.misc_cost = imtbl_misc_cost.pkey LEFT OUTER JOIN
                      imtbl_part_component imtbl_part_component ON
imtbl_po_receipt.component = imtbl_part_component.pkey, imtbl_site s

Thanks David

ok, it seems it might be cut off. if you alias the table names that might shorten it up a bit. I did hit a wall once where my SQL string was too long which looking at this moster is a good posibility.

I thought it was cut off as well. But the integrated report works. And this view is in the 7 databases that we currently have. Just to let you know I am a novice SQL user, so not really sure what you mean by "Alias the tables". Sorry

do you have this in enterprise manager where it shows the tables up on top? if so you can right click on each one and set the properties of the alias. here is a simple alias example

SELECT
    SLS.SHCMPN, SLS.SHORNO, SLS.SHLINE, CM.CNAME
FROM tbl_CUSTMAST CM INNER JOIN tbl_AISAP100 SLS
ON CM.CMPNO = SLS.SHCMPN AND CM.CUSNO = SLS.SHCUSN

In the from clause after the table you state an alias
in this case CM and SLS, so in the select, join, and where clause you can use those instead of the full table name to designate which table the data comes from.

Hope this helps.


If not, you can e-mail me the full content of the view and I'll try and alias it for you.

Email is [email protected]