October 2, 2015 at 3:10 pm
I have a GUI that displays the SQL queries that are built in the application. It is built in ms access using SQL Server Linked tables. There are linked tables that have a prefix of "Dyn_" + a numeric value (ie 9) + "_" + SQL table Name. This makes up the linked table name or alias. But I want to strip off the Dyn_9 from the linked table below. The numeric value can be any number (should not be greater than 100,000). The queries can be simple or complex and have 1 table or multiple tables with joins.
Sample :
SELECT Mytable_ReferenceTables.dbo.CCT_LOOKUP_AGREEMENT_HEADER.BILLING_VENDOR_NAME, PRGX_Mytable_ReferenceTables.dbo.CCT_LOOKUP_AGREEMENT_HEADER.OWNING_VENDOR_CODE, PRGX_Mytable_ReferenceTables.dbo.CCT_LOOKUP_AGREEMENT_HEADER.OWNING_VENDOR_NAME, Dyn_9_CCT_LOOKUP_PP.PROTECTED_ASIN
FROM Mytable_ReferenceTables.dbo.CCT_LOOKUP_AGREEMENT_HEADER INNER JOIN Dyn_9_CCT_LOOKUP_PP ON Mytable_ReferenceTables.dbo.CCT_LOOKUP_AGREEMENT_HEADER.BILLING_VENDOR_NAME = Dyn_9_CCT_LOOKUP_PP.BILLING_VENDOR_NAME;
What I want it to look like:
SELECT Mytable_ReferenceTables.dbo.CCT_LOOKUP_AGREEMENT_HEADER.BILLING_VENDOR_NAME, PRGX_Mytable_ReferenceTables.dbo.CCT_LOOKUP_AGREEMENT_HEADER.OWNING_VENDOR_CODE, PRGX_Mytable_ReferenceTables.dbo.CCT_LOOKUP_AGREEMENT_HEADER.OWNING_VENDOR_NAME, CCT_LOOKUP_PP.PROTECTED_ASIN
FROM Mytable_ReferenceTables.dbo.CCT_LOOKUP_AGREEMENT_HEADER INNER JOIN CCT_LOOKUP_PP ON Mytable_ReferenceTables.dbo.CCT_LOOKUP_AGREEMENT_HEADER.BILLING_VENDOR_NAME = CCT_LOOKUP_PP.BILLING_VENDOR_NAME;
October 2, 2015 at 3:39 pm
GrassHopper (10/2/2015)
I have a GUI that displays the SQL queries that are built in the application. It is built in ms access using SQL Server Linked tables. There are linked tables that have a prefix of "Dyn_" + a numeric value (ie 9) + "_" + SQL table Name. This makes up the linked table name or alias. But I want to strip off the Dyn_9 from the linked table below. The numeric value can be any number (should not be greater than 100,000). The queries can be simple or complex and have 1 table or multiple tables with joins.Sample :
SELECT Mytable_ReferenceTables.dbo.CCT_LOOKUP_AGREEMENT_HEADER.BILLING_VENDOR_NAME, PRGX_Mytable_ReferenceTables.dbo.CCT_LOOKUP_AGREEMENT_HEADER.OWNING_VENDOR_CODE, PRGX_Mytable_ReferenceTables.dbo.CCT_LOOKUP_AGREEMENT_HEADER.OWNING_VENDOR_NAME, Dyn_9_CCT_LOOKUP_PP.PROTECTED_ASIN
FROM Mytable_ReferenceTables.dbo.CCT_LOOKUP_AGREEMENT_HEADER INNER JOIN Dyn_9_CCT_LOOKUP_PP ON Mytable_ReferenceTables.dbo.CCT_LOOKUP_AGREEMENT_HEADER.BILLING_VENDOR_NAME = Dyn_9_CCT_LOOKUP_PP.BILLING_VENDOR_NAME;
What I want it to look like:
SELECT Mytable_ReferenceTables.dbo.CCT_LOOKUP_AGREEMENT_HEADER.BILLING_VENDOR_NAME, PRGX_Mytable_ReferenceTables.dbo.CCT_LOOKUP_AGREEMENT_HEADER.OWNING_VENDOR_CODE, PRGX_Mytable_ReferenceTables.dbo.CCT_LOOKUP_AGREEMENT_HEADER.OWNING_VENDOR_NAME, CCT_LOOKUP_PP.PROTECTED_ASIN
FROM Mytable_ReferenceTables.dbo.CCT_LOOKUP_AGREEMENT_HEADER INNER JOIN CCT_LOOKUP_PP ON Mytable_ReferenceTables.dbo.CCT_LOOKUP_AGREEMENT_HEADER.BILLING_VENDOR_NAME = CCT_LOOKUP_PP.BILLING_VENDOR_NAME;
This really isn't a sql server question because this is all in the front end. I would personally recommend that you use stored procedures and stop using pass through sql built in Access.
In any front end programming language you will have some kind of string replace functionality. All you need to do is replace(MyBigQuery, "DYN_" + SomeNumber + "_", "")
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
October 2, 2015 at 3:58 pm
thanks
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply