[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 295
  • Last Modified:

SQL Query WHERE Issues

I have a SQL statement that I am attempting to utilize three different querystrings to return results. Problems are I need the WHERE statement to use only one querystring when two are present. The url looks like '/ArticleID/4/PageID/6/' when two parameters are present and '/ArticleID/4/' when only one is present. What I need to do is listen for both parameters but when the pageid is present I need to ignore the articleID and when the articleid is present I need it to pull the match. Code I am using now is below and the only issue with it is that when the articleID and PageID are present it grabs both results, which I can't have in this instance. Any advice would be appreciated.
SELECT LeftColumnAd 
FROM Ad_Banners 
WHERE articleID = '[Query:articleID]' 
OR Page = '[Query:PageID]' 
OR CategoryID = '[Query:CategoryID]'

Open in new window

0
AJJR
Asked:
AJJR
  • 21
  • 11
  • 2
  • +3
2 Solutions
 
RiteshShahCommented:
you can do something like



declare @url varchar(100)
set @url='/ArticleID/4/PageID/6/'
 
if charindex('PageID',@url,0)>0
	begin
		SELECT LeftColumnAd 
		FROM Ad_Banners 
		WHERE Page = '[Query:PageID]' 
		OR CategoryID = '[Query:CategoryID]'
	end
else
	begin
		SELECT LeftColumnAd 
		FROM Ad_Banners 
		WHERE articleID = '[Query:articleID]' 
		OR Page = '[Query:PageID]' 
		OR CategoryID = '[Query:CategoryID]'
	end

Open in new window

0
 
vinurajrCommented:
Please Find the Code snippet,... U didt mention about the categoryID so I just leave as it is...
declare @Ad_Banners table(articleID varchar(max),Page int,CategoryID int)
 
insert into @Ad_Banners 
select '1Text',1,123
union all
select '2Text',2,124
union all
select '3Text',3,125
union all
select '4Text',4,126
union all
select '5Text',5,127
 
SELECT articleID 
FROM @Ad_Banners 
WHERE  Page = 1 or (articleID = '2Text' and Page = 2)
OR CategoryID = 123

Open in new window

0
 
MeghanOBrienCommented:

if [Query:articleID] is not null
      begin
            SELECT LeftColumnAd
            FROM Ad_Banners
            WHERE articleID = '[Query:articleID]'
      end
else
if [Query:PageID] is not null
      begin
            SELECT LeftColumnAd
            FROM Ad_Banners
            WHERE Page = '[Query:PageID]'
      end
else
if [Query:CategoryID] is not null
      begin
            SELECT LeftColumnAd
            FROM Ad_Banners
            WHERE CategoryID = '[Query:CategoryID]'
      end
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
AJJRAuthor Commented:
Hi Guy's, Thank you for the swift answers. Both of these are on the right track, but not quite what I need. After re-reading my post, I noticed I did indeed leave out the categoryId parameter. So, to re-iterate;
I am using a DotNetNuke module to furnish the SQL delivery. What it does is takes the statement I have above and looks in the url for matching querystring parameters and if any or all of them match it returns data I have placed in a table called 'Ad_Banners' as decoded HTML. Long story short, I am able to drill down specific ads on the page using the query strings produced for the display of other content.

The table is already populated with all the ads and scripts that I wish to display. I now just need to let the module know what ads to display on any given query string parameter. The Parameters it is set to listen for are 'CategoryID' , 'ArticleID'  and "PageID'. The CategoryID will be the only parameter in the querystring when viewing the page using the category system, so having the WHERE CategoryID = '[Query:CategoryID]' works perfectly. The ArticleID is only displayed alone when viewing a single page article (or the first page of a multi page article) otherwise it is listed in the URL with the PageID as well on multi page articles beginning with page two. Example is /ArticleID/8/articlename.aspx (for page 1 of 1 or page one of *) when you move to a second or any other page of that article the URL now reads ArticleID/8/PageID/4/articlename.aspx  where the pageid is the primary key/unique id of the article page so it's numerical value has no relation to an actual page number.
So my SPEC is to display different ads (within the module) depending on what category, article and article page you are viewing. Well using the statement I listed above it works well until we get to a second or any other page then the first page of an article as the select will return both the ad listed for the PageID as well as the ArticleID. What I want to happen is have it use an IF/Then statement so, lets say, IF ArticleID = '[query:ArticleID]' then display the ad for that id but if ArticleID = '[query:ArticleID]' and PageID = '[query:PageID]' then only display result from PageID and ignore the ArticleID. the CategoryID once again will always be the only parameter in the query because once you click an available article in a category it goes to the ArticleID and drops the CategoryID altogether from the querystring. Let me know if I am not making sense and I will do my best to explain it in further detail. I am sorry for being so brief in the initial question.
Kind Regards,
0
 
AJJRAuthor Commented:
Sorry Meghan, I didn't see your post before I posted mine. I will try this. However the module I am using normally doesn''t like it when I use queries like this, so I may need to put it into a stored procedure and just call the procedure from the module instead....I'll comment back with the results.
Kind Regards,
0
 
AJJRAuthor Commented:
OK, I tried them it. Problem is that the module doesn't like the statement, but upon a closer look at it, I need somethign slightly different. Once you read the post I have above I am sure you will see why. Thanks a million for all the input.
0
 
MeghanOBrienCommented:
Only pass the last parameter to SQL from your querystring.  Pass the text of the parameter (description) and also the value of the parameter.
So, for categoryid pass categoryid and the numeric value
for Article, pass that and for page, pass that.  When you have an article and page, only pass page since that's all that counts.  Then, build dynamic SQL (I convert the value so that it can concatenate with the dynamic sql string).
declare @SQL
 
set @SQL = 'Select LeftColumnAd from Ad_Banners where '+@PassedParameter+'ID = '+convert(varchar(10),@ParameterValue)
exec (@sql)

Open in new window

0
 
AJJRAuthor Commented:
HI Meghan,
I am not that good at SQL so I am not sure i am understainding what to do. I hate to ask, but could you explain this in laymens terms for me please?
0
 
MeghanOBrienCommented:
The SQL statement is just a string.  If you knkow anything about string concatenation in any language you can accomplish your goal.  You just want to dynamically create the SQL string that you are going to execute.  The last example I gave was how to build your SQL string in actual SQL.  You can do the same in any code language.  Just make a variable that is your sql string and then set it.  Set it based on what you are trying to get back.  
Are you executing the SQL directly in your code or are you using a stored procedure?
The example I gave above would be sometihng you would have within a stored procedure.  Also I forgot to add the datatype to my declaration.  It would be declare @SQL varchar(8000).
If you are executing the SQL directly in your code, dynamically build your query string and then execute it.  Declare a variable, set it with your query string (that includes whatever parameter you are using) and then execute.
0
 
AJJRAuthor Commented:
I am using a DotNetNuke module that runs the statement for  me. I attached the manual for it for reference. Never the less, I can only use a Select or Exec to accomplish the query using this module. I will play around with the code you provided in a procedure and then call it through the module and see if I can make something of it.
Kind Regards,
SGSV-UserGuide-V5-4.pdf
0
 
MeghanOBrienCommented:
Page 39 says DotNetNuke supports stored procedures, so you can create one and then call it.

I show how to call it after "GO"

create proc prcReturnInfo 
@ArticleID int
,@PageID int
, @CategoryID int
as
 
if @ArticleID is not null
      begin
            SELECT LeftColumnAd 
            FROM Ad_Banners 
            WHERE articleID = '[Query:articleID]' 
      end
else
if @PageID is not null
      begin
            SELECT LeftColumnAd 
            FROM Ad_Banners 
            WHERE Page = '[Query:PageID]' 
      end
else
if @CategoryID is not null
      begin
            SELECT LeftColumnAd 
            FROM Ad_Banners 
            WHERE CategoryID = '[Query:CategoryID]'
      end
      
     go
      
 exec prcReturnInfo '[Query:articleID]','[Query:PageID]','[Query:CategoryID]'

Open in new window

0
 
AJJRAuthor Commented:
Looks like ti will work. I am just getting an error where it states 'ExecuteSQL: Error converting data type varchar to int'
0
 
MeghanOBrienCommented:
Change the declaration

@ArticleID varchar(50)
,@PageID varchar(50)
, @CategoryID varchar(50)
as
0
 
AJJRAuthor Commented:
OK, I just changed the procedure to use 'varchar (50)' in the declarations and it isn't returning any results. I probably went about it wrong.
0
 
MeghanOBrienCommented:
It might have to do with IS NOT NULL.  I put that because I guessed that you would be passing in NULL if it wasn't there.  But you may be passing in something different.

You can try instead of IS NOT NULL saying

If @ArticleID <> '' and so on
If @PageID <>''

It depends what you pass in when it's blank.
0
 
AJJRAuthor Commented:
The fields are just blank in the databse where there isn't a selection made for that column/row. So if categoryID and articleID arent being used there is just a blank and PageID will have a number corresponding to the pageID.
I am looking this this statement and I am curious, if the articleID stays present when a pageID is also present, will it dosplay the results of both? If not, then we are good. If so, do I just change the otder to have pageID first in the IF/ELSE statements?
0
 
MeghanOBrienCommented:
Right, order counts, as soon as something is not NULL (or blank) it will use that select statement and not use the others.

how about using this instead of null and it will cover both bases.

if isnull(@ArticleID,'') <> ''
if isnull(@PageID,'') <>''
etc
0
 
AJJRAuthor Commented:
No errors, but it's just not returning anything. Sadly I am not skilled enought to figure out why.
0
 
MeghanOBrienCommented:
My mistake, I see why.
if isnull(@ArticleID,'')<>''
      begin
            SELECT LeftColumnAd 
            FROM Ad_Banners 
            WHERE articleID = @ArticleID
      end
else
if isnull(@PageID,'')<>''
      begin
            SELECT LeftColumnAd
            FROM Ad_Banners 
            WHERE Page = @PageID
      end
else
if isnull(@CategoryID,'') <>''
      begin
            SELECT LeftColumnAd 
            FROM Ad_Banners 
            WHERE CategoryID = @CategoryID

Open in new window

0
 
AJJRAuthor Commented:
Here is the table and dtat for review, incase it helps.
Ad-BannersData.jpg
Ad-BannersTable.jpg
0
 
AJJRAuthor Commented:
Meghan, you are very close. It returns data for ArticleID but ignores the pageID's. So on the article first page it dosplays the banners I have set. But on the second page, it stays the same banners because the articleID is still there.
0
 
AJJRAuthor Commented:
Here is the table updated from how it was. Still same result however.
Ad-BannerDataUpdated.jpg
0
 
MeghanOBrienCommented:
Just change the order of your IF, put page first because if page exists, you don't care about article.
if isnull(@PageID,'')<>''
      begin
            SELECT LeftColumnAd
            FROM Ad_Banners 
            WHERE Page = @PageID
      end
else
if isnull(@ArticleID,'')<>''
      begin
            SELECT LeftColumnAd 
            FROM Ad_Banners 
            WHERE articleID = @ArticleID
      end
else
if isnull(@CategoryID,'') <>''
      begin
            SELECT LeftColumnAd 
            FROM Ad_Banners 
            WHERE CategoryID = @CategoryID

Open in new window

0
 
AJJRAuthor Commented:
OK Now it displays banners on the second page but not the first where articleID is all that is present.
0
 
AJJRAuthor Commented:
I could be totally wrong here, but should it be is not null now. So if the pageID is not null then run that select and if it is null move to else?
0
 
MeghanOBrienCommented:
the statement of isnull(@PageID,'')<>'' covers both it being null or being an empty string.

maybe remove your IF's all together and just do a select on each one if it's filled in.  I don't know?


            SELECT LeftColumnAd
            FROM Ad_Banners 
            WHERE Page = @PageID
union all
            SELECT LeftColumnAd 
            FROM Ad_Banners 
            WHERE articleID = @ArticleID
union all
            SELECT LeftColumnAd 
            FROM Ad_Banners 
            WHERE CategoryID = @CategoryID

Open in new window

0
 
AJJRAuthor Commented:
OK I used...
@ArticleID varchar (50)
,@PageID varchar (50)
, @CategoryID varchar (50)
as
if @PageID IS NOT NULL
      begin
            SELECT LeftColumnAd
            FROM Ad_Banners
            WHERE Page = @PageID
      end
else
if @ArticleID IS NOT NULL
      begin
            SELECT LeftColumnAd
            FROM Ad_Banners
            WHERE articleID = @ArticleID
      end
else
if @CategoryID IS NOT NULL
      begin
            SELECT LeftColumnAd
            FROM Ad_Banners
            WHERE CategoryID = @CategoryID
      end
GO


and it will display the second page ads, just not the first. Is it because I don't have 'NULL' in these rows?
0
 
AJJRAuthor Commented:
Sorry was typing before I got the response. I will try that.
0
 
AJJRAuthor Commented:
OK the union displays the correct ads on the fist page, then displays them plus the second page ads on the second page.
0
 
MeghanOBrienCommented:
I am just guessing at this point
if isnull(@PageID,'')<>'' and isnull(@ArticleID,'')<>''
      begin
            SELECT LeftColumnAd
            FROM Ad_Banners 
            WHERE Page = @PageID and ArticleID =@ArticleID
      end
else
if isnull(@PageID,'')<>''
      begin
            SELECT LeftColumnAd
            FROM Ad_Banners 
            WHERE Page = @PageID
      end
if isnull(@ArticleID,'')<>''
      begin
            SELECT LeftColumnAd 
            FROM Ad_Banners 
            WHERE articleID = @ArticleID
      end
else
if isnull(@CategoryID,'') <>''
      begin
            SELECT LeftColumnAd 
            FROM Ad_Banners 
            WHERE CategoryID = @CategoryID

Open in new window

0
 
AJJRAuthor Commented:
HI Meghan,
Thank you so much for trying. The last query is dead, but you were very close with this one:
@ArticleID varchar (50)
,@PageID varchar (50)
, @CategoryID varchar (50)
as
if isnull(@PageID,'')<>''
      begin
            SELECT LeftColumnAd
            FROM Ad_Banners
            WHERE Page = @PageID
      end
else
if isnull(@ArticleID,'')<>''
      begin
            SELECT LeftColumnAd
            FROM Ad_Banners
            WHERE articleID = @ArticleID
      end
else
if isnull(@CategoryID,'') <>''
      begin
            SELECT LeftColumnAd
            FROM Ad_Banners
            WHERE CategoryID = @CategoryID
      end
GO

That said, I will keep playing around with it to see if I have any luck. If you do happen to have a moment of clarity and feel like sharing, I'll be here.
Kindest Regards
0
 
SharathData EngineerCommented:
could you provide sample set from your table and the expected result? I just want a clear idea before commenting.
0
 
irfanazamCommented:
Call this query from a stored procedure. this stored procedure must have 3 parameters and for each NULL should be the default value.

Now from your application. pass null to the stored procedure if you dont want to search using respective columns. then use following query:


SELECT LeftColumnAd
FROM Ad_Banners
WHERE articleID = coalesce(@articleID,articleID)
OR Page = coalesce(@Page,Page)
OR CategoryID = coalesce(@CategoryID, CategoryID)

0
 
AJJRAuthor Commented:
@ Sharath_123: the attached jpg images above should give you what you need.
@irfanazam: The coalesce gets the banners on the front page, but then shows the first page banners in addition to the second + page banners. So it's close, but not quite there yet.
0
 
SharathData EngineerCommented:
your attached jpgs are giving the result you got by executing the sql provided by some experts. I need exact data in your table and the expected result.
0
 
AJJRAuthor Commented:
Sharath, the attached jpegs are screen shots of the table data and structure NOT the result of any query. The result is on a web page as stated in the question and follow-up's.  
0
 
AJJRAuthor Commented:
Found the solution on my own.
USE [XXX]
GO
/****** Object:  StoredProcedure [XXX].[prcReturnLeftColumnAd]    Script Date: 04/26/2009 22:59:57 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER proc [XXX].[prcReturnLeftColumnAd]
@ArticleID varchar (50)
,@PageID varchar (50)
, @CategoryID varchar (50)
as
if isnull(@PageID,'')<>'' or ltrim(rtrim(@pageId))<>''
      begin
            SELECT LeftColumnAd
            FROM Ad_Banners
            WHERE ltrim(rtrim(Page)) = ltrim(rtrim(@PageID))
      end
else
      if isnull(@ArticleID,'')<>'' or ltrim(rtrim(@ArticleID))<>''
            begin
                SELECT LeftColumnAd
                FROM Ad_Banners
                WHERE ltrim(rtrim(articleID)) = ltrim(rtrim(@ArticleID))
            end
      else
      if isnull(@CategoryID,'') <>'' or ltrim(rtrim(@CategoryID))<>''
            begin
                SELECT LeftColumnAd
                FROM Ad_Banners
                WHERE ltrim(rtrim(CategoryID)) = ltrim(rtrim(@CategoryID ))
            end
0

Featured Post

Nothing ever in the clear!

This technical paper will help you implement VMware’s VM encryption as well as implement Veeam encryption which together will achieve the nothing ever in the clear goal. If a bad guy steals VMs, backups or traffic they get nothing.

  • 21
  • 11
  • 2
  • +3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now