May 9, 2019 at 7:11 am
i would like to create a view. but my current script has temp table. how can i replace the temp table. query sample as below:
create table #a
( testing1 varchar(100),
testing2 varchar(100),
testing3 varchar(100),
amount integer
)
insert into #a
values('testing1','testing2','testing3',700)
select *
into #tempA
from #a
select *
--case condition logic
case when total=....
from #tempA y
inner join (select distinct testing1,testing2,count(distinct testing3) total
from #tempA group by testing1,testing2) x
on y.testing1=x.testing1 and y.testing2=x.testing2
May 9, 2019 at 7:57 am
There's probably further improvements you could make, but here's how to eliminate the temp table:
WITH y AS (
SELECT
testing1
, testing2
, testing3
, amount
FROM (VALUES(
'testing1'
, 'testing2'
, 'testing3'
, 700)
) z(testing1,testing2,testing3,amount)
)
SELECT *
--CASE CONDITION LOGIC
--CASE WHEN total=....
FROM y
INNER JOIN (
SELECT DISTINCT
testing1
, testing2
, COUNT(DISTINCT testing3) AS total
FROM y
GROUP BY
testing1
, testing2
) x
ON y.testing1 = x.testing1
AND y.testing2 = x.testing2;
John
May 9, 2019 at 8:54 am
How can I select all columns after updating the case condition?
WITH y AS (
SELECT
testing1
, testing2
, testing3
, amount
FROM (VALUES(
'testing1'
, 'testing2'
, 'testing3'
, 700)
) z(testing1,testing2,testing3,amount)
)
select testing1,testing2
,MAX(NULLIF(updatecolumn, null)) OVER(PARTITION BY testing1) updatecolumn_New
from(
SELECT *
--CASE CONDITION LOGIC
,case when total=1 then 'pls update' else 'N' end as updatecolumn
FROM y
INNER JOIN (
SELECT DISTINCT
testing1
, testing2
, COUNT(DISTINCT testing3) AS total
FROM y
GROUP BY
testing1
, testing2
) x
ON y.testing1 = x.testing1
AND y.testing2 = x.testing2
)a
UPDATE CV
SET cv.testing2 = cv.updatecolumn_New
FROM y AS CV
WHERE cv.ap_kob = 'pls update'
May 9, 2019 at 8:57 am
Another method which could be used if an intermediate table is required is to create a table valued function, with table variables instead of temporary tables, these behave a lot like a view.
May 9, 2019 at 9:07 am
You're not selecting from any tables, so there's nothing to update. Just include that particular manipulation in your SELECT statement. Your actual data clearly has more than the one row that you've shown us, so if you're no sure how to do that, please provide a representative set of values, along with the results you expect to see from such data.
John
May 10, 2019 at 6:57 am
Currently this is my code as below. As i would to create as a view, not sure on how to apply together with the update and temp table replacement.
create table #a
( testing1 varchar(100),
testing2 varchar(100),
testing3 varchar(100),
amount integer
)
insert into #a
values('testing1','testing2','testing3',700)
select *
into #tempA
from #a
WITH CODE_VALUES AS
(
select testing1,testing2,testing3
,MAX(NULLIF(newcolumn, null)) OVER(PARTITION BY testing2) newcolumn
from(
select y.*
--case condition logic
,case when total=1 then 999 else total end as newcolumn
from #tempA y
inner join (select distinct testing1,testing2,count(distinct testing3) total
from #tempA group by testing1,testing2) x
on y.testing1=x.testing1 and y.testing2=x.testing2
)xx
UPDATE CV
SET cv.testing3 = cv.newcolumn
FROM CODE_VALUES AS CV
WHERE cv.total=1
May 10, 2019 at 7:58 am
So your actual code really really uses only one line of data? If that really is the case, why do you need MAX, DISTINCT and PARTITION BY?
John
May 10, 2019 at 8:02 am
It might be easier for us if you describe what the script is doing.
What is the purpose of that part of the script? Is it to update the temporary table #tempA?
Is that the exact code in the script? i.e. Is 'testing1','testing2','testing3',700 hard coded into the script?
May 10, 2019 at 8:11 am
it has multiple line, i am just creating it myself before applying to real data. there's logic to update certain scenarios, that is why I apply max in it. Just having trouble to create this as a view.
May 10, 2019 at 8:16 am
Well, if we don't know what your real data looks like, it's difficult for us to help. That's why I asked for representative sample data along with expected results.
John
May 10, 2019 at 8:18 am
Not sure if this is what you want. If you supply the actual code it might be easier to work out what it's doing.
CREATE VIEW xx AS
with y as
(
SELECT *
FROM (VALUES ('testing1','testing2','testing3',700)) y(testing1,testing2,testing3,amount)
)
select xx.testing1,
xx.testing2,
xx.testing3,
MAX(NULLIF(xx.newcolumn, null)) OVER(PARTITION BY testing2) newcolumn
from (select y.*,
--case condition logic
case when total=1 then 999 else total end as newcolumn
FROM y
inner join (select distinct testing1,
testing2,
count(distinct testing3) total
from y
group by testing1,testing2) x
on y.testing1=x.testing1
and y.testing2=x.testing2
) xx
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply