简单SQL 问题，求解（有分加）

jungle

数据库SQL SERVER 2008. 现在需要使用MICROSTRATEGY的FREE SQL 模式在SQL SERVER执行以下简单SQL语句：

Select * from Sales where SaleDate>= Date_From

Date_From是由Microstrategy控制的一个变量，与前台用户界面上的一个Date Picker控件绑定，无法人为改写。当前台用户界面上的Date Picker有值时，它会返回一个表示日期的字符串值，比如'2011-08-12'（注意：单引号也包含在返回的字符串内）；当前台用户界面上的Date Picker为空时，它会返回一个空字符串。Microstrategy只对上述SQL语句中的变量进行简单的字符串替换后，即传递给SQL SERVER执行。

显然，当前台Date Picker为空时，我希望返回所有记录，所以我这样组织SQL:
Select * from Sales where (Date_From='' or SaleDate>= Date_From)

该SQL 在Date Picker有值时正常执行，其变量替换后结果是：
Select * from Sales where ('2011-08-01'='' or SaleDate>'2011-08-01')

但在无值时报语法错误，因为此时SQL语句变成了
Select * from Sales where (='' or SaleDate>)

好吧，那我给它加上两个单引号。现在我这样组织SQL:
Select * from Sales where ('Date_From'='' or SaleDate>= 'Date_From')

结果反过来了，在无值时可以正常执行，而在有值时报语法错误，因为此时SQL变成了
Select * from Sales where (''2011-08-01''='' or SaleDate>''2011-08-01'')

那么如何才能构造一个SQL，使其在两种情况下均可以正常执行？
显然，如果Microstrategy 能在界面DatePicker为空时返回'' 而非空字符串，该问题就可以解决（或者使其在有值时返回的日期字符串两头不加单引号，也可以解决）。但是现在确实无法控制MicroStrategy的行为，我们只能构建SQL，而它来替换变量。这个问题能解决么？

混不到坑的萝卜

...let me think...

Doh~~~纯粹sql的话我好像无解啊，还是有请各位高人吧！

[ 本帖最后由 混不到坑的萝卜 于 2011-8-12 21:53 编辑 ]

coin_king

用一个傻办法

if Date_From 是空值（不记得syntax了）
Select * from Sales where ('Date_From'='' or SaleDate>= 'Date_From')
else
Select * from Sales where (Date_From='' or SaleDate>= Date_From)

乱码

用store proc么？可以设paramater的default value

CREATE PROCEDURE YourStoreProcName
@dt datetime = '1990-01-01'  -- 可以select所有数据的default value
AS
    SET NOCOUNT ON;
    Select * from Sales where SaleDate>= @dt
go;

Date picker为空的时候，控制logic 不传parameter过来，
不空的时候，传实际参数。

http://msdn.microsoft.com/en-us/library/ms189330.aspx

我不懂Microstrategy，可能store proc用不上

[ 本帖最后由 乱码 于 2011-8-12 21:59 编辑 ]

混不到坑的萝卜

原帖由 乱码 于 2011-8-12 21:53 发表
用store proc么？可以设paramater的default value

CREATE PROCEDURE YourStoreProcName
@dt datetime = '1990-01-01'  -- 可以select所有数据的default value
AS
    SET NOCOUNT ON;
    Select * from Sales wher ...

这可能是唯一的办法了

create stored proc AAA( @dt DateTime = '1990-01-01')
as
begin
select nocount on
Select * from Sales where (@dt='1990-01-01' or SaleDate>= @dt)
end

然后你的SQL statement是
exec AAA Date_From

jungle

有道理啊！在只有一个参数的时候，用 乱码兄 和 萝卜兄的办法确实可以解决。

但是实际情况是：其实我有两个参数，Date_From 和 Date_To

如果两个参数的时候使用带OPTIONAL PARAMETER的SP就杯具了，在参数没有值的时候变成

EXEC AAA ,

这个多余的逗号没办法解决！

porcorosso

Microstrategy是神马东东呀？

乱码

原帖由 混不到坑的萝卜 于 2011-8-12 21:58 发表


这可能是唯一的办法了

create stored proc AAA( @dt DateTime = '1990-01-01')
as
begin
select nocount on
Select * from Sales where (@dt='1990-01-01' or SaleDate>= @dt)
end

然后你的SQL statement是
exec ...

我觉得lz思路有点乱，你别跟着她的思路走。

直接看要求：

1。当前台Date Picker为空时，我希望返回所有记录

2。该SQL 在Date Picker有值时正常执行。 就是大于等于传进来的变量。

直接这一句就满足条件了，Select * from Sales where SaleDate>= @dt

乱码

