June 27, 2012 at 12:51 pm
Hi all,
I have a requirement to add a new field to a view we have currently. This new field is called Market. This field is based off of 2 different tables.
I need to query 1 table (Sales) to find the value in the UDFForMarket field and depending on the numerical value of that field (0 to 8), I need to make the new field Market = to the value stored in fields UDF1 to 8 in another table (TransactionHdr)
Normally I would have no issue doing this, would just do a case statement,
if the UDFForMarket = 1 then TransactionHdr.UDF1 AS Market, etc
But the Sales table just has one row and no keys and no way to join it to the TransactionHdr table, so I am very confused on how to accomplish this for the View. None of the fields match up or are in common with any other fields in the table. I cannot change the tables, this is actually a COBOL system and we have moved the data into SQL Server so reports can be created using SSRS.
If anyone can help me that would be wonderful! I need to get this completed ASAP and hopefully I missing something really easy!
thanks!
angel
June 27, 2012 at 1:38 pm
adiehl (6/27/2012)
Hi all,I have a requirement to add a new field to a view we have currently. This new field is called Market. This field is based off of 2 different tables.
I need to query 1 table (Sales) to find the value in the UDFForMarket field and depending on the numerical value of that field (0 to 8), I need to make the new field Market = to the value stored in fields UDF1 to 8 in another table (TransactionHdr)
Normally I would have no issue doing this, would just do a case statement,
if the UDFForMarket = 1 then TransactionHdr.UDF1 AS Market, etc
But the Sales table just has one row and no keys and no way to join it to the TransactionHdr table, so I am very confused on how to accomplish this for the View. None of the fields match up or are in common with any other fields in the table. I cannot change the tables, this is actually a COBOL system and we have moved the data into SQL Server so reports can be created using SSRS.
If anyone can help me that would be wonderful! I need to get this completed ASAP and hopefully I missing something really easy!
thanks!
angel
If its just one row, I would consider using "CROSS JOIN" as this means that every row in one table will be matched with every row in the other table (of which the sales table will only have one row).
June 28, 2012 at 6:21 am
Either a cross join or a JOIN Sales ON 1 = 1 will work if there is only 1 row in that table.
June 28, 2012 at 7:48 am
Thank you, I am trying out the Cross Join now. Of course, I was told there would only be 1 row in the database and there are actually 10. they said I can just take the first row though and ignore the rest, so I don't know if that will work with the cross join?
This is what I have now, but since there are 10 rows, I am not sure it will work. This is the section out of my Create View script:
select
case
when s.UDFForMarket = 5 then sth.UDF5
END AS Market
from SalesInit s
CROSS JOIN SalesTransactionHdr sth
There are 8 other cases, I am just testing with the 1 that is set in the test data I am using.
Think this will work? I basically just need to know which field the customer has chosen to store their Market information in (UDF1-8), and assign that field to the Market field in the View so I can utilize that for SSRS reporting. I might be making this harder than it is, but for some reason I am really confused by it!
thanks!
June 28, 2012 at 7:56 am
adiehl (6/28/2012)
Thank you, I am trying out the Cross Join now. Of course, I was told there would only be 1 row in the database and there are actually 10. they said I can just take the first row though and ignore the rest, so I don't know if that will work with the cross join?This is what I have now, but since there are 10 rows, I am not sure it will work. This is the section out of my Create View script:
select
case
when s.UDFForMarket = 5 then sth.UDF5
END AS Market
from SalesInit s
CROSS JOIN SalesTransactionHdr sth
There are 8 other cases, I am just testing with the 1 that is set in the test data I am using.
Think this will work? I basically just need to know which field the customer has chosen to store their Market information in (UDF1-8), and assign that field to the Market field in the View so I can utilize that for SSRS reporting. I might be making this harder than it is, but for some reason I am really confused by it!
thanks!
Then you don't want a cross join. A cross join creates a cartesian product and that is not what want since that table has 10 rows. You can pull the "first" row but you need to figure out how to order that table to get the "first" row.
_______________________________________________________________
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/
June 28, 2012 at 8:06 am
In order to you to just use the first row, you'll have to give some ordering criteria to let SQL know what constitutes the order of the rows of which you'll be picking the first of (lol hope you can parse that!)
Or you could actually (within the parentheses demarked subquery) just select the row you want to get values from.
This is my take, some other folks might have different takes on the problem.
select
case
when s.UDFForMarket = 5 then sth.UDF5
END AS Market
from SalesInit s
CROSS JOIN
( select top 1 UDF1, UDF2, UDF3, UDF4, UDF5 FROM SalesTransactionHdr
ORDER BY <some ordering criteria would go here>
) sth
or alternatively put in criteria sufficient to select the right row from salestransactionhdr, this selection criteria needs to limit to 1 the rows returned. If you return more than one row with a cross join you'll get more rows than you want.
select
case
when s.UDFForMarket = 5 then sth.UDF5
END AS Market
from SalesInit s
CROSS JOIN
( SELECT UDF1, UDF2, UDF3, UDF4, UDF5 FROM SalesTransactionHdr
WHERE <your criteria goes here>
) sth
June 28, 2012 at 9:46 am
Or do an inner join on that criteria.
June 28, 2012 at 1:36 pm
patrickmcginnis59 (6/28/2012)
In order to you to just use the first row, you'll have to give some ordering criteria to let SQL know what constitutes the order of the rows of which you'll be picking the first of (lol hope you can parse that!)Or you could actually (within the parentheses demarked subquery) just select the row you want to get values from.
This is my take, some other folks might have different takes on the problem.
select
case
when s.UDFForMarket = 5 then sth.UDF5
END AS Market
from SalesInit s
CROSS JOIN
( select top 1 UDF1, UDF2, UDF3, UDF4, UDF5 FROM SalesTransactionHdr
ORDER BY <some ordering criteria would go here>
) sth
or alternatively put in criteria sufficient to select the right row from salestransactionhdr, this selection criteria needs to limit to 1 the rows returned. If you return more than one row with a cross join you'll get more rows than you want.
select
case
when s.UDFForMarket = 5 then sth.UDF5
END AS Market
from SalesInit s
CROSS JOIN
( SELECT UDF1, UDF2, UDF3, UDF4, UDF5 FROM SalesTransactionHdr
WHERE <your criteria goes here>
) sth
Thanks! I got it to work!
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply