语句值

[buzhunmeiyouni]

sELECT a.FOfferItem,a.FUnit,a.FQty,a.FSupplier,a.FNOTE
,isnull(CASE when c.fname='001' then 'select (FPrice*FQty)as 001 from a' END,'') '001'
,isnull(case when c.fname='002' then 'select (FPrice*FQty)as 002 from a' end,'') '002'
,ISNULL(case when c.fname='张三' then 'select (FPrice*FQty) as 张三 from a' END,'') 张三
,isnull(case when c.fname='李四' then 'select (FPrice*FQty) as 李四 from a' end,'') 李四
FROM LGEntry_OfferSheet a
INNER JOIN LGBOS_OfferSheet b ON a.fid=b.FID
INNER JOIN t_emp c ON c.fitemid=b.FOfficeClerk
WHERE c.fname in (SELECT c.fname FROM t_emp)

运行结果还是语句


怎么把语句变成值啊

[吉普赛的歌]

/************************************************************
 * Code formatted by SoftTree SQL Assistant ?v7.2.331
 * Time: 2017-11-15 15:04:44
 ************************************************************/

SELECT a.FOfferItem,
       a.FUnit,
       a.FQty,
       a.FSupplier,
       a.FNOTE,
       ISNULL(
           CASE 
                WHEN c.fname = '001' THEN (SELECT TOP 1 (FPrice*FQty)as 001  from a)
           END,
           ''
       ) '001',
       ISNULL(
           CASE 
                WHEN c.fname = '002' THEN (select TOP 1 (FPrice*FQty)as 002 from a)
           END,
           ''
       ) '002',
       ISNULL(
           CASE 
                WHEN c.fname = '张三' THEN (select TOP 1 (FPrice*FQty) as 张三 from a)
           END,
           ''
       )     张三,
       ISNULL(
           CASE 
                WHEN c.fname = '李四' THEN (select TOP 1 (FPrice*FQty) as 李四 from a)
           END,
           ''
       )     李四
FROM   LGEntry_OfferSheet a
       INNER JOIN LGBOS_OfferSheet b
            ON  a.fid = b.FID
       INNER JOIN t_emp c
            ON  c.fitemid = b.FOfficeClerk
WHERE  c.fname IN (SELECT c.fname
                   FROM   t_emp)

[buzhunmeiyouni]

可以，但是报这个错误

[buzhunmeiyouni]

引用 1 楼 yenange 的回复:

/************************************************************
 * Code formatted by SoftTree SQL Assistant ?v7.2.331
 * Time: 2017-11-15 15:04:44
 ************************************************************/

SELECT a.FOfferItem,
       a.FUnit,
       a.FQty,
       a.FSupplier,
       a.FNOTE,
       ISNULL(
           CASE 
                WHEN c.fname = '001' THEN (SELECT TOP 1 (FPrice*FQty)as 001  from a)
           END,
           ''
       ) '001',
       ISNULL(
           CASE 
                WHEN c.fname = '002' THEN (select TOP 1 (FPrice*FQty)as 002 from a)
           END,
           ''
       ) '002',
       ISNULL(
           CASE 
                WHEN c.fname = '张三' THEN (select TOP 1 (FPrice*FQty) as 张三 from a)
           END,
           ''
       )     张三,
       ISNULL(
           CASE 
                WHEN c.fname = '李四' THEN (select TOP 1 (FPrice*FQty) as 李四 from a)
           END,
           ''
       )     李四
FROM   LGEntry_OfferSheet a
       INNER JOIN LGBOS_OfferSheet b
            ON  a.fid = b.FID
       INNER JOIN t_emp c
            ON  c.fitemid = b.FOfficeClerk
WHERE  c.fname IN (SELECT c.fname
                   FROM   t_emp)

那条语句单独执行就行，一起执行就报 varchar 转换为 numeric 时出错

[吉普赛的歌]

SELECT a.FOfferItem,
       a.FUnit,
       a.FQty,
       a.FSupplier,
       a.FNOTE,
       ISNULL(
           CASE 
                WHEN c.fname = '001' THEN (SELECT TOP 1 CONVERT(VARCHAR(50),FPrice*FQty) as 001  from a)
           END,
           ''
       ) '001',
       ISNULL(
           CASE 
                WHEN c.fname = '002' THEN (select TOP 1 CONVERT(VARCHAR(50),FPrice*FQty) as 002 from a)
           END,
           ''
       ) '002',
       ISNULL(
           CASE 
                WHEN c.fname = '张三' THEN (select TOP 1 CONVERT(VARCHAR(50),FPrice*FQty)  as 张三 from a)
           END,
           ''
       )     张三,
       ISNULL(
           CASE 
                WHEN c.fname = '李四' THEN (select TOP 1 CONVERT(VARCHAR(50),FPrice*FQty)  as 李四 from a)
           END,
           ''
       )     李四
FROM   LGEntry_OfferSheet a
       INNER JOIN LGBOS_OfferSheet b
            ON  a.fid = b.FID
       INNER JOIN t_emp c
            ON  c.fitemid = b.FOfficeClerk
WHERE  c.fname IN (SELECT c.fname
                   FROM   t_emp)

[buzhunmeiyouni]

可以了，谢谢