原帖由 jungle 于 2011-8-12 22:03 发表
有道理啊！在只有一个参数的时候，用 乱码兄 和 萝卜兄的办法确实可以解决。

但是实际情况是：其实我有两个参数，Date_From 和 Date_To

如果两个参数的时候使用带OPTIONAL PARAMETER的SP就杯具了，在参数没有值的时候变成

EXEC AAA ,

这个多余的逗号没办法解决！

一样的解决方案，把date_to的default value设成将来的100年，没有就用它，有就用实际传进来的。

jungle

乱码兄，LZ的思路没有乱，只是在解释MICROSTRATEGY在FREE SQL模式下传递参数的方式而已

我们没办法控制MICROSTRATEGY怎样传递参数给SQL语句——实际上它只是组装一个静态的SQL语句，不能带任何SQL SERVER模式的@开头的变量。 萝卜兄对于题目的理解是完全正确的。

LoveAu

How about use ISNULL(Date_From,'')=''  instead of Date_From='' ?

jungle

乱码兄，没有抓住关键

关键是两个参数中间的逗号没办法解决！如果你有一个SP带两个OPTIONAL 参数

你这样执行
EXEC AAA DATE_FROM, DATE_TO

当DATE_FROM = ‘2011-01-01’ ， DATE_TO= string.empty 时，你的SQL变成
EXEC AAA '2011-01-01' ,

这个是报语法错误的！

乱码

原帖由 jungle 于 2011-8-12 22:08 发表
乱码兄，LZ的思路没有乱，只是在解释MICROSTRATEGY在FREE SQL模式下传递参数的方式而已

我们没办法控制MICROSTRATEGY怎样传递参数给SQL语句——实际上它只是组装一个静态的SQL语句，不能带任何SQL SERVER模式的@开头的变量。 萝卜兄对于题目的理解是完全正确的。

哦，我没做过这个，萝卜，你继续

混不到坑的萝卜

倒！你倒是一次说完啊……

实在不行就难看点，一次搞三个Stored proc，前两个用我和乱码那个办法：
create stored proc AAA (@DateFrom DateTime = '1990-01-01') as
begin
  create table ##temp (DateFrom DateTime)
  insert into ##Temp values(@DateFrom)
end

create stored proc BBB (@DateTo DateTime = '1990-01-01') as
begin
  create table ##temp2 (DateTo DateTime)
  insert into ##Temp2 values(@DateTo)
end

Create stored proc CCC(@DateFrom DateTime, @DateTo DateTime)
as
...

Now you can call:
exec AAA Date_From
exec BBB Date_To

declare @DF datetime
select @DF = top 1 DateFrom from ##Temp
declare @DT datetime
select @DT = top 1 DateTo from ##temp2
exec CCC @DF, ·@DT
drop table ##temp
drop table ##temp2

[ 本帖最后由 混不到坑的萝卜 于 2011-8-12 22:14 编辑 ]

乱码

原帖由 jungle 于 2011-8-12 22:11 发表
乱码兄，没有抓住关键

关键是两个参数中间的逗号没办法解决！如果你有一个SP带两个OPTIONAL 参数

你这样执行
EXEC AAA DATE_FROM, DATE_TO

当DATE_FROM = ‘2011-01-01’ ， DATE_TO= string.empty 时，你的SQL变成
EXEC AAA '2011-01-01' ,

这个是报语法错误的！

你从datepicker那边想办法, 他们可以设default valude的.

jungle

萝卜兄的解法确实不错，开启了一个新思路。

实际上，这个怎么也都是一个非常TRICKY的问题。我实现的是以下的TRICKY解法，单个SQL语句可解：
Select * from Sales where
(''Date_From''='''' or SaleDate>= Replace(''Date_From'','''',''))

YugaYuga

kuxxc

Select * from Sales where SaleDate > isnull(Date_From, SaleDate-1)

jungle

我倒～～LS这两位同学根本没看懂题嘛。拜托仔细看看再回复啦

kuxxc

看你给我加分，可能你就是语言上有障碍，其实心地善良。我就再给你补充。

你的题目讲了一大堆，废话很多可关键没表达清楚很难明白。我是看其他回帖才能了解你要表达什么。拜托自己先反省吧。要不试试用英语发问？

My answer is correct if your '空字符串' is null. If it's an empty string you need to change it to other qualifier. You could also combine them together with CASE so that it works on either null or empty string.

This complete script gets me the result.



create table #Sales (data int, SaleDate datetime)
insert into #Sales values (1, '2000-01-01')
insert into #Sales values (2, '2000-01-02')
insert into #Sales values (3, '2000-01-03')

--if it's null
--note next two SELECT are the same
declare @Date_From datetime
set @Date_From = '2000-01-02'
Select * from #Sales where SaleDate >= isnull(@Date_From, SaleDate-1)
set @Date_From = null
Select * from #Sales where SaleDate >= isnull(@Date_From, SaleDate-1)


--if it's an empty string (ie. '')
--note next two SELECT are the same
set @Date_From = '2000-01-02'  
Select * from #Sales where SaleDate >= case when @Date_From = '' then SaleDate-1 else @Date_From end
set @Date_From = ''  
Select * from #Sales where SaleDate >= case when @Date_From = '' then SaleDate-1 else @Date_From end

--To make it work for both null and empty string use this SQL
Select * from #Sales where SaleDate >= case when @Date_From = '' or @Date_From is null then SaleDate-1 else @Date_From end

drop table #Sales

Good luck.

混不到坑的萝卜

select
        case when null = null then N'空既是空'
        when null != null then N'空又不是空'
        when null IS null then N'空还是空'
end


开个玩笑，不过你跑跑上面这三句，很好玩的。

[ 本帖最后由 混不到坑的萝卜 于 2011-8-15 11:55 编辑 ]

jungle

这位同学，实在很不好意思，你的两次的理解都是完全错误的。再说白点，这个问题其实根本不是你所理解的NULL和EMPTY STRING的问题，甚至根本不是一个SQL问题。这个纯粹是一个玩弄字符串的技巧问题。

我在一楼的帖子，2楼的 萝卜同学就已经完全正确地理解了我的意思啦。我这个是又描述又举实例，任何人如果仔细看一下，甚至不需要是程序员，都应该可以理解啦。

kuxxc

你说的对。我现在明白了。

Need to get Microstrategy to execute a parameterised SP rather than constructing a sql string. The SP call mechanism must be a built in function of MicroStrategy, as you already found out constructing a SQL string to call an SP doesn't work.

Or if you can set the return value to be '' (empty string, rather than NOTHING) if no date is selected on the datepicker.   Or set a default min/max....

But I'm sure you've tried both.

Regardless, the solution can only lie with the UI or Microstrategy's call mechanism. We're wasting our time on the SQL Syntax. There's no SQL syntax that can interpret NOTHING (not null, not empty string '', but just nothing!) as a placeholder, because nothing is nothing - there's nothing to interpret and SQL server will throw an error if it expects something but there's nothing. Otherwise it'll be called TQL not SQL. Telepathic Query Language.

jungle

Sorry I have to say that you were wrong again (3 times, dude~~).

It's definitely NOT waste of time to think about a solution in pure SQL... It actually cost me 2 minutes to think about it before I found the properly solution. Although it appears to be tricky, it works.

I have put the solution in level 16#. Think about the string below carefully (what does it look like in both cases?) - I believe you have the ability to understand it.

Select * from Sales where
(''Date_From''='''' or SaleDate>= Replace(''Date_From'','''',''))

Also, Mr. "Raddish without a pit" provided a solution in level 14#. Although it was a bit chunky, it worked.

kuxxc

Select * from Sales where (''Date_From''='''' or SaleDate>= Replace(''Date_From'','''',''))
doesn't work on me. ''Date_From'' gives me error (Incorrect syntax ).

#14.... hmmm..... no comments. creative tho. :)

Anyway glad you have a solution. I should retire to play golf...

jungle

Come on dude, ''Date_From'' will NEVER be ''Date_From'' when it's passed to SQL Server. It's either
''''
or
'''2011-01-01'''

Both are correct syntax of SQL. Now you understand?

Probably you made a proper decision in your last sentence...

kuxxc

oh.... ya.... you're so superior and i'm so stupid....

off to play golf.

北风

isnull解决不了？

jungle

嗯，好像是解决不了

IsDonIsGood

还是觉得只有sp可以解决问题。
LZ看看这样行不行：
CREATE PROCEDURE AAA
                   @p1 AS VARCHAR(200)
AS
   DECLARE @DATE_FROM VARCHAR(20), @DATE_TO VARCHAR(20)
   SET @p1= LTRIM(RTRIM(@p1))
   SELECT @DATE_FROM = LEFT(@p1,CHARINDEX(',',@p1,0)),@DATE_TO = RIGHT(@p1,LEN(@p1)-CHARINDEX(',',@p1,0))
   
   SELECT * FROM Sales WHERE SaleDate >= ( CASE WHEN LEN(@Date_From) > 0 THEN @Date_From ELSE '1900-01-01'END )
                         AND SaleDate <= ( CASE WHEN LEN(@DATE_TO) > 0 THEN @DATE_TO ELSE '9999-01-01'END )

干脆把你的两个参数合成一个来看，如果两个都空返回的时候没有comma,在sp里做个单独的判断就好。