asked on

Are wildcards available for SELECT and FROM when creating a TSQL query

Hello all!

I am new to this field and am trying to generate a view that will pickup some data from all tables from my database appDB that start with PL01, but it seems I cannot use _ as a wildcard neither in the SELECT line nor in the FROM line. I have about 40 different PL01cc00 tables, where cc indicates the company code for each separate company.

Is that correct? And if these wildcards are not usable, is there a workaround I could use?

Also, is the syntax of my WHERE clause correct, as I never get that far?

Any assistance is greatly appreciated! Many txs

USE [appDB]
	GO
	/****** Object:  View [dbo].[SupCust_PL]    Script Date: 08/26/2009 15:44:59 ******/
	SET ANSI_NULLS ON
	GO
	SET QUOTED_IDENTIFIER ON
	GO
	ALTER VIEW [dbo].[SupCust_PL_TEST]
	AS
	SELECT     TOP (100) PERCENT appDB.dbo.PL01__00.PL01001 AS SupplierCode, appDB.dbo.PL01__00.PL01002 AS SupplierName, systemDB.dbo.ScaCompanies.CompanyCode as CompanyCode
	FROM         appDB.dbo.PL01__00, systemDB.dbo.ScaCompanies
	where		 systemDB.dbo.ScaCompanies.CompanyCode=substring([appDB.dbo.PL01__00],17,2)
    ORDER BY     CompanyCode, SupplierCode

Open in new window

Aneesh

Enclose the column/tablenames within [] , that will solve the issue with the wildcards like
SELECT [dbo].[table-name].[column-name]
FROM [dbo].[table-name]

also you can use the table/column aliases to make the query forbetter readablity

SELECT a.[column-name] as ColumnAlias
FROM [dbo].[table-name] as a --- a is a tableAlias

Aneesh

ALTER VIEW [dbo].[SupCust_PL_TEST]
AS
SELECT TOP (100) PERCENT pl1.PL01001 AS SupplierCode, pl1.PL01002 AS SupplierName, sc1.CompanyCode as CompanyCode
FROM appDB.dbo.PL01__00 Pl1
INNER JOIN systemDB.dbo.ScaCompanies sc1 on sc1.CompanyCode=substring(pl1.SupplierCode,17,2) ------------> check the column name here
ORDER BY CompanyCode, SupplierCode

DonWolfi007

ASKER

many thanks for the query cleanup, that really does make sense and clarity. But it does not solve my challenge of being able or not to use wildcard characters in the FROM portion of my query.

When I run your second solution, I get the following error: Invalid object name 'scalaDB.dbo.PL01__00'. This to me means that using _ as wildcard did not work. So I would be most gratefull if someone could tell me a workaround for this.

Txs

Aneesh

Theis no problem while using the _ in the table names, all you have to make sure is the table name is correct and exists in the 'scaleDB' database

hyphenpipe

I get what he is saying but don't have a solution.

He wants all the records from tables with the name that matches appDB.dbo.PL01__00 where the underscores might be various values.

Like from tables named:

appDB.dbo.PL011100
appDB.dbo.PL013300
appDB.dbo.PL015500

..all in one from statement.

hyphenpipe

The only thing I can think of is to do a union on the select statements to cover all the data.

Like the code below. I removed the order by clause because I don't think those are allowed in view.

USE [appDB]
        GO
        /****** Object:  View [dbo].[SupCust_PL]    Script Date: 08/26/2009 15:44:59 ******/
        SET ANSI_NULLS ON
        GO
        SET QUOTED_IDENTIFIER ON
        GO
        ALTER VIEW [dbo].[SupCust_PL_TEST]
        AS
        SELECT     TOP (100) PERCENT appDB.dbo.PL01CC00.PL01001 AS SupplierCode, appDB.dbo.PL01CC00.PL01002 AS SupplierName, systemDB.dbo.ScaCompanies.CompanyCode as CompanyCode
        FROM         appDB.dbo.PL01CC00, systemDB.dbo.ScaCompanies
        where            systemDB.dbo.ScaCompanies.CompanyCode=substring([appDB.dbo.PL01CC00],17,2)
 
union all
 
 SELECT     TOP (100) PERCENT appDB.dbo.PL01AA00.PL01001 AS SupplierCode, appDB.dbo.PL01AA00.PL01002 AS SupplierName, systemDB.dbo.ScaCompanies.CompanyCode as CompanyCode
        FROM         appDB.dbo.PL01AA00, systemDB.dbo.ScaCompanies
        where            systemDB.dbo.ScaCompanies.CompanyCode=substring([appDB.dbo.PL01AA00],17,2)

Open in new window

DonWolfi007

ASKER

Txs hyphenpipe, this leads me into the direction of a solution, although unpractical, as this needs maintenance for every company added to the DB and make me maintain a union for 40+ different PL01xx00 tables.

As a further suggestion, could this not be solved via a temp table containing the names of the tables and a CURSOR function? It seem this should be working as a "sexy" solution and I have tried to understand how CURSOR works, but have not yet managed......

hyphenpipe

The problem is your trying to create a view and I don't think a cursor is capable in a view.

hyphenpipe

You could do it in a stored procedure however.

ASKER CERTIFIED SOLUTION

hyphenpipe

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial